Mysql数据库索引优化1—explain命令

平凡也就两个字: 懒和惰;
成功也就两个字: 苦和勤;
优秀也就两个字: 你和我。
跟着我从0学习JAVA、spring全家桶和linux运维等知识，带你从懵懂少年走向人生巅峰，迎娶白富美！
关注微信公众号【 IT特靠谱 】，每天都会分享技术心得~

 

Mysql数据库索引优化1—explain命令

1 数据准备

      创建数据库表：user_t，并插入图中4条测试数据（Tips: 若需要学习mysql相关操作，请关注微信公众号"IT特靠谱"后给你教程）。

 

2 explain命令

2.1 explain命令简介

       在工作中有时会遇到个别慢查询的sql，严重时甚至会导致锁表或者mysql服务宕机。为了分析出sql响应慢的原因，我们可以通过mysql的explain这个命令来查看sql语句的执行计划，查看该SQL语句有没有使用索引，有没有做全表扫描等，从而对sql语句或数据库表进行优化。

      下面的示例图分别为有无explain命令的sql语句执行情况。

      普通查询数据sql命令：

      explain命令查看sql执行计划：

       从上图可以看到，explain出来的执行计划信息有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered和Extra。下面对这12列数据进行详解。

 

2.2 explain信息详解

（1） id：选择标识符。

      SELECT识别符。这是SELECT的查询序列号。我的理解是SQL执行的顺序的标识。

      1）id相同时，执行顺序由上至下；

      2）如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；

      3）id如果相同，可以认为是一组，相同组内从上往下顺序执行；不同组中id值越大，优先级越高，越先执行。

（2）select_type:表示查询的类型。

      1）SIMPLE(简单SELECT，不使用UNION或子查询等)；

      2）PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)；

      3） UNION(UNION中的第二个或后面的SELECT语句)；

      4） DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)；

      5）UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)；

      6）SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)；

      7）DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)；

      8）DERIVED(派生表的SELECT, FROM子句的子查询)

      9）UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

（3）table:输出结果集的表

      显示执行这一步查询时所访问数据库中表名称，有时不是真实的表名字，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。如果是尖括号括起来的<subquery N>，这个表示子查询结果被物化，之后子查询结果可以被复用

（4）partitions:匹配的分区（数据所在表的分区）

      该列显示的为分区表数据被命中的分区情况。非分区表该字段为空（null）。

（5）type:表示表的连接类型

      表示在表中找到所需行数据的方式，又称“访问类型”。

      查询速度依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引！
      1）system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index。
      2）const：使用唯一索引或者主键，返回记录一定是1行记录。其他数据库也叫做唯一索引扫描。
      3）eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。
      4）ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
      5）fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
      6）ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
      7）index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range。
      8）unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。
      9）index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
      10）range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
      11）index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

      在工作中，我们尽量优化索引使得sql查询数据时访问类型（type）在range以上，最差也得时range！！

（6）possible_keys:表示查询时，可能使用的索引

      查询（select）可能使用到的索引都会在这里列出来。查询条件（where）涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）。
      如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果没有走索引，那么我们就需要建一个索引来提高查询效率！

（7）key:表示实际使用的索引

      查询真正使用到的索引，必然包含在possible_keys中。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

（8）key_len:索引字段的长度

      表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。在不损失精确性的情况下，长度越短越好 ！

（9）ref:列与索引的比较

      如果是使用的常数等值查询，这里会显示const；如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

（10）rows:扫描出的行数(估算的行数)

      这里是执行计划中估算的扫描行数，不是精确值。表示MySQL根据表统计信息及索引选用情况，估算的查询到我们想要的记录所需要扫描的行数。

（11）filtered:按表条件过滤的行百分比

      表示返回结果的行数占需扫描读取行数的百分比(注意是百分比，不是具体记录数)，Filtered列的值越大越好，Filtered列的值依赖于统计信息。尽量优化索引提高Filtered百分比值！

（12）Extra:执行情况的描述和说明

      对于extra列，官网上有这样一段话：

If you want to make your queries as fast as possible, look out for Extra 
column values of Using filesort and Using temporary, or, in JSON-formatted 
EXPLAINoutput, for using_filesort and using_temporary_table properties equal
to true.

      大概的意思就是说，如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

      这个列可以显示的信息非常多，有几十种，常用的有：
      1）distinct：表示在select部分使用了distinc关键字。
      2）no tables used：不带from字句的查询或者From dual查询。
      3）using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。
      4）using index：表示直接通过索引就可以获取查询的数据。
      5）using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
      6）using sort_union，using_union，using intersect，using sort_intersection：
　　using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集。
　　using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集。
　　using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
      7）using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
      8）using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition。
      9）Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个。

 

3 总结

      1） EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
      2）EXPLAIN不考虑各种Cache；
      3）EXPLAIN不能显示MySQL在执行查询时所作的优化工作；
      4）部分统计信息是估算的，并非精确值；
      5）EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

 

    下一章将详解不走mysql索引查询的几种情况，以及sql案例，动动你的小手指，关注微信公众号支持一下博主吧~