你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

时间:2021-03-29 14:31:13

前言

过年回来的第二周了,终于有时间继续总结知识了。这次来看一下SQL调优的知识,这类问题基本上面试的时候都会被问到,无论你的岗位是后端,运维,测试等等。

像本文标题中的两个问题,就是我在实际面试过程中遇到的,所以这次就主要围绕着这两个问题来总结一下。

本文的重点在第二部分,请耐心看完!

explain 查询SQL执行计划

我们在想知道一条SQL的执行计划时,是可以通过Explain关键字来模拟优化器执行SQL查询语句,进而来分析SQL的语句。

举例:

创建如下数据表

CREATE TABLE `test_score` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
`subject` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '科目',
`score` int(10) DEFAULT NULL,
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_score` (`name`,`score`) USING BTREE COMMENT '学生名与成绩的联合索引',
KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '创建时间的索引'
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COMMENT='学生科目成绩表';

在上面的学生科目成绩表中,我创建了两个索引,一个是name和score的联合索引,一个是create_time的索引。

然后向test_score表中插入10w条数据,通过存储过程的方式添加:

DROP PROCEDURE  IF EXISTS  insert_test_score;
CREATE DEFINER = `root` @`%` PROCEDURE `insert_test_score` ( )
BEGIN
DECLARE
c_id INTEGER DEFAULT 1;
WHILE
c_id <= 100000 DO
INSERT INTO test_score
VALUES
( c_id, concat( '李明明', c_id ),
convertSubject(RAND()*10),
RAND()*100,
date_sub( NOW( ), INTERVAL c_id SECOND ) ); SET c_id = c_id + 1; END WHILE;
END

自定义函数:convertSubject内容如下:

-- 自定义函数
delimiter $$
DROP FUNCTION IF EXISTS convertSubject $$
create function `convertSubject`(xis int) returns VARCHAR(50)
begin
CASE xis
WHEN 1 THEN RETURN '数学';
WHEN 2 THEN RETURN '语文';
WHEN 3 THEN RETURN "英语";
WHEN 4 THEN RETURN "物理";
WHEN 5 THEN RETURN "化学";
WHEN 6 THEN RETURN "生物";
WHEN 7 THEN RETURN "政治";
WHEN 8 THEN RETURN "历史";
WHEN 9 THEN RETURN "地理";
ELSE RETURN "体育";
END CASE ;
end
$$
delimiter ;

执行存储过程插入数据:

call insert_test_score();

数据插入成功后,我们查看一个查询SQL语句的执行计划:

EXPLAIN select * from test_score where name = '李明明5';

运行结果:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

这里解释一下,Explain执行计划结果每个字段的含义:

id

id代表的主要是SQL语句的执行顺序,主要分两种情况:

  1. id相同

如果只有一条SQL的单表查询(不含中间表)的情况,那么id就是1,如:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

如果存在中间表查询的SQL,这时候会有多条执行结果,但是id值相同,这个时候代表的执行顺序是由上到下的顺序。

EXPLAIN select t2.name,t1.score
from test_score t1 join subject_score t2 on t1.id = t2.id where t2.id = 1;

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

  1. id不相同

    当id值不同时,值越大优先级越高,也就是说,值越大的越先执行。
EXPLAIN select `name`,score from test_score t1 where id = (SELECT id from test_score t2 where t2.id =1);

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

select_type

select_type代表的是查询类型,主要是区别于简单查询、联合查询、子查询等查询类型。

主要有以下几种类型:

  1. simple:简单查询,指SQL中不包含子查询或关联查询。
  2. primary:若SQL中包含子查询,那么最外层的查询将被标记为primary。
  3. subquery:执行的SQL中,在select或where中包含了子查询,那么子查询将被标记为subquery。
  4. derived:在from列表中包含的子查询会被标记为derived,MySQL会先把这些derived的查询结果放到临时表中,然后再为下一步的查询使用。
  5. union:若第二个select出现在union之后,则会被标记为union,如果union包含在form子句的子查询中,外层select被标记为derived。
  6. union result:union 的结果。

table

数据输入行所引用的表名(若表名有别名,则展示别名)。

Type

主要是显示连接类型,有如下几种:

  1. system:表中仅有一行数据(一般系统表才展示此类型),这是const联结类型的一个特例。
  2. const:通过索引一次就找到,const用于比较primary

    key或者unique索引。因为只匹配一行数据,所以如果将主键置于where列表中,mysql能将该查询转换为一个常量。
  3. eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。
  4. ref:非唯一索引扫描,返回匹配某个单独值的所有行,可能会返回多行,本质上也可以归为一直索引扫描,当使用二级索引时,一般都会是ref的连接类型。
  5. range:检索给定范围的行,使用一个索引来选择数据范围。key列显示使用了哪个索引,一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好,因为它开始于索引的某一个点,而结束另一个点,不用全表扫描。
  6. index:index与all区别为index类型只遍历索引树。通常比all快,因为索引文件通常比数据文件小很多。
  7. all:通过全表扫描才找到匹配的数据。

possible_keys

指MySQL能使用哪个索引在该表中找到行,一般真正使用的索引都在possible_keys展示的索引中。

key

SQL在执行的时候实际走的索引名称,如果没有走索引,那么此值为Null。

key_len

表示索引中使用的字节数,该列计算查询中使用的索引的长度在不损失精度的情况下,长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度,并非实际使用长度。

ref

ref列显示使用哪个列或常数与key一起从表中选择行,如果值为const代表的是常数。

rows

根据表统计信息以及索引选用情况,大致估算出找到所需的记录所需要读取的行数。

filtered

表示返回结果的行数占需读取行数的百分比 Filtered列的值越大越好 Filtered列的值依赖于统计信息。

Extra

指不适合在其他列中显示,但是十分重要的额外信息。

主要有如下几个值:

  1. Using filesort:说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”。
  2. Using temporary:使用了临时表保存中间结果,mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
  3. Using index:表示相应的select操作用使用覆盖索引,避免访问了表的数据行。如果同时出现using where,表名索引被用来执行索引键值的查找;如果没有同时出现using where,表示索引用来读取数据而非执行查询动作。
  4. Using where :表明使用where过滤。
  5. Using join buffer:使用了连接缓存。
  6. impossible where:where子句的值总是false,不能用来获取任何元组。
  7. select tables optimized away:在没有group by子句的情况下,基于索引优化Min、max操作或者对于MyISAM存储引擎优化count(*),不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
  8. distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

通过对Explain执行计划结果的了解,我们就可以在分析自己写的SQL时应该做哪些优化,这些操作是必须掌握的一些技能,还有就是要了解一下哪些情况下会造成索引失效,例如:对字段进行计算后查询或者是like '%***',字段类型隐式转换等等。

还有就是尽量避免回表,能用覆盖索引完成查询最好,用到文件排序时,尽量避免产生filesort等等。

MySQL在执行时是如何选择索引的?

通过上面我们对Explain执行计划的各个字段内容的了解,我们知道了,MySQL在执行的SQL的时候,最终的一个执行计划是什么样的。

但是,不知道你们有没有遇到过这种情况,自己写了一个SQL,认为它会走某一个索引,结果却是走的全表扫描,没有走任何索引。还有就是,自己写的SQL,认为应该走A索引,但是通过Explain查看后,结果是走的B索引。

这些情况,其实是MySQL的查询优化器在对你的SQL进行分析后最终用了成本最小的执行计划。这说明有的时候MySQL认为扫描全表比走索引的成本更小。

那么MySQL的查询优化器是如何对SQL进行优化的呢?怎么就选出来了一个成本最低的策略呢。多个索引的时候,应该走哪个索引呢?

我们下面来慢慢分析。

查询成本

MySQL在执行查询之前,会先对可能的方案做执行计划,然后在根据成本决定使用哪个执行计划。

这里说的成本是指:IO成本和CPU成本。

  • IO成本是指,MySQL读取数据的时候会将数据从磁盘读取到内存中,读取数据的单位是数据页,每一页为16KB,所以读取数据页的成本常数记做1(1页的成本为1)。
  • CPU成本是指,查询数据是否满足查询条件或排序条件的CPU的执行成本。默认情况下,检测记录成本常数记录为0.2(这里是指检测每一行数据的成本)。

基于执行成本,我们来看一下,全表扫描的成本,还是基于上面的我们已经创建好的test_score表为例,通过如下SQL,查询出MySQL本身为每个表维护的统计数据。

SHOW TABLE STATUS LIKE 'test_score'

输出结果:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

  • 通过这个结果我们可以看到,test_score表中总行数是99869行。我们之前不是插入了10w行吗?怎么少了100多行数据?其实这里的总行数只是MySQL的一个估算值,但是这个估算值并不影响我们计算成本,我们上面说了,单条记录的CPU成本常数是0.2,那么99869*0.2=19974左右。
  • 表中的数据总长度是6832128字节,InnoDB每一页的数据是16KB,数据总长度是417页,因此,IO成本就是417*1=417左右。

所以全表扫描的成本大概是19974+417=20391

统计成本

接下来,我们以一个SQL为例来说明一下,执行成本统计情况。

还是以上面我们已经创建好的数据表test_score为例。

EXPLAIN select * from test_score
where name > '李明明90017' and create_time < '2021-02-26 18:00:00';

上面这条SQL的执行计划结果是全表扫描:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

但是只要我们把create_tieme的参数从18点改为17点,执行计划显示就会走索引了,并且走的是create_time的索引,而不是name字段的联合索引。

EXPLAIN select * from test_score
where name > '李明明90017' and create_time < '2021-02-26 17:00:00';

执行计划:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

通过上面的例子我们可以看到,同样的查询字段,传入不同的值,有的就会走索引,有的确不走索引,并且MySQL选择的索引,也并不是根据where后面的查询字段的顺序来选择的。

产生这样结果的原因,就是MySQL基于成本来选择了最优的计划来执行了SQL。那么MySQL到底是怎么制定执行计划以及做出选择计划的依据是什么呢?

MySQL执行计划的选择过程。

在MySQL5.6及之后的版本中,我们可以通过optimizer_trace功能来查看优化器生成执行计划的整个过程。通过这个功能,我们可以了解MySQL每个计划的成本,然后来进一步对查询进行优化。

optimizer_trace功能,默认是关闭的。可通过如下代码打开后,再执行具体的SQL,然后通过information_schema.OPTIMIZER_TRACE 表查看执行计划,最后记得手动再关闭optimizer_trace功能。

SET optimizer_trace="enabled=on";
select * from test_score
where name > '李明明90017' and create_time < '2021-02-26 17:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

执行后的结果是个大JSON,内容比较多,所以我就只截取了关键部分。

我们先来看走idx_name_score索引时执行计划:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

我们看到,走idx_name_score索引,要扫描数据21474行,成本(cost)是:25770。

再来看走idx_create_time时的执行计划:

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

这里看到,走idx_create_time时,扫描数据6805行,成本(cost)是8167,比走idx_name_score索引成本要低。所以MySQL最终选择的是走idx_create_time的执行计划。

最后再来将create_time的参数改为18点,然后看一下全表扫描的执行时间。

SET optimizer_trace="enabled=on";
select * from test_score where name > '李明明90017' and create_time < '2021-02-26 18:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

我们看到全表扫描要检索数据99869行,执行成本(cost)是20391这个20391正好是我们上面在统计全表扫描时计算出来的成本。

MySQL其实是将这三个执行计划成本进行比较,然后选取一个成本最小的,然后来执行。

通过optimizer_trace功能,我们了解到了,MySQL是如何选择执行计划的,从而了解到MySQL是如何选择该使用哪个索引的。

其实,MySQL的执行计划选择,也并不是百分百准确的,有的时候,他计算出来的成本并不一定准确,所以我们可以强制让某条SQL使用指定的索引,还是拿上面的那条SQL举例:

-- 全表扫描,耗时3.545s
select * from test_score
where name > '李明明90017' and create_time < '2021-02-26 18:00:00'; -- 强制走索引,耗时3.088s
select * from test_score force index(idx_create_time)
where name > '李明明90017' and create_time < '2021-02-26 18:00:00';

好了,这次内容有点多,前面说Explain的部分有点太基础了,大部分人都会看,但是后面的optimizer_trace相关的知识还是比较有意思的。

总结

最后总结一下,我把Explain的各字段值的相关信息,做了一个表格。

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

optimizer_trace相关的知识,这个就看个人兴趣了,知道有这么个功能就可以了,用到机会不是太多。

最后的最后

用上面的例子的test_score表,来分享一道我面试中遇到的SQL题。

你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?

还是科目成绩表,根据这张表,请用一条SQL写出来,每科成绩最高的同学。返回的字段要有同学的名称,科目和成绩。