【译】用EXPLAIN写出更加高效的MySQL查询语句

原文地址：https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

当你进行一次查询的时候，MySQL的查询优化系统就会尝试为这次查询制定出一个最好的查询方案。你可以通过在查询语句之前加上EXPLAIN来查看系统做出的最优方案的具体信息。EXPLAIN是一个帮助你理解和优化MySQL查询的较为强大的工具，但是很悲剧的是很少有开发者会使用到它。在这篇文章中你将会了解到EXPLAIN究竟会输出哪些信息，然后根据这些信息去优化你的架构和查询语句。

理解EXPLAIN的输出

使用EXPLAIN是非常容易的，只要在SELECT查询之前加上它就可以。首先我们来分析一个简单查询的输出，用来熟悉该命令返回的列。

EXPLAIN SELECT * FROM categories G;

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)

它看起来没什么用，但是这10列信息却包含了大量的信息！分别来看看这条指令返回的列都有哪些：

id - 一次查询任务中的每一次SELECT的有序识别码（当你有嵌套子查询的时候）
select_type - SELECT查询的类型，可能的值有下面几种：
1. SIMPLE - 当前查询是一个简单的SELECT查询，并没有子查询或者联合查询（UNION）
2. PRIMARY - 当前的SELECT是JOIN查询的最外层查询
3. DERIVED - 当前SELECT是一个在FROM语句中的子查询的一部分
4. SUBQUERY - 子查询中的第一个SELECT
5. DEPENDENT SUBQUERY - 一个依赖上一个查询结果的子查询
6. UNCACHEABLE SUBQUERY - 一个没有被缓存过的子查询（一些符合特定条件的查询会被缓存）
7. UNION - 这个SELECT是一个UNION的第二个或者更靠后的语句
8. DEPENDENT UNION - 一个UNION的第二个或者更靠后的依赖于外部查询的SELECT
9. UNION RESULT - 这个SELECT是一个UNION的结果
table - 结果数据涉及到的表
type - MySQL链接表的方式。这是输出数据中非常有作用的一个字段，因为，它可以指出缺少的索引或者可以指导你重新考虑如何编写查询语句，它可能的值如下：
1. system - 这个表只有一条记录或者没有记录
2. const - 这个表只有一条被索引的匹配记录。这是链接表最快的一种方式，因为只需要读一次，然后在链接别的表的时候将列数据当作常量看待
3. eq_ref - 在链接的时候索引的所有部分都被使用到，并且索引都是主键索引PRIMARY KEY或者联合索引UNION NOT NULL。这是链接类型中的第二快的类型
4. ref - 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。这类情况会在被索引的列用=或者<=>组合的时候出现
5. fulltext - 这个链接使用了表的FULLTEXT索引
6. ref_or_null - 这个链接和ref很相似，但是也包含了列的值为NULL的记录
7. index_merge - 这个链接使用索引的列表来生成结果集。EXPLAIN输出的列键将包含被使用的键
8. range - 一个被用于查询特殊范围内的匹配记录的索引，一般而言，当列键与一个常量比较会用类似于BETWEEN、IN、>、>=等等这样的式子。
9. index - 扫描整个索引数来找匹配的记录
10. all - 查找整个表来找链接的匹配行，这是最差的链接方式，通常说明这个表缺少适当的索引
possible_keys - 显示可以被MySQL用来从表中查找记录的键，尽管它们可能会或可能不会在实践中被使用到。事实上，这个通常可以用来帮助简化因为记录值为NULL的查询，它表明没有响应的索引能够被发现
key - 表明MySQL实际上使用到的索引。这个记录可能包含一个没有被possible_key记录下来的索引，MySQL优化程序往往会找一个更好的便于查询的键。在链接表的时候，它会找出一些没有被possible_key记录下来的但是却更加优秀的其他键
key_len - 表明被查询优化程序使用的索引的长度。打个比方，如果key_len值为4那么意味着需要4个字节的内存空间。可以跳转到《MySQL的数据存储类型需知》去了解更多相关信息
ref - 显示与记录键相比较的记录和常量，MySQL也会挑出一个常量来比较或者一个本身就基于执行方案的列，你可以通过下面给出的例子看到
rows - 列出被检测过的生产输出的记录条数。这又是一个在优化查询尤其是在使用了JOIN查询和子查询的查询中较为重要的列值
Extra - 包含了一些执行查询方案的其他的一些信息。一些类似于“临时调用啊”，“文件排序调用啊”等等的这类值。在这个栏目中可能会指出有问题的查询。对于可能的值及其含义的完整列表可以查阅《MySQL说明文档》。

你也可以在查询语句中EXPLAIN的后面加上EXTENDED，MySQL会显示一些关于它执行语句的方式的额外的信息。想要查看更多信息，请在执行完EXPLAIN语句后紧接着执行SHOW WARNINGS。这是一个查看那些被查询优化程序转换过后执行的语句的利器。

EXPLAIN EXTENDED SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia' G

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: Country
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 1
     filtered: 100.00
        Extra: 
