MySQL高级讲座篇之：读懂`EXPLAIN`：从执行计划中洞察查询瓶颈的艺术。

各位朋友，大家好！我是今天的主讲人，咱们今天聊聊MySQL的EXPLAIN，这玩意儿就像医生的CT扫描，能帮咱们诊断SQL语句的健康状况，找出潜在的性能问题。别看它输出的东西密密麻麻的，掌握了它，优化SQL就像开了上帝视角，指哪儿打哪儿！

一、 EXPLAIN 是个啥？

EXPLAIN 语句是MySQL提供的一个非常有用的工具，它可以显示MySQL如何执行一条SQL语句。通过分析EXPLAIN的输出结果，我们可以了解MySQL优化器是如何工作的，并根据这些信息来优化SQL语句，从而提高查询性能。简单来说，就是让MySQL自己“坦白”，告诉我们它打算怎么干活，我们再看看它干活的姿势对不对。

二、 怎么用 EXPLAIN？

使用方法很简单，在你想要分析的SQL语句前面加上 EXPLAIN 即可。例如：

EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'Beijing';

执行这条语句后，MySQL会返回一个表格，里面包含了关于查询执行计划的各种信息。

三、 EXPLAIN 输出结果详解

EXPLAIN 的输出结果包含多个列，每个列都代表了查询执行过程中的一个方面。下面我们逐个分析这些列的含义。

列名	含义	可能的值	优化建议
id	查询的标识符。如果查询中包含子查询，则每个子查询都会有一个唯一的 id。id 值越大，查询的优先级越高。如果 id 相同，则执行顺序从上到下。	正整数，NULL (用于 UNION RESULT)	无
select_type	查询的类型。它描述了查询中每个 SELECT 子句的类型。	SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT, DEPENDENT SUBQUERY, DEPENDENT UNION, MATERIALIZED	尽量避免复杂的子查询，可以将子查询转换为 JOIN 操作。
table	查询涉及的表名。如果查询涉及多个表，则会显示多个表名。	表名，别名，<derivedN> (表示 id 为 N 的查询结果)，<unionM,N> (表示 id 为 M 和 N 的查询结果的 UNION)	检查表名是否正确，是否使用了别名。
partitions	查询涉及的分区。如果表是分区表，则会显示查询涉及的分区。	分区名，NULL (如果表不是分区表)	如果查询没有使用分区，可以考虑添加分区。
type	访问类型。这是最重要的列之一，它描述了MySQL如何查找表中的行。值越好，查询性能越高。	system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL	尽量将 type 优化到 range 或更好。避免 ALL (全表扫描) 和 index (全索引扫描)。
possible_keys	MySQL可以使用哪些索引来查找行。	索引名，NULL (如果没有可用的索引)	如果 possible_keys 有值，但 key 为 NULL，说明MySQL认为使用索引不如全表扫描。可以尝试强制使用索引。
key	MySQL实际选择使用的索引。	索引名，NULL (如果没有使用索引)	如果 key 为 NULL，说明MySQL没有使用索引。可以考虑添加索引或优化查询条件。
key_len	索引的长度，表示MySQL在索引里使用的字节数。通过这个值，可以计算具体使用了索引中的哪些列。	整数	了解索引长度有助于判断是否充分利用了索引。如果只使用了索引的一部分，可以考虑创建更合适的索引。
ref	显示索引的哪一列被使用了。如果可能的话，是一个常数。	列名，const (表示常量)，NULL	检查 ref 列是否正确，是否使用了常量或列名。
rows	MySQL估计需要扫描的行数。这个值只是一个估计值，并不一定准确。	整数	尽量减少 rows 的值，可以通过添加索引、优化查询条件等方式实现。
filtered	表示返回结果的行数占需要读取行数的百分比。filtered 的值越大越好。	0 到 100 的整数	如果 filtered 的值很小，说明查询条件过滤性较差，可以考虑优化查询条件。
Extra	包含MySQL解决查询的额外信息。	各种字符串，如 Using index, Using where, Using temporary, Using filesort, Using join buffer (Block Nested Loop), Impossible WHERE noticed after reading const tables, No tables used, Not exists	Extra 列的信息非常重要，可以帮助我们了解查询的瓶颈。

