如何分析和优化SQL性能：深入解析EXPLAIN语句

在数据库优化中，EXPLAIN语句是一种强大的工具，用于分析SQL查询的性能。正确理解和使用EXPLAIN可以帮助开发者识别和解决性能瓶颈。本文将深入探讨EXPLAIN的使用方法，详细解读输出结果，并通过实例分析如何利用这些信息优化查询。

EXPLAIN 语句详解

EXPLAIN语句在大多数关系数据库管理系统（RDBMS）中都存在，用于获取SQL查询的执行计划。这包括查询如何使用索引，如何进行表联接，以及查询操作的成本估算等关键信息。

EXPLAIN基本用法

在使用EXPLAIN时，只需将其放在SQL查询前。例如，对于一个基本的查询：

EXPLAIN SELECT * FROM users WHERE age > 25;

此语句将输出查询的执行计划，包括使用的索引、扫描的行数等信息。

输出结果解析

下面是一个EXPLAIN输出的示例，该示例来自一个简单的查询，在一个假设的users表上执行，这个表有一个基于age字段的索引。

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

解读EXPLAIN输出的关键字段

EXPLAIN语句的输出包含多个字段，每个字段都提供了关于SQL执行计划的重要信息。下面是对这些关键字段的详细解读：

id

查询序列号，用于区分同一查询中的不同部分，如在复杂的嵌套查询中识别和关联查询执行计划的不同阶段。

select_type

查询的类型，表示查询的复杂性和范围，帮助了解查询结构，判断是否可以优化子查询或减少查询层次。常见类型包括：

• SIMPLE: 一个不涉及子查询或联接的简单查询。
• PRIMARY: 复杂查询中的主查询。
• SUBQUERY: 在SELECT或WHERE子句中的子查询。
• DERIVED: 来自派生表的查询，MySQL会将这类子查询的结果放在临时表中。

table

正在访问的表名，用于确定查询涉及的数据表，特别是对于识别涉及多个表的查询非常重要。

type

表示MySQL决定如何查找表中的行的联接类型，帮助识别查询效率，指示是否需要优化查询条件或增加/调整索引。常见类型包括：

• system: 表只有一行（=系统表），这是const联接类型的一个特例。
• const: 表示通过索引一次就能找到一行数据。
• eq_ref: 一对一联接，用于主键或唯一索引扫描。
• ref: 非唯一索引扫描，返回匹配某个单一值的所有行。
• range: 只检索给定范围的行，使用索引来选择行。
• index: 索引扫描，扫描整个索引。
• ALL: 全表扫描。

possible_keys

显示可能被查询使用的索引，提供优化索引的线索，如果这里没有列出索引，可能需要创建新的索引。

key

实际使用的索引，显示哪个索引被实际使用，有助于评估索引的效果。

key_len

使用的索引的最大可能长度，索引长度影响索引选择和性能，较短的索引通常更快。

ref

显示哪些列或常量被用于查找索引列上的值，了解索引的使用方式，评估索引的有效性。

rows

估计查询将要检查的行数，是性能指标之一，行数越多，执行查询所需的时间可能越长。

filtered

根据表条件，预计能返回结果的行数百分比，评估查询条件的效果，低百分比可能表明需要优化查询。

Extra

包含关于查询执行的额外信息，如是否使用了索引或是否进行了排序，提供查询执行的额外细节，有助于识别可能的性能问题。常见值包括：

• Using index: 仅通过索引就完成了查询，未读取表的行。
• Using temporary: 使用了临时表来处理查询，通常涉及排序操作。
• Using filesort: MySQL需要进行额外的排序步骤，没有使用索引完成排序。

使用EXPLAIN关键字段进行SQL性能优化

理解EXPLAIN输出的每个字段对于优化SQL查询至关重要。以下是如何利用这些关键信息来指导具体的SQL优化策略，以及一些实际的例子。

优化基于type字段

问题: 如果type字段显示为ALL（全表扫描），则查询性能可能非常低，特别是在大型数据集上。

优化策略:

• 增加索引: 确保查询中的条件列上有索引。例如，如果EXPLAIN显示全表扫描用于SELECT * FROM users WHERE age > 25;，可以考虑在age列上添加索引。
• 重写查询: 修改查询逻辑，减少需要扫描的数据量。比如，使用具体的列名替代SELECT *，减少返回的数据量。

利用possible_keys和key字段

问题: possible_keys列出了可能的索引，但key字段为空或选择了非最优索引。

优化策略:

• 索引选择: 确保最有可能提高性能的索引被使用。如果发现更适合的索引没有被选用，可能需要调整索引或查询结构。
• 索引调整: 如果发现EXPLAIN经常忽略某些索引，考虑删除不必要的索引或创建更适合当前查询模式的复合索引。

关注rows和filtered字段

问题: rows值过高，filtered百分比过低，表明扫描了大量不必要的行。

优化策略:

• 调整WHERE子句: 确保WHERE子句中使用的是高选择性的条件，这样可以减少扫描的行数。
• 使用更精确的联接和子查询: 优化查询中的联接条件，减少不必要的数据合并和重复扫描。

解析Extra字段

问题: Extra列包含如Using filesort或Using temporary的值，这些通常指出查询需要额外的资源来完成排序或临时表操作。

优化策略:

• 避免文件排序: 通过确保用于ORDER BY的列上有索引，可以避免外部文件排序。
• 优化分组和排序操作: 如果查询包括GROUP BY或ORDER BY子句，确保相应的列上有适当的索引。

举个栗子

假设有以下查询：

EXPLAIN SELECT name FROM users WHERE age > 25 ORDER BY signup_date;

假定EXPLAIN输出如下：

+----+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | users | ALL  | idx_age       | NULL | NULL    | NULL | 1000 |   100.00 | Using where; Using filesort     |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+

根据explain输出结果可以尝试以下优化措施:

• 添加索引: 看到key为NULL且type为ALL，可添加一个覆盖索引，包含age和signup_date，这样查询就可以使用索引完成排序而无需外部排序。
• 修改查询: 如果name列中数据量大，考虑是否可以通过调整应用逻辑来减少必须从数据库中检索的字段数量，可以加个limit。

通过这样的具体策略和实例，我们可以系统地应用EXPLAIN输出来优化SQL查询，从而提高数据库的整体性能。

总结

EXPLAIN是数据库优化工具箱中非常重要的工具。通过深入理解其输出，开发者可以识别潜在的性能问题，并采取措施进行优化。实践中，定期审核和优化SQL查询是确保数据库性能的关键步骤。

如果觉得EXPLAIN方法去优化SQL性能太复杂的话，你也可以直接利用 Chat2DB 的优化SQL的能力，不再需要掌握复杂的分析技巧。如下图所示，直接输入SQL，AI将帮你分析有哪些可以优化的点，比如哪里应该建索引，哪里最好不要用函数等等。

---

• Chat2DB 文档：https://docs.chat2db.ai/zh-CN/docs/start-guide/getting-started
• Chat2DB 官网：https://chat2db.ai/zh-CN
• Chat2DB GitHub：https://github.com/codePhiliaX/Chat2DB

本文由mdnice多平台发布