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>?的查询
复合索引字段顺序建议：
- 等值查询字段在前
- 范围查询字段在后
- 常用排序字段可考虑加入
2.3 索引失效的常见陷阱
- 隐式类型转换：
WHERE user_id='123'（user_id是INT） - 使用函数：
WHERE DATE(create_time)='2023-01-01' - 模糊查询：
WHERE name LIKE '%张' - 使用OR条件：
WHERE a=1 OR b=2 - 使用!=或<>操作符
实测案例：一个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;优化方案：
- 延迟关联
SELECT a.* FROM articles a JOIN (SELECT id FROM articles ORDER BY create_time DESC LIMIT 100000, 10) b ON a.id = b.id;- 记录位点（适合无限滚动）
SELECT * FROM articles WHERE create_time < '2023-01-01 00:00:00' ORDER BY create_time DESC LIMIT 10;3.3 事务与锁优化
- 避免长事务：单个事务尽量不超过1秒
- 合理设置隔离级别：RR可能导致更多锁冲突
- 死锁排查：
SHOW ENGINE INNODB STATUS - 锁升级监控：
SELECT * FROM performance_schema.metadata_locks
4. 分库分表架构设计
4.1 何时需要考虑分库分表
一般建议单表数据量达到以下阈值时考虑拆分：
- 数据量：MySQL单表建议不超过500万行
- 数据大小：单表不超过10GB
- 性能指标：查询延迟超过500ms
4.2 分片策略对比
| 策略类型 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| 范围分片 | 易于扩展 | 可能热点 | 有时间属性的数据 |
| 哈希分片 | 分布均匀 | 难以范围查询 | 无明显分区特征的数据 |
| 目录分片 | 灵活 | 需要维护映射 | 复杂分片规则 |
4.3 分库分表后的挑战与解决方案
分布式ID生成：
- 雪花算法（推荐）
- UUID（索引效率低）
- 数据库自增序列（需要中心化）
跨库查询：
- 字段冗余
- 数据异构
- 使用搜索引擎
分布式事务：
- 最终一致性
- TCC模式
- SAGA模式
5. 实战中的经验总结
- 监控先行：部署Prometheus+Granfa监控QPS、慢查询、连接数等指标
- 渐进式优化：不要一开始就过度设计，先满足当前需求
- 定期维护：每周执行
ANALYZE TABLE更新统计信息 - 参数调优：
innodb_buffer_pool_size：建议设为物理内存的70%max_connections：根据实际需要设置，避免过高sort_buffer_size：对于复杂排序可适当调大
曾经遇到一个案例：将innodb_flush_log_at_trx_commit从1改为2，写入性能提升5倍，但需要评估数据安全性要求。
优化是一个持续的过程，每个业务场景都有其特殊性。建议先通过慢查询日志找出最需要优化的SQL，再结合业务特点制定优化方案。记住：没有放之四海皆准的最优解，只有最适合当前业务场景的解决方案。