四、查询优化器工作原理

4.1 基于成本的优化(CBO)

现代优化器通过统计信息估算不同执行计划的成本：

• cardinality：索引列的唯一值估算数
• 选择性：过滤条件筛选比例
• I/O成本：读取一页的成本（默认1.0）
• CPU成本：处理一行记录的成本（默认0.2）

4.2 EXPLAIN执行计划解读

关键字段含义：

4.3 统计信息更新

MySQL通过 ANALYZE TABLE 更新统计信息。对于频繁更新的大表，建议在非高峰时段手动触发。InnoDB的持久化统计信息通过参数控制：

innodb_stats_persistent=ON
innodb_stats_auto_recalc=ON
innodb_stats_persistent_sample_pages=128

五、实战：慢查询优化案例

5.1 案例：范围查询导致索引失效

原始查询：

SELECT * FROM orders WHERE create_time > 2024-01-01 ORDER BY id LIMIT 100;

索引 idx_create_time 存在但优化器选择了全表扫描——原因是create_time范围太大，回表成本过高。

优化方案：

-- 创建覆盖索引避免回表
ALTER TABLE orders ADD INDEX idx_ct_id(create_time, id);
-- 或改用延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE create_time > 2024-01-01 ORDER BY id LIMIT 100) tmp
ON o.id = tmp.id;

5.2 案例：隐式类型转换导致索引失效

-- phone为VARCHAR，但传入数字导致全表扫描
SELECT * FROM users WHERE phone = 13800138000;
-- 正确写法
SELECT * FROM users WHERE phone = 13800138000;

这看似没区别，但前者将触发列上的隐式CAST，使得 idx_phone 索引完全失效。

5.3 案例：分页深翻页优化

问题：LIMIT 1000000, 20 需要扫描前100万行再丢弃。

游标分页方案：

SELECT * FROM articles WHERE id > 999999 ORDER BY id LIMIT 20;

利用主键有序性，深翻页延迟从秒级降到毫秒级。

六、索引维护与监控

6.1 索引碎片整理

频繁UPDATE/DELETE导致页分裂和碎片。通过 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建表可消除碎片。

6.2 慢查询日志分析

开启慢查询日志并配合 pt-query-digest 分析热点SQL：

slow_query_log=ON
long_query_time=0.5
log_queries_not_using_indexes=ON

6.3 性能监控指标

关注以下InnoDB状态指标：

• Innodb_buffer_pool_read_requests：buffer pool命中次数
• Innodb_buffer_pool_reads：从磁盘读取的页数（越小越好）
• Handler_read_rnd_next：全表扫描次数
• Select_scan：全表扫描查询数

七、高级索引策略

7.1 函数索引(MySQL 8.0+)

对表达式结果建索引，解决日期截断等场景：

CREATE INDEX idx ON orders((DATE(create_time)));

7.2 降序索引

MySQL 8.0支持真正的降序索引：

CREATE INDEX idx_ts ON metrics(ts DESC, device_id ASC);

7.3 不可见索引

将索引设为INVISIBLE用于安全测试，无需删除即可验证性能影响：

ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

八、总结

索引优化的核心是减少I/O次数。B+树的多路平衡特性使得千万级数据查询保持在3次I/O内。工程师应做到：

1. 理解B+树物理结构，预判索引层数和扫描范围
2. 熟练使用EXPLAIN分析执行计划，识别全表扫描
3. 利用覆盖索引、最左前缀等原则设计高效联合索引
4. 定期监控慢查询，用统计信息更新保障优化器决策准确