mysql的优化_第十一篇（查询计划篇）

table

即表名，但是也有可能是下面三种情况。
- <union*M*,*N*>：表示多个表的union，其中M和N分别为第一列的id
- <derived*N*>：表示来自于其他表的衍生表，同上，M和N表示第一列的id，通常，衍生表来自于from语句后面的select
- <subquery*N*>: 表示物化后的子查询，N为第一列的id。
partitions

对于有分区的表该列有效
type

即join type，分别有常见的system``， const， eq_ref， ALL等，下一节将详细介绍。
possible_keys

表示待选的索引
key

表示实际上用到的索引，来自于possible_keys，当possible_keys不为NULL的时候，该列也有可能为none，因为有时候用索引的效率不定义会比扫表高。
key_len

Mysql决定使用索引的长度（哪些部分），当一列有可能为NULL的时候，key_len的长度可能比该列大。
ref

表示用来和索引比较的常量或者某一列。
rows， filtered

rows表示Mysql预估将要扫描的行数，filtered表示Mysql预估的能过滤的行数（这里值的是返回给用户的行数），比如rows为100， filtered为70%，那么Mysql预估返回给用户70行数据。
Extra

表示Mysql解析一个query的一些额外信息，比如Using Index，Using filesort等等。

EXPLAIN Join Types

对于type列，经常用于join的场景，下面将根据效率由高到低进行说明：

system

const的一种特殊情况，表示该表只有一行数据
const

表示只有最多只有一行匹配的数据，虽然该表可能有多行数据，但是对于和其join的表，其只需要一行数据，可以视作为const，经常用于primary key或者unique key中，比如下面的语句：
```
SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
```
eq_ref

Mysql可以使用索引进行equal操作，只适用于equal的情况，并且索引类型必须为primary key或者unique not null，在join的时候，这是除了system和const最好的情况。
ref

当一个索引不是primary key或者unique index的时候，又或者只能使用leftmost的时候，就会使用到ref的情况，适用于=或者<=>
fulltext
ref_or_null

和ref很像，但是除此之外，还需要额外寻找null字段，经常用于一些子查询的情况，比如下面的语句：
```
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;
```
index_merge

表示会使用Section 8.2.1.3, “Index Merge Optimization”.技术进行优化
unique_subquery

和eq_ref等价的情况，不过是针对于子查询的，譬如下面的语句:
```
value IN (SELECT primary_key FROM single_table WHERE some_expr)
```
index_subquery

和unique_subquery很像，不过是针对于子查询中的nonunique index的，譬如下面的语句：
```
value IN (SELECT key_column FROM single_table WHERE some_expr)
```
range

一般索引的范围查找会用到，该种情况下，key列表示所使用的索引，key_len表示所使用的最长的key part，ref是NULL，适用于索引列和一个常量进行对比的情况，包括：=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, 或者IN，譬如下面的语句：
```
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
```
index

类似于ALL，不过这种情况下是索引扫描，通常情况下，Extra列为Using Index
ALL

最糟糕的情况，这种情况下只能进行全表扫描，此时应该考虑创建索引来加快join的速度。

EXPLAIN Extra Information

该小节省略，因为Extra列的信息都比较易懂，碰到不清楚的可参考这里。

2 Extended EXPLAIN Output Format

在EXPLAIN之后，执行SHOW WARNINGS命令，会看到EXPLAIN的一些附加信息，在8.0.12之后，该附加信息的适用条件为：SELECT, DELETE, INSERT, REPLACE和UPDATE，在8.0.12之前，该附加信息只有在SELECT语句下才会产生，看下面的一个例子：

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         <primary_index_lookup>(`test`.`t1`.`a` in
         <temporary table> on <auto_key>
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)

注意，可以看到这些查询语句可能会被重写，且包含特殊的标记符，所以这些语句不一定是合法的SQL语句，该语句只是为了表示Mysql是如何具体来解析和执行的。

对于这些特殊的标记，部分解释如下：

<auto_key>

为临时自动生成的key
(expr)

表示该语句会被缓存在内存中，以后可能会使用到。
(query fragment)

表示语句被重写成了EXISTS

等等，还有很多，这里就不一一介绍了。

3 Extended EXPLAIN Output Format

EXPLAIN FOR CONNECTION语句用于一些性能诊断的情况，比如：一个session执行的时间很长，那么可以在另外一个session中使用该命令，指定特定的connection id，那么就可以看到之所以执行这么长时间的额原因，该命令的格式如下：

EXPLAIN [options] FOR CONNECTION connection_id;

该命令适用于 SELECT, DELETE, INSERT, REPLACE, and UPDATE. 但是，该命令不适用于任何prepared语句。

connection_id是一个conection的标识符，该id可从 INFORMATION_SCHEMA PROCESSLIST table or the SHOW PROCESSLIST statement中获取，但是使用者必须要有相关的权限，否则无法获取。

手机扫一扫

移动阅读更方便

你可能感兴趣的文章

mongoDB 学习笔记纯干货（mongoose、增删改查、聚合、索引、连接、备份与恢复、监控等等）

in用不用索引，啥时候能用啥时候不能用，一文说清

一招带你吃透MySQL高级