企业官网建设流程全解析

1. MySQL执行计划解析基础

在数据库性能优化工作中，EXPLAIN命令是我们分析SQL查询性能最常用的工具之一。这个看似简单的命令背后，其实隐藏着许多值得深入探讨的技术细节。作为一名长期与MySQL打交道的DBA，我发现很多开发者在日常工作中只是机械地使用EXPLAIN查看执行计划，却很少关注不同输出格式带来的信息差异。

EXPLAIN命令最基础的使用方式是直接在查询前添加EXPLAIN关键字：

EXPLAIN SELECT * FROM users WHERE age > 30;

这种传统用法会返回一个表格形式的结果，显示MySQL优化器选择的执行计划。但很多人不知道的是，从MySQL 5.6版本开始，EXPLAIN已经支持多种输出格式，每种格式都提供了不同的视角来观察查询执行过程。

2. EXPLAIN的四种输出格式详解

2.1 传统表格格式（默认）

当我们不指定FORMAT选项时，MySQL默认返回表格形式的执行计划。这种格式的优势在于结构清晰，便于快速浏览关键指标：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

表格中每个字段都有特定含义：

• type列显示访问类型（从最优到最差：system > const > eq_ref > ref > range > index > ALL）
• rows是预估需要检查的行数
• Extra包含额外信息，如"Using temporary"表示使用了临时表

提示：在MySQL 8.0+版本中，默认表格格式新增了filtered列，表示存储引擎返回的数据在server层过滤后剩余的比例，这对评估索引效率很有帮助。

2.2 JSON格式（FORMAT=JSON）

JSON格式提供了最为详尽的执行计划信息，特别适合程序化分析：

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;

JSON输出包含了许多表格格式中没有的细节：

• 完整的成本估算数据
• 表访问方式的具体原因
• 子查询的详细执行流程
• 使用的优化器策略

一个典型的JSON输出片段如下：

{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "102.50" }, "table": { "table_name": "orders", "access_type": "ref", "possible_keys": ["idx_user"], "key": "idx_user", "used_key_parts": ["user_id"], "key_length": "4", "ref": ["const"], "rows_examined_per_scan": 50, "rows_produced_per_join": 50, "filtered": "100.00", "cost_info": { "read_cost": "52.50", "eval_cost": "50.00", "prefix_cost": "102.50", "data_read_per_join": "15K" }, "used_columns": ["id", "user_id", "amount", "create_time"] } } }

JSON格式特别适合以下场景：

• 需要将执行计划集成到监控系统中
• 进行复杂的性能分析时
• 比较不同查询计划的成本估算

2.3 TREE格式（MySQL 8.0+）

TREE格式是MySQL 8.0引入的新特性，它以层次结构展示查询执行过程：

EXPLAIN FORMAT=TREE SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

输出示例：

-> Nested loop inner join (cost=1250.50 rows=500) -> Filter: (u.age > 30) (cost=250.25 rows=100) -> Table scan on u (cost=250.25 rows=1000) -> Index lookup on o using idx_user (user_id=u.id) (cost=10.00 rows=5)

TREE格式的优势在于：

• 直观展示多表连接的执行顺序
• 清晰呈现查询的物理执行流程
• 方便识别性能瓶颈所在的操作节点

2.4 传统格式（FORMAT=TRADITIONAL）

这种格式与不指定FORMAT时的默认格式相同，主要为了保持向后兼容：

EXPLAIN FORMAT=TRADITIONAL SELECT * FROM products WHERE price > 100;

3. 格式差异的实际影响分析

3.1 信息完整度对比

不同格式提供的信息量有显著差异。以下是一个简单的对比表格：

3.2 不同场景下的格式选择建议

根据我的经验，不同场景适合不同的EXPLAIN格式：

1. 日常快速检查：使用默认表格格式，快速了解执行计划概况
2. 深度性能分析：使用JSON格式获取完整优化器信息
3. 复杂查询调试：使用TREE格式理清多表连接执行顺序
4. 自动化监控：使用JSON格式便于程序解析和处理

3.3 常见工具对格式的支持

不同MySQL客户端工具对EXPLAIN格式的支持程度不同：

• MySQL命令行客户端：支持所有格式
• MySQL Workbench：可视化展示执行计划，基于传统格式
• DBeaver：部分版本可能只显示传统格式
• Navicat：提供图形化执行计划展示

注意：有些工具如DBeaver可能在显示EXPLAIN结果时存在问题，这时可以尝试直接运行EXPLAIN FORMAT=JSON获取原始数据。

4. 实战中的高级应用技巧

4.1 结合性能模式分析

在MySQL 5.7+版本中，可以结合EXPLAIN和performance_schema获取更全面的性能数据：

-- 首先启用性能模式跟踪 SET optimizer_trace="enabled=on"; -- 执行查询 SELECT * FROM large_table WHERE category = 'books'; -- 获取优化器跟踪信息 SELECT * FROM information_schema.optimizer_trace; -- 最后不要忘记关闭跟踪 SET optimizer_trace="enabled=off";

