MySQL 5.7 知识点汇总
MySQL 很早就已经更新到 8 了，但是 5.7 依然还是主流，我想知道为什么。
MySQL 5.7 的版本特性知识点：
- Online DDL
- JSON 原生支持，虚拟列 Generated Column
- 索引下推 ICP（Index Condition Pushdown）
- 主从复制：无损半同步复制 AFTER_SYNC (lossless Semi-Sync Replication) + 并行复制（MTS Multi-Threaded-Slave）
如何优化 SQL
optimizer_trace
SET optimizer_trace=“enabled=on”;
会显示优化器生成的 JSON 决策树，
profile
explain 命令只是表面，面试官有时候会不满你的回答。
SET profiling = 1;
SHOW PROFILE 会分阶段显示 SQL 执行过程中的耗时（未来被 performance schema 取代）
- wating for table metadata lock——MDL 锁等待
- sending data——IO 开销
- creating tmp table——临时表生成时间（磁盘 IO）
perform schema
从系统资源层级别，查看 performance schema，检查 wait events 等待事件
- 磁盘 IO waiting
- 内存限制比如 sort_buffer_size 太小导致 filesort
- 互斥锁竞争
回表和 filesort
回表和 filesort 是不同的性能开销术语
回表（look up / bookmark lookup）
在查询时，建了索引，但是索引没有覆盖返回的列。
比如 select col_a, col_b from table where col_a = 'Mutsumi'
建了索引 index 包含列 col_a
在这个查询中，引擎会根据 where col_a 先去索引 index ，拿到主键 id 再到主键索引找 col_b，这个过程叫回表。
解决方案：
文件排序 filesort
案例：select * from table where col_a = 'Mutsumi' order by col_b desc
由于 select *，引擎可以直接根据 where c_a 找到所有 Mutsimi，但是索引不会排序，引擎只能在所有 Mutsimi 中自己对它排序。
解决方案：
建立索引 index 包含 col_a / col_b ，b + 树在 col_a = ‘Mutsumi’ 条件下天然是有序的，b + 树的伟大设计体现之处。
三级查询优化
| 级别 | 优化手段 | 解决的问题 | 核心标志 |
|---|---|---|---|
| 第一级 | 最左前缀索引 | 解决“全表扫描” (ALL) | type 变为 ref 或 range |
| 第二级 | 索引下推 (ICP) | 解决“回表次数过多” | Extra 显示 Using index condition |
| 第三级 | 索引覆盖 | 彻底消除“回表” | Extra 显示 Using index |
索引
| 索引类型 | 底层结构 | 核心解决的问题 | 5.7 的关键里程碑 |
|---|---|---|---|
| 普通/唯一索引 | B+ 树 | 精确匹配、范围查询、排序、索引覆盖 | 5.7 的 Online DDL 让加索引不锁表 |
| 全文索引 | 倒排索引 | 长文本的关键词模糊搜索 (MATCH...AGAINST) |
内置 ngram 解析器，原生支持中文分词 |
| 空间索引 | R-Tree | 地理坐标、多维空间范围查询 | 支持 InnoDB 存储引擎及其 Online 操作 |
| 虚拟列索引 | B+ 树 | 非结构化数据 (JSON) 的索引化 | 5.7 引入，极大增强了 NoSQL 扩展性 |
全文索引
除了普通索引和唯一索引，全文索引解决了一个开发者忽视的问题，模糊查询。
由于 b + 树是从左到右顺序存储的，因此他其实只适合做左缀查询：where name like 'Mutsu%'
全文索引不再使用 b + 树，而是使用倒排索引，彻底解决了模糊查询的问题：where name like '%Mutsimi%' or name like '%Sakiko'
5.7 版本引入了 ngram 解析器，原生支持了中文索引。
空间索引（spatial index）
坐标包含两个数据：经度，纬度
b + 树是一维的，很难高效处理这种范围
空间索引基于 r-tree，专门处理 GEOMETRY 结构（字段）的数据。
虚拟列索引（generated column）
本质上其实还是 b + 树，但是使得 JSON 的使用更加高效。
1-- 假设你的表里有个 json 字段叫 info，里面存着 {"age": 25}
2ALTER TABLE users ADD COLUMN v_age INT AS (info->'$.age') VIRTUAL;
3CREATE INDEX idx_v_age ON users(v_age);
4-- 不需要 select users.v_age
5SELECT * FROM users WHERE info->'$.age' = 25;
6-- info->'$.age' 等同于 JSON_EXTRACT(info, '$.age')
虚拟列甚至可以和普通列一起组成索引
1ALTER TABLE users ADD INDEX idx_user_json (user_type, v_age);
虚拟列索引的模式
- virtual：不占磁盘空间。只有在读取或索引时才计算。最推荐，因为索引本身已经存了值。
- stored：数据持久化到磁盘。会占空间，修改 JSON 时会有额外写入压力。除非该列需要频繁被没有索引的查询访问，否则不建议使用。
索引决策优化
现有场景如下，已知有 index(name, sex)，index(name, age)
1select name, age from table where name like '金%' and sex = '男';
想当然，我们以为同时对条件列和返回列都分别建了索引，而且符合索引覆盖。
但是大概率只会走 index(name, age) 或者 index(name, sex) 中的其中一个。
常规逻辑中，MySQL 一次查询只会走一个最优的索引。
- 如果走 index_1 需要对 age 放在内存中查询
- 如果走 index_2 需要对 sex 回表查询获得 age
优化器会计算 cost，由于 index_1 不需要回表，所以在优化器看来普遍效率更高，大概率会只走 index(name, age)
| 索引情况 | 执行行为 | 性能表现 | Extra 标志 |
|---|---|---|---|
| 选 index(name, age) | 索引覆盖 | 极好（无需回表） | Using index; Using where |
| 选 index(name, sex) | 索引下推 + 回表 | 一般（受回表次数限制） | Using index condition |
| 不走索引 | 全表扫描 | 极差 | Using where |
该场景的最优解
建立联合索引 index(name, sex, age) / index(name, age, sex)
index(name, age, sex) 是否可以被 index(sex, age, name) 代替？
不可以，因为此时该索引会不可用。
优化器根据 where name like ‘金%’ and sex = ‘男’ 匹配不到该索引。
因为根据最左匹配原则 sex -> name 中间存在 age，导致无法直接索引下推，因此只能在 b+ 树里一个个去扫描 name。
最后会变成全索引扫描（Index Full Scan），索引性能非常差。
那么 index(sex, name, age) 如何呢？
符合最左匹配原则，达成索引下推条件。且符合最优索引策略。
最优索引策略：
- 条件列等值判断 ( = )
- 条件列范围判断（like / > / <）
- 结果列
大事务索引
大事务 / 长事务（Long-Running Transactions），是我第一份工作中最主要的挑战。
处理大事务，需要从业务层 / 数据库层面两方面入手。
业务层，可以通过规划提交颗粒度，将大数据量分片为小颗粒提交，长事务就变成了短事务。
数据库层面，则比较复杂，
核心底层影响
讲述一个我之前遇到的供应链场景下的常见场景。某一时刻开始系统进入活跃期，海量大事务提交，每个大事务内含百万行级别的插入，和十万行级别的更新（状态字段），全部都在 RR 隔离级别下。
性能瓶颈：海量 undo log 导致的 ReadView 版本链路长
如果不是 AI 流行，开始参与日常运维评估。
团队内部甚至没有人注意到这些问题（大公司病，运维不懂实际业务，技术无权参与底层运维）。
由于 MVCC 设计，事务开启时，需要生成快照。只要大事务没有被提交，MySQL 就无法删除快照底层支持的 undo log。
除了系统表空间会膨胀，由于大事务下的 undo log 的生存周期长，导致 MVCC 快照版本链路在单位时间内更长。
其他事务查询到当前版本的数据就更慢。
锁占用 / 冲突
大事务长期占有间隙锁 / 行锁，其他事务只能 Wait。
而在大事务并行读写同一张表的场景下，间隙锁、临建锁、插入意向锁（特殊间隙锁）需要重点关注。
回到我之前提到的场景（此时会产生严重的锁饥饿和性能滑坡）：
多个大事务并发读写百万级别的数据，很遗憾，如果使用 MySQL 5.7，实际情况并不像我们程序员想象中的那样完美并发。
由于默认在 RR 隔离级别下，事务 A 插入时会取得 Next-Key Lock，其他事务必须等待事务 A 释放临建锁，才能获得插入意向锁。
时间段作为条件进行范围查询，会导致如果没有索引支撑，会导致大范围的间隙锁甚至全表级别的锁。
简直是地狱级别的锁竞争场景
遗憾的是，我在之前的工作中并没有得到重用，技术方被业务方和运营方完全支配，我的决策和建议都被忽视，人微言轻。
没有人在乎这个系统的好坏 / 有用，底层用户是不是通过一个良好的交互提高了工作效率，团队里没人在乎这些。
如何减少大事务的锁占用：
- 降低隔离级别：如果业务允许，将
tx_isolation设置为READ-COMMITTED(RC)。在 RC 下，间隙锁会被禁用（除了外键检查），大大减少死锁概率。 - 精细化索引：确保
WHERE条件精准命中索引，让 Next-Key Lock 降级为 Record Lock（精准匹配主键/唯一索引时会降级）。 - 控制事务大小：事务里的 SQL 越少，持有锁的时间越短，并发能力越强。
RC 的隔离级别，正是诸多大厂面对海量大事务和锁占用问题的经典操作
主从延迟
由于事务提交比较慢，提交后从节点才回去同步。
磁盘空间
表空间文件无法默认回收，占用大量磁盘。
如何定位大事务
1-- 1. 查找运行时间超过 60 秒的事务
2SELECT * FROM information_schema.innodb_trx
3WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
4
5-- 2. 查看 Undo Log 的积压情况 (History list length)
6-- 如果这个值上万或几十万，说明有大事务在阻塞清理
7SHOW ENGINE INNODB STATUS;
8
9-- 3. 查看锁等待关系
10SELECT * FROM sys.innodb_lock_waits;
经典场景：自增 ID 的缺陷
- 事务 A 删除了 ID = 233（持有了间隙锁）。
- 事务 B 也想删除 ID = 233（由于是间隙锁，B 也能持有成功，两者兼容）。
- 事务 A 尝试插入 ID = 233，被事务 B 的间隙锁阻塞。
- 事务 B 尝试插入 ID = 233，被事务 A 的间隙锁阻塞。
- 死锁达成