code4f1. MySQL优化全攻略：从索引设计到分库分表实战
作为关系型数据库的经典代表，MySQL的性能优化一直是开发者关注的焦点。记得第一次处理千万级数据表时，一个未优化的COUNT(*)查询让整个系统瘫痪了半小时，那次教训让我深刻认识到：数据库优化不是选修课，而是生存技能。本文将分享我在电商、金融等领域积累的MySQL优化方法论，涵盖索引设计、SQL调优、分库分表等核心场景。
需要模型API调用？ 免费领10W Token，多模型网关一键接入 Claude、DeepSeek 等主流模型。
2. 索引优化：B+树背后的设计哲学
2.1 索引选择的核心原则
B+树索引就像图书馆的目录系统——好的索引能让查询从全表扫描变成精准定位。但索引不是越多越好，我曾见过一个20个字段的表建了15个索引，导致写入性能下降60%。关键原则包括：
- 高频查询条件优先：WHERE子句中的常客字段必须索引
- 区分度大于30%：如性别字段只有两种值就不适合单独建索引
- 联合索引左前缀匹配：INDEX(a,b,c) 能支持 WHERE a=? AND b=? 但不支持 WHERE b=? AND c=?
2.2 实战中的索引陷阱
sql复制-- 反例：模糊查询使用左通配
SELECT * FROM users WHERE name LIKE '%张%';
-- 正例：使用右通配+全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_idx(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张*' IN BOOLEAN MODE);
注意：EXPLAIN执行计划中type=range表示索引范围扫描，而type=index意味着全索引扫描（依然低效）
3. SQL语句优化：从执行计划到改写技巧
3.1 执行计划深度解读
通过EXPLAIN分析一个订单查询案例：
sql复制EXPLAIN SELECT o.* FROM orders o
JOIN users u ON o.user_id=u.id
WHERE u.register_time>'2023-01-01'
ORDER BY o.create_time DESC LIMIT 100;
常见问题包括：
- Using filesort：排序字段无索引
- Using temporary：产生了临时表
- Select tables optimized away：优化器已做智能处理
3.2 分页查询优化方案
传统分页的性能杀手：
sql复制SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 10;
优化方案（延迟关联）：
sql复制SELECT a.* FROM articles a
INNER JOIN (SELECT id FROM articles ORDER BY id DESC LIMIT 100000, 10) tmp
ON a.id=tmp.id;
4. 分库分表：从架构设计到平滑迁移
4.1 拆分策略对比
| 策略类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 水平拆分 | 数据量大但结构简单 | 扩展性强 | 跨分片查询复杂 |
| 垂直拆分 | 字段多且访问模式差异大 | 降低单表宽度 | 需要业务改造 |
| 哈希取模 | 随机分布需求 | 数据均匀 | 扩容困难 |
| 范围分片 | 有明显冷热区分 | 易于管理 | 可能产生热点 |
4.2 使用ShardingSphere实现零停机迁移
- 配置双写策略：
yaml复制spring:
shardingsphere:
datasource:
names: ds_0,ds_1
sharding:
tables:
t_order:
actual-data-nodes: ds_$->{0..1}.t_order_$->{0..15}
database-strategy:
inline:
algorithm-expression: ds_$->{order_id % 2}
table-strategy:
inline:
algorithm-expression: t_order_$->{order_id % 16}
- 通过影子库验证数据一致性：
sql复制-- 在测试环境执行
CHECK TABLE t_order WITH BACKUP TABLE;
5. 实战中的避坑指南
5.1 索引失效的六大场景
- 隐式类型转换：WHERE varchar_col=123
- 函数操作：WHERE DATE(create_time)='2023-01-01'
- 不等于操作：WHERE status<>1
- IS NULL判断：WHERE name IS NULL
- OR条件未全覆盖：WHERE a=1 OR b=2（需a、b都有索引）
- 最左前缀缺失：联合索引INDEX(a,b)但查询WHERE b=?
5.2 连接池配置黄金参数
properties复制# Druid连接池推荐配置（8核16G机器）
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=20
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-wait=3000
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
6. 监控与持续优化体系
6.1 性能基线监控
配置Prometheus+Granafa监控看板：
-
关键指标：
- QPS/TPS波动
- 慢查询比例
- 连接池等待数
- InnoDB缓冲池命中率
-
报警阈值设置：
yaml复制groups: - name: mysql-alert rules: - alert: HighSlowQueryRate expr: rate(mysql_global_status_slow_queries[1m]) > 0.1 for: 5m
6.2 压测工具选型
- sysbench：基准测试
- JMeter：复杂场景模拟
- go-tpc：TPC-C标准测试
在最近一次大促前，通过sysbench发现当并发超过200时，CPU瓶颈导致95%延迟从15ms飙升到800ms，最终通过增加只读实例解决。
7. 前沿技术演进观察
新一代优化技术值得关注：
- InnoDB并行查询（MySQL 8.0.14+）
- 直方图统计信息（替代索引区分度估算）
- 不可见索引（测试索引不影响生产）
- 函数索引（针对JSON等复杂类型）
比如通过函数索引优化JSON查询：
sql复制ALTER TABLE products ADD INDEX idx_category((CAST(properties->'$.category' AS CHAR(20))));
优化永无止境，每次MySQL版本升级都应该重新评估现有的优化策略。最近将5.7升级到8.0后，原先的很多手工优化被内置功能替代，这提醒我们：既要掌握底层原理，也要保持技术敏感度。