MySQL 性能优化实战指南
本文整理自实际项目中的 MySQL 优化经验，涵盖索引优化、查询优化、锁机制和架构设计等多个维度。
一、索引优化
1.1 索引基本原则
- 最左前缀原则：联合索引
(a,b,c)查询时必须从最左列开始 - 覆盖索引：查询字段全部在索引中，避免回表
- 索引选择性：选择性高的列更适合建索引
1.2 索引失效场景
-- ❌ 索引失效：对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- ✅ 优化方案
SELECT * FROM users
WHERE create_time >= '2024-01-01'
AND create_time < '2025-01-01';
-- ❌ 索引失效：模糊查询以%开头
SELECT * FROM users WHERE name LIKE '%张%';
-- ✅ 优化方案：使用全文索引
SELECT * FROM users WHERE MATCH(name) AGAINST('张');1.3 EXPLAIN 分析
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 10;| 字段 | 说明 | 优化目标 |
|---|---|---|
| type | 访问类型 | 至少达到 range，最优 ref |
| key | 实际使用的索引 | 避免 NULL |
| rows | 扫描行数 | 越少越好 |
| Extra | 额外信息 | 出现 Using filesort 需优化 |
二、查询优化
2.1 分页优化
-- ❌ 深度分页性能差
SELECT * FROM orders LIMIT 100000, 10;
-- ✅ 优化方案 1：子查询
SELECT * FROM orders
WHERE id > (SELECT id FROM orders LIMIT 100000, 1)
LIMIT 10;
-- ✅ 优化方案 2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 100000, 10) tmp
ON o.id = tmp.id;2.2 JOIN 优化
-- ✅ 小表驱动大表
SELECT * FROM small_table s
LEFT JOIN large_table l ON s.id = l.small_id;
-- ✅ 确保关联字段有索引
-- small_table.id (主键)
-- large_table.small_id (普通索引)三、锁机制
3.1 行锁 vs 表锁
| 锁类型 | 粒度 | 并发度 | 适用场景 |
|---|---|---|---|
| 行锁 | 行级 | 高 | 高并发写操作 |
| 表锁 | 表级 | 低 | 批量操作 |
3.2 死锁避免
-- ❌ 可能死锁：不同事务以不同顺序更新
-- 事务 A: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 事务 B: UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- ✅ 避免方案：统一更新顺序
-- 所有事务都按 id 从小到大顺序更新四、架构优化
4.1 读写分离
主库（写）→ binlog → 从库（读）
↓
数据同步4.2 分库分表策略
| 策略 | 适用场景 | 缺点 |
|---|---|---|
| 垂直分库 | 业务模块解耦 | 跨库 JOIN 复杂 |
| 水平分表 | 单表数据量大 | 分布式事务 |
| 分区表 | 历史数据归档 | 维护成本高 |
五、监控与调优
5.1 慢查询日志
# my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 超过 2 秒的查询5.2 性能监控指标
- QPS/TPS：每秒查询/事务数
- 连接数：当前连接数 / 最大连接数
- 缓冲池命中率：
Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads
总结
MySQL 性能优化是一个系统工程，需要：
- 合理设计索引：遵循最左前缀，避免索引失效
- 优化 SQL 查询：减少扫描行数，避免深度分页
- 理解锁机制：避免死锁，提高并发度
- 架构层面优化：读写分离、分库分表
- 持续监控：慢查询日志、性能指标监控
优化原则：先优化 SQL，再优化索引，最后考虑架构调整