从慢查询到可证明优化

← 本站索引 ｜ 练习答案

可运行 SQL

USE mysql_course;

ANALYZE TABLE sales_order;

SELECT COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'mysql_course'
  AND TABLE_NAME = 'sales_order';

ANALYZE TABLE sales_order
  UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

EXPLAIN ANALYZE
SELECT customer_id, SUM(total_amount)
FROM sales_order
WHERE status = 'paid'
GROUP BY customer_id;

ANALYZE TABLE sales_order DROP HISTOGRAM ON status;

机制解释

优化器基于统计信息估算访问成本并选择计划。直方图描述未建立合适索引列的数据分布，可改善选择性估算，但不会直接加速访问。相关列、倾斜参数和过期统计都可能造成估算偏差。

性能优化必须固定数据规模、分布、参数、缓存状态、并发和测量窗口。只比较一次墙钟时间无法区分计划变化、缓存预热和邻居负载。

真实边界

• 慢查询日志、performance_schema 和应用 tracing 各自只看到链路的一部分。
• 增大内存参数可能挤压操作系统或同机服务；参数不能按“越大越快”调整。
• 强制索引会冻结优化器选择，只适合有证据、监控和退出条件的场景。
• 延迟改善不能以错误结果、写放大或副本延迟为代价。

练习

1. 为 status 制造倾斜数据，比较 histogram 前后的估算。
2. 写一份只允许单变量变化的基准记录模板。
3. 解释为什么 P95 改善但总 CPU 翻倍不一定可以上线。

验收清单

• 基准固定版本、数据分布、参数、并发和缓存条件。
• 优化只改变一个主要变量，并同时报告延迟与资源。
• histogram 实验包含创建前、创建后和清理步骤。

完成后核对答案

深度加餐：优化器也会猜错，证据要能复盘

慢查询优化不是猜索引，而是对比基线、估算、实际行数和资源消耗。

先做一个可观察实验

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

EXPLAIN FORMAT=JSON
SELECT *
FROM orders
WHERE status = 'PENDING'
  AND placed_at >= CURRENT_DATE - INTERVAL 7 DAY;

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

预期：统计信息和直方图帮助优化器理解数据分布；性能归因来自 digest 聚合，不是某一次偶然执行。

原理下潜与工程边界

优化器需要基数估算、成本模型和索引统计；直方图适合非均匀分布但会过期。性能要同时看 CPU、I/O、锁等待、临时表和网络返回量。

ANALYZE TABLE 会有资源与锁影响；SQL_NO_CACHE 不能模拟真实 buffer pool；把 p99 慢归咎于平均值会掩盖长尾。

反例排错路线

先捕获真实样本与绑定参数，再做冷/暖缓存对照；变更一次只动一个变量，保留回滚脚本和观察窗口。

练习加码（仍不提供答案）

加码：为一条慢报表建立 24 小时基线，分别判断索引、统计信息、锁等待和返回数据过大四种根因。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）