查询优化器揭秘：统计信息与执行计划

问题引入：优化器选错了执行计划

-- 同样的查询，有时快有时慢
SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- 有时用user_id索引（快，返回10条）
-- 有时用status索引（慢，返回100万条）
-- 为什么优化器会做出错误的选择？

这个案例让我深入了解了MySQL查询优化器的工作原理，以及统计信息对执行计划的影响。

现象描述：执行计划异常的表现

场景1：索引选择错误

-- 表结构
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    created_at TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);

-- 数据分布
-- user_id = 100 的订单：10条
-- status = 'PAID' 的订单：100万条

-- 查询
SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- 错误的执行计划：使用idx_status
-- 扫描100万行，再过滤user_id，耗时5秒

-- 正确的执行计划：使用idx_user_id
-- 扫描10行，再过滤status，耗时10ms

场景2：统计信息过期

-- 刚插入大量数据后查询
INSERT INTO orders SELECT * FROM orders_backup; -- 插入1000万条

-- 立即查询
SELECT * FROM orders WHERE user_id = 100;
-- 优化器认为只有100条，实际有10万条
-- 选择了错误的执行计划

-- 分析表后查询
ANALYZE TABLE orders;
SELECT * FROM orders WHERE user_id = 100;
-- 执行计划正确，性能提升100倍

原因分析：查询优化器工作原理

1. 查询优化器架构

SQL查询 -> 解析器 -> 预处理器 -> 优化器 -> 执行器 -> 结果
                      |
                      v
                统计信息(Statistics)
                      |
                      v
                成本模型(Cost Model)
                      |
                      v
                执行计划(Execution Plan)

2. 统计信息详解

表统计信息

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'orders'\G

*************************** 1. row ***************************
           Name: orders
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10000000          -- 预估行数
 Avg_row_length: 150
    Data_length: 1500000000
Max_data_length: 0
   Index_length: 500000000
      Data_free: 0
 Auto_increment: 10000001
...

索引统计信息

-- 查看索引统计信息
SHOW INDEX FROM orders\G

*************************** 1. row ***************************
        Table: orders
   Non_unique: 1
     Key_name: idx_user_id
 Seq_in_index: 1
  Column_name: user_id
    Collation: A
  Cardinality: 1000000    -- 基数（不同值的数量）
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
...

-- 计算选择性
-- 选择性 = Cardinality / Rows = 1000000 / 10000000 = 0.1
-- 选择性越接近1，索引价值越高

直方图统计（MySQL 8.0）

-- 创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- 查看直方图
SELECT 
    COLUMN_NAME,
    BUCKET_NUMBER,
    BUCKET_BOUNDARY,
    BUCKET_FREQUENCY
FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'status';

-- 直方图作用：帮助优化器了解数据分布
-- 例如：status='PAID'占80%，status='CANCELLED'占2%

3. 成本模型

成本计算因素

-- MySQL成本模型考虑的因素：
-- 1. IO成本：读取数据页的代价
-- 2. CPU成本：比较、排序的代价
-- 3. 网络成本：传输数据的代价（分布式场景）

-- 查看成本参数
SHOW VARIABLES LIKE 'optimizer_cost%';

-- 主要成本参数：
-- optimizer_cost_constants_disk_io_cost: 磁盘IO成本权重
-- optimizer_cost_constants_memory_io_cost: 内存IO成本权重
-- optimizer_cost_constants_cpu_cost: CPU成本权重

索引选择成本计算

-- 场景：SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID'

-- 选项1：使用idx_user_id
-- 成本 = 索引IO成本 + 回表IO成本 + CPU成本
--      = (10行 * 索引页成本) + (10行 * 数据页成本) + (10行 * 比较成本)
--      ≈ 10 * 1 + 10 * 1 + 10 * 0.1 = 21

-- 选项2：使用idx_status
-- 成本 = 索引IO成本 + 回表IO成本 + CPU成本
--      = (100万行 * 索引页成本) + (100万行 * 数据页成本) + (100万行 * 比较成本)
--      ≈ 1000000 * 1 + 1000000 * 1 + 1000000 * 0.1 = 2100000

-- 优化器会选择成本更低的idx_user_id

解决方案：优化器调优实战

1. 统计信息维护

自动统计信息更新

-- MySQL自动更新统计信息的阈值：
-- 当表中10%的数据发生变化时，自动更新统计信息

-- 查看统计信息自动更新配置
SHOW VARIABLES LIKE 'innodb_stats%';

-- 关键参数：
-- innodb_stats_auto_recalc: 是否自动重新计算统计信息（默认ON）
-- innodb_stats_persistent: 是否持久化统计信息（默认ON）
-- innodb_stats_sample_pages: 采样页数（默认20）

手动更新统计信息

-- 手动分析表（更新统计信息）
ANALYZE TABLE orders;

-- 分析多个表
ANALYZE TABLE orders, users, products;

-- 在业务低峰期执行
-- 对于大表，ANALYZE TABLE会短暂锁表

-- MySQL 8.0支持在线分析
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, user_id;

2. 执行计划分析

EXPLAIN详解

