22.mysql高级 - sql执行计划

SQL执行计划——Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

能干嘛

通过EXPLAIN分析某条SQL语句执行时的如下特征：

• 表的读取顺序（涉及到多张表时）
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用

• 每张表有多少行被优化器查询

  怎么玩

  格式为：explain <SQL语句>：
  

  表头解析

  id

  select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。根据id是否相同可以分为下列三种情况：

• 所有表项的id相同，如：
  
  则上表中的3个表项按照从上到下的顺序执行，如读表顺序为t1,t3,t2。由第一节提到的SQL解析顺序也可验证，首先from t1,t2,t3表明此次查询设计到的表，由于没有join，接着解析where时开始读表，值得注意的是并不是按照where书写的顺序，而是逆序，即先解析t1.other_column=''于是读表t1，然后t1.id=t3.id读表t3，最后t1.id=t2.id读表t2。解析顺序如下： | 123456 | from t1,t2,t3where t1.other_column=’’, t1.id=t3.id, t1.id=t2.idselect t2.* | | :—- | :—- |

• 所有表项的id不同：嵌套查询，id的序号会递增，id值越大优先级越高，越先被执行。如：
  
  对于多层嵌套的查询，执行顺序由内而外。解析顺序： | 123456789101112131415161718 | from t2where t2.id= from t1 where t1.id= from t3 where t3.other_column=’’ select t3.id select t1.idselect t2.* | | :—- | :—- |

• 
  由第12,8,4行可知查表顺序为t3,t1,t2。

• 有的表项id相同，有的则不同。id相同的表项遵循结论1，不同的则遵循结论2
  
  解析顺序： | 12345678910111213 | from ( from t3 where t3.other_column=’’ select t3.id ) s1, t2 #s1是衍生表where s1.id=t2.idselect t2.* | | :—- | :—- |

• 
  由第6,11两行可以看出读表顺序为t3,s1,t2

  select_type

  该列常出现的值如下：

• SIMPLE，表示此SQL是简单的select查询，查询中不包含子查询或者union
  
  

• PRIMARY，查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
  
  
• SUBQUERY，在select或where列表中包含的子查询
  
• DERIVED，在from子句中的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询, 把结果放在临时表里
  
• UNION，union右侧的select
  

• UNION RESULT，union的结果
  

  table

  表名，表示该表项是关于哪张表的，也可以是如形式：

• <derivedN>，表示该表是表项id为N的衍生表

• <unionM,N>，表示该表是表项id为M和N两者union之后的结果

  partition

  如果启用了表分区策略，则该字段显示可能匹配查询的记录所在的分区

  type

  type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 。

• system，表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  

• const，表示通过索引一次就找到了，const用于比较primary key或者unique key。因为只匹配一行数据，所以很快。若将主键置于where列表中，MySQL就能将该查询转换为一个常量 | 1234567 | mysql> select * from student;+——+—————-+———+| id | stuId | name |+——+—————-+———+| 1 | 123456789 | jack || 3 | NULL | tom |+——+—————-+———+ | | :—- | :—- |

• 

  • eq_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    
    对于b中的每一条数据，从a的主键索引中查找id和其相等的
    

  • ref，非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。（查找是基于有序性的能利用二分，而扫描则是线性的） | 1234567 | mysql> create table person ( -> id int(32) not null auto_increment, -> firstName varchar(30) default null, -> lastName varchar(30) default null, -> primary key(id), -> index idx_name (firstName,lastName) -> ) engine=innodb auto_increment=1 default charset=utf8; | | :—- | :—- |

  • 
    查询姓张的人：
    

  • range，根据索引的有序性检索特定范围内的行，通常出现在between、<、>、in等范围检索中
    
    
  • index，在索引中扫描，只需读取索引数据。

由于复合索引idx_name是基于（firstName，lastName）的，这种索引只能保证在整体上是按定义时的第一列（即firstName）有序的，当firstName相同时，再按lastName排序，如果不只两列则以此类推。也就是说在根据lastName查找时是无法利用二分的，只能做全索引扫描。

• all，全表扫描，需要从磁盘上读取表数据。
  

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

possible_keys

MySQL可以利用以快速检索行的索引。

key

MySQL执行时实际使用的索引。

key_len

• 表示索引中每个元素最大字节数，可通过该列计算查询中使用的索引的长度（如何计算稍后详细结束）。

  在不损失精确性的情况下，长度越短越好。

• key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
  

