MySQL 8.0查询性能骤降诊断：执行计划突变与统计信息修复

MySQL查询性能骤降的典型场景

数据库运维中，查询性能骤降是最影响业务稳定性的问题之一。一条原本10毫秒的SQL突然执行超过10秒，背后原因往往不是数据量增长，而是MySQL优化器选择了完全不同的执行计划。这种执行计划突变（Plan Regression）在MySQL 8.0中尤为常见，原因在于优化器的成本估算依赖统计信息，而统计信息的采样精度和更新时机都可能导致估算偏差。

执行计划对比与突变识别

诊断执行计划突变的第一步是对比变更前后的EXPLAIN输出：

-- 查看当前执行计划
EXPLAIN FORMAT=TREE
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > '2026-07-01'
  AND c.region = 'east'
  AND oi.product_category = 'electronics'
ORDER BY o.total_amount DESC
LIMIT 50;

-- 查看优化器跟踪信息（定位选择依据）
SET optimizer_trace='enabled=on';
-- 执行查询
SELECT ...;
-- 查看跟踪结果
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

OPTIMIZER_TRACE是MySQL 8.0的强大诊断工具，它会记录优化器评估的每一个执行计划选项及其成本估算，可以精确看到优化器为什么放弃了索引扫描而选择了全表扫描。

统计信息不准确导致计划突变的修复

InnoDB统计信息机制

InnoDB的统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中，通过随机采样页面估算。默认采样页面数由innodb_stats_persistent_sample_pages控制（默认20页）。当表数据量增长但采样不够代表性时，统计信息偏差导致计划突变。

-- 查看表统计信息
SELECT * FROM mysql.innodb_table_stats 
WHERE database_name = 'your_db' AND table_name = 'orders'\G

-- 查看索引统计信息
SELECT * FROM mysql.innodb_index_stats
WHERE database_name = 'your_db' AND table_name = 'orders'\G

-- 关键字段：
-- n_rows: 估算行数
-- stat_value: 索录不同值数量（Cardinality）
-- 如果n_rows与实际差距超过50%，统计信息需要修复

修复统计信息的方法

-- 方法1：增加采样精度后重新分析
SET GLOBAL innodb_stats_persistent_sample_pages = 128;
ANALYZE TABLE orders;
-- 分析完成后恢复默认值
SET GLOBAL innodb_stats_persistent_sample_pages = 20;

-- 方法2：强制全量采样（大表慎用）
SET GLOBAL innodb_stats_persistent_sample_pages = 0;  -- 0表示全量
ANALYZE TABLE orders;
-- 立即恢复
SET GLOBAL innodb_stats_persistent_sample_pages = 20;

-- 方法3：锁定执行计划（临时方案）
-- 使用Optimizer Hint强制索引
SELECT /*+ INDEX(o idx_created_at) */ o.order_id, ...
FROM orders o ...

-- 方法4：使用Optimizer Switch禁用特定优化
SET SESSION optimizer_switch = 'index_merge=off,range_optimizer=off';

对于大表（超过千万行），方法2的ANALYZE TABLE可能锁表数分钟，务必在低峰期执行。生产环境推荐方法1：将采样页数提高到128或256，通常足以修正统计偏差。

MySQL 8.0直方图统计优化

MySQL 8.0引入了直方图统计（Histogram Statistics），对非索引列的过滤条件估算更准确：

-- 为高频过滤列创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at, status 
WITH 256 BUCKETS;

-- 查看直方图信息
SELECT COLUMN_NAME, HISTOGRAM->'$."number-of-buckets-specified"' AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

-- 删除不需要的直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;

直方图不依赖索引，在下列场景特别有效：WHERE条件包含没有索引的列、多列组合过滤的独立性假设偏差过大、枚举型列值分布严重倾斜（如status列90%为completed）。直方图的数据在ANALYZE TABLE时更新，不会自动随DML更新，对数据变化频繁的列需要定期刷新。

执行计划稳定性保障方案

索引设计优化

从根本上避免执行计划突变的方法是设计覆盖查询的复合索引：

-- 为高频查询创建覆盖索引
-- 查询: SELECT order_id, total_amount FROM orders 
--       WHERE created_at > ? AND status = 'pending' ORDER BY total_amount

-- 覆盖索引：过滤列在前，排序列在中，返回列在后
CREATE INDEX idx_covering ON orders(created_at, status, total_amount, order_id);

-- 验证是否走覆盖索引
EXPLAIN SELECT order_id, total_amount FROM orders
WHERE created_at > '2026-07-01' AND status = 'pending'
ORDER BY total_amount;
-- Extra列应显示 Using index

定期统计信息维护

建立自动化统计信息维护机制，避免因统计信息过期导致计划突变：

-- 创建定期分析存储过程
DELIMITER //
CREATE PROCEDURE sp_refresh_stats()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(128);
    DECLARE cur CURSOR FOR
        SELECT table_name FROM information_schema.tables
        WHERE table_schema = 'your_db' 
        AND table_type = 'BASE TABLE'
        AND table_rows > 100000;  -- 只分析超过10万行的表
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN LEAVE read_loop; END IF;
        SET @sql = CONCAT('ANALYZE TABLE your_db.', tbl_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 配合事件调度器每天凌晨执行
CREATE EVENT evt_refresh_stats
ON SCHEDULE EVERY 1 DAY STARTS '2026-08-07 03:00:00'
DO CALL sp_refresh_stats();

SQL查询性能监控与告警

在数据库运维体系中，查询性能骤降需要靠监控发现而非用户投诉。关键监控指标：慢查询数量和平均执行时间（slow_query_log配合long_query_time）、单条SQL执行时间突增（与7天前基线对比）、临时表使用量和磁盘临时表比例、InnoDB行操作扫描行数与返回行数比值（超过1000:1说明计划严重不合理）。Prometheus+Grafana配合mysqld_exporter可以采集这些指标，设置环比增长超过200%时触发告警，为DBA争取主动修复时间。