MySQL架构与底层原理之：`MySQL`的`explain`命令：如何从`extra`、`type`和`key`看懂执行计划。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 执行计划解读：从 Extra、Type 和 Key 理解查询优化

大家好，今天我们来深入探讨 MySQL 的 EXPLAIN 命令，它能帮助我们理解 MySQL 如何执行我们的 SQL 查询，并为我们优化查询提供重要的线索。我们将重点关注 EXPLAIN 输出中的 Extra、Type 和 Key 这三个关键列，学习如何利用它们来识别潜在的性能瓶颈。

什么是执行计划？

在 MySQL 执行一个 SQL 查询之前，优化器会根据查询的结构、索引、数据分布等因素，生成一个执行计划。这个执行计划描述了 MySQL 将如何访问表、使用索引、连接表以及执行排序等操作。EXPLAIN 命令就是用来显示这个执行计划的。

如何使用 `EXPLAIN`

在 SQL 查询语句前加上 EXPLAIN 关键字即可：

EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'New York';

EXPLAIN 命令的输出会包含多列信息，其中最关键的几列是：

id: 查询的标识符，用于区分复杂的查询计划中的不同部分。
select_type: 查询的类型，例如 SIMPLE、PRIMARY、SUBQUERY 等。
table: 查询访问的表名。
partitions: 查询将访问的分区（如果表进行了分区）。
type: 访问类型，这是优化器选择的访问数据的方式，这是我们关注的重点之一。
possible_keys: 可能用到的索引。
key: 实际用到的索引，这是我们关注的重点之一。
key_len: 使用的索引的长度（字节）。
ref: 将哪些常量或者字段与索引进行比较。
rows: MySQL 估计需要扫描的行数。
filtered: 根据 WHERE 条件过滤后，剩余的行数百分比。
Extra: 包含的额外信息，例如是否使用了临时表、是否使用了文件排序等，这是我们关注的重点之一。

接下来，我们将逐一深入探讨 Type、Key 和 Extra 这三列。

`Type` 列：访问类型详解

Type 列描述了 MySQL 如何查找表中的行。理想情况下，我们希望 Type 尽可能接近 const，这意味着查询效率最高。以下是一些常见的 Type 值，按性能从好到差排列：

system: 表只有一行记录（等于系统表），这是 const 类型的一种特殊情况，通常出现在非常简单的查询中。
const: MySQL 可以通过主键或者唯一索引一次定位到目标行。这是一种非常高效的访问类型。
```
-- 假设 users 表的 id 是主键
EXPLAIN SELECT * FROM users WHERE id = 1;
```
eq_ref: 对于来自之前表的每一行，在此表中最多检索一行。这通常发生在主键或唯一索引的连接查询中。
```
-- 假设 orders 表有 user_id 列，并且 users 表的 id 是主键
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
```
ref: 对于来自之前表的每一行，在此表中检索匹配某个值的行。这通常发生在非唯一索引的查找中。
```
-- 假设 users 表的 city 列有索引
EXPLAIN SELECT * FROM users WHERE city = 'New York';
```

ref_or_null: 类似于 ref，但是 MySQL 需要额外搜索包含 NULL 值的行。

-- 假设 users 表的 city 列有索引
EXPLAIN SELECT * FROM users WHERE city = 'New York' OR city IS NULL;

index_merge: 使用了多个索引来优化查询。当单个索引无法满足查询条件时，MySQL 可能会合并多个索引的结果。
```
-- 假设 users 表的 age 和 city 列分别有索引
EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'New York';
```
如果看到 index_merge，通常意味着索引设计可能需要改进，考虑创建一个组合索引。

unique_subquery: 在 IN 子查询中使用唯一索引。

-- 假设 orders 表的 user_id 列有索引, users 表的id是主键
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

index_subquery: 类似于 unique_subquery，但是使用非唯一索引。

-- 假设 orders 表的 user_id 列有索引, users 表的status有索引
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

range: 使用索引进行范围查找。

-- 假设 users 表的 age 列有索引
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

index: 扫描整个索引树来查找行。这通常发生在查询只访问索引列，或者需要排序的时候。虽然比全表扫描快，但仍然不是最优选择。
```
-- 假设 users 表的 city 列有索引
EXPLAIN SELECT city FROM users ORDER BY city;
```
ALL: 全表扫描。这是最差的访问类型，意味着 MySQL 需要扫描整个表来找到匹配的行。应该尽量避免。
```
EXPLAIN SELECT * FROM users WHERE city = 'New York'; -- 如果 city 没有索引
```

`Key` 列：实际使用的索引

Key 列显示了 MySQL 实际使用的索引。如果该列为 NULL，则表示 MySQL 没有使用任何索引。优化查询的一个关键目标就是让 Key 列显示你期望使用的索引。

例如，如果我们创建一个 city 索引，再次运行上面的查询：

CREATE INDEX idx_city ON users(city);

EXPLAIN SELECT * FROM users WHERE city = 'New York';

现在，Key 列应该显示 idx_city，表明 MySQL 正在使用这个索引。

`Extra` 列：额外信息解读

