MySQL的explain执行计划你真的了解吗？

MySQL的explain执行计划你真的了解吗？

引用

CSDN

1.

https://blog.csdn.net/qq_56158663/article/details/146180071

一、什么是 MySQL 执行计划？

简单来说，MySQL 执行计划就是 MySQL 优化器对 SQL 查询语句的“预演”。它会告诉你 MySQL 打算如何执行你的 SQL 语句，包括：

• 使用哪些表
• 表的连接顺序
• 使用哪些索引
• 扫描多少行数据
• 等等

通过分析执行计划，你可以发现 SQL 语句的瓶颈，从而进行优化，提高查询效率。

二、如何查看执行计划？

在 MySQL 中，使用 EXPLAIN 关键字可以查看 SQL 语句的执行计划。

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

执行这条语句后，MySQL 会返回一个表格，表格中的每一行代表执行计划中的一个步骤。

三、执行计划的字段详解（结合例子）

我们用一个更具体的例子来讲解执行计划的各个字段。假设我们有两张表：

• users 表：包含 id (主键), name, age, city 字段
• orders 表：包含 id (主键), user_id (外键关联 users.id), order_date, amount 字段

-- 创建 users 表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    city VARCHAR(255)
);

-- 创建 orders 表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入一些示例数据
INSERT INTO users (name, age, city) VALUES
('Alice', 30, 'New York'),
('Bob', 25, 'Los Angeles'),
('Charlie', 35, 'Chicago'),
('David', 28, 'New York');

