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

前言

过年回来的第二周了，终于有时间继续总结知识了。这次来看一下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执行计划
这里解释一下，Explain执行计划结果每个字段的含义：

id

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

  1. id相同

如果只有一条SQL的单表查询（不含中间表）的情况，那么id就是1，如：
SQL执行计划
如果存在中间表查询的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;

在这里插入图片描述

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

在这里插入图片描述

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'

输出结果：
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的执行计划结果是全表扫描：
18点的执行计划
但是只要我们把create_tieme的参数从18点改为17点，执行计划显示就会走索引了，并且走的是create_time的索引，而不是name字段的联合索引。

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

执行计划：
17点的执行计划
通过上面的例子我们可以看到，同样的查询字段，传入不同的值，有的就会走索引，有的确不走索引，并且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索引时执行计划：
idx_name_score执行计划成本
我们看到，走idx_name_score索引，要扫描数据21474行，成本（cost）是：25770。

再来看走idx_create_time时的执行计划：
idx_create_time执行计划成本
这里看到，走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";

全表扫描执行计划成本
我们看到全表扫描要检索数据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的各字段值的相关信息，做了一个表格。
explain字段解释表格

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

最后的最后

用上面的例子的test_score表，来分享一道我面试中遇到的SQL题。
SQL题
还是科目成绩表，根据这张表，请用一条SQL写出来，每科成绩最高的同学。返回的字段要有同学的名称，科目和成绩。

posted @ 2021-03-01 08:33  纪莫  阅读(1352)  评论(2)  编辑  收藏  举报