Explain命令在优化查询中的实际应用

在 MySQL 中，EXPLAIN 命令是一种非常重要的查询优化工具，它可以帮助我们分析 SQL 查询语句的执行计划，以及如何优化它们。在使用 EXPLAIN 命令时，我们可以得到一系列重要的参数，这些参数代表着查询执行的各个阶段的细节，了解这些参数的含义对于 SQL 查询优化至关重要。在本篇文章中，我将会详细讲解 EXPLAIN 命令中各个参数的含义。

首先，我们来看一个简单的示例：

EXPLAIN SELECT * FROM `users` WHERE `id` = 1;

这条 SQL 查询语句查询了 users 表中 id 等于 1 的行。下面是这条 SQL 语句的 EXPLAIN 结果：

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

接下来，我们将逐一分析每一个字段的含义。

id

id 是一个唯一标识符，用于区分每个 SELECT 语句。在一个复杂的查询中，可能会包含多个 SELECT 语句，每个 SELECT 语句都会有一个不同的 id。在 EXPLAIN 的输出结果中，如果 id 相同，那么这些查询将被认为是相互关联的。

select_type

select_type 表示查询类型。下面是 select_type 可能出现的取值及其含义：

• SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION 查询；

• PRIMARY：最外层的 SELECT 查询；

• SUBQUERY：子查询中的第一个 SELECT 查询，该查询在最终结果中返回一个值，用于作为主查询的条件；

• DEPENDENT SUBQUERY：依赖于外部查询的子查询，子查询中的 SELECT 查询会被重复执行；

• DERIVED：派生表，查询中包含子查询作为 FROM 子句的一部分；

• UNION：UNION 中的第二个及后面的 SELECT 查询；

• DEPENDENT UNION：依赖于外部查询的 UNION 查询；

• UNION RESULT：UNION 的结果集；

• DEPENDENT UNION RESULT：依赖于外部查询的 UNION 结果集。

table

table 表示查询涉及的表

partitions

partitions 表示查询涉及的分区。

type

type 表示 MySQL 执行查询时采用的访问类型。下面是 type 可能出现的取值及其含义：

• system：仅包含一行的表，系统表（例如 MySQL 中的 mysql.user 表）；

• const：仅查询一行，基于主键或唯一索引的等值查询（例如 id = 1）；

• eq_ref：使用唯一索引或主键从单个表中查询一行数据；

• ref：使用非唯一索引从单个表中查询多行数据；

• fulltext：全文搜索；

• ref_or_null：类似于 ref，但是还包含 NULL 值；

• index_merge：使用多个索引合并结果，比如使用 OR 来连接多个索引；

• unique_subquery：使用 IN 或 EXISTS 进行子查询；

• index_subquery：使用 IN 或 EXISTS 进行子查询，但是子查询使用了索引；

• range：查询范围内的行，使用一个索引进行查找；

• index：全表扫描，但是只遍历索引树；

• ALL：全表扫描。

在优化查询时，我们通常希望避免出现 ALL、index 或 fulltext 这样的访问类型，而是希望查询能够使用更加高效的索引访问方式，例如 eq_ref、ref 或 range。

possible_keys

possible_keys 表示 MySQL 可能使用的索引列表。

key

key 表示 MySQL 实际使用的索引。

key_len

key_len 表示索引使用的字节数。

ref

ref 表示查询使用的索引列或常量。

rows

rows 表示 MySQL 估计需要扫描的行数。

filtered

filtered 表示结果集的行占全部匹配行的比例。如果 filtered 很小，说明查询的结果集很小。

Extra

Extra 字段包含了执行查询的额外信息，通常包括以下信息：

• Using where：表示 MySQL 会在存储引擎层面使用 WHERE 子句来过滤结果集；

• Using index：表示 MySQL 使用了覆盖索引来查询数据，不需要访问表；

• Using temporary：表示 MySQL 在查询过程中需要使用临时表；

