一、性能优化的正确顺序
很多人一遇到MySQL慢就想着加缓存或分库，但正确顺序是：
1. 开慢查询日志 → 找到真正的慢SQL 2. 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_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2026-01-01' GROUP BY u.id ORDER BY order_count DESC LIMIT 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); -- 再次EXPLAIN EXPLAIN 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=123 CREATE INDEX idx_covering ON orders (user_id, status, amount); -- EXPLAIN Extra: "Using index" → 无需回表 -- 前缀索引：对长字符串字段 -- 先找合适的前缀长度 SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM users; -- 0.94 SELECT 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 = 2G innodb_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的实际IOPS innodb_io_capacity_max = 8000
六、常见性能问题快速诊断
-- 1. 找当前执行中的慢查询 SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 5 -- 执行超过5秒 ORDER BY TIME DESC; -- 2. 找没有索引的表（超小表除外） SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND TABLE_ROWS > 10000 AND TABLE_NAME NOT IN ( SELECT DISTINCT TABLE_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' ); -- 3. 找未使用的索引（长期运行后） SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA = 'your_db' ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
MySQL优化80%的问题靠索引解决，只有在索引优化到位后，才值得考虑分库分表的复杂度。