如何使用 EXPLAIN 分析 Azure Database for MySQL 中的查询性能How to use EXPLAIN to profile query performance in Azure Database for MySQL

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

EXPLAIN 是一个可用来优化查询的易用工具。EXPLAIN is a handy tool to optimize queries. 可以使用 EXPLAIN 语句来获取有关 SQL 语句执行情况的信息。EXPLAIN statement can be used to get information about how SQL statements are executed. 下面的输出显示了 EXPLAIN 语句的一个执行示例。The following output shows an example of the execution of an EXPLAIN statement.

mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 10.00
        Extra: Using where

如此示例中所示,key 的值为 NULL。As can be seen from this example, the value of key is NULL. 此输出表明,MySQL 找不到针对查询优化的任何索引,并且它执行全表扫描。This output means MySQL cannot find any indexes optimized for the query and it performs a full table scan. 让我们通过添加一个基于 ID 列的索引来优化此查询。Let's optimize this query by adding an index on the ID column.

mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

新的 EXPLAIN 表明,MySQL 现在使用索引将行数限制为 1,并因此显著缩短了搜索时间。The new EXPLAIN shows that MySQL now uses an index to limit the number of rows to 1, which in turn dramatically shortened the search time.  

涵盖索引Covering index

涵盖索引在索引中包含了查询中的所有列以减少从数据表进行的值检索。A covering index consists of all columns of a query in the index to reduce value retrieval from data tables. 下面的 GROUP BY 语句中进行了展示。Here's an illustration in the following GROUP BY statement.  

mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

如输出所示,MySQL 未使用任何索引,因为没有正确的索引可用。As can be seen from the output, MySQL does not use any indexes because no proper indexes are available. 它还显示了 Using temporary; Using file sort,这意味着 MySQL 创建一个临时表来满足 GROUP BY 子句。It also shows Using temporary; Using file sort, which means MySQL creates a temporary table to satisfy the GROUP BY clause.   单独基于 c2 列创建索引没有任何区别,并且 MySQL 仍然需要创建一个临时表:Creating an index on column c2 alone makes no difference, and MySQL still needs to create a temporary table:

mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

在本例中,可以同时基于 c1c2 创建一个涵盖索引,从而将 c2 的值直接添加到索引中以避免进一步的数据查找。In this case, a covered index on both c1 and c2 can be created, whereby adding the value of c2" directly in the index to eliminate further data lookup.

mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: covered
          key: covered
      key_len: 108
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using index

如上面的 EXPLAIN 所表明,MySQL 现在使用涵盖索引并避免了创建临时表。As the above EXPLAIN shows, MySQL now uses the covered index and avoid creating a temporary table.

组合索引Combined index

组合索引由来自多个列的值组成,并且可以包含行数组,其中的行按已编制索引的列的串联值排序。A combined index consists values from multiple columns and can be considered an array of rows that are sorted by concatenating values of the indexed columns. 可以在 GROUP BY 语句中使用此方法。 This method can be useful in a GROUP BY statement.

mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using filesort

MySQL 执行“文件排序”操作时非常缓慢,尤其是必须对大量行进行排序时。 MySQL performs a file sort operation that is fairly slow, especially when it has to sort many rows. 若要优化此查询,可以基于要排序的两个列创建一个组合索引。To optimize this query, a combined index can be created on both columns that are being sorted.

mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_sort2
      key_len: 108
          ref: NULL
         rows: 10
     filtered: 11.11
        Extra: Using where; Using index

EXPLAIN 现在表明,MySQL 能够使用组合索引避免额外的排序,因为该索引已排序。The EXPLAIN now shows that MySQL is able to use combined index to avoid additional sorting since the index is already sorted.  

结论Conclusion

  使用 EXPLAIN 和各种类型的索引可以显著提高性能。Using EXPLAIN and different type of Indexes can increase performance significantly. 表上有索引并不一定意味着 MySQL 能够将其用于查询。Having an index on the table does not necessarily mean MySQL would be able to use it for your queries. 请始终使用 EXPLAIN 来验证假设并使用索引优化查询。Always validate your assumptions using EXPLAIN and optimize your queries using indexes.

后续步骤Next steps

  • 若要查找同行对你最关心问题的解答,或者要发布新的问题/答案,请访问 MSDN 论坛Stack OverflowTo find peer answers to your most concerned questions or post a new question/answer, visit MSDN forum or Stack Overflow.