MySQL的EXPLAIN

发现生产上有一条SQL执行的比较慢，检查了下发现是单表查询，但是一次要2秒多的的时间。EXPLAIN执行的SQL发现type是index，索引基本没用，检查下索引，发现只有一个二级索引，也不适合直接修改原索引，于是加了一个索引。由于是范围查询，做到ref的基本不太可能，所以让type升为range就差不多了。我在我的笔记本上测试了100W的数据由0.6秒左右下降到0.1秒左右。

这次就说说EXPLAIN怎么用吧，这个命令在优化SQL的时候还是挺有用的。

本文中用到的表都来自于MySQL官网的示例数据库Sakila。

查询执行计划(Query Execution Plan)：
根据表，列，索引的详细信息以及WHERE子句中的条件，MySQL优化器会考虑多种技术来有效执行SQL查询。优化器选择执行最有效查询的一组操作称为“查询执行计划”，也称为EXPLAIN计划。

在SELECT关键字之前增加EXPLAIN这个词，MySQL会在查询上设置一个标记。当执行查询时，这个标记会使其返回关于在执行计划中每一步的信息，而不是执行它。它会返回一行或多行信息，显示出执行计划中的每一部分和执行的次序。下面是一个示例：

MariaDB [sakila]> explain select * from film;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1000 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.000 sec)

在查询中每个表在输出中只有一行，如果查询是两个表的联接，那么输出将有两行。别名表单独算一个表，因此，如果一个表和自己联接，输出中也会有两行。“表”的意思在这相当广，可以是一个子查询，一个UNION结果等等。

MariaDB [sakila]> EXPLAIN SELECT * FROM film LEFT JOIN film_category AS film_cat ON film.film_id = film_cat.film_id LEFT JOIN category AS cat ON film_cat.category_id = cat.category_id WHERE cat.category_id = 10;
+------+-------------+----------+--------+-----------------------------------+---------------------------+---------+-------------------------+------+-------+
| id   | select_type | table    | type   | possible_keys                     | key                       | key_len | ref                     | rows | Extra |
+------+-------------+----------+--------+-----------------------------------+---------------------------+---------+-------------------------+------+-------+
|    1 | SIMPLE      | cat      | const  | PRIMARY                           | PRIMARY                   | 1       | const                   |    1 |       |
|    1 | SIMPLE      | film_cat | ref    | PRIMARY,fk_film_category_category | fk_film_category_category | 1       | const                   |   61 |       |
|    1 | SIMPLE      | film     | eq_ref | PRIMARY                           | PRIMARY                   | 2       | sakila.film_cat.film_id |    1 |       |
+------+-------------+----------+--------+-----------------------------------+---------------------------+---------+-------------------------+------+-------+
3 rows in set (0.001 sec)

MariaDB [sakila]> EXPLAIN SELECT (SELECT 1 FROM actor LIMIT 1) FROM film;
+------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
|    1 | PRIMARY     | film  | index | NULL          | idx_fk_language_id  | 1       | NULL | 1000 | Using index |
|    2 | SUBQUERY    | actor | index | NULL          | idx_actor_last_name | 182     | NULL |  200 | Using index |
+------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
2 rows in set (0.034 sec)

认为EXPLAIN时MySQL不会执行查询，这是一个常见的错误。事实上，如果查询在FROM子句中包含子查询，那么MySQL实际上会执行子查询，将其结果放在一个临时表中，然后完成外层查询优化。这意味着如果语句包含开销较大的子查询或使用临时表算法的视图，实际上会给服务器带来大量工作。

要意识到EXPLAIN只是个近似结果，有时候它是一个很好的近似值，但在其他时候，可能与真实相差甚远。以下是一些相关的限制：

  • EXPLAIN根本不会告诉你触发器、存储过程或UDF会如何影响查询
  • 不支持存储过程
  • 不会告诉你MySQL在查询执行计划中所作的特定优化
  • 不区分具有相同名字的事务。例如，对内存排序和临时文件都是用“filesort”，对于磁盘上和内存中的临时表都显示“Using temporary”。
  • 可能会误导。例如，它会对一个有着很小LIMIT的查询显示全索引扫描

虽然EXPLAIN功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息。

EXPLAIN的输出总是有相同的列，下面介绍每一列的意义。

  • id (The SELECT identifier)
    这一列总是包含一个编号，标识SELECT所属的行。如果在语句中没有子查询或联合，那么只有唯一的SELECT，于是每一行都将显示1.否则，内层的SELECT语句一般会顺序编号，对应其在原始语句中的位置。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
  • select_type (The SELECT type)
    这一列显示对应行是简单还是复杂SELECT。SIMPLE意味着查询不包括子查询和UNION，如果查询有任何复杂的子部份，则最外层部分标记为PRIMARY。