如何计算？首先我们要了解MySQL各数据类型所占空间：

• 数值类型
  
  
• 日期类型（datetime类型在MySQL5.6中字段长度是5个字节，在5.5中字段长度是8个字节）
  
  
• 字符串类型
  

  latin1编码的字符占1个字节，gbk编码的字符占2个字节，utf8编码的字符占3个字节。 c1 char(10)表示每行记录的c1字段固定占用10个字节；而c2 varchar(10)则不一定，如果某数据行的c2字段值只占3个字节，那么该数据行的c2字段实际占5个字节，因为该类型字段所占空间大小是可变的，所以需要额外2个字节来保存字段值的长度，并且因为varchar最大字节数为65535，因此字段值最多占65533个字节。 因此，

  • 如果事先知道某字段存储的数据都是固定个数的字符则优先使用char以节省存储空间。
  • 尽量设置not null并将默认值设为‘’或0

以字符串类型字段的索引演示key_len的计算过程（以utf8编码为例）：

• 索引字段为char类型 + not null：key_len = 字段申明字符个数 * 3（utf8编码的每个字符占3个字节） | 12345678 | mysql> create table test( -> id int(10) not null auto_increment, -> primary key(id) -> ) engine=innodb auto_increment=1 default charset=utf8;mysql> alter table test add c1 char(10) not null;mysql> create index idx_c1 on test(c1); | | :—- | :—- |

• 
  

• 索引字段为char类型 + 可以为null：key_len = 字段申明字符个数 * 3 + 1（单独用一个字节表示字段值是否为null） | 123 | mysql> alter table test add c2 char(10) default null;mysql> create index idx_c2 on test(c2); | | :—- | :—- |

• 
  

• 索引字段为varchar + not null，key_len = 字段申明字符个数 * 3 + 2（用来保存字段值所占字节数） | 123 | mysql> alter table test add c3 varchar(10) not null;mysql> create index idx_c3 on test(c3); | | :—- | :—- |

• 
  

• varchar + 可以为null，key_len = 字段申明字符个数 * 3 + 2 + 1（用来标识字段值是否为null）
  

  根据这个值，就可以判断索引使用情况，特别是在使用复合索引时判断组成该复合索引的多个字段是否都能被查询用到。 如：

前者使用了部分复合索引，而后者使用了全部，这在索引类型一节中也提到过，是由最左前缀（定义复合索引时的第一列 ）有序这一特性决定的。

ref

显示哪一列或常量被拿来与索引列进行比较以从表中检索行。

如上我们使用‘’到索引中检索行。

rows

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

Extra

包含不适合在其他列中显示但十分重要的额外信息：

• Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” | 1234567891011121314 | mysql> explain select from person order by lastName\G** 1. row * id: 1 select_type: SIMPLE table: person partitions: NULL type: indexpossible_keys: NULL key: idx_name key_len: 186 ref: NULL rows: 1 filtered: 100.00 Extra: Using index; Using filesort | | :—- | :—- |

• 使用\G代替;结尾可以使执行计划垂直显示。

• Using temporary：使用了临时表保存中间结果。MySQL在对查询结果聚合时使用临时表。常见于排序 order by 和分组查询 group by。 | 123456789101112131415161718192021222324252627282930 | mysql> insert into person(firstName,lastName) values(‘张’,’三’);mysql> insert into person(firstName,lastName) values(‘李’,’三’);mysql> insert into person(firstName,lastName) values(‘王’,’三’);mysql> insert into person(firstName,lastName) values(‘李’,’明’);mysql> select lastName,count(lastName) from person group by lastName;+—————+————————-+| lastName | count(lastName) |+—————+————————-+| 三 | 3 || 明 | 1 |+—————+————————-+mysql> explain select lastName,count(lastName) from person group by lastName\G* 1. row * id: 1 select_type: SIMPLE table: person partitions: NULL type: indexpossible_keys: idx_name key: idx_name key_len: 186 ref: NULL rows: 4 filtered: 100.00 Extra: Using index; Using temporary; Using filesort | | :—- | :—- |

• Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行（需要读磁盘），效率不错！如果同时出现Using where，表明索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

  索引覆盖：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。 如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

• Using where：查询使用到了where语句
  

• Using join buffer：使用了连接缓存
  
• Impossible where：where子句的值总是false，如 | 1 | select * from person where id=1 and id=2; | | :—- | :—- |

————————————————

原文链接：https://zhenganwen.top/posts/9982416a/