INSERT INTO orders (user_id, order_date, amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-05', 50.00),
(2, '2023-01-10', 200.00),
(3, '2023-01-15', 75.00);

-- 创建索引
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_user_id ON orders(user_id);

现在，我们执行以下 SQL 语句，并查看其执行计划：

EXPLAIN SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 27 AND u.city = 'New York';

执行 EXPLAIN 后，你可能会看到类似下面的表格：

接下来，我们逐个字段进行解释：

1. id: 查询的标识符。

• 含义：表示查询中执行 SELECT 子句或操作的顺序。如果 id 相同，则表示这些行按照从上到下的顺序执行。如果 id 不同，id 值越大，优先级越高，越先被执行。
• 可能的值：正整数。
• 解释：可以理解为 SQL 语句执行的步骤编号。如果多个步骤 id 相同，说明它们是并行执行的（或者说，MySQL 优化器认为它们可以并行执行）。

2. select_type: 查询的类型。

• 含义：描述了查询的复杂程度和类型。
• 可能的值：
• SIMPLE: 简单查询，不包含子查询或 UNION。
• PRIMARY: 最外层的 SELECT 查询。
• SUBQUERY: 子查询。
• DERIVED: 在 FROM 子句中的子查询（派生表）。
• UNION: UNION 语句中的第二个或后面的 SELECT 查询。
• UNION RESULT: 从 UNION 的匿名临时表检索结果。
• 解释：告诉我们这个查询是简单的还是复杂的，有没有用到子查询、UNION 等。

3. table: 查询涉及的表名。

• 含义：显示这一行数据是关于哪张表的。
• 可能的值：表名，或者 <derivedN>（表示 id 为 N 的查询结果）。
• 解释：就是告诉你这一步操作是针对哪个表的。

4. partitions: 查询涉及的分区。

• 含义：如果表是分区表，显示查询涉及的分区。
• 可能的值：分区名，或者 NULL（如果表没有分区）。
• 解释：如果你的表很大，并且做了分区，这个字段会告诉你用到了哪些分区，可以帮助你了解查询是否高效地利用了分区。

5. type: 访问类型，这是最重要的字段之一。

• 含义：描述了 MySQL 如何查找表中的行，也就是访问数据的方式。值越好，性能越高。
• 可能的值（从最好到最坏）：
• system: 表只有一行记录，这是 const 类型的一个特例，通常出现在系统表中。
• const: 通过主键或唯一索引一次就能找到。MySQL 可以将查询转换为常量。
• eq_ref: 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引的关联查询。
• ref: 非唯一索引扫描，返回匹配某个单独值的所有行。
• range: 索引范围扫描，常见于 BETWEEN, >, <, IN 等操作。
• index: 索引全扫描，扫描整个索引树。
• ALL: 全表扫描，扫描整个表。这是最差的情况，应该尽量避免。
• 解释：告诉我们 MySQL 是怎么找到数据的。system 和 const 是最好的，说明 MySQL 很快就能找到数据。ALL 是最差的，说明 MySQL 要把整个表都扫描一遍才能找到数据，效率很低。

6. possible_keys: 可能用到的索引。

• 含义：MySQL 在查询中可能使用的索引列表。注意，这只是 MySQL 认为可能用到的索引，实际不一定用到。
• 可能的值：索引名列表，或者 NULL（如果没有可用的索引）。
• 解释：MySQL 认为哪些索引可能对这个查询有帮助。

7. key: 实际用到的索引。

• 含义：MySQL 实际使用的索引。
• 可能的值：索引名，或者 NULL（如果没有使用索引）。
• 解释：MySQL 最终选择了哪个索引来加速查询。如果这个字段是 NULL，说明 MySQL 没有使用索引，这通常意味着你需要优化你的查询或者添加合适的索引。

8. key_len: 索引的长度。

• 含义：MySQL 使用的索引的长度（字节数）。可以用来判断使用了联合索引的哪些列。
• 可能的值：正整数。
• 解释：索引越长，通常意味着 MySQL 需要比较更多的字节才能找到匹配的行，所以索引长度也是一个需要考虑的因素。

9. ref: 索引的哪一列被使用了。

• 含义：显示了哪些列或常量被用于查找索引列上的值。
• 可能的值：列名，const（常量），或者 NULL。
• 解释：告诉我们索引是根据哪些值来查找数据的。

10. rows: 估计要检查的行数。

• 含义：MySQL 估计为了找到所需的数据，需要扫描的行数。这是一个估计值，不是精确值。
• 可能的值：正整数。
• 解释：MySQL 估计要扫描多少行才能找到你要的数据。这个值越小越好，说明 MySQL 能够更快地找到数据。

11. filtered: 过滤的比例。

• 含义：表示经过条件过滤后，剩余的行数的百分比。
• 可能的值：0.00 到 100.00 之间的百分比。
• 解释：在使用了索引或者全表扫描后，MySQL 还会根据 WHERE 子句中的其他条件进行过滤。这个值越高，说明 WHERE 子句的过滤效果越好。

12. Extra: 额外的信息。

• 含义：包含一些额外的信息，可以帮助你更好地理解 MySQL 的执行计划。
• 可能的值（常见的）：
• Using index: 使用了覆盖索引，不需要回表查询。这是个好消息！
• Using where: 使用了 WHERE 子句来过滤结果。
• Using temporary: MySQL 需要创建一个临时表来存储结果。这通常意味着需要优化查询。
• Using filesort: MySQL 需要对结果进行文件排序。这通常意味着需要优化查询。
• Using join buffer (Block Nested Loop): 使用了连接缓冲区。
• Impossible WHERE: WHERE 子句的条件永远为假，查询不会返回任何结果。
• 解释：提供了一些额外的提示，告诉你 MySQL 在执行查询时做了一些什么额外的事情。例如，Using index 说明 MySQL 直接从索引中获取了数据，不需要再回到表中查找，效率很高。而 Using temporary 和 Using filesort 则说明 MySQL 做了一些额外的排序或者创建了临时表，这通常意味着查询效率不高，需要优化。

分析例子中的执行计划

回到我们之前的例子：

EXPLAIN SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 27 AND u.city = 'New York';

• users 表（别名 u）:

• type 是 ref，说明使用了索引。

• possible_keys 是 PRIMARY,idx_age,idx_city，说明 主键，age 和 city 两个索引都可能被使用。

• key 是 idx_city，说明 MySQL 最终选择了 idx_city 索引。

• rows 是 2，说明 MySQL 估计要扫描 2 行才能找到满足 city = 'New York' 条件的用户。

• filtered 是 75.00，说明经过 city = 'New York' 条件过滤后，剩余的行数比例是 75%。这意味着 age > 27 这个条件还会进一步过滤数据。

• Extra 是 Using where，说明使用了 WHERE 子句来过滤结果。

• orders 表（别名 o）:

• type 是 ref，说明使用了索引。

• possible_keys 是 idx_user_id。

• key 是 idx_user_id，说明 MySQL 使用了 idx_user_id 索引。

• ref 是 u.id，说明 orders 表的 user_id 列是根据 users 表的 id 列来查找的。

• rows 是 1，说明 MySQL 估计要扫描 1 行才能找到匹配的订单。

• filtered 是 100.00，说明所有扫描到的行都满足连接条件。

如何利用执行计划进行优化？

1. 关注 type 字段：尽量避免 ALL（全表扫描）和 index（全索引扫描）。如果出现这两种情况，通常需要添加索引或者优化查询条件。
2. 关注 key 字段：确保 MySQL 使用了合适的索引。如果没有使用索引，检查是否缺少索引，或者索引是否有效。
3. 关注 rows 字段：尽量减少扫描的行数。可以通过添加索引、优化查询条件、或者使用覆盖索引来减少扫描的行数。
4. 关注 Extra 字段：避免 Using temporary 和 Using filesort。如果出现这两种情况，通常需要优化查询语句，例如，通过添加索引来避免文件排序，或者通过重写查询来避免创建临时表。

优化示例

在上面的例子中，MySQL 选择了 idx_city 索引，而不是 idx_age 索引。这可能是因为 city = 'New York' 条件能够过滤掉更多的行。但是，如果 age > 27 条件能够过滤掉更多的行，那么使用 idx_age 索引可能会更好。

为了让 MySQL 更好地选择索引，我们可以尝试以下方法：

• 强制使用 idx_age 索引：

EXPLAIN SELECT u.name, o.order_date, o.amount
FROM users u FORCE INDEX (idx_age)
JOIN orders o ON u.id = o.user_id
WHERE u.age > 27 AND u.city = 'New York';

使用 FORCE INDEX 提示 MySQL 使用指定的索引。但是，不要滥用 FORCE INDEX，只有在你确定 MySQL 的选择是错误的时候才使用它。

• 创建联合索引：

ALTER TABLE users ADD INDEX idx_age_city (age, city);

创建一个包含 age 和 city 两列的联合索引。这样，MySQL 就可以同时使用这两个条件来过滤数据，提高查询效率。

注意：联合索引的列的顺序很重要。通常，应该将选择性更高的列放在前面。选择性是指列中不同值的数量。例如，如果 city 列中有很多不同的城市，而 age 列中的年龄范围比较小，那么应该将 city 列放在联合索引的前面。

四、总结

MySQL 执行计划是优化 SQL 查询的重要工具。通过分析执行计划，你可以了解 MySQL 如何执行你的 SQL 语句，发现潜在的瓶颈，并采取相应的措施进行优化。记住，优化是一个迭代的过程，需要不断地尝试和调整，才能找到最佳的解决方案。

希望这篇文章能够帮助你更好地理解 MySQL 执行计划。