导读

作为一个后端程序员，数据库这个东西是绕不开的，特别是写sql的能力，如果您参加过多次面试，那么一定会从面试复盘中发现面试官总是会考察到sql优化这个东西。

我在之前的多次面试中最常遇到的一个问题的sql优化，不论是大厂还是小厂。但我之前没有详细去了解过这些东西啊，我就瞎鸡儿吹了，毕竟我也干过两三年的crud，sql还是写过不少的，也遇到过一些特别长的sql，执行时间特别长的sql，所以以前经常牺牲午睡时间给客户出报表还是有点效果的，mmp的。

我是这样说的：Sql优化，首先就是看sql的执行计划，然后按照执行计划对应的执行修改，比如该建索引建索引，然后就balabala....

其实老子之前压根就没学过怎么看执行计划，纯靠这么些年写sql的经验瞎鸡儿吹。这也是上个月开始看MySqL，才学会看，那今天就现学现卖，给您们展示两下子。

SQL执行计划（以MySQL为例）

1、如何查看sql的执行计划

在需要执行的查询SQL前添加一个关键字“EXPLAIN”

 

从上图可以看到，执行计划共有12字段，先来简便看每一列的作用：

字段解释idselect查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序select_type查询类型table访问到的表partitions匹配的分区type访问类型possible_keys有可能会使用到的索引key实际使用到的索引，如果为NULL，则没有使用索引key_len索引中使用的字节数，可通过该列计算查询中使用的索引长度ref显示索引的哪一列被使用了rows估算找到所需数据需读取的行数filtered查询的表行占表的百分比extra包含不适合在其他列展示但异常重要的信息，比如是使用索引排序还是文件排序

2、EXPLAIN中的列

（1）id

1、标识select所属的行，sql语句中有多少个select就有多少个id，并且id的顺序是按照select出现的顺序增长的

2、id越大，越先执行

3、id相同，从上往下执行

4、id为NUll的最后执行

例如：（1）id相同，从上往下依次执行

explain 
select * from student,class,class_student;

 

（2）id不同，id越大执行优先级越高

explain 
select s.id,s.name,(select 1 from class) from student s;

 

（3）id相同又不同，id越大越先执行，id相同从上往下执行

explain 
select id,name from student where id <5 union select s.id,s.
name from student s,class_student cs where cs.stu_id = s.id and cs.class_id = 4;

 

(2) select_type

显示对应行是简单还是复杂select，SIMPLE值表明没有子查询或Union，如果有子查询，那么最外层标记为Primary

（1）SUBQUERY 包含在select字段中的子查询，不在From语句中 例如：

explain 
select id,name,(select class_id from class_student) from student;

 

（2）DERIVED 包含在From中的子查询，MySQL会递归执行并将结果放在一个临时表中，成为派生表，从子查询中派生出来的。

（3）UNION 在UNION中的第二个和随后的select被标记为UNION。第一个select被标记为外查询来执行，如果UNION被From子句中的子查询包含，那么它的第一个Select会被标记为DERIVED。

explain 
     select id,name from student where id>5
     union
     select id,name from student where id>15;

 

UNION被包含在From子句中的示例：

explain
     select * from (
     select id,name from student where id>15
     union
     select id,name from student where id<4
     ) a;

 

（4）UNION RESULT 用来从UNION的匿名临时表检索结果的select被标记为UNION RESULT。上例中可以看到

（5）DEPENDENT select依赖于外层查询中发现的数据。

explain
     select s.id,s.name,(select class_id from class_student cs where cs.stu_id = s.id) from student s;

 

（6）UNCACHEABLE select中的某些特性阻止结果被缓存在一个Item_cache中。

(3) table

显示当前行的数据来自于哪一张表

（4）type

访问类型，结果值从好到坏依次是：NULL>system>const>eq_ref>ref>range>index>ALL

一般来说，保证查询至少能到达range级别，最好能达到ref。

（1）system 表中只有一行数据（系统表）

（2）const 通过索引一次就能找到的数据，比如primary key 和union key，主键在where条件中，就能将查询转换成一个常量。比如：student表中id是主键

explain
   select * from student where id = 1;

 

（3）eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

explain
   select * from student s left join (select * from class_student where stu_id > 10)cs on cs.stu_id = s.id where cs.class_id = 4;

 

（4）ref 非唯一性索引扫描，返回匹配某个单独值得所有行 比如：student表中的age是一个普通索引

explain 
select * from student where age = 20;

 

（5）range

1、只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引

2、一般就是where条件中出现“>”、“<”、“between”、“in”等条件

3、这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

例如：

explain
select * from student where id > 5;

 

(6) All 全表扫描，不使用任何条件或索引。比如：student表中score字段是没有设置索引的。

 

（5）possible_keys

可能会使用到的key

（6）key

实际使用到的key

（7）key_len

索引中使用的字节数，可通过该列计算出使用的是哪些列，长度越短越好。显示的是索引字段的最大可能长度，并非实际使用长度，根据表定义计算而来，不是通过表内检索而来。

需要注意的是：1、char字段一个字符在utf8编码下最多占3个字节，可变长字段需要额外的两个字节记录长度，外加需要存入一个null值，一个null是一个字节 2、复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

比如：student表的id是int类型，四个字节，所以key_len是4

explain
select * from student where id > 5;

 

name字段类型是varchar(20)，所以ken_len=20*3+2+1=63

EXPLAIN
  select * from student where name = '张三';

 

（8）ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值。

explain
select * from student s,class_student cs where cs.stu_id=s.id and cs.class_id = 4;

 

从第二行可知，使用了student表中的主键查询，ken_len为4 ref为test.cs.stu_id表明使用了cs表中的stu_id字段。

(9) rows

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

（10）extra

值描述Using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"Using temporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。Using index表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！  如果同时出现using where，表明索引被用来执行索引键值的查找;   如果没有同时出现using where，表明索引用来读取数据而非执行查找动作Using where使用了where条件Using join buffer使用了连接缓存impossible wherewhere子句的值总是false,不能用来获取任何元素distinct一单mysql找到了与形相联合匹配的行,就不在搜索了

注意：当在Extra列出现了Using filesort时候，就说明可以建立相应的索引进行排序优化查询了。

下面是一个完整的思维导图：

 

===============================

我是Liusy，一个喜欢健身的程序员。

获取更多干货以及最新消息，请关注公众号：上古伪神

如果对您有帮助，点个关注就是对我最大的支持！！！

结尾小惊喜：公众号回复“MySQL”，送您一个MySQL的整体思维导图。