如何分析一条 SQL 的性能-HQY 一个和谐有爱的空间

09

2024
05
10:22:37

如何分析一条 SQL 的性能

这篇文章将深入解析如何使用 EXPLAIN 来分析一条 SQL 查询。旨在通过实例与原理的结合，提供更加清晰和深入的理解。

EXPLAIN 在其字面上意味着“解释”，而在 MySQL 的上下文中，它被称为执行计划。这个命令可以展示 MySQL 在优化器分析后决定如何执行特定的 SQL 查询。

MySQL 配备了一个功能强大的优化器。这个优化器的核心任务是对执行的 SQL 进行进一步的优化，以尽可能降低执行成本。

什么时候应该使用 EXPLAIN 呢？

大多数情况下，当从 MySQL 的慢查询日志中识别出某些执行效率较低的 SQL 查询时，EXPLAIN 会派上用场。

此外，在进行数据库优化时，例如添加索引，通过 EXPLAIN 可以分析新添加的索引是否得到了有效利用。

在业务开发中，当需要在满足需求的同时选择更高效的 SQL 时，EXPLAIN 同样是一个有力的工具。

使用 EXPLAIN 相当简单。只需在 SQL 查询前加上 EXPLAIN 关键字即可，如下所示：

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

本文不会详细解析`EXPLAIN`返回的每一个字段，因为内容太多，可能不容易记住。只需要重点关注几个关键字段：`type`、`key`、`rows`和`Extra`，并通过实例来更好地理解它们的含义。

简单介绍一下这几个字段的基本含义。

`type`字段表示MySQL如何访问数据。常见的访问方式有全表扫描（all）、遍历索引（index）、范围查询（range）、常量或等值查询（ref、eq_ref）、主键等值查询（const）以及当表中只有一条记录时的系统查询（system）。

从效率角度来看，这些查询方式有一个从最佳到最差的排序。

system > const > eq_ref > ref > range > index > all

`key` 指的是在查询过程中实际被使用的索引名称。

`rows` 反映了查询过程中可能涉及的行数，这一数据是由MySQL抽样统计得出的，因此并不绝对精确。

`Extra` 列提供了额外的信息，通常用于指示是否利用了索引、是否需要排序操作，或者是否使用了临时表等。

接下来，我们将通过实例来深入分析这些概念。

表的结构如下，数据量10条：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

然后看下面这条查询语句，注意这个表目前只有一个主键索引，还没有创建普通索引。

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

`type` 的值为 `ALL` 表明进行了全表扫描。

`rows` 字段显示有 100,332 条数据，而实际上我们的数据总量只有 10 万条。这意味着这个字段只是 MySQL 的一个预估，并不完全准确。

全表扫描的效率非常低，是我们需要重点优化的对象。

接下来，我们将分别为字段 `a` 和 `b` 添加普通索引，并观察添加索引后 SQL 语句的表现。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

上面提到的 SQL 语句中 `type` 显示为全表扫描（`ALL`），即使我们为字段 `a` 添加了索引（`a_index`），并且 `possible_keys` 也显示了该索引是可用的。但 `key` 显示为 `null`，意味着 MySQL 在实际执行查询时并没有使用这个索引。

原因在于，当使用 `SELECT *` 时，除了从索引 `a_index` 中筛选数据外，还需要回到主键索引中去查找字段 `b` 的值，因为字段 `b` 并没有包含在索引 `a_index` 中。这种回到主键索引查找的过程被称为“回表”。

在这个例子中，有大约 9 万条记录满足条件，意味着需要进行 9 万次回表操作。

由于全表扫描大约涉及 10 万条数据，而使用索引并进行回表操作会涉及 9 万次额外的查找，从 MySQL 优化器的角度来看，全表扫描的代价可能更低，因为它避免了额外的回表操作。

但这并不意味着只要有回表操作，索引就不会被使用。

是否使用索引取决于 MySQL 优化器对查询代价的评估。我们可以通过调整 SQL 语句来影响优化器的决策。

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

这次，`type` 的值变为了 `range`，而 `key` 为 `a_index`，这表示 `a` 索引被成功命中了。满足这条 SQL 查询条件的数据仅有 1000 条。MySQL 评估后认为，即使需要回表操作，这 1000 条数据的回表代价也低于全表扫描。

进一步观察，`Extra` 字段中的值为 `Using index condition`。这表示虽然索引被用到了，但仍然需要回表操作来获取更多的数据。

再看下面的 SQL 语句。

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

这个 `Extra` 中的值为 `Using where; Using index` ，表示查询用到了索引，且要查询的字段在索引中就能拿到，不需要回表，显然这种效率比上面的要高，所以不要轻易写 `SELECT *` ，只查询业务需要的字段即可，这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 `Extra` 中返回了一个 `Using filesort`，意味着需要排序，也就是说查到数据后，还需要 MySQL 在内存中对其进行排序，而索引本身就是有序的，所以一般来讲要尽量利用索引的有序性，比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | a_index | 5       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | ab_index | 5       | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)