Extra 列包含关于 MySQL 如何执行查询的额外信息。这些信息可以帮助我们识别潜在的性能问题。以下是一些常见的 Extra 值及其含义：

Using index: MySQL 可以仅通过索引就能满足查询，无需访问表中的实际数据行。这被称为 "覆盖索引"（covering index），是非常高效的。
```
-- 假设 users 表的 city 列有索引
EXPLAIN SELECT city FROM users WHERE city = 'New York';
```
要实现覆盖索引，查询必须只访问索引包含的列。
Using where: MySQL 使用 WHERE 子句来过滤结果集。这通常发生在没有使用索引或者索引无法完全满足 WHERE 子句的条件时。
```
EXPLAIN SELECT * FROM users WHERE age > 30; -- 如果 age 没有索引
```
Using temporary: MySQL 需要创建一个临时表来存储中间结果。这通常发生在 GROUP BY 或 ORDER BY 子句中，如果无法使用索引来优化排序或者分组，就会创建临时表。创建临时表会消耗额外的资源，应该尽量避免。
```
EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city; -- 如果 city 没有索引
```
Using filesort: MySQL 需要对结果集进行文件排序。这意味着 MySQL 无法使用索引来满足 ORDER BY 子句的要求，需要将数据读取到内存或者磁盘进行排序。文件排序的性能通常很差，应该尽量避免。
```
EXPLAIN SELECT * FROM users ORDER BY age; -- 如果 age 没有索引
```
Using join buffer (Block Nested Loop): MySQL 使用了连接缓冲区来加速连接操作。这通常发生在连接的表没有合适的索引时。应该尽量为连接的列创建索引，以避免使用连接缓冲区。
```
-- 假设 orders 表的 user_id 列没有索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
```
Impossible WHERE noticed after reading const tables: 优化器发现 WHERE 子句始终为假，因此查询永远不会返回任何行。
```
EXPLAIN SELECT * FROM users WHERE 1 = 0;
```
Select tables optimized away: 查询可以被优化掉，例如，当使用 MIN() 或 MAX() 函数查询索引列时。
```
-- 假设 users 表的 age 列有索引
EXPLAIN SELECT MIN(age) FROM users;
```
Distinct: MySQL 正在寻找不同的值，因此在找到第一个匹配的行后，将停止扫描。
Not exists: MySQL 优化了 NOT EXISTS 子查询，并且可以在找到第一个匹配的行后停止搜索。

综合案例：优化查询

假设我们有一个 products 表，包含 id、name、category_id 和 price 列。我们需要查询某个分类下价格高于某个值的商品：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (name, category_id, price) VALUES
('Product A', 1, 10.00),
('Product B', 1, 20.00),
('Product C', 2, 15.00),
('Product D', 2, 25.00);

我们先执行以下查询：

EXPLAIN SELECT * FROM products WHERE category_id = 1 AND price > 15.00;

如果 category_id 和 price 列都没有索引，EXPLAIN 的输出可能会显示 Type 为 ALL，Key 为 NULL，Extra 为 Using where。这表明 MySQL 正在进行全表扫描，并且需要使用 WHERE 子句来过滤结果。

为了优化这个查询，我们可以创建一个组合索引：

CREATE INDEX idx_category_price ON products(category_id, price);

再次执行 EXPLAIN：

EXPLAIN SELECT * FROM products WHERE category_id = 1 AND price > 15.00;

现在，Type 可能会变为 range，Key 会显示 idx_category_price，Extra 可能会显示 Using index condition。这表明 MySQL 正在使用索引进行范围查找，并且可以利用索引下推（Index Condition Pushdown）来优化查询。

进一步优化

即使使用了索引，仍然可能存在优化的空间。例如，如果查询只需要 name 列，可以创建一个覆盖索引：

CREATE INDEX idx_category_price_name ON products(category_id, price, name);

EXPLAIN SELECT name FROM products WHERE category_id = 1 AND price > 15.00;

现在，Extra 可能会显示 Using index，表明查询可以完全通过索引来满足，无需访问表中的实际数据行。

总结

EXPLAIN 命令是 MySQL 性能优化的重要工具。理解 Type、Key 和 Extra 列的含义，可以帮助我们识别潜在的性能瓶颈，并采取相应的措施进行优化。通过创建合适的索引、优化查询结构和避免不必要的排序和临时表，我们可以显著提高 MySQL 的查询性能。

索引设计和查询优化的重要性

理解 EXPLAIN 的输出可以帮助我们更好地设计索引和编写更高效的 SQL 查询。针对具体的业务场景和数据特点，合理地选择索引类型和组合方式，可以最大程度地提升查询性能。

持续学习和实践

MySQL 优化是一个持续学习和实践的过程。通过不断地学习新的优化技术和工具，并结合实际的业务场景进行实践，我们可以不断提高 MySQL 的性能和可靠性。

MySQL 执行计划解读：从 Extra、Type 和 Key 理解查询优化

什么是执行计划？

如何使用 EXPLAIN

Type 列：访问类型详解

Key 列：实际使用的索引

Extra 列：额外信息解读