4.2 解读JSON格式中的成本估算

JSON格式中的成本估算数据特别有价值。以下是一个典型成本分析示例：

"cost_info": { "read_cost": "520.25", "eval_cost": "120.50", "prefix_cost": "640.75", "data_read_per_join": "2M" }

• read_cost：从存储引擎读取数据的成本
• eval_cost：处理数据的CPU成本
• prefix_cost：该操作节点的总成本
• data_read_per_join：预估读取的数据量

通过比较不同执行计划的这些成本值，可以更准确地预测查询性能。

4.3 识别潜在问题的技巧

1. 全表扫描警告：当type=ALL且rows值很大时，考虑添加适当索引
2. 临时表使用：Extra中出现"Using temporary"可能影响性能
3. 文件排序："Using filesort"表示无法利用索引排序
4. 索引合并：虽然使用了多个索引(index_merge)，但有时不如单个合适索引高效

4.4 分区表查询分析

对于分区表，EXPLAIN会显示分区访问信息：

EXPLAIN SELECT * FROM sales PARTITION(p2023) WHERE amount > 1000;

在JSON格式中，可以看到详细的分区修剪(partition pruning)信息：

"partitions": ["p2023"], "partition_filter": { "type": "range", "partitions": ["p2023"], "pruned": "true" }

5. 常见问题与解决方案

5.1 为什么EXPLAIN估算的行数与实际不符？

这是DBA经常遇到的问题，主要原因包括：

1. 统计信息过时 - 运行ANALYZE TABLE更新统计信息
2. 索引选择性估算不准确 - 考虑使用索引提示
3. 查询条件过于复杂 - 优化器难以准确估算

解决方案：

ANALYZE TABLE users; EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

5.2 如何分析子查询性能？

对于包含子查询的复杂SQL，建议：

1. 使用FORMAT=TREE查看执行顺序
2. 检查子查询是否被正确优化为连接
3. 注意DEPENDENT SUBQUERY类型，这通常性能较差

示例：

EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

5.3 不同MySQL版本的EXPLAIN差异

MySQL各版本对EXPLAIN的输出有持续改进：

• 5.6：引入JSON格式
• 5.7：增强JSON格式，添加更多成本信息
• 8.0：引入TREE格式，改进可视化展示

在升级MySQL版本后，建议重新检查关键查询的EXPLAIN输出，因为优化器改进可能导致执行计划变化。

5.4 存储引擎对EXPLAIN的影响

不同存储引擎可能产生不同的执行计划：

• InnoDB：提供最完整的统计信息
• MyISAM：统计信息可能不够精确
• Memory：不考虑磁盘I/O成本

在比较执行计划时，确保使用相同的存储引擎。

6. 性能优化实战案例

6.1 案例一：索引优化

原始查询：

EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) = 2023;

问题：对列使用函数导致索引失效

优化方案：

-- 添加基于函数的索引(MySQL 8.0+) ALTER TABLE orders ADD INDEX idx_created_year ((YEAR(create_time))); -- 或修改查询方式 EXPLAIN SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31 23:59:59';

6.2 案例二：连接顺序优化

复杂连接查询：

EXPLAIN FORMAT=TREE SELECT * FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id WHERE u.status = 'active' AND p.price > 100;

通过TREE格式可以发现连接顺序是否最优，必要时可以使用STRAIGHT_JOIN提示：

EXPLAIN SELECT STRAIGHT_JOIN * FROM users u...;

6.3 案例三：分页查询优化

低效分页：

EXPLAIN SELECT * FROM large_table ORDER BY id LIMIT 1000000, 20;

优化方案：

-- 使用索引覆盖+延迟关联 EXPLAIN SELECT t.* FROM large_table t JOIN (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 20) tmp ON t.id = tmp.id;

7. 扩展知识与工具链

7.1 可视化分析工具

1. MySQL Workbench Visual Explain：图形化展示执行计划
2. Percona PMM：监控和查询分析平台
3. pt-visual-explain：Percona Toolkit中的命令行可视化工具

7.2 相关系统变量

这些变量会影响EXPLAIN输出和查询优化：

SHOW VARIABLES LIKE 'optimizer_switch'; SHOW VARIABLES LIKE 'optimizer_trace%';

7.3 执行计划与慢查询日志结合

在my.cnf中配置：

slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1

然后可以使用pt-query-digest等工具分析慢查询，再针对性地使用EXPLAIN分析。

7.4 使用EXPLAIN ANALYZE（MySQL 8.0.18+）

这个增强版命令会实际执行查询并返回实际执行统计：

EXPLAIN ANALYZE SELECT * FROM large_table WHERE category = 'books';

输出包含实际执行时间、返回行数等真实数据，比传统EXPLAIN更有参考价值。