适用 MySQL 5.7 / 8.0，默认 InnoDB，不罗列玄学方案，优先低成本、高收益手段
一、索引优化（收益最高，优先做）
1. 索引设计原则
最左前缀原则：联合索引
idx(a,b,c)，查询条件必须带上最左列a才能走索引；范围条件（> < between like 前缀通配）后面的列失效。 ✅where a=1 and b=2走索引 ❌where b=2 and c=3不走 ❌where a>1 and b=2：只有 a 用到索引，b 失效区分度优先：选择性高的字段放联合索引前面（性别、状态这类低区分度不要单独建索引）。
覆盖索引优先：查询字段全部在索引里，避免回表（
Extra: Using index）-- 不要 select * select id,name from t where uid=100; -- 索引 idx_uid(uid,name) 就是覆盖索引禁止过度索引：单表索引建议控制在 5 个以内；索引会加速查询、减慢写入（insert/update/delete）。
前缀索引：长字符串（url、摘要）只建前缀索引，降低索引体积
create index idx_url on t(url(64));避免索引失效场景
函数、运算、隐式转换：
where age+1=18/where phone='13800138000'但 phone 是 intlike '%关键词'前导通配不走 B + 树索引；需要全文检索用FULLTEXT或者 ESor两边字段没有都建索引，容易不走索引，可以改成union all
2. 索引检查工具
-- 查看执行计划
explain select ...;
-- 重点看 type（ref/range 好，ALL 全表扫描危险）、key、rows、Extra
Extra 关键字：
Using filesort：需要额外排序，尽量优化 order by 走索引排序Using temporary：创建临时表，常见 group by 没索引Using index：覆盖索引，优秀
3. 主键选择
InnoDB 主键是聚簇索引，推荐自增主键 bigint，不要用 uuid、随机字符串做主键（页分裂、碎片暴涨）
主键尽量小，二级索引叶子节点都会存主键值
二、SQL 语句写法优化（零成本）
杜绝
select *，只查需要字段，减少 IO、方便覆盖索引分页优化：深分页
limit 100000,10很慢，推荐主键书签分页-- 差：limit offset 大的时候扫描大量行 select * from t order by id limit 100000,10; -- 优：书签 select * from t where id > 100000 order by id limit 10;in里面值不要太多（建议 < 1000），太多可以拆分或者临时表union all代替union，不需要去重排序，性能高很多大表禁止
order by rand()join 优化：小表驱动大表，关联字段必须建立索引；尽量减少 join 层数
group by：尽量利用索引有序性，避免临时表和文件排序；不需要去重统计不要
distinct事务里少写大查询，缩短事务时长，减少锁等待、行锁冲突
三、表结构设计优化（前期成本，后期收益巨大）
字段类型够用就好，越小越好
能用
tinyint不用 int，能用int不用 bigint时间优先
datetime（MySQL8.0 支持毫秒），不要用字符串存时间；不推荐 timestamp（时区、范围限制）IP 可以用
inet6_aton存二进制，比 varchar 省空间字符串：固定长度用 char，变长用 varchar；超长文本用
text单独分表，不要混在高频查询表
避免 NULL：允许 NULL 会让索引、统计、判断更复杂，默认值用 0 / ''
适当分表：
水平分表：数据量大（千万级 +）、按时间 / 用户 ID 分片
垂直分表：把大字段（text、blob）拆到单独附表
字符集：新项目统一
utf8mb4（支持 emoji），排序规则utf8mb4_unicode_ci或者utf8mb4_general_ci；不要混用字符集，会隐式转换索引失效控制单行大小：InnoDB 一页 16KB，单行过大，一页存放行数变少，IO 上升
四、InnoDB 核心参数优化（my.cnf/my.ini）
下面是生产常用参考，根据服务器内存调整，以 8G 内存机器举例
# 最重要参数：缓冲池，建议物理内存 50%~70%
innodb_buffer_pool_size = 4G
# 8.0支持多实例buffer pool，减少锁竞争
innodb_buffer_pool_instances = 4
# 日志文件，redo log，刷盘策略，平衡性能和安全
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1 # 1=最安全；2=性能更高，宕机丢少量数据
# 脏页刷盘，防止突然大量刷盘抖动
innodb_max_dirty_pages_pct = 75
# 关闭查询缓存（MySQL8.0已经移除，5.7建议关闭，极易失效）
query_cache_type = 0
# 连接数，不要盲目开很大，连接是消耗资源的
max_connections = 500
wait_timeout = 600
# 排序缓冲、join缓冲：是每个连接独享！不要设太大
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
重点说明：
innodb_buffer_pool_size：InnoDB 最重要参数，缓存索引 + 数据页innodb_flush_log_at_trx_commit=1配合sync_binlog=1是 ACID 安全标准；高并发可改成 2 换取性能提升sort/join buffer 是每个连接单独分配，设置过大，并发上来瞬间吃光内存
五、事务、锁与并发优化（解决慢、阻塞、死锁）
事务尽量短：不要在事务里做网络调用、sleep、大查询
行锁前提：SQL 能精准走到索引；不走索引 → 行锁升级为表锁（大坑！）
更新顺序统一：多个事务更新多行，固定更新顺序，避免死锁
减少 gap lock（间隙锁）：MySQL8.0 RR 隔离级别存在；业务允许可以降级为 RC（读已提交），间隙锁消失，并发大幅提升，但会出现不可重复读
避免 select ... for update 长事务占用行锁；只锁定必要行
乐观锁代替悲观锁：版本号 version 机制，减少锁争抢
六、运维层面优化
定期统计信息更新
analyze table table_name;
InnoDB 统计信息不准会导致优化器选错索引，explain 看到 rows 严重失真时执行 2. 碎片清理
delete/update 产生碎片：
optimize table t;（InnoDB 重建表，锁表，低峰期执行）8.0 支持在线 DDL，改表尽量用
ALGORITHM=INPLACE, LOCK=NONE
慢查询日志，抓慢 SQL（最常用排查手段）
slow_query_log = ON
slow_query_log_file = slow.log
long_query_time = 1
log_queries_not_using_indexes = ON # 记录没走索引的SQL
工具：pt-query-digest 分析慢日志 4. binlog 优化
格式推荐
binlog_format=ROW，安全；MIXED 老项目兼容定期清理 binlog：
expire_logs_days=7
磁盘：优先 SSD；InnoDB 是随机 IO 敏感，机械盘高并发很容易瓶颈；单独放数据目录、redo log
七、架构层面优化（单库优化到顶之后）
读写分离：主库写，从库读；业务代码路由读请求到从库
分库分表：ShardingSphere / MyCat；按哈希、时间分片
热点缓存：Redis 缓存热点数据，击穿、穿透、雪崩防护，减少直接压 MySQL
归档：历史冷数据归档到单独库，主库只保留近期热数据
异构：搜索场景交给 ES，时序数据交给时序库，不要全部压 MySQL
八、常见坑速查清单
❌ 索引建了但是隐式转换不走
❌ limit 超大偏移分页
❌ 用 uuid 当主键
❌ 长事务、for update 乱加
❌ sort_buffer 等会话缓冲设置超大
❌ 单表索引十几个
❌ 用 text/blob 放高频查询字段
❌ 混用字符集排序规则