-- 基础EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 100\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_user_id,idx_status
          key: idx_user_id
      key_len: 9
          ref: const
         rows: 10
     filtered: 100.00
        Extra: NULL

EXPLAIN字段解读

字段	含义	优化建议
id	查询标识	相同id表示同一层，不同id表示嵌套
select_type	查询类型	SIMPLE简单查询，SUBQUERY子查询等
table	访问的表	注意派生表和临时表
type	访问类型	至少range，最好是ref或const
possible_keys	可能使用的索引	检查是否包含预期索引
key	实际使用的索引	确保使用了正确的索引
key_len	索引长度	越长表示使用索引列越多
ref	与索引比较的列	const表示常量，列名表示列比较
rows	预估扫描行数	越小越好
filtered	过滤比例	越高越好
Extra	额外信息	避免Using filesort, Using temporary

EXPLAIN FORMAT=JSON

-- JSON格式更详细的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100\G

-- 输出包含：
-- - 详细的成本估算
-- - 每个操作的具体信息
-- - 索引使用详情
-- - 临时表和文件排序信息

3. 优化器提示（Hints）

索引提示

-- 强制使用某个索引
SELECT * FROM orders USE INDEX(idx_user_id) WHERE user_id = 100;

-- 强制忽略某个索引
SELECT * FROM orders IGNORE INDEX(idx_status) WHERE user_id = 100;

-- 强制使用某个索引，如果没有则报错
SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 100;

优化器开关

-- 查看优化器开关
SHOW VARIABLES LIKE 'optimizer_switch';

-- 临时关闭某些优化
SET SESSION optimizer_switch = 'index_merge=off';

-- 常用优化器开关：
-- index_merge: 索引合并优化
-- index_merge_union: 索引合并UNION优化
-- index_merge_sort_union: 索引合并排序UNION优化
-- index_merge_intersection: 索引合并交集优化
-- engine_condition_pushdown: 引擎条件下推
-- index_condition_pushdown: 索引条件下推

4. 直方图应用（MySQL 8.0）

创建和维护直方图

-- 创建直方图
ANALYZE TABLE orders 
UPDATE HISTOGRAM ON status, pay_type, source 
WITH 100 BUCKETS;

-- 删除直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;

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

直方图优化案例

-- 场景：订单状态分布不均匀
-- PAID: 80%, PENDING: 15%, CANCELLED: 4%, REFUNDED: 1%

-- 没有直方图时，优化器假设均匀分布
-- 估计每个状态占25%

-- 有直方图后，优化器知道真实分布
-- 对于status='REFUNDED'的查询，会选择更优的执行计划

-- 创建直方图前
EXPLAIN SELECT * FROM orders WHERE status = 'REFUNDED';
-- type: ALL, rows: 10000000 (全表扫描)

-- 创建直方图后
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
EXPLAIN SELECT * FROM orders WHERE status = 'REFUNDED';
-- type: ref, rows: 100000 (使用索引)

实施步骤：执行计划优化流程

步骤1：发现问题

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 或使用Performance Schema
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%stage/sql%';

步骤2：分析执行计划

-- 获取执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100\G

-- 关注要点：
-- 1. type列：避免ALL（全表扫描）
-- 2. rows列：预估扫描行数是否合理
-- 3. key列：是否使用了正确的索引
-- 4. Extra列：是否有Using filesort, Using temporary

步骤3：诊断问题

-- 检查统计信息是否过期
SHOW TABLE STATUS LIKE 'orders';
-- 对比实际行数和Rows字段

-- 检查索引 Cardinality
SHOW INDEX FROM orders;
-- 对比Cardinality和实际不同值数量

-- 更新统计信息
ANALYZE TABLE orders;

步骤4：优化实施

-- 方案1：更新统计信息
ANALYZE TABLE orders;

-- 方案2：创建直方图（MySQL 8.0）
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;

-- 方案3：使用优化器提示
SELECT * FROM orders USE INDEX(idx_user_id) WHERE user_id = 100;

-- 方案4：调整优化器参数
SET SESSION optimizer_switch = 'index_merge=off';

步骤5：效果验证

-- 对比优化前后执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100\G

-- 对比性能
-- 优化前：扫描100万行，耗时5秒
-- 优化后：扫描10行，耗时10ms

效果验证：优化前后对比

案例1：统计信息过期导致的性能问题

指标	优化前	优化后	提升
查询时间	5s	50ms	99% ↓
扫描行数	1000万	1000	99.99% ↓
使用索引	无	idx_user_id	新增
统计信息	过期	已更新	修复

优化措施：

执行ANALYZE TABLE更新统计信息
建立定期ANALYZE TABLE的维护计划

案例2：直方图优化索引选择

查询条件	优化前执行计划	优化后执行计划	效果
status='PAID' (80%)	全表扫描	全表扫描	正确
status='REFUNDED' (1%)	全表扫描	索引扫描	提升100倍

优化措施：

创建status列的直方图
优化器根据数据分布选择最优执行计划

经验总结：优化器调优最佳实践

✅ 应该做的

