MySQL-执行计划

简述

一条MySQL的语句，在到优化器的时候，会生成执行计划，我们可以通过执行计划显示的东西来对我们的SQL进行优化。

EXPLAIN

通过EXPLAIN命令可以查看sql的执行计划。

各列解析

我们只看最常关注的列。

type

type显示该sql对存储引擎的访问方式

1. system：表中只有一条记录，并且使用的存储引擎对数据的统计是精准的（InnoDB不精准，MyIsam或者Memory可以）。
2. const：单表查询，条件是主键或者非NULL唯一索引的等值判断就是const（常数级别）。
3. eq_ref：连接查询中，被驱动表可能出现，出现条件与单表查询的const一样。
4. ref：单表查询，条件是唯一索引为NULL或者非唯一索引的等值判断就是ref（单点扫描区间）。
5. ref_or_null：与ref一样，条件加上OR 列 = NULL。
6. index_merge：之前有说过，在一些情况下（使用二级索引做条件，主键有序）MySQL用到多个索引可以做Intersection、union、sort-union，就会出现这个。
7. range：走索引，进行范围扫描。
8. index：我们要的列在二级索引树上已经存在，只要通过遍历二级索引B+树的叶子结点就可以。
9. all：全表扫描。

possible_keys

可能用到的key，在优化器生成执行计划之前，优化器会生成多个方案（走哪个索引，全表扫描等），并计算成本（IO成本和CPU成本），这个字段就是记录优化器生成方案的索引，possible_keys越多，那么生成的方案就越多，对性能的消耗就越多，所以该列越少越好。

key

实际用到的索引。

key_len

用到索引的长度，该列可以用来判断我们用了联合索引的多少个列，比如我们有联合索引a，b，c，a和b是varchar（100）并可以为null，c是int不为null，表的字符集为utf-8（一个字符1-3个字节）。

• key_len为303：只用到了a
• key_len为606：只用到了a，b
• key_len为610：只用到了a，b，c
  为什么走a是303，多了3，MySQL规定可以为null就多1，可变长就多2，所以是303。

extra

1. No tables used：查询没有用到表，如 SELECT 1
2. Impossible WHERE：查询中包含不可能成立的条件，如 1 != 1
3. Using Index：查询的列全部在二级索引上存在，不需要回表（覆盖索引）。
4. Using index condition：不需要server进行判断，通过二级索引就可以判断。
5. Using where：要在server执行的判断。
6. using join buffer：使用了join buffer来加快连接查询。
7. using temporary：使用了派生表。
8. using filesort：不能用索引的B+树进行排序时，ORDER BY会用到内存和磁盘来完成排序。

Using index condition和Using where

假如我们有索引列col1，普通列col2。我们执行以下sql：

SELECT * FROM TABLE WHERE col1 > 'c' AND col1 LIKE '%bc';

第一种方案：

• server（执行器）生成扫描区间（‘c’，正无穷）,发送col1 > 'c’给存储引擎，存储引擎找到第一条满足col1 > 'c’的记录，并之后往后检索，每一次记录满足col1 > 'c’的条件，就回表然后把完整的记录返回给server。
• server收到返回的记录就判断col1 LIKE '%bc’是否成立，成立就返回给客户端，不成立就丢弃。

实际上由于col1 LIKE '%bc’在我们回表之前就可以进行判断了，因为我们有col1的值，所以实际方案可以如下：（索引下推）

• server（执行器）生成扫描区间（‘c’，正无穷）,发送col1 > 'c’和col1 LIKE '%bc’给存储引擎，存储引擎找到第一条满足col1 > 'c’的记录，并之后往后检索，每一次记录满足col1 > 'c’的条件，就继续判断是否满足条件col1 LIKE ‘%bc’，如果是就回表，不是就丢弃，然后把完整的记录返回给server。
• server拿到判断其他条件（可能没有在索引的条件，本列子中没有了），然后返回给客户端。

本列子中第一种方案就是Using where，第二种就是Using index condition。

JSON格式的执行计划

EXPLAIN FORMAT=JSON可以生成json格式的执行计划，其中包含有成本的信息。

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "student",
      "access_type": "system",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.20",
        "prefix_cost": "0.00",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "id",
        "student_no",
        "student_name",
        "subject_no",
        "subject_name",
        "score"
      ]
    }
  }
}

SHOW WARNINGS

我们都知道优化器会优化我们的sql语句，然后查看优化后的方案呢？在执行一次EXPLAIN之后，立刻执行SHOW WARNINGS就可以看到。


message记录的就是优化之后的信息（该语句不一定可以直接执行）。