MySQL性能优化实战：索引、SQL与分库分表

1. MySQL优化全攻略：从理论到实战

记得刚入行时接手过一个日活百万的社区项目，数据库查询经常超时，页面加载需要5秒以上。经过一系列优化后，最终将响应时间控制在300毫秒内。这个过程中积累的MySQL优化经验，今天系统地分享给大家。

MySQL作为最流行的关系型数据库，优化工作贯穿项目全生命周期。合理的优化能让查询性能提升10倍以上，而不当的优化反而可能导致性能下降。我们将从索引设计、SQL编写、架构扩展三个维度，深入剖析MySQL优化的核心方法论。

2. 索引优化：数据库的性能加速器

2.1 索引的工作原理与选择

B+树是MySQL索引的默认数据结构，其高度通常维持在3-4层。以主键ID为例，假设每个节点存储500个键值，三层B+树就能支持500^3=1.25亿条记录的高效查询。

选择索引列时需要考虑：

• 区分度：Cardinality值越高越好，比如手机号比性别更适合建索引
• 数据长度：优先选择短字段，如INT比VARCHAR(255)更优
• 查询频率：WHERE条件中最常出现的字段

注意：不要在低区分度字段上盲目建索引。曾有个项目在"性别"字段建了索引，反而使写入性能下降30%。

2.2 复合索引的最左匹配原则

建立(user_id, create_time)的复合索引时：

• 能加速WHERE user_id=?
• 能加速WHERE user_id=? AND create_time>?
• 但不能加速WHERE create_time>?的查询

复合索引字段顺序建议：

1. 等值查询字段在前
2. 范围查询字段在后
3. 常用排序字段可考虑加入

2.3 索引失效的常见陷阱

1. 隐式类型转换：WHERE user_id='123'（user_id是INT）
2. 使用函数：WHERE DATE(create_time)='2023-01-01'
3. 模糊查询：WHERE name LIKE '%张'
4. 使用OR条件：WHERE a=1 OR b=2
5. 使用!=或<>操作符

实测案例：一个WHERE status!=1的查询，在status字段有索引的情况下仍然全表扫描，改为WHERE status IN (2,3,4)后性能提升20倍。

3. SQL语句优化实战技巧

3.1 EXPLAIN执行计划详解

执行计划中的关键指标：

• type：从优到劣 system > const > eq_ref > ref > range > index > ALL
• rows：预估扫描行数
• Extra：Using filesort和Using temporary需要特别注意

分析案例：

EXPLAIN SELECT * FROM orders WHERE user_id=100 ORDER BY create_time DESC;

如果出现Using filesort，说明需要优化排序操作。

3.2 分页查询优化方案

典型反例：

SELECT * FROM articles LIMIT 100000, 10;

优化方案：

1. 延迟关联

SELECT a.* FROM articles a JOIN (SELECT id FROM articles ORDER BY create_time DESC LIMIT 100000, 10) b ON a.id = b.id;

2. 记录位点（适合无限滚动）

SELECT * FROM articles WHERE create_time < '2023-01-01 00:00:00' ORDER BY create_time DESC LIMIT 10;

3.3 事务与锁优化

1. 避免长事务：单个事务尽量不超过1秒
2. 合理设置隔离级别：RR可能导致更多锁冲突
3. 死锁排查：SHOW ENGINE INNODB STATUS
4. 锁升级监控：SELECT * FROM performance_schema.metadata_locks

4. 分库分表架构设计

4.1 何时需要考虑分库分表

一般建议单表数据量达到以下阈值时考虑拆分：

• 数据量：MySQL单表建议不超过500万行
• 数据大小：单表不超过10GB
• 性能指标：查询延迟超过500ms

4.2 分片策略对比

4.3 分库分表后的挑战与解决方案

1. 分布式ID生成：

   • 雪花算法（推荐）
   • UUID（索引效率低）
   • 数据库自增序列（需要中心化）

2. 跨库查询：

   • 字段冗余
   • 数据异构
   • 使用搜索引擎

3. 分布式事务：

   • 最终一致性
   • TCC模式
   • SAGA模式

5. 实战中的经验总结

1. 监控先行：部署Prometheus+Granfa监控QPS、慢查询、连接数等指标
2. 渐进式优化：不要一开始就过度设计，先满足当前需求
3. 定期维护：每周执行ANALYZE TABLE更新统计信息
4. 参数调优：
   • innodb_buffer_pool_size：建议设为物理内存的70%
   • max_connections：根据实际需要设置，避免过高
   • sort_buffer_size：对于复杂排序可适当调大

曾经遇到一个案例：将innodb_flush_log_at_trx_commit从1改为2，写入性能提升5倍，但需要评估数据安全性要求。

优化是一个持续的过程，每个业务场景都有其特殊性。建议先通过慢查询日志找出最需要优化的SQL，再结合业务特点制定优化方案。记住：没有放之四海皆准的最优解，只有最适合当前业务场景的解决方案。