男人要慢，SQL要快：记一次慢SQL优化
最新回答
傲气稳了全场
2023-05-29 02:53:26
此次慢SQL优化通过调整索引结构和优化排序逻辑，将查询时间从11秒降至0.8秒，性能提升超10倍。 以下是具体分析与优化过程：
问题分析- 慢查询定位：日志显示某SQL执行时间达11.146秒，属于典型慢查询。原始SQL包含WHERE条件过滤和ORDER BY amount DESC, create_time ASC排序。
- 表与索引情况：表存在两个联合索引(status, to_account_id)和(status, from_account_id)，但未覆盖排序字段amount和create_time。
- 执行计划关键点：
using filesort：排序未利用索引，导致MySQL需额外排序操作。
排序流程：数据先通过索引查找，再放入排序缓冲区；若数据量超过缓冲区（sort_buffer_size），则使用磁盘多路归并排序，性能急剧下降。
影响因素：排序字段长度、数据量、缓冲区大小等均影响性能。
- 问题根源：原索引未覆盖排序字段，导致排序回表查询数据。
- 解决方案：新建联合索引(status, amount DESC, create_time ASC)，使查询可直接通过索引获取排序后数据，避免filesort。
索引设计原则：
覆盖索引：索引包含WHERE条件字段和排序字段，减少回表操作。
排序方向匹配：索引中amount按DESC排序，create_time按ASC排序，与ORDER BY一致。
效果：优化后执行计划中Extra字段显示Using index，表明使用覆盖索引，无需额外排序。
- 问题场景：代码中存在for循环内逐条更新数据库状态的操作，导致1500次独立写操作。
- 解决方案：改为批量更新，例如每100条记录执行一次UPDATE，减少数据库写压力。
效果：写操作次数从3000次降至30次（按批量大小100计算），显著降低I/O开销。
- 测试环境（30万数据）：
优化前：查询时间≥1.5秒。
优化后：查询时间约0.4秒，提升3～4倍。
- 生产环境（3000万+数据，符合条件数据约300万）：
优化前：查询时间11～14秒。
优化后：查询时间约0.8秒，提升超10倍。
- 避免filesort：通过联合索引覆盖排序字段，使数据按需排序存储。
- 索引设计：
优先满足WHERE条件过滤。
排序字段需与索引顺序一致，包括排序方向。
- 批量操作：减少高频小事务，降低数据库负载。
- 监控与调优：定期分析慢查询日志，关注rows_examined和Extra字段。
- 索引维护：避免过度索引，定期清理未使用的索引以减少写开销。
- 参数调优：根据数据量调整sort_buffer_size和tmp_table_size等参数。
此次优化证明，扎实的SQL基础与对执行计划的深入理解是解决性能问题的关键。通过索引优化和代码调整，可显著提升查询效率。
热门标签
