上周三凌晨监控告警突然炸了订单列表接口平均响应时间突破3秒P99更是飙到8秒。用户端大量超时客服电话被打爆。我打开慢查询日志一场从3秒到30毫秒的优化战役就此打响。定位慢查询日志揪出元凶先看慢查询日志一条SQL赫然在目sql复制下载SELECT FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 10;这张订单表有2000万行数据user_id和status各自有单列索引。EXPLAIN结果让人倒吸一口凉气typeALL全表扫描rows19800000Extra里写着Using filesort。MySQL先扫全表再排序最后取10条——3秒已经算快了。第一步联合索引但顺序有讲究直觉告诉我该建联合索引。先试了(user_id, status)结果typeref但Using filesort依然存在。因为排序字段create_time不在索引里MySQL仍需额外排序。调整为(user_id, status, create_time)后Extra终于变成了Using where; Using index condition排序消失了。响应时间从3秒降到500毫秒。但离30毫秒还差得远。第二步消除回表覆盖索引立功问题出在SELECT。虽然索引命中了但查询需要所有字段MySQL必须拿着主键ID回表查完整行。10条数据就要回表10次如果数据分散在不同页随机IO开销巨大。我改成先查主键再关联取详情sql复制下载SELECT o. FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 10 ) t ON o.id t.id;子查询用上了覆盖索引(user_id, status, create_time, id)不需要回表外层只用10次主键查询。响应时间骤降到80毫秒。第三步细节打磨压榨最后50毫秒80毫秒已经不错但距离30毫秒还有差距。继续排查统计信息过期。ANALYZE TABLE orders更新统计信息后优化器选择了更优的执行计划。缓冲池命中率。检查innodb_buffer_pool_size发现只有2GB而热数据就有8GB。调整为12GB后磁盘IO大幅减少。分页优化。这个接口其实还有翻页逻辑深分页时LIMIT 100000, 10会扫描大量无用行。改用游标分页基于create_time和id做条件过滤。连接池调优。HikariCP的maxPoolSize从20调到50避免高并发下连接等待。一轮组合拳下来再次压测平均响应时间稳定在30毫秒P99控制在50毫秒以内。从3秒到30毫秒整整提速100倍。复盘慢查询优化的四个原则索引不是越多越好顺序决定成败。联合索引要兼顾过滤和排序把等值查询字段放前面范围或排序字段放后面。警惕SELECT。它让覆盖索引失效强制回表。只取需要的列或者用延迟关联。统计信息和缓冲池是隐形杀手。优化器依赖统计信息选执行计划缓冲池大小决定磁盘IO次数。这两项配置不当再好的索引也白搭。深分页必须用游标。LIMIT offset, size在offset大时性能断崖式下跌游标分页才是正解。优化不是一次性的上线后我设置了慢查询阈值200毫秒每天自动推送Top 10慢SQL。毕竟30毫秒的成绩需要持续守护。