********************** 2. row **********************
           id: 1
  select_type: SIMPLE
        table: City
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

SHOW WARNINGS G

********************** 1. row **********************
  Level: Note
   Code: 1003
Message: select `World`.`City`.`Name` AS `Name` from `World`.`City` join `World`.`Country` where ((`World`.`City`.`CountryCode` = 'IND'))
1 row in set (0.00 sec)

用EXPLAIN解决性能问题

现在我们来看下如何通过分析EXPLAIN的输出来优化一个很差劲的查询。在一个现实的项目中必然会存在大量的彼此之间有联系的表，有时候很难决定如何写查询语句才是最佳的。

这里，我们先创建一个简单的电商项目的数据库，它并没有任何的索引和主键，然后模拟一个写了很烂的查询语句的垃圾设计，你也可以在github上下载源码 schema sample

EXPLAIN SELECT * FROM
orderdetails d
INNER JOIN orders o ON d.orderNumber = o.orderNumber
INNER JOIN products p ON p.productCode = d.productCode
INNER JOIN productlines l ON p.productLine = l.productLine
INNER JOIN customers c on c.customerNumber = o.customerNumber
WHERE o.orderNumber = 10101 G

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: 
********************** 2. row **********************
           id: 1
  select_type: SIMPLE
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 110
        Extra: Using where; Using join buffer
********************** 3. row **********************
           id: 1
  select_type: SIMPLE
        table: c
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 122
        Extra: Using join buffer
********************** 4. row **********************
           id: 1
  select_type: SIMPLE
        table: o
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 326
        Extra: Using where; Using join buffer
********************** 5. row **********************
           id: 1
  select_type: SIMPLE
        table: d
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2996
        Extra: Using where; Using join buffer
5 rows in set (0.00 sec)

如果你看了上面的结果，你可以看出所有不好的查询的病症所在。就算我写了不错的查询语句，结果还是和上面的差不多，因为没有建索引。这个链接的类型就会显示成ALL(最差的那个)，这就意味着MySQL不能够在链接中找到任何的可以被用的键，所以possible_keys和key的值都是NULL，最重要的是rows一栏显示了MySQL为了这条查询扫描了每个表的每条记录，这就意味着执行这个查询，MySQL将要扫描7 × 110 × 122 × 326 × 2996 = 91,750,822,240个记录，来找到四个匹配的记录。这真是太恐怖了，而且扫描次数会随着数据库的变大成指数级上涨。

现在让我们增加一些常见的索引，例如给每个表加上主键，然后再次执行查询语句。按照常规的经验来看，你会看到在JOIN子句查询中用到的列，会作为很好的候选键，因为MySQL通常会扫描这些列来查询匹配的记录。

ALTER TABLE customers
ADD PRIMARY KEY (customerNumber);
ALTER TABLE employees
ADD PRIMARY KEY (employeeNumber);
ALTER TABLE offices
ADD PRIMARY KEY (officeCode);
ALTER TABLE orderdetails
ADD PRIMARY KEY (orderNumber, productCode);
ALTER TABLE orders
ADD PRIMARY KEY (orderNumber),
ADD KEY (customerNumber);
ALTER TABLE payments
ADD PRIMARY KEY (customerNumber, checkNumber);
ALTER TABLE productlines
ADD PRIMARY KEY (productLine);
ALTER TABLE products
ADD PRIMARY KEY (productCode),
ADD KEY (buyPrice),
ADD KEY (productLine);
ALTER TABLE productvariants
ADD PRIMARY KEY (variantId),
ADD KEY (buyPrice),
ADD KEY (productCode);

我们在增加了索引之后，再次运行一次相同的查询语句，你会看到如下的结果：

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: o
         type: const
possible_keys: PRIMARY,customerNumber
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
********************** 2. row **********************
           id: 1
  select_type: SIMPLE
        table: c
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
********************** 3. row **********************
           id: 1
  select_type: SIMPLE
        table: d
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 4
        Extra: 
********************** 4. row **********************
           id: 1
  select_type: SIMPLE
        table: p
         type: eq_ref
possible_keys: PRIMARY,productLine
          key: PRIMARY
      key_len: 17
          ref: classicmodels.d.productCode
         rows: 1
        Extra: 
********************** 5. row **********************
           id: 1
  select_type: SIMPLE
        table: l
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 52
          ref: classicmodels.p.productLine
         rows: 1
        Extra: 
5 rows in set (0.00 sec)

加了索引之后，需要扫描的记录数量骤减到1 × 1 × 4 × 1 × 1 = 4次，这就意味着在orderdetails表中的每一条记录都有一个orderNumber 10101，MySQL可以使用索引在其它所有表中直接找到匹配的记录，而不必要去扫描整个表。

在第一组输出的记录中，你可以看到链接的方式是const，也就是超过一条记录的表中最快的一种链接方式，MySQL可以使用PRIMARY KEY作为索引，ref这列的值也是const，除了当查询语句中的WHERE语句使用了值10101，否则没什么作用。

