MySQL索引分析工具EXPLAIN

5 EXPLAIN 工具

可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

参考资料:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

语法

EXPLAIN SELECT clause

EXPLAIN输出信息说明

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select， 每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询: SIMPLE 复杂查询: PRIMARY(最外面的SELECT) DERIVED(用于FROM中的子查询) UNION(UNION语句的第一个之后的SELECT语句) UNION RESUlT(匿名临时表) SUBQUERY(简单子查询)
table	访问引用哪个表(引用某个查询，如“derived3”)
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘， 可粗略估算整个查询会检查的行数
Extra	额外信息 Using index:MySQL将会使用覆盖索引，以避免访问表 Using where:MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary:MySQL对结果排序时会使用临时表 Using filesort:对结果使用一个外部索引排序

说明: type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是:

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

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

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>in dex_subquery>range>index>ALL //最好到最差
备注:掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据， 它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用 =、 <>、>、 >=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使 用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引 或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀， 或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使 用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。 当主键放入where子句时，mysql把这个查询转为一个常量(高效)
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引(高 效)

示例

MariaDB [wuxia]> explain select * from wudang where id not in (5,10,20);
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | ALL  | PRIMARY       | NULL | NULL    | NULL |    7 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [wuxia]> explain select * from wudang where id <> 10 ;
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | range | PRIMARY       | PRIMARY | 4       | NULL |    7 | Using where |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [wuxia]> explain select * from wudang where age > (select avg(age)from emei);
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY     | wudang | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
|    2 | SUBQUERY    | emei   | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.002 sec)

MariaDB [wuxia]>

示例：创建索引和使用索引

MariaDB [wuxia]> create index idx_name on wudang(name(10));
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wuxia]> show indexes from wudang\G
*************************** 1. row ***************************
        Table: wudang
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: wudang
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 7
     Sub_part: 10
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.000 sec)

MariaDB [wuxia]> explain select * from wudang where name like 'zhang%';
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | ALL  | idx_name      | NULL | NULL    | NULL |    7 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.002 sec)

MariaDB [wuxia]> explain select * from wudang where name like 'song%';
+------+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | range | idx_name      | idx_name | 42      | NULL |    2 | Using where |
+------+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.001 sec)

MariaDB [wuxia]> select * from wudang where name like 'zhang%';
+----+--------------+------+--------+
| id | name         | age  | gender |
+----+--------------+------+--------+
| 16 | zhangsanfeng |  100 | M      |
| 19 | zhangcuishan |   30 | M      |
| 20 | zhangwuji    |   10 | M      |
+----+--------------+------+--------+
3 rows in set (0.001 sec)

MariaDB [wuxia]> select * from wudang where name like 'song%';
+----+--------------+------+--------+
| id | name         | age  | gender |
+----+--------------+------+--------+
| 18 | songqingshu  |   20 | M      |
| 17 | songyuanqiao |   50 | M      |
+----+--------------+------+--------+
2 rows in set (0.000 sec)

MariaDB [wuxia]>

示例：复合索引

MariaDB [wuxia]> desc emei;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

# 创建符合索引
MariaDB [wuxia]> create index idx_name_age on emei(name,age);
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wuxia]> desc emei;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   | MUL | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

MariaDB [wuxia]> show indexes from emei\G
*************************** 1. row ***************************
        Table: emei
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: emei
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: emei
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.000 sec)

# 跳过查询复合索引的前面字段,后续字段的条件查询无法利用复合索引
MariaDB [wuxia]> explain select * from emei where age=20;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | emei  | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [wuxia]>