定期更新统计信息：ANALYZE TABLE
使用EXPLAIN分析执行计划
关注rows和type字段
MySQL 8.0使用直方图优化
监控慢查询日志
理解成本模型原理
必要时使用优化器提示
建立统计信息维护计划

❌ 不应该做的

从不更新统计信息
忽略执行计划分析
过度依赖优化器提示
不了解数据分布就创建索引
不监控统计信息过期
盲目调整优化器参数
忽视直方图的作用（MySQL 8.0）
不验证优化效果

深度案例分析：优化器问题排查

项目背景

某电商平台出现间歇性慢查询：

同样的SQL有时10ms，有时10秒
高峰期性能急剧下降
重启数据库后暂时好转

问题诊断

-- 问题SQL
SELECT * FROM orders 
WHERE user_id = 100 
AND status = 'PAID' 
AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 查看执行计划（慢的时候）
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID'\G
-- type: ALL, rows: 10000000, key: NULL
-- 全表扫描！

-- 查看执行计划（快的时候）
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID'\G
-- type: ref, rows: 100, key: idx_user_id
-- 使用索引

-- 检查统计信息
SHOW TABLE STATUS LIKE 'orders';
-- Rows: 1000000 (实际1000万)
-- 统计信息严重过期！

根因分析

大量数据导入后统计信息未更新
优化器基于过期的统计信息选择执行计划
有时运气好选对了索引，有时选错

解决方案

-- 立即修复
ANALYZE TABLE orders;

-- 长期方案：定期维护计划
-- 创建事件定时更新统计信息
CREATE EVENT update_stats 
ON SCHEDULE EVERY 1 DAY
DO
    ANALYZE TABLE orders, users, products;

-- 启用事件
SET GLOBAL event_scheduler = ON;

优化效果

指标	优化前	优化后	提升
平均查询时间	5s	20ms	99.6% ↓
性能稳定性	波动大	稳定	大幅改善
统计信息准确度	10%误差	<1%误差	精准

查询优化器决策树

查询性能问题
    ↓
是否使用了索引？
    ↓ 否
检查统计信息是否过期
    ↓
执行ANALYZE TABLE
    ↓
再次检查执行计划
    ↓
是否选择了正确的索引？
    ↓ 否
检查数据分布是否均匀
    ↓
是否需要创建直方图？
    ↓ 是
ANALYZE TABLE UPDATE HISTOGRAM
    ↓
是否仍有问题？
    ↓ 是
考虑使用优化器提示
    ↓
优化完成

常见误区与避坑指南

❌ 误区1：优化器总是对的

问题：优化器基于统计信息做决策，统计信息过期会导致错误选择。

正确做法：定期更新统计信息，监控执行计划变化。

❌ 误区2：优化器提示是万能药

问题：过度使用hints会导致代码难以维护，且可能不是最优解。

正确做法：先尝试修复统计信息，hints作为最后手段。

❌ 误区3：忽略直方图的作用

问题：MySQL 8.0的直方图可以显著提升非均匀分布数据的查询性能。

正确做法：为数据分布不均匀的列创建直方图。

❌ 误区4：不关注执行计划变化

问题：数据量变化后，原来的最优执行计划可能不再最优。

正确做法：建立执行计划监控，及时发现异常。

执行计划分析检查清单

□ 是否使用了预期的索引？
□ type列是否至少为range？
□ rows预估是否准确？
□ 是否存在Using filesort？
□ 是否存在Using temporary？
□ 统计信息是否过期？
□ 是否需要创建直方图？
□ 是否可以使用覆盖索引？
□ 是否可以使用索引条件下推？
□ 执行计划是否稳定？

附录

A. EXPLAIN type字段速查

type	效率	说明
system	最高	表只有一行
const	极高	通过主键或唯一索引一次找到
eq_ref	极高	JOIN中通过主键或唯一索引关联
ref	高	使用非唯一索引
range	中	索引范围扫描
index	低	全索引扫描
ALL	最低	全表扫描

B. EXPLAIN Extra字段速查

Extra	含义	优化建议
Using index	使用覆盖索引	好事
Using where	使用WHERE过滤	正常
Using filesort	需要额外排序	添加索引避免
Using temporary	需要临时表	优化查询或添加索引
Using join buffer	使用连接缓冲	考虑添加索引
Impossible WHERE	WHERE条件永远为假	检查逻辑
Select tables optimized away	优化器确定只返回一行	好事

C. 统计信息维护脚本

-- 定期维护脚本
DELIMITER $$

CREATE PROCEDURE MaintainStatistics()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_TYPE = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @sql = CONCAT('ANALYZE TABLE ', table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cur;
END$$

DELIMITER ;

-- 创建定时事件
CREATE EVENT maintain_stats_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO CALL MaintainStatistics();

读者练习

思考题：分析你项目中的慢查询，检查是否存在统计信息过期问题。
实践题：为以下查询分析执行计划并优化：

SELECT o.*, u.username 
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.status = 'PAID'
AND o.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY o.total_amount DESC
LIMIT 100;

挑战题：实现一个执行计划监控工具，自动发现执行计划变化。

系列上一篇：索引深度解析：B+树、哈希与覆盖索引

系列下一篇：高级查询技巧：窗口函数与递归查询