select_type值意义
SIMPLE简单的SELECT（不使用UNION或子查询）
PRIMARY最外层SELECT
UNION在UNION第二个或之后的SELECT语句
DEPENDENT UNION在UNION查询中第二个或之后的SELECT语句，依赖于外部查询
UNION RESULTUNION结果
SUBQUERY子查询的第一个SELECT
DEPENDENT SUBQUERY子查询的第一个SELECT，依赖于外部查询
DERIVED派生表，用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放在一个临时表中。MySQL将其称为派生表，因为是从子查询派生出来的
DEPENDENT DERIVED派生表依赖于另一个表
MATERIALIZED物化子查询
UNCACHEABLE SUBQUERY子查询，其结果无法缓存，必须对外部查询的每一行重新评估
UNCACHEABLE UNIONUNION的第二个或之后的SELECT，且UNION属于不可缓存子查询（参考UNCACHEABLE SUBQUERY）
  • table (The table for the output row)
    显示对应行正在访问哪个表，如果SQL定义了别名，则展示表的别名。关联查询有多行时可以从上往下观察MySQL的关联优化器为查询选择的关联顺序。
  • partitions (The matching partitions)
    当前查询匹配记录的分区。对于未分区的表，返回null

  • type (The join type)
    联接类型——MySQL决定如何查找表中的行，下面的类型性能依次从最好到最差：

    • NULL

    这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找素引来完成，不需要在执行时访问表。

    MariaDB [sakila]> EXPLAIN SELECT MIN(actor_id) FROM film_actor;
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
 1 row in set (0.000 sec)
    • system

    该表只有一行（相当于系统表），system是const类型的特例

    • const

    该表最多有一个匹配行，在查询的开始就会读取到数据。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。const非常快，因为它只读取一次。当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型。举例来说，如果你通过将某一行的主键放入WHERE子句里的方式来选取此行的主键，MySQL就能把这个查询转换为一个常量。然后就可以高效地将表从联接执行中移除。在将主键或唯一索引和常量值的所有部分（如联合主键或多列索引）比较时使用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

    当使用了索引的全部组成部分，并且索引是主键（PRIMARY KEY）或非空唯一索引(UNIQUE NOT NULL)时会使用此类型，仅次于system和const。使用这种素引查找，MySQL知道最多只返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到，它会将它们与某个参考值做比较。MySQL对于这类访问类型的优化做得非常好，因为它知道无须估计匹配行的范围或在找到匹配行后再继续查找。

    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;
    • ref

    当满足索引的最左前缀规则或者索引不是主键也不是唯一索引时才会发生（换句话说，不能根据索引确定唯一的一行数据）。如果使用的索引只会匹配到少量的行，也是不错的类型。ref可以用于索引列的 = 或 <=> 比较操作。

    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

    全文索引

    • ref_or_null

    类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型优化常见于解析子查询

    SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
    • index_merge

    此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引。

    • unique_subquery

    该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。

    value IN (SELECT primary_key FROM single_table WHERE some_expr)
    • index_subquery

    和unique_subquery类似，只是子查询使用的是非唯一索引

    • range

    范围扫描，表示检索了指定范围的行。key列显示的是使用了哪个索引，key_len显示了使用的索引有多长。比较常见的范围扫描是当使用=、<>、>、>=、<、<=、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类型一样，只是此类型会扫描索引树。MySQL扫描表时按索引序进行而不是行。它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将会非常大。扫描索引树分为两种情况：

      * 如果索引是覆盖索引（covering index），能够返回查询需要的全部数据，那么只会扫描索引树。这时Extra列显示"Using index"。这时会比ALL快，因为索引的大小通常比表数据小。
  * 以索引序进行全表扫描，这时Uses index不会出现在Extra列。
    • ALL

    俗称的全表扫描，通常意味着MySQL必须扫描整个表，从头到尾，去找到需要的行。

  • possible_keys (The possible indexes to choose)
    这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。

  • key (The index actually chosen)
    这一列显示了MySQL决定采用的索引。如果该索引没有出现在possible_keys列中，那么MySQL选用它是处于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没有WHERE子句。换句话说possible_keys揭示了哪一个索引能有助于高效的行查找，而key显示的是优化采用哪一个索引可以最小化查询成本。

    MariaDB [sakila]> EXPLAIN SELECT actor_id, film_id FROM film_actor\G
*************************** 1. row ***************************
        id: 1
select_type: SIMPLE
     table: film_actor
      type: index
possible_keys: NULL
       key: idx_fk_film_id
   key_len: 2
       ref: NULL
      rows: 5462
     Extra: Using index
