一、性能优化的正确顺序
很多人一遇到MySQL慢就想着加缓存或分库，但正确顺序是：
1. 开慢查询日志 → 找到真正的慢SQL2. EXPLAIN分析 → 理解执行计划3. 添加/优化索引 → 通常能解决80%问题4. SQL重写 → 业务逻辑层面优化5. InnoDB参数调优 → 内存/IO层面优化6. 读写分离 → 读流量横向扩展7. 分库分表 → 写流量/数据量突破单机极限
二、慢查询日志配置与分析
-- 开启慢查询日志SET GLOBAL slow_query_log = ON;SET GLOBAL long_query_time = 1; -- 超过1秒记录SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';SET GLOBAL log_queries_not_using_indexes = ON; -- 未用索引的也记录
# 用pt-query-digest分析慢查询日志（Percona工具箱）pt-query-digest /var/log/mysql/slow.log --limit 10 \ # 只看Top 10 --since "2026-05-28 00:00:00" --until "2026-05-28 23:59:59" > slow_report.txt# 报告中重点关注：# Query_time: 执行时间# Rows_examined: 扫描行数（越大越慢）# Rows_sent: 返回行数# 比率Rows_sent/Rows_examined 越低越糟糕
三、EXPLAIN执行计划精读
EXPLAIN SELECT u.name, COUNT(o.id) as order_countFROM users uLEFT JOIN orders o ON u.id = o.user_idWHERE u.created_at > '2026-01-01'GROUP BY u.idORDER BY order_count DESCLIMIT 10;
+----+-------------+-------+------+------------------+------+---------+------+--------+------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+------------------+------+---------+------+--------+------------------------------------------+| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 500000 | Using where; Using temporary; filesort || 1 | SIMPLE | o | ref | idx_user_id | idx | 4 | u.id | 5 | NULL |+----+-------------+-------+------+------------------+------+---------+------+--------+------------------------------------------+⚠️ 红色警报： type=ALL → users表全表扫描50万行！ Using temporary → 用了临时表（GROUP BY代价） Using filesort → 内存中排序（ORDER BY代价）
修复方案：给users表加索引
-- 复合索引（顺序很重要！）-- 原则：等值查询列在前，范围查询列在后ALTER TABLE users ADD INDEX idx_created_name (created_at, id, name);-- 再次EXPLAINEXPLAIN SELECT...;-- type: range → 扫描创建时间范围，不再全表扫描-- rows: 12000 → 从50万降到1.2万
四、索引设计最佳实践
-- ❌ 错误：区分度低的列放前面CREATE INDEX idx_bad ON orders (status, user_id);-- status只有3个值，扫描大量数据后才能用user_id过滤-- ✅ 正确：高区分度列放前面CREATE INDEX idx_good ON orders (user_id, status, created_at);-- user_id区分度高，快速定位到某用户的订单-- ✅ 覆盖索引：查询字段都在索引中，避免回表-- 查询：SELECT id, status, amount FROM orders WHERE user_id=123CREATE INDEX idx_covering ON orders (user_id, status, amount);-- EXPLAIN Extra: "Using index" → 无需回表-- 前缀索引：对长字符串字段-- 先找合适的前缀长度SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM users; -- 0.94SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) FROM users; -- 0.91-- 前8位区分度已经很高ALTER TABLE users ADD INDEX idx_email_prefix (email(8));
五、InnoDB关键参数调优
[mysqld]# 缓冲池：最重要的参数，建议设置为物理内存的60-80%innodb_buffer_pool_size = 12G # 16G内存服务器# 缓冲池实例数：减少锁争用，建议每1-2G一个实例innodb_buffer_pool_instances = 8# Redo Log大小：写密集型场景增大，减少checkpoint频率innodb_log_file_size = 2Ginnodb_log_buffer_size = 64M# 刷盘策略：# 0 = 每秒刷盘（性能最好，断电丢1秒数据）# 1 = 每次提交刷盘（最安全，性能最差）# 2 = 每次提交写OS缓存，每秒刷盘（平衡）innodb_flush_log_at_trx_commit = 2 # 对大多数业务足够# 并发线程数：防止过多线程争用导致性能下降innodb_thread_concurrency = 16 # 通常 = CPU核心数 * 2# IO能力配置（SSD机器）innodb_io_capacity = 4000 # SSD的实际IOPSinnodb_io_capacity_max = 8000
六、常见性能问题快速诊断
-- 1. 找当前执行中的慢查询SELECT * FROM information_schema.PROCESSLISTWHERE TIME > 5 -- 执行超过5秒ORDER BY TIME DESC;-- 2. 找没有索引的表（超小表除外）SELECT TABLE_NAME, TABLE_ROWSFROM information_schema.TABLESWHERE TABLE_SCHEMA = 'your_db' AND TABLE_ROWS > 10000AND TABLE_NAME NOT IN ( SELECT DISTINCT TABLE_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db');-- 3. 找未使用的索引（长期运行后）SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAMEFROM performance_schema.table_io_waits_summary_by_index_usageWHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA = 'your_db'ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
MySQL优化80%的问题靠索引解决，只有在索引优化到位后，才值得考虑分库分表的复杂度。