SQL优化：读懂explain

在使用关系数据库的时候，很多时候需要对慢查询进行优化，那么需要首先定位慢查询的sql语句。例如没有正确的使用index，加入了无谓的order by导致了结果排序等。一般来说可以通过explain sql语句看一下sql语句的执行流程，这样可以对sql的执行效率做到心中有数。因此优化之前呢，需要看懂explain的结果。以下说明均基于Mysql5.6版本。

理解查询执行计划（Query Execution Plan）

EXPLAIN语句可以用来查看MySQL将会如何执行一条语句，包括table是如何关联的、关联顺序如何。EXPLAIN EXTENDED可以用来获取附加信息，EXPLAIN PARTITIONS可以用来检查那么包括partitioned table的查询。

  1. 借助EXPLAIN，可以知道什么时候应该为table添加index，以达到让SELECT语句使用这个index加快查询效率。
  2. 还可以知道优化器是否以一个最佳次序连接表。可以使用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的输出

columnJSON 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类型，可能是下文的一种：（略去部分参见文档讲解，没见过这几种，我不太懂）
    1. SIMPLE：简单的SELECT（不使用UNION或者子查询）
    2. PRIMARY：表示主查询或者最外面的查询（多表连接时）
    3. UNION：略
    4. DEPENDENT UNION：略
    5. UNION RESULT：略
    6. SUBQUERY：子查询中的第一个SELECT语句
    7. DEPENDENT SUBQUERY：子查询中的第一个SELECT语句，取决于外面的查询
    8. DERIVED：略
    9. MATERIALIZED：略
    10. UNCACHEABLE SUBQUERY：略
    11. 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类型几乎是一样的，不同在于这个类型扫描的数据是索引。有两种情况：

  1. 当索引数据能够满足查询请求（即索引数据包含查询所要的所有列），那么只需扫描索引数据即可。在这种情况下，Extra字段显示Using index。通常扫描索引数据比ALL要快，因为索引数据通常比表数据要小。
  2. 执行全表扫描时，需要根据索引的顺序进行查询。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时，那么可能你的查询中有一些错误。（这里的错误只效率不高，导致变为慢查询，其实也数据上的错误，而是效率上的错误）

未完待续

其实这个字段还有很多信息，以上只是挑出了常见的几种，如果看到其他的，再查手册啦。

下一篇文章对以上说过的各种情况进行实例分析。

posted @ 2016-02-01 19:34  FigoCui  阅读(505)  评论(0)  编辑  收藏  举报