1 row in set (0.000 sec)

  • key_len (The length of the chosen key)
    该列显示了MySQL在索引里使用的字节数。如果MySQL正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。例如当满足最左前缀时，只使用部分索引也可以完成查询的优化。actor_id和film_id都是smallint类型，索引中一项就是4个字节，这里只用了前2个字节。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。

    MariaDB [sakila]> EXPLAIN SELECT actor_id, film_id FROM film_actor WHERE actor_id = 4\G
*************************** 1. row ***************************
        id: 1
select_type: SIMPLE
     table: film_actor
      type: ref
possible_keys: PRIMARY
       key: PRIMARY
   key_len: 2
       ref: const
      rows: 22
     Extra: Using index
1 row in set (0.000 sec)

  • ref (The columns compared to the index)
    这一列显示了在key列的索引中查找值所用的列或常量。如果值为func，则使用的是某些函数的结果。下面是一个展示关联条件和别名组合的例子。ref列反映了在查询中film表是如何以f为别名的。

    MariaDB [sakila]> EXPLAIN
 -> SELECT STRAIGHT_JOIN f.film_id
 -> FROM film AS f
 ->    INNER JOIN film_actor AS fa
 ->       ON f.film_id = fa.film_id AND fa.actor_id = 1
 ->    INNER JOIN actor AS a USING ( actor_id );
+------+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys          | key                | key_len | ref                    | rows | Extra       |
+------+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
|    1 | SIMPLE      | f     | index  | PRIMARY                | idx_fk_language_id | 1       | NULL                   | 1000 | Using index |
|    1 | SIMPLE      | fa    | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY            | 4       | const,sakila.f.film_id |    1 | Using index |
|    1 | SIMPLE      | a     | const  | PRIMARY                | PRIMARY            | 2       | const                  |    1 |             |
+------+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
3 rows in set (0.000 sec)
  • rows (Estimate of rows to be examined)
    这一列是MySQL估计为了找到所需的行而需要读取的行数，这个估算可能很不精确。这个值是MySQL认为它要检查的行数，而不是结果集里的行数。同时很多优化如关联缓冲区和缓存无法影响到行数的显示。
  • filtered (Percentage of rows filtered by table condition)
    这一列在使用EXPLAIN EXTENDED时出现。它显示的是针对表里符合条件的记录数的百分比所做的一个悲观估算，最大值为100。如果把rows × filtered，就能看到MySQL估算下一张表连接的行数。例如rows = 1000，filtered = 50.00（50％），则估算下一张表连接的行数是500。

  • Extra (Additional information)
    这一列包含的是额外信息。全部内容可以查看官方文档，常见的重要值如下：

    • "Using filesort"
      当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。这意味着MySQL会额外进行一次传输以按排序规则获取行数据。MySQL有两种文件排序算法（两次传输排序 和 单次传输排序），两种方式都可以在内存或磁盘上完成。EXPLAIN不会告诉你MySQL将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
    • "Using index"
      此值表示MySQL将使用覆盖索引，以避免回表。
    • "Using index condition"
      表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE子句中的其他条件去过滤这些数据行。通过这种方式，除非有必要，否则索引信息将可以延迟（“索引下推”）读取整个行的数据。
    • "Using temporary"
      这意味着MySQL在对查询结果排序时会使用一个临时表。
    • "Using where"
      如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现using where信息。MySQL服务器将在存储引擎检索行后再进行过滤。许多WHERE条件里涉及索引中的列，当它读取素引时，就能被存储引擎检验，因此不是所有带WHERE子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
    • "Range checked for each record(index map: N)"
      这个值意味着没有好用的索引，新的索引将在联接的每一行上重新估算。N是显示在possible_keys列中素引的位掩码（bitmask）。例如0x19（二进制11001）的值意味着将考虑索引1、4和5。

额外多说一点，在生产环境上执行DDL语句可能会导致表被锁定，使表在几分钟或几小时内不可用。这部分详细可以参考官方的Online DDL部分，了解具体的执行过程以选择合适的方式和时间。

我在我的笔记本上给一个100W数据的表添加一个索引耗时6秒，同样的索引在我们的生产环境上大概是2秒左右，这个数据可以参考一下。当然也可以选择无锁的模式，DDL语句追加例如ALGORITHM=INPLACE, LOCK=NONE;这些关键词。但是一定要好好测试，防止由于版本或数据等不一致导致生产锁表，数据库一段时间不可用可能直接导致生产事故。

标签: none

添加新评论

ali-01.gifali-58.gifali-09.gifali-23.gifali-04.gifali-46.gifali-57.gifali-22.gifali-38.gifali-13.gifali-10.gifali-34.gifali-06.gifali-37.gifali-42.gifali-35.gifali-12.gifali-30.gifali-16.gifali-54.gifali-55.gifali-59.gif

加载中……