你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn

通过使用 EXPLAIN 分析 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 来验证假设,并请使用索引来优化查询。

下一步