产品中心
项目开发中，性能是我们比较关注的问题，特别是数据库的性能；作为一个开发，经常和SQL语句打交道，想要写出合格的SQL语句，我们需要了解SQL语句在数据库中是如何扫描表、如何使用索引的；
MySQL提供explain/desc命令输出执行计划，我们通过执行计划优化SQL语句。
下面我们以MySQL5.7为例了解一下执行计划：
注：文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划，有不合理之处勿喷
只需要在我们的查询语句前加explain/desc即可
准备数据表
执行计划输出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，这些内容有什么意义，下面简单介绍一下
1 id相同，从上往下一次执行；
通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；rightjoin 先扫描b表，再扫描a表
2 id不同，id越大优先级越高，越先被执行
我们编写查询角色为开发的用户；可以知道先查询角色name为开发角色id，查询序列号为2；再根据角色id查询用户，查询序列号为1；
(1)SIMPLE(简单SELECT,不使用UNION或子查询等)
(2)PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3)UNION(UNION中的第二个或后面的SELECT语句)
(4)DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5)UNION RESULT(UNION的结果)
(6)SUBQUERY(子查询中的第一个SELECT)
(7)DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
(8)DERIVED(派生/衍生表的SELECT, FROM子句的子查询)
(9) MATERIALIZED(物化子查询) 在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。
(10)UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
(11)UNCACHEABLE UNION(UNION查询的结果不能被缓存)
table分别user、role表
创建分区表，
通过type字段, 我们判断此次查询是全表扫描还是索引扫描等，下面简单介绍一下常用的type；
（1）system: 表中只有一条数据，相当于系统表； 这个类型是特殊的const类型；
（2）const:主键或者唯一索引的常量查询，表格最多只有1行记录符合查询，通常const使用到主键或者唯一索引进行定值查询。
主键
将主键设置为id和role_id
唯一索引
普通索引
const用于主键或唯一索引查询；将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时使用；与索引类型有关。
（3）eq_ref: 除了system和const类型之外,效率最高的连接类型；唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；常用于主键或唯一索引扫描
准备数据
根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：
先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；
再查询tc即teacher_card表使用主键和之前的t.tc_id关联；由于是关联查询，并且是通过唯一索引（主键）进行查询，仅能返回1或0条记录，所以type为eq_ref。
分析结果，将teacher_card的id设置为唯一索引，type为eq_ref；满足仅能返回1或0条记录。
分析结果，将teacher_card的id设置为普通索引，type为ref；不满足仅能返回1或0条记录。
equ_ref用于唯一索引查询，对每个索引键，表中只有一条或零条记录与之匹配。
（4）ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询（换句话说，连接不能基于键值选择单行,可能是多行）。
先查询tc表就是teacher_card表中remark字段为aa的记录，由于remark字段没有索引，所以全表扫描（type:ALL），一共有2条记录，扫描了2行（rows:2）,1条符合条件（filtered:50,1/2）；
tc_id无索引 再查询t即teacher表使用tc_id和之前的tc.id关联；由于是关联查询，不是索引,全表扫描，所以type为ALL。
tc_id有索引再查询t即teacher表使用tc_id和之前的tc.id关联；由于是关联查询，索引扫描，能返回0或1或多条记录，所以type为ref。
（5）range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。
（6）index: 扫描索引树
如果索引是复合索引，并且复合索引列满足select所需的所有数据，则仅扫描索引树。在这种情况下，Extra为Using index。仅索引扫描通常比ALL索引的大小通常小于表数据更快。
索引列不满足select所需的所有数据，此时需要回表扫描；按索引顺序查找数据行。Uses index没有出现在Extra列中。
查询tc_id，扫描索引树，type为index，Extra为Using index；
按tc_id分组，全表扫描，以按索引顺序查找数据行。
（7）ALL: 全表扫描,没有任何索引可以使用时。这是最差的情况，应该避免。
由于name字段不存在索引，type:ALL全表扫描；可通过对name字段设置索引进行优化。
查询1，查询name为Java或t_id为1的记录；可能用到的索引possible_keys为index_name，index_tid；实际用到的索引key为NULL
查询2，查询name为Java；可能用到的索引possible_keys为index_name；实际用到的索引key为index_name
字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段需要额外的2个字节，固定长度字段不需要额外的字节。而null都需要1个字节的额外空间，所以以前有个说法：索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。
按照主键id查询possible_keys为primary，实际用到的索引key为primary，key_len为4；
按照索引role_id查询possible_keys为index_role，实际用到的索引key为index_role，key_len为5；
分析结果：按照role_id比按照id（均为int类型）的key_len大5-4=1，因为role_id可以为null，需要一个标志位；
按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为32=10*3+2(变长)；
按照alias_name查询possible_keys为index_alias，实际用到的索引key为index_alias，key_len为33=10*3+2(变长)+1(null标志位)；
分析结果：name与remark均为变长且字符集一致，remark可以为null，33-32=1多占一个标志位；
按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为32=10*3(utf8一个字符3个字节)+2(变长)；
按照remark查询possible_keys为index_remark，实际用到的索引key为index_remark，key_len为22=10*2(gbk一个字符2个字节)+2(变长)；
分析结果：name与remark均为变长但字符集不一致，分别为utf8与gbk；符合公式；
按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为30；
因为将name修改为char(10) 定长 character set utf8 not null，10*3=30；符合公式
通过执行计划可知，role表执行计划ref为study.user.role_id；说明role.id关联user.role_id；
根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：
先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；
再查询tc即teacher_card表使用主键和之前的t.tc_id关联；扫描索引（type:ref），返回1条记录，最终返回1条记录，（filtered:100 1/1）。
use filesort：MySQL会对数据使用非索引列进行排序，而不是按照索引顺序进行读取；若出现改值，应优化索引
use temporary：使用临时表保存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by；若出现改值，应优化索引
use index：表示select操作使用了索引覆盖，避免回表访问数据行，效率不错
use where：where子句用于限制哪一行
分析结果：
当设置first_name为普通索引(单列索引)，按照first_name查询；type:ref、possible_keys:indes_first、key:indes_first、extra:null，用到索引；
当设置first_name，last_name为复合索引(联合索引)，按照first_name查询；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因为是复合索引不需要回表扫描，extra:Using index索引覆盖；注意此时key_len为33=10*3(utf8)+2(变长)+1(null标志位)，用到了复合索引的一部分即first_name
当设置first_name，last_name为复合索引(联合索引)，按照last_name查询；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，扫描索引树，复合索引的最左原则；此时key_len为66=10*3(utf8)+2(变长)+1(null)+10*3(utf8)+2(变长)+1(null标志位)；Using where应where子句进行限制
根据MySQL执行计划的输出，分析索引使用情况、扫描的行数可以预估查询效率；进而可以重构SQL语句、调整索引，提升查询效率。
本文只是简单介绍一下MySQL执行计划，想全面深入了解MySQL,可优先阅读MySQL官方手册。
到此这篇关于MySQL执行计划的文章就介绍到这了,更多相关MySQL执行计划内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