• Using filesort：表示 MySQL 需要对结果集进行排序；

• Using join buffer：表示 MySQL 需要使用连接缓存区；

• Impossible where：表示 WHERE 子句总是返回 false；

• Select tables optimized away：表示 MySQL 可以在查询过程中删除未引用的表；

• No tables used：表示查询不需要访问任何表。

怎么优化查询？

通过 EXPLAIN 命令输出的结果，我们可以判断查询的瓶颈在哪里，然后进行优化。通常，我们可以从以下几个方面入手：

• 选择合适的索引

在 EXPLAIN 输出中，可以看到 possible_keys 和 key 字段，它们分别表示可能使用的索引和实际使用的索引。如果 key 字段是 NULL，那么说明查询没有使用任何索引，这是需要优化的重点。为了提高查询效率，我们应该尽可能地使用索引，而不是全表扫描。

在选择索引时，我们需要根据查询条件的类型和频率来选择合适的索引。通常来说，可以选择与 WHERE 子句中使用的条件完全匹配的索引。如果查询中有多个条件，那么可以选择多个条件的交集（AND）或并集（OR）的索引。另外，也可以使用联合索引来覆盖多个查询条件。

在选择索引时，我们还需要注意一些性能问题。例如，我们应该选择基于数据密度较高的列的索引，避免使用字符串类型的索引，避免使用过多的联合索引等。

• 减少数据访问

在 EXPLAIN 输出中，可以看到 type 字段，它表示 MySQL 执行查询时采用的访问类型。如果 type 字段是 ALL 或 index，那么说明查询需要进行全表扫描，这是需要优化的重点。为了提高查询效率，我们需要尽可能地避免全表扫描。

一种减少数据访问的方法是使用覆盖索引。覆盖索引是指查询只需要从索引中读取数据，而不需要回到数据表中查找其他数据。使用覆盖索引可以避免 MySQL 进行全表扫描，从而大大提高查询效率。

为了使用覆盖索引，我们需要选择合适的索引，并将查询所需的所有列都包含在索引中。如果索引中的列不能满足查询的需求，那么 MySQL 就需要回到数据表中查找其他数据，从而导致性能下降。

• 减少排序和分组

在 EXPLAIN 输出中，可以看到 Extra 字段，它表示 MySQL 需要进行的额外操作。如果 Extra 字段中出现了 Using filesort 或 Using temporary，那么说明查询需要进行排序或分组，这是需要优化的重点。为了提高查询效率，我们需要尽可能地减少排序和分组操作。

一种减少排序和分组的方法是使用索引。通过选择合适的索引，我们可以避免 MySQL 进行排序和分组操作，从而提高查询效率。另外，我们也可以使用 ORDER BY 和 GROUP BY 子句来明确排序和分组的顺序，避免 MySQL 进行额外的操作。

• 避免隐式类型转换

在 EXPLAIN 输出中，可以看到 type 字段和 key 字段。如果这些字段中出现了 Using where，那么说明查询需要使用 WHERE 子句进行过滤。在进行 WHERE 过滤时，MySQL 可能会对查询条件进行隐式类型转换，从而导致性能下降。

为了避免隐式类型转换，我们应该在查询条件中使用与数据类型相同的值。例如，如果某个列的数据类型是整数，那么我们应该使用整数值进行查询，而不是字符串值或浮点数值。

• 减少查询次数

在 EXPLAIN 输出中，可以看到 rows 字段和 Extra 字段。如果这些字段中出现了 Using index，那么说明查询可以通过索引直接返回结果，而不需要回到数据表中进行查询。这种情况下，查询次数将会减少，从而提高查询效率。

为了减少查询次数，我们应该尽可能地使用索引，并避免在查询中使用子查询、联合查询等复杂的查询语句。此外，我们也可以使用缓存技术来减少查询次数，例如使用 Memcached 等内存缓存工具。

来源：https://juejin.cn/post/7223545617763205176