在使用关系数据库的时候，很多时候需要对慢查询进行优化，那么需要首先定位慢查询的sql语句。例如没有正确的使用index，加入了无谓的order by导致了结果排序等。一般来说可以通过explain sql语句看一下sql语句的执行流程，这样可以对sql的执行效率做到心中有数。因此优化之前呢，需要看懂explain的结果。以下说明均基于Mysql5.6版本。
EXPLAIN语句可以用来查看MySQL将会如何执行一条语句，包括table是如何关联的、关联顺序如何。EXPLAIN EXTENDED可以用来获取附加信息，EXPLAIN PARTITIONS可以用来检查那么包括partitioned table的查询。
- 借助EXPLAIN，可以知道什么时候应该为table添加index，以达到让SELECT语句使用这个index加快查询效率。
- 还可以知道优化器是否以一个最佳次序连接表。可以使用SELECT STRAIGHT_JOIN让优化器使用一个指定的关联次序。
基本语法
EXPLAIN [EXTENDED] SELECT select_options
EXPLAIN语句为SELECT语句中使用到的每张table返回一行信息，这些信息显示了Mysql在处理SQL语句时将读取这些表的读取顺序。Mysql在解析join操作时使用了nested-loop的join方法。这意味着MySQL从第一个表中读取一行记录，然后在第二个表中找到一条匹配到的记录，然后在同样方法处理第三、第四等等的表。当所有的表都被处理后，MySQL输出选中的列（在这次执行中，最后一张表的所有记录被完全遍历，但是倒数第二张表只是遍历到了一部分记录，因此需要回溯，将倒数第二个表、倒数第三个表等等，直到第二个表被完全遍历），然后按照表的顺序进行回溯，找到表中更多的匹配的记录。然后读取第一个表中第二条记录，然后按照前面流程进行同样处理。
当EXTENDED被使用时，EXPLAIN生成附加信息，可以通过SHOW WARNING来查看。EXPLAIN EXTENDED同时也显示了filtered列。
举例：
CREATE DATABASE dbTest;CREATE TABLE tbPerson(Id int PRIMARY KEY,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255));CREATE INDEX id_tbTest_LastName ON tbPerson(LastName);INSERT INTO tbPerson values(1,'Li', 'Lei', 'Nanjing Road', 'Nanjing');INSERT INTO tbPerson values(2,'Han', 'Meimei', 'Dalian Road', 'Shanghai');INSERT INTO tbPerson values(3,'Green', 'Jim', 'Nanjing Road', 'Nanjing');INSERT INTO tbPerson values(4,'Li', 'Xiaolei', 'Nanjing Road', 'Nanjing');
USE dbTest;EXPLAIN EXTENDED SELECT * FROM tbPerson;mysql> EXPLAIN EXTENDED SELECT * FROM tbPerson;+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | tbPerson | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
EXPLAIN的输出
| column | JSON Name | 含义 |
|---|---|---|
| id | select_id | SELECT识别符 |
| select_type | None | SELECT类型 |
| table | table_name | 表名称 |
| partitions | partitions | 匹配的partitions |
| type | access_type | 表的连接类型 |
| possible_keys | possible_keys | 可以使用的index |
| key | select_id | 实际决定使用的索引 |
| key_len | select_id | 决定使用索引的长度 |
| ref | select_id | 显示使用哪个列或常数与索引比较 |
| rows | select_id | MySQL预估要检索的行数 |
| filtered | select_id | 依据表condition被过滤掉行数的百分比 |
| Extra | select_id | 附加信息 |
id
- SELECT识别符。这是SELECT的查询序号。这个值可能为NULL，更多请看MySQL文档。
select_type
- SELECT类型，可能是下文的一种：（略去部分参见文档讲解，没见过这几种，我不太懂）
- SIMPLE：简单的SELECT（不使用UNION或者子查询）
- PRIMARY：表示主查询或者最外面的查询（多表连接时）
- UNION：略
- DEPENDENT UNION：略
- UNION RESULT：略
- SUBQUERY：子查询中的第一个SELECT语句
- DEPENDENT SUBQUERY：子查询中的第一个SELECT语句，取决于外面的查询
- DERIVED：略
- MATERIALIZED：略
- UNCACHEABLE SUBQUERY：略
- UNCACHEABLE UNION：略
table
- 表示查询的表
partitions
- 可能匹配查询条件的分区（partition）
type
- 表示表的连接类型，对于优化极其重要的一列。内容太多，后面另起一段。
possible_keys
- 用来指出mysql能够提供的索引键有哪些。
- 注意，该列完全独立于EXPLAIN输出的表的次序，这意味着possible_keys中的某些索引键实际上不能按生成的表次序使用。
- 如果该列是NULL，则没有相关的引用。在这种情况下，可以通过检查where子句查看是否可以引用某些列或适合的索引列来提高查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。
key
- 表示mysql实际决定使用的键（索引）。
- 有可能key所显示的index并未包含在possible_keys中。这可能发生在，possible_keys中没有合适的索引供查找记录，但是所要查询的字段都被某个index所包含，即这个索引可以包括被查询的所有字段，因此虽然这些索引并没有被用来决定哪些记录被返回，但是遍历索引是遍历整个数据记录要高效的多。
- 例如，对于InnoDB，除了主键外的其它索引（a secondary index），可以用来处理哪些包含了主键的查询，因为InnoDB在其它索引中保存了相对应的主键值。如果key是NULL，MySQL发现没有主键可以用，则查询效率可能不高。
- 要想强制MySQL使用或者忽略possible_keys列中的索引，在查询中可以使用FORCE INDEX,USE INDEX,IGNORE INDEX。
key_len
- key_len列显示MySQL决定使用的索引键的长度（按字节计算）。如果key列是NULL，则key_len为NULL。
- 注意通过key_len值让我们可以确定MySQL将实际使用一个multiple-part索引的几个part。
ref
- 显示使用哪个列或常数来与key列指定的索引进行比较，以筛选记录。
- 如果是func，那么某个function的值将会被使用。可以通过EXPLAIN EXTENDED+SHOW WARNINGS查看，该function也有可能是运算符，例如算数运算符。
rows
- 表示mysql认为在执行查询时，应该检查的记录的行数。
- 对于InnoDB来说，这个值有时可能不准确，因为是个估计值。
filtered
- 该列表示一个估算的百分比，该比例 x filtered/100表示要和前一张表关联的记录的数据。
- 使用EXPLAIN EXTENDED时，输出该列。
Extra
- 该列包含了关于mysql如何解析该次查询的额外的附加信息。内容太多，另起一段，见后文。
EXPLAIN关联类型
EXPLAIN输出的type列描述了表是如何进行关联的，如下：
system
表示该表只有一行记录(通常是系统表)，是const类型的一个特殊情况。
const
该表之多有一条匹配的记录，在查询的开始时被读取。因为只有一条记录，余下的优化器将视之为常量。const类型的表因为仅被读一次，所以非常快。
const类型在对PRIMARY KEY和UNIQUE index的部分与常值比较时使用，以下查询中，tbl_name被认为const表：
SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
eq_ref
对之前表的每一个行组合，只从该表中读取一条记录。这是system和const意外的，最好的关联类型。它的应用场景是：一个索引的全部被关联使用，且该索引是primary key或者unique的非NULL索引。
eq_ref可以用于使用“=”比较那些被索引过的列。比较的值可以是常量，或者表达式（例如排在这张表之前要读取的表的列）。以下查询中，mysql可以使用eq_ref来处理ref_table:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;# 上面查询中，key_column是主键，或者唯一非空索引SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;# 上面查询中，（key_column_part1, key_column_part2）是主键，或者唯一非空索引
ref
对之前表的每一个行组合，匹配到索引值的所有记录将被读取，ref用于哪些最左侧前缀匹配的键或者键不是primary key或者unique index（也就是说，这个关联不能只选择一条记录）。如果当匹配几行记录时，这是个好的关联类型。例如
SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
fulltext
用于使用fulltext index。（Figo：未见过）
ref_or_null
和ref类似，但是mysql需要对包含NULL的记录做额外的搜索。这个连接类型优化常用于处理子查询。下面例子，mysql可以使用ref_or_null来处理ref_table。
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了所用到的索引的清单，key_len列包含了所用到的索引的最长长度。(Figo: 未见过)
unique_subquery
在IN子查询时用于替换ref类型，例如
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数，用于替换子查询，已达到更高的效率。
index_subquery
和unique_subquery类似，用于替换IN的子查询，但它用于对那些非unique的index的子查询，例如
value IN (SELECT key_column FROM single_table WHERE some_expr)
range
对于一个给定的range，使用index来获取记录。key列给出使用的index类，key_len包含使用的index的最大长度，ref列为空。
range被用来当key列使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 或者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要快，因为索引数据通常比表数据要小。
- 执行全表扫描时，需要根据索引的顺序进行查询。Extra列不显示Using index。
MySQL可以使用这种连接类型，前提是查询的结果仅是某个索引的部分数据。
ALL
对之前的表的所有行组合执行全表扫描。在该表是第一个表，且没有标记为const时，这个类型不好。若该表不是第一个表时，那就是非常差劲的使用场景了。通常可以通过增加index，从而使得可以根据常值，或从之前表的列值，来获取行记录，这样就避免了ALL的情形。
EXPLAIN Extra Infomation
Extra列包含了MySQL解析查询的附加信息。下面给出了可能出现的值。如果想尽量让你的查询尽量快，请注意“Using filesort”和“Using temporary”。
Distinct
MySQL查找不同的值，所以当为当前的行组合找到第一个匹配行时，便停止搜索。
Using filesort
MySQL需要一次额外的传递，已找出如何按照有序的次序查询记录。这个过程包括遍历所有的关联的表，根据where条件查找所有的记录，然后保存待排序的值。然后对保存的待排序的值进行排序，然后根据排序结果，取出所有的记录。
Using index
仅使用index数据来取出列信息，不需要额外的读取真实行数据，这种应用场景仅在于待查询的信息是单个索引的部分数据。
若Extra是Using where，这意味着参照where条件，使用该index过滤数据。若没有Using where，优化器会读取所有index数据（避免读取原始数据）。
在InnoDB表中，有一用户定义的聚合索引，该索引甚至可以在Extra中没有Using index时使用。有一个应用场景：当type是index，key是PRIMARY。
Using temporary
为了解决查询，需要一个临时表来容纳结果。典型场景如查询可以按不同情况列出列的group by和order by子句。
Using where
Where字句用于限制哪一个行来匹配下一个表，或者发送给客户端。除非想取出或检查表中的所有行，在join类型是ALL或者index时，那么可能你的查询中有一些错误。（这里的错误只效率不高，导致变为慢查询，其实也数据上的错误，而是效率上的错误）
未完待续
其实这个字段还有很多信息，以上只是挑出了常见的几种，如果看到其他的，再查手册啦。
下一篇文章对以上说过的各种情况进行实例分析。