MySQL优化 - EXPLAIN

​

​EXPLAIN​

​​是我们最常用的SQL分析工具，在使用工具之前我们需要先了解下工具中每一项代表的含义，如下是​

​EXPLAIN​

​中的所有列：

mysql> explain select * from user u1 left join user u2 on u1.id=u2.id where u1.name='wyh';
+----+-------------+-------+------------+--------+------------------+------------------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys    | key              | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+------------------+------------------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref    | idx_name_address | idx_name_address | 1023    | const          |    1 |      100 | NULL  |
|  1 | SIMPLE      | u2    | NULL       | eq_ref | PRIMARY          | PRIMARY          | 8       | ssb_test.u1.id |    1 |      100 | NULL  |
+----+-------------+-------+------------+--------+------------------+------------------+---------+----------------+------+----------+-------+
2 rows in set      

重点关注​

​id​

​​，​

​type​

​​，​

​possible_keys​

​​，​

​key​

​​，​

​Extra​

​

id

​

​id​

​​表示执行顺序：​

​id​

​​越大越先执行，如果​

​id​

​相同则从上往下执行。

select_type

​

​select_type​

​表示查询类型，主要用来区别普通查询，子查询，联合查询等。

• ​

  ​SIMPLE​

  ​： 简单的 select 查询,查询中不包含子查询或者UNION；
• ​

  ​PRIMARY​

  ​： 查询中若包含任何复杂的子部分，最外层查询则被标记为；
• ​

  ​SUBQUERY​

  ​： 在SELECT或WHERE列表中包含了子查询；
• ​

  ​DERIVED​

  ​： 在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里；
• ​

  ​UNION​

  ​： 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
• ​

  ​UNION RESULT​

  ​： 从UNION表获取结果的SELECT

table

表示这一行的数据是哪张表的。

type

​

​type​

​​表示访问类型，从好到坏依次是：​

​system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL​

​

我们需要主要关注的是：​

​system>const>eq_ref>ref>range>index>ALL​

​

• ​

  ​system​

  ​：表只有一行记录（等于系统表），这是const类型的特列；
• ​

  ​const​

  ​​：表示通过索引一次就找到了；​

  ​const​

  ​用于比较主键或者唯一索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量；
• ​

  ​eq_ref​

  ​：表示只有一行数据与之匹配，通常是使用了主键索引或者唯一索引；
• ​

  ​ref​

  ​：表示使用了非唯一索引；
• ​

  ​range​

  ​：表示使用了索引来做范围查询
• ​

  ​index​

  ​：表示扫描了全部的索引，一般是用了索引来排序；
• ​

  ​ALL​

  ​：表示全表扫描；

possible_keys

​

​possible_keys​

​表示这次查询可以用到的索引有哪些。

key

​

​key​

​​表示优化器选中那个索引来执行这个查询，如果要想强制MySQL使用或忽视​

​possible_keys​

​列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

​

​key_len​

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

ref

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

rows

Extra

• ​

  ​Using filesort​

  ​：表示mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
• ​

  ​Using temporary​

  ​：表示使用了临时表，常见于排序 order by 和分组查询 group by。
• ​

  ​Not exists​

  ​：MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。
• ​

  ​Using index​

  ​：表示使用了覆盖索引。MySQL直接从索引中过滤不需要的记录并返回命中的结果。这是MySQL服务层完成的，但无需再回表查询记录。
• ​

  ​Using index condition​

  ​：这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
• ​

  ​Using where​

  ​​：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。

  注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

• ​

  ​Using join buffer​

  ​：表示使用了连接缓存。
• ​

  ​Impossible where​

  ​​：表示where子句的值总是false，不能用来获取任何元组，比如在一个​

  ​NOT NULL​

  ​列上执行​

  ​is null​

  ​的查询