网硕互联帮助中心MySQL性能优化是后端开发和DBA的必修课。本文整理了笔者在生产环境中实战验证的20条MySQL 8.0调优经验，涵盖索引优化、慢查询分析、InnoDB参数、SQL优化、读写分离与分库分表全链路，每条都有真实案例支撑。
一、索引优化：80%的性能问题出在这里
1.1 覆盖索引：避免回表
|
— 假设有一张用户表 CREATE TABLE users ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id VARCHAR(32) NOT NULL, phone VARCHAR(20) NOT NULL, nickname VARCHAR(64), status TINYINT DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_phone (phone), INDEX idx_status_created (status, created_at) ) ENGINE=InnoDB; |
|
— ❌ 慢查询：需要回表取nickname SELECT id, user_id, phone, nickname FROM users WHERE user_id = 'U10086'; — ✅ 优化：建立覆盖索引，直接从索引获取数据 ALTER TABLE users ADD INDEX idx_uid_phone_nick (user_id, phone, nickname); SELECT id, user_id, phone, nickname FROM users WHERE user_id = 'U10086'; |
原理：InnoDB的二级索引叶子节点存储的是主键值，查出主键后还要回表到聚簇索引取完整行。如果查询字段全在索引中（覆盖索引），就省去了回表操作。
1.2 联合索引与最左前缀原则
|
— 联合索引 (status, created_at, user_id) — 以下查询能走索引： SELECT * FROM users WHERE status = 1; — ✅ 命中 SELECT * FROM users WHERE status = 1 AND created_at > '2026-01-01'; — ✅ 命中 SELECT * FROM users WHERE status = 1 AND created_at > '2026-01-01' AND user_id LIKE 'U%'; — ✅ 命中 — 以下查询不能有效走索引： SELECT * FROM users WHERE created_at > '2026-01-01'; — ❌ 跳过了status SELECT * FROM users WHERE user_id = 'U10086'; — ❌ 跳过了前两列 |
1.3 索引优化速查表
|
场景 |
推荐索引类型 |
注意事项 |
|
等值查询 |
普通B+Tree索引 |
最常用 |
|
范围查询 |
联合索引，范围列放最后 |
范围查询后的列无法走索引 |
|
排序 |
覆盖索引包含排序列 |
避免filesort |
|
模糊查询 |
前缀匹配可走索引 |
`LIKE 'abc%'`可以，`LIKE '%abc'`不行 |
|
去重统计 |
前缀索引 |
`COUNT(DISTINCT LEFT(col, 10))`评估前缀长度 |
|
JSON查询 |
函数索引 |
MySQL 8.0+支持 |
二、慢查询分析：用EXPLAIN看透执行计划
2.1 EXPLAIN关键字段解读
|
EXPLAIN SELECT u.nickname, o.order_no, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1 AND o.created_at > '2026-01-01' ORDER BY o.amount DESC LIMIT 20; |
|
字段 |
含义 |
关注点 |
|
type |
访问类型 |
至少达到range，最好const/ref |
|
key |
实际使用的索引 |
为NULL说明没走索引 |
|
rows |
预估扫描行数 |
越小越好 |
|
filtered |
过滤比例 |
低于10%说明索引选择不佳 |
|
Extra |
额外信息 |
出现Using filesort/Using temporary要优化 |
2.2 type字段等级对照
|
性能从好到差： const > eq_ref > ref > range > index > ALL │ │ │ │ │ │ │ │ │ │ │ └─ 全表扫描（必须优化） │ │ │ │ └─ 全索引扫描 │ │ │ └─ 范围扫描 │ │ └─ 非唯一索引等值查找 │ └─ 唯一索引等值关联 └─ 主键/唯一索引等值查询 |
2.3 开启慢查询日志
|
— 查看慢查询配置 SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time'; — 动态开启慢查询日志 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'; |
|
# 使用mysqldumpslow分析慢查询 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 参数说明： # -s t 按总耗时排序 # -s l 按锁定时间排序 # -s r 按返回行数排序 # -t 10 只显示前10条 |
三、InnoDB参数调优
3.1 核心参数配置
|
# my.cnf 核心调优配置 [mysqld] # ===== 内存相关 ===== # 缓冲池大小，通常设为物理内存的60-70% innodb_buffer_pool_size = 8G # MySQL 8.0可以设置多个缓冲池实例，减少锁竞争 innodb_buffer_pool_instances = 8 # ===== 日志相关 ===== # Redo Log大小，写入密集场景建议调大 innodb_redo_log_capacity = 4G # Binlog配置 binlog_format = ROW binlog_row_image = MINIMAL expire_logs_days = 7 sync_binlog = 1000 # ===== IO相关 ===== # 刷脏页策略，0=依赖OS, 1=每次提交刷盘, 2=每次提交写OS buffer innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT # 并发相关 innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 # ===== 连接相关 ===== max_connections = 500 wait_timeout = 28800 interactive_timeout = 28800 # ===== 临时表 ===== tmp_table_size = 256M max_heap_table_size = 256M # ===== 排序 ===== sort_buffer_size = 4M join_buffer_size = 4M |
3.2 参数调优对照表
|
参数 |
默认值 |
推荐值（16G内存） |
说明 |
|
innodb_buffer_pool_size |
128M |
10G |
缓存数据和索引，最重要 |
|
innodb_buffer_pool_instances |
1 |
8 |
减少缓冲池锁竞争 |
|
innodb_redo_log_capacity |
100MB |
4G |
影响写入性能和崩溃恢复 |
|
innodb_io_capacity |
200 |
2000 |
SSD可调高 |
|
max_connections |
151 |
500 |
根据业务并发调整 |
|
tmp_table_size |
16M |
256M |
临时表内存大小 |
|
sort_buffer_size |
256K |
4M |
排序缓冲区 |
注意：sort_buffer_size 和 join_buffer_size 是每连接分配的，不要设太大，否则高并发时内存会爆。
四、SQL语句优化技巧
4.1 批量操作代替循环单条
|
# ❌ 慢：循环插入1000条 for user in user_list: cursor.execute("INSERT INTO users (user_id, phone) VALUES (%s, %s)", (user['id'], user['phone'])) # ✅ 快：批量插入 values = [(u['id'], u['phone']) for u in user_list] cursor.executemany("INSERT INTO users (user_id, phone) VALUES (%s, %s)", values) |
4.2 避免SELECT *
|
— ❌ 传输不必要的列，无法利用覆盖索引 SELECT * FROM orders WHERE user_id = 10086; — ✅ 只查需要的列 SELECT order_no, amount, status FROM orders WHERE user_id = 10086; |
4.3 分页优化
|
— ❌ 深度分页，越往后越慢（LIMIT 1000000, 20） SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; — ✅ 方案1：游标分页（推荐） SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20; — ✅ 方案2：延迟关联 SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id = t.id; |
4.4 常见SQL反模式与正解
|
反模式 |
问题 |
正解 |
|
`WHERE YEAR(created_at) = 2026` |
函数导致索引失效 |
`WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'` |
|
`WHERE col IS NOT NULL` |
可能不走索引 |
建立IS NULL优化索引或用默认值替代NULL |
|
`OR`连接不同字段 |
可能导致全表扫描 |
用`UNION ALL`拆分 |
|
`ORDER BY RAND()` |
随机排序全表扫描 |
预生成随机ID再查询 |
|
`COUNT(*)`大表 |
慢 |
维护计数器表或使用估算 |
五、读写分离与分库分表
5.1 读写分离架构
|
┌──────────────────┐ │ 应用服务层 │ │ (读写请求分离) │ └────────┬─────────┘ │ ┌──────────┴──────────┐ │ │ ┌────────▼────────┐ ┌───────▼────────┐ │ 主库 (写) │ │ 从库 (读) │ │ MySQL Master │───▶│ MySQL Slave │ │ │ 复制 │ │ └─────────────────┘ └────────────────┘ │ ┌──────────┴──────────┐ │ │ ┌────────▼────────┐ ┌───────▼────────┐ │ 从库2 (读) │ │ 从库3 (读) │ │ MySQL Slave2 │ │ MySQL Slave3 │ └────────────────┘ └────────────────┘ |
5.2 分库分表策略选择
|
策略 |
适用场景 |
优点 |
缺点 |
|
垂直分库 |
按业务拆分 |
职责清晰 |
不能解决单表数据量大的问题 |
|
垂直分表 |
大字段拆分 |
减少IO |
跨表查询需要JOIN |
|
水平分表 |
单表超千万行 |
分散压力 |
跨分片查询复杂 |
|
水平分库+分表 |
单库单表都瓶颈 |
最大扩展性 |
运维复杂度最高 |
5.3 分片键选择建议
|
// ShardingSphere 分库分表配置示例（Java） // 按user_id取模分表 // user_id % 4 = 0 → user_0 // user_id % 4 = 1 → user_1 // … // 分片规则配置 // 实际配置以ShardingSphere官方文档为准 // 关键点：分片键要能覆盖80%以上的查询条件 |
|
— 分片键选择评估 — 1. 统计高频查询的WHERE条件 SELECT argument, count FROM performance_schema.events_statements_summary_by_digest ORDER BY count DESC LIMIT 20; — 2. 选择出现频率最高的字段作为分片键 — 通常user_id是最常见的分片键选择 |
5.4 分库分表后的难题与方案
|
┌────────────────┬─────────────────────────────────────────────┐ │ 难题 │ 解决方案 │ ├────────────────┼─────────────────────────────────────────────┤ │ 跨分片查询 │ 1. 冗余字段 2. 异步同步 3. 限制跨片查询 │ │ 跨分片JOIN │ 1. 广播表 2. 绑定表 3. 应用层组装 │ │ 分布式事务 │ 1. Seata AT模式 2. TCC 3. 最终一致性(消息表) │ │ 全局唯一ID │ 1. 雪花算法 2. 号段模式 3. UUID │ │ 分页排序 │ 1. 各分片查询后合并 2. 禁止深度跳页 │ │ 数据迁移 │ 1. 双写灰度 2. 影子表 3. 在线DDL工具 │ └────────────────┴─────────────────────────────────────────────┘ |
六、MySQL 8.0新特性利用
6.1 隐藏索引（Invisible Index）
|
— 上线新索引前先设为隐藏，观察无影响后再正式启用 ALTER TABLE orders ADD INDEX idx_test (user_id, status) INVISIBLE; — 确认无影响后设为可见 ALTER TABLE orders ALTER INDEX idx_test VISIBLE; — 如有负面影响，直接删除 ALTER TABLE orders DROP INDEX idx_test; |
6.2 降序索引
|
— MySQL 8.0真正支持降序索引（8.0之前只是语法支持） CREATE INDEX idx_desc ON orders (user_id, created_at DESC); — 查询时匹配降序，避免filesort SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC LIMIT 10; |
6.3 直方图统计
|
— 对没有索引的列创建直方图，帮助优化器选择更好的执行计划 ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS; — 查看直方图 SELECT * FROM information_schema.column_statistics WHERE table_name = 'orders'; |
七、监控与运维
7.1 关键监控指标
|
— 当前连接数 SHOW STATUS LIKE 'Threads_connected'; — 缓冲池命中率（应 > 99%） SELECT (1 – (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_rate FROM (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') a, (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') b; — 慢查询数量 SHOW STATUS LIKE 'Slow_queries'; — 死锁信息 SHOW ENGINE INNODB STATUS\\G |
7.2 运维建议
在生产环境中，如果团队运维人力有限，自建MySQL主从、备份、监控是一笔不小的成本。笔者所在团队在业务规模上来后，逐步将MySQL迁移到了[XX云]云数据库。云数据库自带主从高可用、自动备份、慢查询分析等功能，还有性能洞察和SQL优化建议，对于中小团队来说性价比很高。核心交易库可以选高可用版，读写分离的从库可以弹性增减，大促时临时加从库应对读流量高峰。
八、20条调优经验速查总结
|
序号 |
经验 |
优先级 |
|
1 |
覆盖索引避免回表 |
★★★★★ |
|
2 |
联合索引遵循最左前缀 |
★★★★★ |
|
3 |
EXPLAIN分析执行计划 |
★★★★★ |
|
4 |
innodb_buffer_pool_size设为内存60-70% |
★★★★★ |
|
5 |
开启慢查询日志 |
★★★★☆ |
|
6 |
避免SELECT * |
★★★★☆ |
|
7 |
批量操作代替循环单条 |
★★★★☆ |
|
8 |
深度分页用游标分页 |
★★★★☆ |
|
9 |
函数操作导致索引失效要避免 |
★★★★☆ |
|
10 |
innodb_redo_log_capacity调大 |
★★★★☆ |
|
11 |
sync_binlog和flush_log_at_trx_commit权衡 |
★★★☆☆ |
|
12 |
sort_buffer_size不要设太大 |
★★★☆☆ |
|
13 |
读写分离分散读压力 |
★★★☆☆ |
|
14 |
单表超千万考虑分表 |
★★★☆☆ |
|
15 |
利用隐藏索引灰度上线 |
★★★☆☆ |
|
16 |
降序索引优化排序 |
★★☆☆☆ |
|
17 |
直方图辅助优化器 |
★★☆☆☆ |
|
18 |
定期ANALYZE TABLE更新统计信息 |
★★☆☆☆ |
|
19 |
监控缓冲池命中率 |
★★☆☆☆ |
|
20 |
死锁日志定期排查 |
★★☆☆☆ |
写在最后
MySQL性能优化是一个系统工程，不是调几个参数就能解决的。核心思路是：先定位问题（慢查询日志+EXPLAIN），再针对性优化（索引+SQL+参数），最后做架构层面的扩展（读写分离+分库分表）。
索引优化能解决80%的问题，InnoDB参数调优能再解决15%，剩下的5%才需要分库分表。不要一上来就分库分表，那是最后的手段。
以上就是笔者在生产环境中验证过的20条调优经验，有问题欢迎评论区交流，看到会回复。
评论前必须登录！
注册