下面我们来详细解释几个重要的列：

• type (访问类型):

  • system: 表只有一行记录（等于系统表），这是 const 类型的一个特例，平时不会出现，可以忽略不计。
  • const: 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。例如：SELECT * FROM users WHERE id = 1; (假设 id 是 PRIMARY KEY 或者 UNIQUE 索引)。
  • eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引的关联查询。例如：SELECT * FROM orders o JOIN users u ON o.user_id = u.id; (假设 u.id 是 PRIMARY KEY)。
  • ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。例如：SELECT * FROM users WHERE city = 'Beijing'; (假设 city 是一个普通索引)。
  • range: 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 WHERE 语句中出现了 BETWEEN、<、>、IN 等的查询。例如：SELECT * FROM users WHERE age BETWEEN 20 AND 30; (假设 age 是索引)。
  • index: 全索引扫描，index 与 ALL 的区别为 index 类型只遍历索引树。通常比 ALL 快，因为索引文件通常比数据文件小。
  • ALL: 全表扫描，MySQL 必须扫描整张表才能找到所需的行。这是最差的一种情况，应该尽量避免。

• Extra (额外信息):

  • Using index: 表示查询使用了覆盖索引，避免了回表查询，性能很高。覆盖索引是指查询的字段都在索引中，不需要再到数据表中查找数据。例如：SELECT id, name FROM users WHERE city = 'Beijing'; (假设 (city, id, name) 是一个联合索引)。
  • Using where: 表示MySQL服务器在存储引擎收到行后进行“后过滤”（Post-filter）。这意味着 MySQL 使用 WHERE 子句来过滤结果集。这通常发生在没有合适的索引可以使用的情况下。
  • Using temporary: 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。这通常是一个性能瓶颈，应该尽量避免。例如：SELECT city, COUNT(*) FROM users GROUP BY city; (如果 city 没有索引)。
  • Using filesort: 表示MySQL需要对结果集进行外部排序，而不是使用索引排序。这通常是一个性能瓶颈，应该尽量避免。例如：SELECT * FROM users ORDER BY age; (如果 age 没有索引)。
  • Using join buffer (Block Nested Loop): 使用连接缓存来加速 JOIN 操作。 这通常发生在 JOIN 的驱动表没有合适的索引可以使用的情况下。

四、 优化案例分析

下面我们通过几个案例来演示如何使用 EXPLAIN 来优化SQL语句。

案例 1： 全表扫描 (ALL)

假设我们有以下SQL语句：

SELECT * FROM orders WHERE order_date = '2023-10-26';

执行 EXPLAIN 后，我们发现 type 是 ALL，表示全表扫描。这意味着MySQL需要扫描整个 orders 表才能找到符合条件的行。

EXPLAIN SELECT * FROM orders WHERE order_date = '2023-10-26';

输出结果（简化）：

| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra |
|----|-------------|--------|------|---------------|------|---------|------|-------|-------|
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 10000 | Using where |

优化方案：

在 order_date 列上创建索引：

CREATE INDEX idx_order_date ON orders (order_date);

再次执行 EXPLAIN，我们发现 type 变成了 ref，表示使用了索引，性能大大提高。

EXPLAIN SELECT * FROM orders WHERE order_date = '2023-10-26';

输出结果（简化）：

| id | select_type | table  | type | possible_keys | key            | key_len | ref   | rows | Extra |
|----|-------------|--------|------|---------------|----------------|---------|-------|------|-------|
| 1  | SIMPLE      | orders | ref  | idx_order_date | idx_order_date | 3       | const | 100  | NULL  |

案例 2： 使用临时表和文件排序 (Using temporary, Using filesort)

假设我们有以下SQL语句：

SELECT city, AVG(age) FROM users GROUP BY city ORDER BY AVG(age) DESC;

