优化分 5 大层次：架构层 → 配置参数 → 索引优化 → SQL 语句 → 运维监控，由上到下落地，优先改 SQL 和索引（收益最高），再调参数，最后考虑架构扩容。
说明：下面默认是 InnoDB（MySQL 默认存储引擎，生产几乎都用）
一、架构层面（高并发、大数据量场景）
读写分离 主库写，从库读；应用层路由，读请求走从库，分担主库压力。
简单：一主一从 / 一主多从
中间件：ProxySQL、MyCat、ShardingSphere-JDBC
注意：有同步延迟，强一致性场景不能读从库
分库分表 单表数据过大（InnoDB 建议单表控制在 2000w 以内，视字段而定）：
水平分表：按 user_id、order_id 哈希 / 范围分片
垂直分表：大字段（text、blob）拆到独立附表
优先分表，不要过早分库，分库带来分布式事务、跨库 join 成本很高
缓存兜底 Redis 缓存热点数据（商品、配置、用户基础信息），挡住大部分读请求，避免打到 MySQL。
缓存策略：Cache Aside（旁路缓存）最常用
避免：缓存穿透、击穿、雪崩
存储与硬件
SSD 优先，不要机械盘；随机 IO 是 MySQL 瓶颈
独立磁盘放 ibdata、redo log、binlog，减少 IO 争抢
内存优先给 InnoDB Buffer Pool
二、核心配置优化（my.cnf/my.ini，MySQL8.0）
原则：Buffer Pool 是最重要参数，不要盲目抄网上配置，按服务器内存配比
[mysqld]
# 基础
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# 1. InnoDB 内存（重中之重）
innodb_buffer_pool_size = 物理内存的 50%~70%
# 例：32G服务器 → 20G左右；不要超过70%，留内存给系统、连接、缓存
innodb_buffer_pool_instances = 8 # 大于16G时设置，一般每1~2G一个实例
innodb_log_file_size = 1G # redo log，8.0推荐512M~2G，不要太大（故障恢复慢）
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 # 1=强持久（默认，安全）；2=每秒刷盘，性能高、宕机丢1s数据
innodb_flush_method = O_DIRECT # 绕过OS缓存，避免双缓存，SSD必开
innodb_file_per_table = ON # 每张表独立ibd文件，方便回收空间
# 2. 连接相关
max_connections = 1000 # 不要无限大，连接越多内存开销越大，配合连接池
wait_timeout = 300
interactive_timeout = 300
# 连接池建议：应用侧（HikariCP）控制连接数，不要依赖MySQL开大max_connections
# 3. Binlog（主从、数据恢复）
log_bin = ON
binlog_format = ROW # 推荐ROW，主从数据一致性更好；STATEMENT有坑
sync_binlog = 1 # 1=每次事务刷盘，安全；0/100=提升性能，丢失风险
binlog_expire_logs_seconds = 86400*7 # 自动清理7天前binlog
# 4. 排序、分组、join临时内存（会话级，不要设置过大）
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
# 5. 其他
tmp_table_size = 64M
max_heap_table_size = 64M # 超过这个大小，临时表落地磁盘，性能暴跌
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
❌ 坑：sort_buffer /join_buffer 是每个连接单独分配，开大了高并发下瞬间吃光内存！
三、索引优化（收益最高，优先做）
1. 索引基础原则
优先 B+Tree 普通索引，少用唯一索引（唯一校验有开销）
联合索引遵循最左前缀
索引不是越多越好：索引加快查询，但降低写入（insert/update/delete）速度，每个索引都要维护
不索引：低区分度字段（性别、状态 0/1），索引几乎无效
2. 失效场景（高频踩坑）
索引列做函数运算：
where date(create_time)='2026-10-01'→ 索引失效，改成范围create_time between ...隐式类型转换：
where phone=13800138000，phone 是 varchar，会自动转数字，索引失效like
%关键词，前缀模糊查询，索引失效；关键词%可以走索引or 左右一边无索引，整个不走索引
不等于
!= <> not in容易不走索引
3. 推荐实践
主键：自增主键（聚簇索引有序，页分裂少），不要用 UUID 做主键
覆盖索引：查询字段全部在索引里，避免回表（Extra: Using index）
-- 好：索引 idx_uid_name(uid,name) select name from t where uid=1001;避免 select *，只查需要字段
定期监控冗余索引、未使用索引
-- 查看没用的索引（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;
四、SQL 语句优化（日常最常见瓶颈）
先用
explain分析执行计划 重点看字段：
type：最好是ref/range，最差ALL（全表扫描，必须优化）key：实际用到的索引rows：预估扫描行数，越小越好Extra：Using filesort：文件排序，需要优化 order by，加到联合索引Using temporary：创建临时表（group by/distinct 无索引）Using index：覆盖索引，优秀
优化规则
大表禁止
select count(*)实时统计；用 Redis、定时统计表计数避免大事务：事务越大，锁持有越久，容易锁等待、死锁、undo 膨胀
limit 深分页：
limit 100000,10很慢 → 改成主键分页：where id>100000 limit 10批量插入用
insert into t values (),(),()，不要循环单条 insert禁止 join 大表，尽量小表驱动大表
尽量少用
distinct，能业务层去重就不用数据库
五、锁、事务与 InnoDB 细节优化
InnoDB 行锁是基于索引，不走索引就会升级为表锁，并发直接废掉
尽量缩短事务执行时间，不要在事务里做网络调用、sleep
避免间隙锁：范围查询（between、< >）会产生间隙锁，高并发秒杀场景注意
死锁：统一资源访问顺序；减少 update 多表、多行
六、监控 & 慢查询（定位问题）
开启慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 超过1s的SQL记录
log_queries_not_using_indexes = ON # 记录没走索引的SQL
工具：
mysqldumpslow 分析慢日志
pt-query-digest（Percona Toolkit）
监控指标：QPS、TPS、连接数、锁等待、InnoDB 缓冲池命中率、redo 刷盘、binlog 延迟
关键健康指标
Buffer Pool 命中率 > 99% 比较健康
Innodb_row_lock_waits：锁等待次数持续走高 → 锁问题
Handler_read_rnd_next 持续暴涨 → 大量全表扫描
七、其他避坑
不要用 MyISAM（表锁，并发差，不支持事务）
大 DDL（加索引、改字段）不要业务高峰执行，8.0 支持 Instant DDL，但不是所有场景都支持；低版本用 pt-online-schema-change /gh-ost 在线改表，避免锁表
定期清理大表历史数据，不要直接 delete 大量数据（会产生大量 undo、碎片），推荐分批次 delete 或者分区表
分区表：按时间分区（日志、订单），删除历史分区极快，适合时序类数据
八、优化优先级总结（照着这个顺序排查）
抓慢 SQL，explain，补索引、改写 SQL（投入小收益最大 ✅）
检查事务、锁、死锁问题
调整 innodb_buffer_pool_size 等核心参数
热点数据加 Redis 缓存
读写分离、分表、升级 SSD
最后才考虑分库
本文原创作者：易君召，详见：https://www.yijunzhao.cn/authors/yijunzhao，转载请注明出处。
原文链接
欢迎访问 小易撩挨踢