好的，下面我们开始今天的讲座，主题是MySQL优化器与执行计划之：MySQL的EXPLAIN ANALYZE，重点探讨其在MySQL 8.0中如何提供详细的运行时信息。
引言：理解查询执行的黑盒
对于任何数据库系统，理解查询是如何被执行的至关重要。MySQL的优化器负责将我们编写的SQL语句转换成一系列的操作，最终获取所需的数据。然而，在过去，我们通常只能通过EXPLAIN语句来推测这些操作的执行情况。EXPLAIN能够提供优化器选择的执行计划，包括使用的索引、连接类型等等。但这仍然像是在一个黑盒子里观察，难以真正了解查询执行过程中发生的具体细节。
MySQL 8.0引入了EXPLAIN ANALYZE，它改变了游戏规则。EXPLAIN ANALYZE不仅展示了优化器选择的执行计划，还提供了实际的运行时信息，让我们能够深入了解查询的执行过程，从而更有效地进行性能调优。
EXPLAIN ANALYZE的基础：EXPLAIN的复习
在深入了解EXPLAIN ANALYZE之前，我们先回顾一下EXPLAIN语句。EXPLAIN命令可以帮助我们了解MySQL优化器如何执行查询。它会返回一个包含了查询执行计划信息的表格。
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;EXPLAIN输出结果中，最重要的列包括：
- id: 查询中每个
SELECT语句的标识符。 - select_type: 查询的类型（如
SIMPLE、PRIMARY、SUBQUERY等）。 - table: 查询涉及的表。
- partitions: 查询涉及的分区（如果表进行了分区）。
- type: 连接类型，表示
MySQL如何查找表中的行（如ALL、index、range、ref、eq_ref、const、system）。 - possible_keys: 可能用于查询的索引。
- key: 实际使用的索引。
- key_len: 使用的索引的长度。
- ref: 将哪些列或常量与索引进行比较。
- rows: 估计需要检查的行数。
- filtered: 估计有多少百分比的行在应用
WHERE条件后会被过滤掉。 - Extra: 包含有关
MySQL如何执行查询的其他信息（如Using index、Using where、Using temporary、Using filesort）。
理解这些列的含义对于理解EXPLAIN的输出至关重要。例如，type列的取值越靠近const，查询效率通常越高；而ALL则表示全表扫描，通常需要优化。Extra列的信息也很有用，例如Using filesort表示使用了文件排序，这通常是一个性能瓶颈。
EXPLAIN ANALYZE：运行时信息的利器
EXPLAIN ANALYZE在EXPLAIN的基础上更进一步，它会实际执行查询，并在执行计划中添加实际的运行时统计信息。这意味着我们可以看到每个操作实际花费的时间、读取的行数、使用的内存等等。
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;EXPLAIN ANALYZE的输出通常是一个树状结构，每个节点代表查询执行计划中的一个操作。每个节点都包含以下信息：
- 操作描述: 描述了该节点执行的操作，例如
Table scan on orders。 - 持续时间: 该操作实际花费的时间（包括第一个结果的返回时间和总时间）。
- 行数: 该操作返回的行数。
- 内存使用: 该操作使用的内存量。
- 其他信息: 根据操作的不同，可能包含其他有用的信息，例如使用的索引、连接类型等等。
EXPLAIN ANALYZE的输出格式
EXPLAIN ANALYZE的输出格式可以分为两种：
- 文本格式 (默认): 以树状结构展示，易于阅读，适合快速了解执行计划。
- JSON格式: 提供更详细的信息，方便程序解析和分析。
我们可以使用 FORMAT=JSON 选项来获取 JSON 格式的输出。
EXPLAIN FORMAT=JSON ANALYZE SELECT * FROM orders WHERE customer_id = 123;EXPLAIN ANALYZE的详细解读
下面，我们通过一个更复杂的例子来深入解读EXPLAIN ANALYZE的输出。假设我们有三个表：customers、orders和order_items。
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
INSERT INTO customers (customer_id, name) VALUES
(1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2023-01-01'), (102, 1, '2023-01-02'), (103, 2, '2023-01-03');
INSERT INTO order_items (order_item_id, order_id, product_id, quantity) VALUES
(1001, 101, 1, 2), (1002, 101, 2, 1), (1003, 102, 1, 3), (1004, 103, 3, 1);现在，我们执行一个查询，找出所有名为 "Alice" 的客户的订单信息，包括订单日期和订单中的商品数量。
EXPLAIN ANALYZE
SELECT
c.name,
o.order_date,
SUM(oi.quantity) AS total_quantity
FROM
customers c
JOIN
orders o ON c.customer_id = o.customer_id
JOIN
order_items oi ON o.order_id = oi.order_id
WHERE
c.name = 'Alice'
GROUP BY
o.order_id, o.order_date;一个可能的文本格式的 EXPLAIN ANALYZE 输出如下 (实际输出会因数据量和硬件环境而异):
-> Group aggregate: sum(order_items.quantity) (actual time=0.025..0.025 rows=2 loops=1)
-> Nested loop inner join (actual time=0.019..0.024 rows=2 loops=1)
-> Nested loop inner join (actual time=0.010..0.015 rows=2 loops=1)
-> Index lookup on customers using PRIMARY (customer_id=orders.customer_id) (actual time=0.004..0.005 rows=1 loops=1)
-> Filter: (customers.name = 'Alice') (cost=0.35 rows=1) (actual time=0.003..0.004 rows=1 loops=1)
-> Table scan on customers (actual time=0.001..0.003 rows=3 loops=1)
-> Index lookup on orders using PRIMARY (order_id=order_items.order_id) (actual time=0.004..0.005 rows=2 loops=1)
-> Covering index lookup on order_items using order_id (order_id=orders.order_id) (actual time=0.005..0.006 rows=2 loops=2)逐行解读：
Table scan on customers (actual time=0.001..0.003 rows=3 loops=1): 首先，对customers表进行全表扫描，实际耗时 0.001 到 0.003 秒，扫描了 3 行。Filter: (customers.name = 'Alice') (cost=0.35 rows=1) (actual time=0.003..0.004 rows=1 loops=1): 然后，根据WHERE子句的条件c.name = 'Alice'进行过滤。优化器估计会过滤到 1 行 (rows=1)，实际耗时 0.003 到 0.004 秒，最终过滤出 1 行。Index lookup on customers using PRIMARY (customer_id=orders.customer_id) (actual time=0.004..0.005 rows=1 loops=1): 根据主键customer_id在customers表上进行索引查找，耗时 0.004 到 0.005 秒，找到 1 行。loops=1表示这个操作执行了一次。Index lookup on orders using PRIMARY (order_id=order_items.order_id) (actual time=0.004..0.005 rows=2 loops=1): 根据主键order_id在orders表上进行索引查找, 耗时 0.004 到 0.005 秒, 找到 2 行.loops=1表示这个操作执行了一次。Covering index lookup on order_items using order_id (order_id=orders.order_id) (actual time=0.005..0.006 rows=2 loops=2): 使用覆盖索引在order_items表上进行索引查找。注意loops=2，表示这个操作执行了两次，对应了orders表中找到的 2 行订单。实际耗时 0.005 到 0.006 秒，找到 2 行。Nested loop inner join (actual time=0.010..0.015 rows=2 loops=1): 将customers表和orders表进行嵌套循环连接，耗时 0.010 到 0.015 秒，连接后产生 2 行。Nested loop inner join (actual time=0.019..0.024 rows=2 loops=1): 将连接后的结果与order_items表进行嵌套循环连接，耗时 0.019 到 0.024 秒，连接后产生 2 行。Group aggregate: sum(order_items.quantity) (actual time=0.025..0.025 rows=2 loops=1): 最后，对结果进行分组聚合，计算quantity的总和，耗时 0.025 秒，产生 2 行。
JSON 格式的解读
JSON 格式的输出提供了更详细的信息，例如内存使用情况、锁等待时间等等。虽然可读性不如文本格式，但更适合程序分析。
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.85"
},
"nested_loop": [
{
"table": {
"table_name": "c",
"access_type": "ALL",
"rows_examined_per_scan": 3,
"rows_produced_per_join": 1,
"filtered": "33.33",
"cost_info": {
"read_cost": "1.05",
"eval_cost": "0.30",
"prefix_cost": "1.35",
"data_read_per_join": "96"
},
"attached_condition": "c.name = 'Alice'"
}
},
{
"table": {
"table_name": "o",
"access_type": "ref",
"possible_keys": [
"customer_id"
],
"key": "customer_id",
"used_key_parts": [
"customer_id"
],
"key_length": "4",
"ref": [
"c.customer_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.30",
"prefix_cost": "1.90",
"data_read_per_join": "32"
},
"attached_condition": "o.customer_id = c.customer_id"
}
},
{
"table": {
"table_name": "oi",
"access_type": "ref",
"possible_keys": [
"order_id"
],
"key": "order_id",
"used_key_parts": [
"order_id"
],
"key_length": "4",
"ref": [
"o.order_id"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.60",
"prefix_cost": "2.75",
"data_read_per_join": "32"
},
"attached_condition": "oi.order_id = o.order_id"
}
}
]
}
}这个 JSON 输出展示了每个表的访问类型、使用的索引、过滤比例、成本信息等等。
EXPLAIN ANALYZE的应用：性能调优的实践
通过EXPLAIN ANALYZE，我们可以识别查询中的瓶颈，并采取相应的优化措施。
- 全表扫描: 如果发现某个表进行了全表扫描（
type为ALL），应该考虑添加索引，或者优化WHERE子句，减少需要扫描的行数。在本例中，customers表最初进行了全表扫描，但由于数据量小，影响不大。如果数据量很大，应该考虑在name列上添加索引。 - 文件排序: 如果
Extra列显示Using filesort，表示使用了文件排序，这通常是一个性能瓶颈。应该尝试通过添加索引或者修改查询语句来避免文件排序。 - 嵌套循环连接: 嵌套循环连接的效率通常较低，特别是当连接的表都很大时。应该考虑使用其他连接类型，例如哈希连接（
Hash Join），或者优化连接条件。 - 不合理的索引: 优化器可能选择了不合适的索引，导致查询效率低下。通过
EXPLAIN ANALYZE，我们可以看到实际使用的索引，并判断是否需要强制使用其他索引（使用FORCE INDEX提示）。 - 高成本的操作: 关注
EXPLAIN ANALYZE输出中成本较高的操作，例如耗时较长的过滤、排序或连接。针对这些操作进行优化，可以显著提升查询性能。
一些高级技巧和注意事项
- 对比不同方案: 尝试不同的查询写法或索引方案，然后使用
EXPLAIN ANALYZE对比它们的性能，选择最优的方案。 - 关注平均时间和最大时间:
EXPLAIN ANALYZE输出中会显示操作的平均时间和最大时间。如果最大时间明显高于平均时间，可能存在锁竞争或其他资源瓶颈。 - 结合性能监控工具: 将
EXPLAIN ANALYZE与性能监控工具（例如Performance Schema、sys库）结合使用，可以更全面地了解查询的性能瓶颈。 - 数据量对结果的影响:
EXPLAIN ANALYZE的结果受到数据量的影响。在不同的数据量下，优化器可能会选择不同的执行计划。因此，应该在接近生产环境的数据量下进行测试。 - 缓存的影响:
EXPLAIN ANALYZE会实际执行查询，因此会受到缓存的影响。为了获得更准确的结果，可以先清空缓存（使用FLUSH TABLES或FLUSH QUERY CACHE命令）。 - 只读事务: 建议在只读事务中运行
EXPLAIN ANALYZE，以避免对数据库产生意外的修改。
EXPLAIN ANALYZE的限制
虽然EXPLAIN ANALYZE是一个强大的工具，但也存在一些限制：
- 需要执行查询:
EXPLAIN ANALYZE会实际执行查询，因此可能会对数据库产生一定的负载。在生产环境中，应该谨慎使用，避免影响正常业务。 - 不支持所有语句:
EXPLAIN ANALYZE不支持所有的SQL语句。例如，它不支持存储过程、触发器等。 - 输出结果可能难以理解: 对于复杂的查询，
EXPLAIN ANALYZE的输出结果可能会很长，难以理解。需要仔细分析，才能找到性能瓶颈。
总结：用运行时信息驱动优化
EXPLAIN ANALYZE是MySQL 8.0中一项非常有价值的工具，它提供了查询执行的运行时信息，帮助我们深入了解查询的执行过程，从而更有效地进行性能调优。它弥补了传统 EXPLAIN 语句的不足，将查询优化从猜测变为基于数据的决策。掌握EXPLAIN ANALYZE，是成为一名优秀的MySQL数据库工程师的必备技能。利用好EXPLAIN ANALYZE，可以大幅度提高数据库的查询效率，提升应用系统的性能。