通过使用 EXPLAIN 分析 Azure Database for MySQL 灵活服务器中的查询性能

适用于：Azure Database for MySQL - 单一服务器 Azure Database for MySQL - 灵活服务器

EXPLAIN 是一个方便的工具，可帮助你优化查询。 可以使用 EXPLAIN 语句来获取有关 SQL 语句运行情况的信息。 下面显示了运行 EXPLAIN 语句的示例输出。

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

在此示例中，键的值为 NULL，这意味着 Azure Database for MySQL 灵活服务器找不到针对查询优化的任何索引。 因此，它会执行全表扫描。 让我们通过添加 ID 列的索引来优化此查询，然后再次运行 EXPLAIN 语句。

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

现在，输出显示 Azure Database for MySQL 灵活服务器使用索引将行数限制为 1，这会大大缩短搜索时间。

涵盖索引

涵盖索引包含查询的所有列，减少从数据表进行的值检索。 以下 GROUP BY 语句和相关输出说明了这一点。

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

输出显示 Azure Database for MySQL 灵活服务器不使用任何索引，因为适当的索引不可用。 输出还显示 Using temporary; Using filesor，指示 Azure Database for MySQL 灵活服务器会创建一个临时表来满足 GROUP BY 子句。

仅对 c2 列创建索引没有区别，Azure Database for MySQL 灵活服务器仍需要创建临时表：

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

在本例中，可以同时基于 c1 和 c2 创建一个涵盖索引，方法是将 c2 的值直接添加到索引中以避免进一步的数据查找。

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 的输出所示，Azure Database for MySQL 灵活服务器现在使用涵盖的索引，并避免创建临时表。

组合索引

组合索引由来自多个列的值组成，并且可以包含行数组，其中的行按已编制索引的列的串联值排序。 可以在 GROUP BY 语句中使用此方法。

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

Azure Database for MySQL 灵活服务器执行文件排序操作相当慢，尤其是在必须对多行进行排序时。 若要优化此查询，请基于要排序的两个列创建一个组合索引。

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 语句的输出现在显示，Azure Database for MySQL 灵活服务器会使用组合索引来避免额外的排序，因为索引已经排序好了。

结论

可以通过将 EXPLAIN 与不同类型的索引一起使用来显著提高性能。 对表编制索引并不一定意味着 Azure Database for MySQL 灵活服务器可以将其用于查询。 请始终使用 EXPLAIN 来验证假设，并请使用索引来优化查询。

后续步骤

• 若要查找同行对你的最重要问题的回答，或者要发布或回答问题，请访问 Stack Overflow。