让我们再来看一个例子。这里我们将基于两个表的联合查询，分别是products和productvariants，每个都会和productline链接，productvariants表是由拥有不一样的以productCode当参考键和它们价格的商品型号组成。

EXPLAIN SELECT * FROM (
SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM
products p
INNER JOIN productlines l ON p.productLine = l.productLine
UNION
SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM productvariants v
INNER JOIN products p ON p.productCode = v.productCode
INNER JOIN productlines l ON p.productLine = l.productLine
) products
WHERE status = 'Active' AND lineStatus = 'Active' AND buyPrice BETWEEN 30 AND 50 G

********************** 1. row **********************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 219
        Extra: Using where
********************** 2. row **********************
           id: 2
  select_type: DERIVED
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 110
        Extra: 
********************** 3. row **********************
           id: 2
  select_type: DERIVED
        table: l
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 52
          ref: classicmodels.p.productLine
         rows: 1
        Extra: 
********************** 4. row **********************
           id: 3
  select_type: UNION
        table: v
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 109
        Extra: 
********************** 5. row **********************
           id: 3
  select_type: UNION
        table: p
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 17
          ref: classicmodels.v.productCode
         rows: 1
        Extra: 
********************** 6. row **********************
           id: 3
  select_type: UNION
        table: l
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 52
          ref: classicmodels.p.productLine
         rows: 1
        Extra: 
********************** 7. row **********************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
7 rows in set (0.01 sec)

你可以看出在这次查询中出现了大量的问题，它扫描了products和productvariants表中的所有的数据，因为在这些表中并没有为productLine和buyPrice这两列做索引，possible_keys和key这两列的输出是null，在UNION后products和productvariants表的状态被检查了，所以将它们从UNION里面删除掉，将会减少扫描记录的数量。让我们增加些额外的索引，同时重写一下查询语句。

CREATE INDEX idx_buyPrice ON products(buyPrice);
CREATE INDEX idx_buyPrice ON productvariants(buyPrice);
CREATE INDEX idx_productCode ON productvariants(productCode);
CREATE INDEX idx_productLine ON products(productLine);

EXPLAIN SELECT * FROM (
SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status as lineStatus FROM products p
INNER JOIN productlines AS l ON (p.productLine = l.productLine AND p.status = 'Active' AND l.status = 'Active')
WHERE buyPrice BETWEEN 30 AND 50
UNION
SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status FROM productvariants v
INNER JOIN products p ON (p.productCode = v.productCode AND p.status = 'Active')
INNER JOIN productlines l ON (p.productLine = l.productLine AND l.status = 'Active')
WHERE
v.buyPrice BETWEEN 30 AND 50
) product G

********************** 1. row **********************
          id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: 
********************** 2. row **********************
           id: 2
  select_type: DERIVED
        table: p
         type: range
possible_keys: idx_buyPrice,idx_productLine
          key: idx_buyPrice
      key_len: 8
          ref: NULL
         rows: 23
        Extra: Using where
********************** 3. row **********************
           id: 2
  select_type: DERIVED
        table: l
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 52
          ref: classicmodels.p.productLine
         rows: 1
        Extra: Using where
********************** 4. row **********************
           id: 3
  select_type: UNION
        table: v
         type: range
possible_keys: idx_buyPrice,idx_productCode
          key: idx_buyPrice
      key_len: 9
          ref: NULL
         rows: 1
        Extra: Using where
********************** 5. row **********************
           id: 3
  select_type: UNION
        table: p
         type: eq_ref
possible_keys: PRIMARY,idx_productLine
          key: PRIMARY
      key_len: 17
          ref: classicmodels.v.productCode
         rows: 1
        Extra: Using where
********************** 6. row **********************
           id: 3
  select_type: UNION
        table: l
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 52
          ref: classicmodels.p.productLine
         rows: 1
        Extra: Using where
********************** 7. row **********************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
7 rows in set (0.01 sec)

结果正如你所看到的那样，大概的扫描次数已经从2,625,810次(219 × 110 × 109)大幅度降低到276次(12 × 23)，这是一个巨大的性能提升。如果你在没有经过前面的重新写SQL，只是加了索引，就尝试同样的查询，你将不会看到如此巨大的降低。MySQL不能够使用索引，因为，在它的派生结果中拥有WHERE子句，从UNION内移除了这些条件后，它就可以使用索引了。这就意味着只添加一个索引是不够的，MySQL将不能使用它，除非你编写额外的查询。

概要

在这篇文章中，我讨论了MySQL关键字EXPLAIN,我们可以根据它的输出内容构建出更加出色的查询，在真实的项目中它将比演示的更加有用，在通常情况下，你会同时链接大量的表，或者使用复杂的WHERE子句。简单的在几个字段上加上索引如果没有起到什么帮助，那么，这时候你就该更加贴近的去关注你查询语句本身了。

本文由陌上花开创作，采用知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: Jul 28, 2016 at 12:29 am