执行 EXPLAIN 后，我们发现 Extra 列包含 Using temporary 和 Using filesort，表示使用了临时表和文件排序。这意味着MySQL需要先创建一个临时表来存储分组结果，然后再对临时表进行排序，性能很差。

EXPLAIN SELECT city, AVG(age) FROM users GROUP BY city ORDER BY AVG(age) DESC;

输出结果（简化）：

| id | select_type | table | type | possible_keys | key  | key_len | ref | rows | Extra                                     |
|----|-------------|-------|------|---------------|------|---------|-----|------|-------------------------------------------|
| 1  | SIMPLE      | users | ALL  | NULL          | NULL | NULL    |     | 1000 | Using temporary; Using filesort           |

优化方案：

创建覆盖索引，包含 city 和 age 列：

CREATE INDEX idx_city_age ON users (city, age);

再次执行 EXPLAIN，我们发现 Extra 列只包含 Using index，表示使用了覆盖索引，避免了临时表和文件排序，性能大大提高。但是，Using index 只是避免了回表，Using temporary 和 Using filesort 仍然存在。我们需要更进一步的优化。

为了避免 Using temporary 和 Using filesort，我们需要让MySQL可以直接使用索引进行排序和分组。这需要修改SQL语句，并创建一个更合适的索引。

ALTER TABLE users ADD COLUMN avg_age DECIMAL(10,2) GENERATED ALWAYS AS ( (SELECT AVG(age) FROM users AS u2 WHERE u2.city = users.city) ) STORED;

CREATE INDEX idx_city_avg_age ON users (city, avg_age);

EXPLAIN SELECT city, avg_age FROM users GROUP BY city ORDER BY avg_age DESC;

由于MySQL的限制，直接在GROUP BY和ORDER BY中使用函数计算的值通常很难优化，所以我们这里使用生成列（Generated Column）预先计算好平均年龄，并创建一个基于city和avg_age的索引。这样，查询就可以直接使用索引进行分组和排序，避免了临时表和文件排序。 (注意: 实际生产环境中生成列需要谨慎评估对写入性能的影响)

案例 3： 子查询 (SUBQUERY, DEPENDENT SUBQUERY)

假设我们有以下SQL语句：

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Beijing');

执行 EXPLAIN 后，我们发现 select_type 包含 SUBQUERY 或 DEPENDENT SUBQUERY，表示使用了子查询。子查询的性能通常比较差，特别是当子查询的结果集很大时。

EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Beijing');

输出结果（简化）：

| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
|----|--------------------|--------|------|---------------|------|---------|------|------|-------------|
| 1  | PRIMARY            | orders | ALL  | user_id       | NULL | NULL    | NULL | 1000 | Using where |
| 2  | DEPENDENT SUBQUERY | users  | ref  | idx_city      | idx_city | 152   | const| 100  | Using index |

优化方案：

将子查询转换为 JOIN 操作：

SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Beijing';

再次执行 EXPLAIN，我们发现 select_type 变成了 SIMPLE，表示使用了 JOIN 操作，性能大大提高。

EXPLAIN SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Beijing';

输出结果（简化）：

| id | select_type | table  | type | possible_keys | key      | key_len | ref             | rows | Extra       |
|----|-------------|--------|------|---------------|----------|---------|-----------------|------|-------------|
| 1  | SIMPLE      | u      | ref  | idx_city      | idx_city | 152     | const           | 100  | Using index |
| 1  | SIMPLE      | o      | ref  | user_id       | user_id  | 4       | u.id            | 10   | Using where |

五、 总结

EXPLAIN 是一个强大的工具，可以帮助我们了解MySQL如何执行SQL语句，并找出潜在的性能问题。通过分析EXPLAIN的输出结果，我们可以根据实际情况进行优化，例如添加索引、优化查询条件、避免子查询、避免临时表和文件排序等，从而提高查询性能。

记住，优化SQL是一个持续的过程，需要不断地学习和实践。 掌握 EXPLAIN 只是第一步，更重要的是理解MySQL的内部工作原理，才能写出高效的SQL语句。

希望今天的分享对大家有所帮助！ 咱们下次再见！