在点滴中成长

【译】用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 - 当前的SELECTJOIN查询的最外层查询

    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 - 一个被用于查询特殊范围内的匹配记录的索引,一般而言,当列键与一个常量比较会用类似于BETWEENIN>>=等等这样的式子。

    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_keyskey的值都是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,否则没什么作用。

让我们再来看一个例子。这里我们将基于两个表的联合查询,分别是productsproductvariants,每个都会和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)

你可以看出在这次查询中出现了大量的问题,它扫描了productsproductvariants表中的所有的数据,因为在这些表中并没有为productLinebuyPrice这两列做索引,possible_keyskey这两列的输出是null,在UNIONproductsproductvariants表的状态被检查了,所以将它们从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子句。简单的在几个字段上加上索引如果没有起到什么帮助,那么,这时候你就该更加贴近的去关注你查询语句本身了。

发表新评论