MySQL: 喂,别走,听我解释一下好吗?

网友投稿 574 2023-05-20

MySQL: 喂,别走,听我解释一下好吗?

MySQL: 喂,别走,听我解释一下好吗?

MySQL:先别跑好吗?

程序员:不跑你养我啊?

MySQL:你听我给你「解释」啊。

程序员:你先管好你自己吧。

现在的互联网应用也好,传统应用也罢,不管开发语言是什么,涉及到关系型数据库,MySQL 基本都是不二之选。

在 Java 的开发框架里, MyBatis 可以自定义 SQL,ORM/JPA 会根据对象映射,自动生成一系列相关的 SQL。至于自动生成的SQL,甚至自定义的SQL 执行效率到底高不高,不用 select *, 增加 where 条件,避免查全量数据等等,我们开发人员可能基本都是根据现有的知识储备,在大脑里过滤一遍,至于到底 SQL 是否高效执行,却从来没有去问过MySQL 一句。

而实际上,SQL语句写完,这项工作完成了还不到一半,其余还需要评估我们SQL写的质量如何,执行效率够不够高。MySQL 像一位智者一样,一直待在那里,只要你问,知无不言。重点在于,你不要急着走,要听他解释一下。

Explain

在 MySQL 中,我们一般常用 desc tableName 来查看一张表的信息,各个列的定义等、通过 Explain SQL 来了解MySQL 是如何执行当前这条SQL的 。

实际上 desc、describe、explain 都可以用来查看MySQL 是如何执行当前这条SQL的,在 MySQL 8.0.19 之后,这三者的作用可以说是等价的,explain 也可以用来查看表信息。后面我们会直接以 explain 为例,来说明具体的作用。

官方文档说的明白, explain 可以和SELECT、INSERT、UPDATE、DELETE 一起工作,显示 MySQL 优化器的语句执行计划,即用来告诉用户 MySQL 会怎样执行这条 SQL,以什么样的顺序,如果是多表的话是怎样 Join的。

输出字段官网文档截图如下:

上面返回看似不少,不过我们重点关注 type、key、rows 这三个。

我们常见应用的场景都是读多写少,而且对于 SQL 的执行的效率评估,一般也是说从已经存储的十成、百万甚至千万条数据中查询需要数据的效率。

后面以 SELECT 为例,来看看 explain 能带给我们什么帮忙和建议。

假设有如下表定义及数据:

CREATE TABLE `t3` (   `id` int NOT NULL,   `a` int DEFAULT NULL,   `b` int DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `a` (`a`) ) ENGINE=InnoDB;  delimiter ;; create procedure idata() begin    declare i int;   set i=1;   while(i<=100000) do     insert into t3 values(i,i,i);      set i=i+1;     end while;   end;; delimiter ; call idata();

执行完上述SQL,我们来试想一下,在当前十万行数据的表中如果执行一条查询SQL,那在少量数据中查找一定比全表查找要快很好。

比如我们最熟悉的通过主键查询

select a from t3 where id=100;

你会发现,explain 中 type 是 const, key 是 PRIMARY

再比如执行

select  * from t3 where b=100;

这个时候, explain 告诉我们,查询类型是ALL,全表扫描:

如果我们是想要把这个表里全部数据显示也就罢了,目前只查一条数据却执行全表扫描,explain 告诉我们扫描行可能会到9万多行,效率可想而知。

如果我们把SQL 改成这样:

select * from t3 where a=100;

此时 explain 变成了这样:

你会发现,type 变成了 ref, key 变成了a, rows 是1, 区别只在于 a 列上建立了索引,此时扫描行数变成了一行,差别太明显了。

如果我们要查找一个范围内的数据,通过主键或者包含索引的列进行查询时,

扫描的还是有限行,此时type是range,但如果还是通过 b 做为条件进行过滤,那还是全表扫描:

另外,为什么一般的SQL优化建议里都会说别用 select * ,指定具体用到的列呢?

肯定是用到什么列的数据查什么数据更节省内存,传输,不要等到查出结果再在内存里进行过滤,此外更重要的一点是,每个创建的索引,都有自己的索引树,能够在索引树上完成查询操作,就不需要再回表去查询,效率当然会更高。

比如,我们把查询换成了

select a,id from t3 where a < 100;

此时,explain 会在Extra里告诉咱们,查询的时候没有回表,用到了index

如果把查询列改成星,这个时候,就需要回表了,

咱们前面说重点关注 type, key, rows,可以再看下 Extra, type 里查询效率从优到差,有

const

表中只有一行匹配,查询一次即可满足。常用来匹配主键或者唯一索引。

eq_ref

唯一索引

ref

非唯一索引

range

通过一个索引去查询,只扫描指定范围内的行。一般是在检索列中包含 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()

index

类似于全表扫描,区别在于只扫描索引树

all

全表扫描,效率最低的。

在 MySQL 8.0.18 开始,还加入了一个 explain analyze,可以查看具体预计SQL执行耗时,比如我们通过它来查看上面的几个命令,会有如下输出,你会更直观的感觉到加了索引带来的效率提升。

是不是很明显?

所有,你听到MySQL 又在和你说了吗?

『小伙子,你慢些走。有事没事听我给你先解释解释,说道说道啊』

小结一下,通过 explain,我们能在执行前就从 MySQL的优化器拿到对于当前 SQL 的执行计划,了解执行中至少会扫描多少行,是否会使用到索引,大致会用多少时间等,这样该加索引加索引,该改SQL就改SQL,这样做到心中有数,应用的性能会更心中有数。

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Redis企业级开发与运维-初识Redis
下一篇:Redis都要老了,你还在用什么古董客户端?
相关文章