Explain详解(优化查询好帮手)-Part2(possible_keys、key、key_len、ref、rows、filtered、Extra、Json格式的执行计画)

此篇为前篇的延续唷！

方便阅读再贴一次基本的explain及测试表

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.05 sec)

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引

而single_table2跟single_table一样。

possible_keys与key
possible_keys表示可能会用到的索引，key表示分别使用索引比较成本後最终决定使用的索引。

key_len
我们知道要使用索引来查询时，要先搞清楚扫描区间及对应的边界条件，但有些情况下我们希望从执行计画中就可以看出行成扫描区间的边界条件是什麽！这时key_len就可以派上用场。
这边是根据资料储存的类型(固定长度类型或是可变长度类型)所占用的长度及是否可以储存NULL值来计算这个数字，
透过这个数字可以判断出形成扫描区间的边界条件是什麽。

ref
跟type有点关系，显示与索引相等匹配的条件是什麽。

mysql> explain select * from single_table where key1 = 'key11';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key1      | idx_key1 | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

以上面的例子，与索引相等匹配的条件就是常数(const)。

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.single_table2.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

以上面的例子，与索引相等匹配的条件就是ryan_demo_db.single_table2.id。

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.single_table2.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

以上面的例子，与索引相等匹配的条件就是ryan_demo_db.single_table2.id。

rows
如果是全表资料扫描就是预估的表行数，如果是使用索引查询就是要扫描的索引纪录数。

filtered
计算驱动表扇出的策略。看下面的例子会比较明白。

mysql> explain select * from single_table where key1 > '5000' and common_field = 'a';
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL | 5073 |    10.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

以这个叙述会形成扫描区间(5000,+无限大)，边界条件是key1 > 5000
并可以看出使用的索引是idx_key1，使用该索引要扫描的纪录数是5073，而这边的filtered是10，意思就是最佳化查询工具『预测』这5073笔纪录有10%的资料符合common_field = 'a'条件。

extra
额外补充说明的资讯。这边列出一些可能会看不懂的。

• No tables used：叙述没有from子句会出现
• Impossible where：where子句内的条件永远为false
• Using index/Using index condition：前面表示扫描索引的树就可以得到需要的值并不需要回表操作，而後面则是有用索引但却没有生成合适的边界条件减少扫描数量。
• Using temporary：使用临时表(去重、排序等)

Json格式的执行计画

这个可以辅助我们看出目前的执行计画到底好不好，每个栏位的说明我直接写在下面的内容里。

mysql> explain format=json select * from single_table s1 inner join single_table2 s2 on s1.key1 = s2.key2  where s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,//整个查询只有一个select关键字，id为1
    "cost_info": {
      "query_cost": "4105.04"//查询预计成本4105.04
    },
    "nested_loop": [//使用槽状结构回圈连接演算法执行
      {
        "table": {
          "table_name": "s2",//驱动表
          "access_type": "ALL",//存取方法
          "possible_keys": [
            "uk_key2"
          ],
          "rows_examined_per_scan": 1,//查一次s2表大约要扫描的纪录数
          "rows_produced_per_join": 1,//扇出值
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.40",
            "prefix_cost": "0.65",//单次查询s2表的成本
            "data_read_per_join": "1K"//读取的资料大小
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      },
      {
        "table": {
          "table_name": "s1",//被驱动表
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 10146,
          "rows_produced_per_join": 101,
          "filtered": "1.00",
          "using_join_buffer": "hash join",//当被驱动表不能有效利用索引，mysql会分配一块join_buffer给它加快速度
          "cost_info": {
            "read_cost": "3698.55",
            "eval_cost": "40.58",
            "prefix_cost": "4105.04",
            "data_read_per_join": "153K"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`ryan_demo_db`.`s1`.`common_field` = 'a') and (cast(`ryan_demo_db`.`s1`.`key1` as double) = cast(`ryan_demo_db`.`s2`.`key2` as double)))"
        }
      }
    ]
  }
}
1 row in set, 3 warnings (0.00 sec)

ERROR: 
No query specified

Extended Explain

可以在exlain後使用show warnings显示与这个查询计划有关的扩充资讯。
Level：通常就是Note
Code：通常就是1003
Message：如code是1003，这边显示就是查询最佳化工具将叙述重新定义後的叙述！

mysql> explain select * from single_table where key1 > '5000' and common_field = 'a';
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL | 5073 |    10.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `ryan_demo_db`.`single_table`.`id` AS `id`,`ryan_demo_db`.`single_table`.`key1` AS `key1`,`ryan_demo_db`.`single_table`.`key2` AS `key2`,`ryan_demo_db`.`single_table`.`key3` AS `key3`,`ryan_demo_db`.`single_table`.`key_part1` AS `key_part1`,`ryan_demo_db`.`single_table`.`key_part2` AS `key_part2`,`ryan_demo_db`.`single_table`.`key_part3` AS `key_part3`,`ryan_demo_db`.`single_table`.`common_field` AS `common_field` from `ryan_demo_db`.`single_table` where ((`ryan_demo_db`.`single_table`.`key1` > '5000') and (`ryan_demo_db`.`single_table`.`common_field` = 'a'))
1 row in set (0.04 sec)