新来的架构师优化SQL后，查询速度反而慢10倍
2020-10-22 11:43:34
答案：新架构师优化SQL后查询速度变慢10倍，主要原因是错误使用FORCE INDEX强制复合索引，导致回表后大量数据文件排序。应移除强制索引、改用优化器提示或创建覆盖索引，并建立索引使用规范、自动化分析工具和监控体系来预防此类问题。
详细阐述：
- 事故现场：订单查询接口响应时间从120ms增至1.2s，数据库CPU使用率从15%升至90%，慢查询日志出现大量Using index; Using filesort警告。
- 优化操作分析：
原始SQL：使用idx_create_time单列索引，执行时间0.1s。
“优化”后SQL：强制使用idx_user_status复合索引，执行时间1.2s。
执行计划对比：原始SQL使用idx_create_time，扫描532行；强制索引后使用idx_user_status，扫描12万行并产生文件排序。
- 原理解密：
索引结构：idx_user_status为复合索引，idx_create_time为单列索引。
优化器决策逻辑：原始SQL通过单列索引快速定位并排序，而强制复合索引导致大量回表和文件排序。
强制索引的代价：通过复合索引找到12万条记录，回表后排序，仅取10条，浪费大量计算。
- 止血方案：
移除FORCE INDEX：恢复原始SQL或使用USE INDEX建议。
优化器提示替代方案：使用USE INDEX(idx_create_time)替代强制索引。
终极优化：创建覆盖索引idx_user_status_create，包含user_id、status、create_time，避免回表和排序。
- 防御体系：
索引使用规范：禁止生产环境使用FORCE INDEX，所有索引变更需通过EXPLAIN验证。
自动化索引分析工具：使用pt-index-usage分析慢查询日志。
索引红黑榜机制：区分高效和低效索引，如idx_user_status_create为红榜，idx_user_status为黑榜。
版本控制陷阱预防：在迁移脚本中标记风险索引。
索引监控大盘：监控索引使用率差异和文件排序次数，设置告警阈值。
- 推荐延伸阅读：《高性能MySQL（第4版）》第6章和第7章，附录包含EXPLAIN全字段解读和MySQL 8.0新特性实战。
- 工程师自查清单：确保所有SQL通过EXPLAIN验证，检查强制索引使用，联合索引覆盖排序字段，定期运行pt-index-usage，配置索引使用率监控。
技术总结：FORCE INDEX需谨慎使用，优化器的索引选择算法通常更优。真正的优化是让索引和查询协同工作，而非强制控制。
热门标签
