MySQL索引优化与慢查询实战指南-开发者社区

1. MySQL索引调优的核心逻辑

数据库性能优化的本质就是一场与磁盘I/O的战争。作为一名长期奋战在一线的DBA，我见过太多因为索引不当导致的性能灾难。有一次，一个简单的用户查询竟然拖垮了整个生产库，排查后发现仅仅是因为漏建了一个联合索引。

MySQL的索引优化遵循一个黄金法则：用空间换时间。通过额外的存储空间（索引）来减少磁盘扫描量，从而提升查询速度。但索引不是越多越好，每增加一个索引都会带来写入时的额外开销。

2. 慢查询定位实战技巧

2.1 慢查询日志配置要点

生产环境中我推荐这样配置慢查询日志：

# /etc/my.cnf 关键配置 slow_query_log = ON slow_query_log_file = /data/mysql/slow.log long_query_time = 1 # 超过1秒的查询记录 log_queries_not_using_indexes = ON min_examined_row_limit = 100 # 扫描行数少于100的不记录

注意：日志文件一定要放在数据盘而非系统盘，避免日志写入影响系统运行。我曾经遇到因为慢日志写满系统盘导致MySQL崩溃的案例。

2.2 日志分析工具链

除了pt-query-digest，我常用的分析组合是：

先用awk快速统计慢查询类型：

awk '/Query_time/{print $NF}' slow.log | sort | uniq -c | sort -nr

再用mysqldumpslow分析特定模式：

mysqldumpslow -s t -t 10 slow.log

对于Java应用，建议在JDBC连接串加上参数：

jdbc:mysql://host:3306/db?profileSQL=true&useSlowQueryLog=true

3. EXPLAIN执行计划深度解析

3.1 type字段的实战意义

执行计划中的type字段是性能判断的第一指标。在我的调优经验中：

const/system：理想状态，多见于主键查询
eq_ref：联表查询时被驱动表通过主键或唯一索引访问
ref：普通索引等值查询，要注意索引的选择性
range：范围查询，要关注rows字段的值
index：全索引扫描，常见于覆盖索引但需要排序的情况
ALL：全表扫描，必须优化

3.2 容易被忽视的Extra信息

除了常见的Using index、Using filesort，这些Extra信息也值得关注：

Using index condition：使用了索引条件下推(ICP)
Using MRR：使用了多范围读优化
Using join buffer：需要使用join buffer，说明索引可能没用好

4. 索引优化高阶策略

4.1 联合索引设计原则

设计联合索引时，我遵循"最左前缀+高频优先+区分度高"三原则：

将等值查询条件放在最左边
高频查询字段优先
区分度高的字段靠前

例如用户表查询场景：

SELECT * FROM users WHERE province='浙江' AND city='杭州' AND age>20

应该建立联合索引：(province, city, age)

4.2 索引跳跃扫描优化

MySQL 8.0新增的索引跳跃扫描特性可以在特定场景下突破最左前缀限制。例如有索引(a,b)，查询where b=1时，8.0可以拆解为：

WHERE a=1 AND b=1 OR a=2 AND b=1 ...

虽然能用上索引，但效率取决于a的取值数量，要谨慎使用。

5. SQL语句优化实战

5.1 分页查询优化方案

对于深分页问题，我常用的优化方案：

延迟关联：

SELECT * FROM orders INNER JOIN ( SELECT id FROM orders WHERE user_id=100 ORDER BY create_time DESC LIMIT 100000,10 ) AS tmp USING(id)

游标分页：

SELECT * FROM orders WHERE user_id=100 AND id > 100000 ORDER BY id LIMIT 10

5.2 隐式类型转换陷阱

字符集不一致也会导致隐式转换。曾经处理过一个性能问题，原因是：

SELECT * FROM table1 JOIN table2 ON table1.utf8_field = table2.utf8mb4_field

虽然字段内容看似相同，但字符集不同导致索引失效。

6. 架构级优化方案

6.1 读写分离实现要点

实施读写分离时要注意：

主从延迟监控：show slave status查看Seconds_Behind_Master
写后读一致性：关键业务读操作可以走主库
分库分表后，同一事务跨库操作需要分布式事务支持

6.2 分库分表策略选择

我常用的分片策略：

范围分片：按时间或ID范围
哈希分片：均匀分布数据
目录分片：通过路由表维护映射关系

对于JOIN操作多的场景，建议使用相同分片键保证相关数据在同一分片。

7. 性能监控体系搭建

完善的监控应该包括：

实时监控：Prometheus + Grafana
慢查询分析：pt-query-digest
性能剖析：performance_schema
压测工具：sysbench

关键指标阈值建议：

CPU使用率：<70%
磁盘IO等待：<20%
连接数使用率：<80%
缓存命中率：>95%

8. 真实案例复盘

8.1 电商订单查询优化

某电商平台订单查询超时，原SQL：

SELECT * FROM orders WHERE user_id=? AND status=? ORDER BY create_time DESC LIMIT 10

优化步骤：

建立联合索引(user_id, status, create_time)
改写为只查询必要字段
对历史订单进行归档处理

优化后响应时间从2s降到50ms。

8.2 社交平台Feed流优化

Feed流查询存在严重的filesort问题。通过以下措施优化：

使用覆盖索引
引入Redis缓存热数据
对非实时内容采用预生成策略

9. 索引设计checklist

在创建新索引前，我都会检查：

是否已有类似索引可以复用
字段区分度是否足够高
是否会显著影响写入性能
是否满足最左前缀原则
是否考虑了未来业务变化

10. 性能优化文化建议

最后分享几点团队实践建议：

建立SQL审核流程
新功能上线前进行性能评估
定期进行索引健康检查
建立性能基线指标
培养开发人员的数据库意识

记住，最好的优化是在设计阶段就考虑性能，而不是等问题发生后再补救。每次优化都要有完整的数据支撑和效果验证，避免盲目调整。

MySQL索引优化与慢查询实战指南