🧱 第一章：存储引擎 — 数据的两种"存法"
想象一本厚厚的字典。字词的编排有两种方式：
方法A：每个字按拼音顺序排在正文里，旁边就是完整的解释（释义、例句、词性全都有）。想查哪个字，翻到那一页就全看到了。—— 这就是 InnoDB，数据按主键聚集存储。
方法B：字按录入顺序堆着，另附一个索引表查位置，解释和索引分开。—— 这就是 MyISAM，数据和索引分开存。
大部分字典（90%以上）都选方法A，为什么？往下看👇
📊 InnoDB vs MyISAM：一次看懂
| 特性 | InnoDB（默认） | MyISAM（老引擎） | 生活类比 |
|---|
| 事务 | ✅ 支持 | ❌ 不支持 | 查字过程要能"回退"，MyISAM做不到 |
| 行级锁 | ✅ 支持 | ❌ 只有表锁 | 两人同时查不同的字，InnoDB互不影响；MyISAM整本锁住 |
| 外键 | ✅ 支持 | ❌ 不支持 | 每个词条必须属于某个部首 |
| 崩溃恢复 | ✅ redolog | ❌ 可能丢数据 | 突然停电，InnoDB能恢复到最后一秒 |
| 聚簇索引 | ✅ 数据即索引 | 索引和数据分离 | 正文按拼音排列 vs 另附索引 |
| 全文索引 | ✅ 5.6+支持 | ✅ 原生支持 | 搜索"包含某偏旁的字"，MyISAM曾经更擅长 |
| COUNT(*) | 要遍历索引 | 直接存了总数 | InnoDB因为MVCC要数，MyISAM直接记住 |
🔑 关键结论：MySQL 5.5 之后默认引擎就是 InnoDB。你工作里 99% 用的都是 InnoDB，面试也主要问 InnoDB。MyISAM 知道它不支持事务就行了。
📖 InnoDB 的"聚簇索引"到底是什么意思？
在 InnoDB 里，数据本身就是按主键排好的一棵B+树。这棵树叫"聚簇索引"（Clustered Index）。
类比：字典的正文，从第1页到第1000页，就是按 拼音a, b, c, ... 排好的。
"聚簇"的意思就是：数据的物理存储顺序 = 主键的排序顺序。
这意味着：如果你查 WHERE id=5，直接翻到第5页就拿到了，超级快。
但如果你查 WHERE user_id=101，怎么办？你没有按user_id排的订单本啊！—— 这就需要二级索引了，后面讲。
✅ 存储引擎搞懂了：InnoDB用B+树存数据 → 下面是核心：B+树索引怎么让你3毫秒找到2000万行中的某一条
📖 第二章：B+树索引 — 数据库怎么快速找到一条记录
🤔 先搞明白：没有索引会怎样？
假设 order_info 表有
100万行 数据，你要找
id = 500000 的订单。
没有索引：MySQL 从第1行开始，逐行对比 id，直到找到第50万行。
→ 最坏情况要看
100万行，时间复杂度 O(N)
→ 就像一本没有目录的字典，你要从第1页翻到第5000页
有索引：MySQL 用 B+树 结构，像一个"多级目录"。
→ 100万行的表，B+树大约只有
3层
→ 也就是说，最多翻
3页 就能找到任何一条数据！
→ 时间复杂度 O(log N) ≈ 找10亿条数据也只需要 4次翻页
📖 生活类比：B+树就像"字典的多级目录"
想象一本大字典有
8个正文区块，每个区块若干页。
第1层（部首目录）：字典开头的部首总表，写着"一部→第10区，人部→第20区，口部→第30区"
第2层（检字表）：每个部首下的检字表，写着"一画→第101页，二画→第103页"等
第3层（正文页面）：正文里每个字的完整解释，每页底部写着"下续第XX页"（
链表）
你要查
"何"字（编号5）：
① 翻部首目录 → "何"在人部，翻到人部
② 查检字表 → 5画，翻到5画那页
③ 翻到正文页 → 找到"何"字的完整解释！
整个过程只需要翻3次（部首目录→检字表→正文），不需要从第1页逐页翻。
📦 第一步：理解"数据页"（Page）
MySQL 不是一行一行存数据的，而是把数据放在 "页" 里。
一页 = 16KB（固定大小），就像一本固定厚度的笔记本。
每一页里可以放很多行数据。InnoDB 存储引擎的所有数据（索引 + 行数据）都存在页里面。
📄 什么是数据页（Page）？16KB/页
页头 (Header)
页号、页类型
上一页/下一页指针
行数统计
行数据 (Records)
一行行真实数据
按主键顺序排列
行之间用链表串起
页目录 (Page Dir)
页内的"小目录"
加速页内查找
类似书的章节索引
一个数据页(16KB) 大约能存 200-500行 数据（取决于行大小）
💡 关键概念：B+树的每个"节点"就是一个数据页。一个节点占16KB。
非叶子节点(目录页)只存 key + 指针，所以一页能放几百个指针，意味着树的分叉很多，树就很矮。
🌳 第二步：聚簇索引（主键索引）— 完整行数据存在叶子节点
聚簇索引 = 按主键(id)排列的 B+树。
它的叶子节点存的是完整的行数据（id, user_id, product_id, amount, status, created_at 全都有）。
一张表只能有一个聚簇索引（因为数据只能按一种方式物理排列）。
📖 类比：字典正文，按拼音排列，每个字有完整解释（读音、释义、例句全都有）。
第3层 — 叶子节点（存完整行数据）
id=1
user=101, product=5, amount=18.00, status=paid
id=2
user=102, product=3, amount=22.00, status=paid
id=3
user=101, product=5, amount=18.00, status=completed
id=4
user=103, product=7, amount=35.00, status=paid
id=5
user=101, product=5, amount=18.00, status=completed
id=6
user=104, product=2, amount=12.00, status=paid
id=7
user=102, product=5, amount=18.00, status=cancelled
id=8
user=105, product=3, amount=22.00, status=paid
页A⇄页B⇄页C⇄页D
← 叶子节点之间双向链表连接，支持范围查询
⬆ ⬆ ⬆ ⬆
第2层 — 非叶子节点（只存 key + 页号指针）
这个非叶子节点存的是：key(分界值) + 指针(指向下一层的页号)
它 不存 amount、status 等数据！只存"目录信息"
⬆
第1层 — Root根节点（整棵树的入口）
本例简化为3层。实际上，百万行数据也只需3-4层！
因为一个非叶子节点只存 key+指针(很小)，一页16KB能放几百个指针
✅ 你现在应该理解了
聚簇索引（主键索引）的叶子节点：存的是
完整的行数据（id + user_id + product_id + amount + status + created_at）
非叶子节点：只存
key（主键值） + 指针（子节点页号），不存实际数据
为什么 B+树矮？：非叶子节点只存很小的 key+指针，一页能放几百个，所以3-4层就能索引千万行
🚶 第三步：手把手走一遍 — SELECT * FROM order_info WHERE id = 5
1
读取 Root 根页
MySQL 从磁盘读取根页到内存。根页里存着：key≤3 → 页F, key≤6 → 页G
我们要找 id=5。5 > 3 所以不走页F。5 ≤ 6 所以走页G。
类比：查部首目录 → "何"在人部
2
读取页G（非叶子节点）
页G里存着：key≤4 → 页B, key≤6 → 页C
5 > 4 所以不走页B。5 ≤ 6 所以走页C。
类比：查检字表 → 5画在第103页
3
读取页C（叶子节点）— 找到了！
页C是叶子节点，里面存着完整的行数据：
id=5 user=101, product=5, amount=18.00, status=completed
只读了3个页，就找到了 id=5 的全部数据！
类比：翻到正文页 → 看到"何"的完整解释
💡 重点：不管表有8行还是800万行，B+树的层数都是3-4层。也就是说，找任何一条数据都只需要3-4次磁盘IO。这就是B+树牛的地方。
🗂️ 第四步：二级索引 — 按非主键字段查找
现实中的查询大多不是按主键id查的。比如：
• SELECT * FROM order_info WHERE user_id = 101 ← 按 user_id 查
• SELECT * FROM order_info WHERE amount > 20 ← 按 amount 查
但主键索引是按 id 排列的，按 user_id 查怎么办？
答案：MySQL 会为 user_id 单独建一棵 B+树，这棵树按 user_id 排序。
但这棵树的叶子节点不存完整行数据，只存 user_id + 主键id。
📖 类比：你做了一本按用户编号排列的小册子，每页写着"用户101 → 订单1号"、"用户101 → 订单3号"。
你用这本小册子查到用户101下了哪些订单号，然后拿着这些订单号，回到主订单本去取完整信息。
🔍 第五步：二级索引 idx_user 的 B+树 — 放大看存了什么
🌳 聚簇索引（主键索引）
按 id 排列
叶子节点存的：
id=1
user=101, prod=5, amt=18, status=paid, time=...
id=2
user=102, prod=3, amt=15, status=paid, time=...
→ 存的是完整行数据
→ amount、status、created_at 全都有
🌳 二级索引 idx_user（user_id索引）
按 user_id 排列
叶子节点存的：
→ 只存 user_id + 主键id
→ amount、status、created_at 都没有！
🔴 核心区别（一定要记住）
聚簇索引的叶子节点：存
完整行数据（所有列都有）→ 找到就等于拿到全部信息
二级索引的叶子节点：只存
索引列值 + 主键（其他列都没有）→ 找到之后还要去聚簇索引拿完整数据
这个"去聚簇索引拿完整数据"的动作，就叫
回表。
📐 二级索引 idx_user 的完整 B+树结构
idx_user 索引树（按 user_id 排列）
↓ ↓
↓ ↓ ↓ ↓
↑ 叶子节点：每条记录只有 user_id + 主键id，没有 amount、status 等字段！叶子间也是链表串联。
💡 注意看：二级索引的叶子节点里只有 user_id → id 这两个值。如果你要查 amount 或 status，这个树里根本没有这些信息！必须拿着 id 回到聚簇索引那棵树去找。
🔄 第六步：回表的完整过程 — 两棵树配合工作
查询：SELECT * FROM order_info WHERE user_id = 101
这个查询需要两棵树配合：
1
在二级索引树（idx_user）里找 user_id=101
从根节点开始 → 沿着指针往下走 → 到达叶子节点
找到 3 条记录：user_id=101 → id=1, user_id=101 → id=3, user_id=101 → id=5
2
拿着 id=1 回到聚簇索引树
从聚簇索引根节点 → 沿着指针走 → 找到 id=1 的叶子节点
拿到完整数据：id=1, user=101, product=5, amount=18.00, status=paid, time=...
3
拿着 id=3 回到聚簇索引树
同样从根节点走 → 找到 id=3 的完整数据：id=3, user=101, product=5, amount=18.00, status=completed
4
拿着 id=5 回到聚簇索引树
同样从根节点走 → 找到 id=5 的完整数据：id=5, user=101, product=5, amount=18.00, status=completed
回表的代价：匹配 N 条 → 回表 N 次
idx_user找3条
→
回表×3
→
3条完整数据
⚠️ 如果 user_id=101 匹配了 10000条，就要回表 10000次！这时候可能还不如全表扫描快。
💡 回表代价公式：回表次数 = 二级索引匹配的行数。
如果二级索引过滤性不好（匹配太多行），MySQL 可能直接放弃索引，走全表扫描。
这就是为什么 EXPLAIN 有时候显示 type=ALL 即使你建了索引 —— MySQL 觉得回表代价太大，不如直接扫。
📐 B+树 vs B树 vs Hash（面试必问对比）
B+树（InnoDB使用）
✅ 数据只在叶子节点
✅ 非叶子只存 key+指针 → 一页放更多 → 树更矮
✅ 叶子节点有链表 → 范围查询快
✅ 每次查找路径长度一样（稳定）
✅ 天然有序 → 支持 ORDER BY
100万行 → 树高约3层
10亿行 → 树高约4层
B树
❌ 每个节点都可能存数据
❌ 非叶子也存数据 → 一页放更少 → 树更高
❌ 没有链表 → 范围查询要中序遍历
⚠️ 有时快（数据在根节点就找到了）
⚠️ 但不稳定（有时3层有时1层）
面试答法：InnoDB选B+树因为
①中间节点不存数据，扇出大
②叶子链表支持范围查
🖥️ 实战：用 EXPLAIN 看看索引有没有被用上
amount 字段没有索引，MySQL 只能从第1行翻到最后一行，看每一行的 amount 是否 > 20。
🔄 第三章：回表 & 覆盖索引 — 多走一步 vs 一步到位
回表：你先在"部首检字表"（二级索引）里找到 user_id=101 对应的主键 id=1,3,5,10。然后拿着这些id，回到"字典正文"（聚簇索引）去取完整数据。这就是回表。
覆盖索引：如果你的查询只需要 user_id 和 id，那在"部首检字表"里就已经拿到了，不用再翻正文！—— 这就是覆盖索引，"索引已经覆盖了你要查的所有字段"。
类比：你只想知道"有多少个字属于人部"。部首检字表上已经列了所有人部字的页码，数一下就行，不用翻正文看解释。
📊 回表过程可视化 — 两棵树怎么配合
SELECT * FROM order_info WHERE user_id = 101
🌳 二级索引树 idx_user
根：user_id ≤ 102 | ≤ 104
↓
≤101 | ≤102 | ≤103
↓
101→id1
101→id3
101→id5
← 找到3条匹配
🌳 聚簇索引树（主键）
根：id ≤ 3 | ≤ 6
↓
≤2 | ≤4 | ≤6
↓
id=1 ✅ 全部字段
id=3 ✅ 全部字段
id=5 ✅ 全部字段
← 每次回表走一遍B+树
总结：3条匹配 = 1次索引查找 + 3次回表 = 4次B+树遍历。匹配越多行，回表越慢！
📖 什么是联合索引？
联合索引就是 在多个列上建一个索引，而不是每个列各建一个。
之前我们用的是单列索引：INDEX idx_user (user_id) — 只按 user_id 一列建索引。
联合索引是：INDEX idx_user_status (user_id, status) — 按 user_id + status 两列一起建索引。
类比：单列索引像"只按姓氏查"的通讯录；联合索引像"先按部门，再按姓名"排的两级通讯录。
⚠️ 关键问题：联合索引的B+树叶子节点，到底存了什么？
这是理解"覆盖索引"和"最左前缀"的核心！让我画给你看👇
🌳 联合索引 B+树 叶子节点里存了什么？
联合索引 idx_user_status (user_id, status) 的 B+树结构
📚 排序规则（先排第一列，第一列相同的再排第二列）
排序顺序：user_id ASC, status ASC
也就是：先按 user_id 从小到大排，user_id 相同的 再按 status 字母序排
类比：电话簿先按姓排，同姓再按名排 → (张,三) (张,四) (李,四) (王,五)
📋 原始数据（聚簇索引/字典正文里的完整行）
id=1: user_id=101, status='paid', amount=25.00, product_id=1
id=2: user_id=102, status='pending', amount=30.00, product_id=2
id=3: user_id=101, status='completed', amount=18.00, product_id=3
id=4: user_id=103, status='paid', amount=45.00, product_id=4
id=5: user_id=101, status='pending', amount=22.00, product_id=5
id=6: user_id=102, status='completed', amount=35.00, product_id=1
🔥 联合索引叶子节点 = 只存索引列 + 主键（不存其他列！）
user_id（第1列）status（第2列）id（主键）行指针
101completed3→
101paid1→
101pending5→
102completed6→
102pending2→
103paid4→
⚠️ 注意看：没有 amount！没有 product_id！没有 created_at！
联合索引只存了 user_id + status + id 这三样东西。
🔑 所以：为什么 SELECT * 会回表？
因为 SELECT * 要取 全部字段（amount, product_id, created_at...），但联合索引里 只有 user_id, status, id！
缺少的字段只能拿着 id → 回到聚簇索引树 → 取完整行数据。这就是回表。
🔥 为什么联合索引能"覆盖"——不用回表？
覆盖索引的本质：你的 SELECT 只需要 user_id, status, id 这几列 → 联合索引的叶子节点 全部都有 → 不用去聚簇索引取数据 → 不用回表！
❌ 需要回表
SELECT user_id, amount
FROM order_info
WHERE user_id = 101;
需要取: user_id, amount
索引里有: user_id ✅
索引里有: amount ❌ 没有！
→ 拿着 id 去聚簇索引取 amount
→ 这就是回表！
✅ 覆盖索引，不用回表！
SELECT user_id, status
FROM order_info
WHERE user_id = 101;
需要取: user_id, status
索引里有: user_id ✅
索引里有: status ✅
→ 全都在索引里！直接返回
→ 不回表！ Using index 🎉
📝 一张表搞懂：查什么字段，会不会回表？
联合索引 idx_user_status (user_id, status) → 叶子存了：user_id + status + id
| SQL语句 | 需要取的字段 | 索引里有? | 结果 |
|---|
SELECT id FROM order_info WHERE user_id=101 |
id |
✅ 全有 |
Using index 覆盖 |
SELECT user_id, status FROM order_info WHERE user_id=101 |
user_id, status |
✅ 全有 |
Using index 覆盖 |
SELECT user_id, status, id FROM order_info WHERE user_id=101 |
user_id, status, id |
✅ 全有 |
Using index 覆盖 |
SELECT user_id, amount FROM order_info WHERE user_id=101 |
user_id, amount |
❌ 缺 amount |
回表！ |
SELECT * FROM order_info WHERE user_id=101 |
全部字段 |
❌ 缺很多 |
回表！ |
💡 判断方法很简单：看你的 SELECT 后面要取哪些字段 → 去联合索引的叶子节点里找 → 全找得到 = 覆盖索引 → 有找不到的 = 回表。
💡 覆盖索引的实战意义：如果你发现某个高频查询总是回表，可以考虑建一个联合索引，让索引"覆盖"查询需要的所有字段。这样查询只需要扫索引树，不碰数据行，速度能快好几倍。
举个例子：首页总是 SELECT user_id, status FROM order_info WHERE user_id = ?
→ 这个查询只需要 user_id 和 status → 联合索引 idx_user_status 里就有 → 不回表！
→ 如果没有联合索引，只有单列索引 idx_user，那 status 不在索引里 → 回表！
📖 索引下推（Index Condition Pushdown, ICP）
MySQL 5.6 之后有个优化叫"索引下推"。举个例子：
联合索引 idx_user_status (user_id, status)
查询：SELECT * FROM order_info WHERE user_id = 101 AND status LIKE 'comp%'
没有ICP：在索引里找到所有 user_id=101 的记录（4条），全部回表，再在回表后的数据上过滤 status LIKE 'comp%'。
有ICP：在索引里找 user_id=101 的同时，直接在索引里判断 status LIKE 'comp%'，只有满足条件的才回表（可能只剩1条要回表了）。
类比：你在检字表里查某个部首时，顺便看看笔画数那一列，把笔画不对的直接跳过，不用白白翻正文。
🔀 第四章：子查询原理 — 为什么子查询有时候很慢？
子查询就是 SQL 里套 SQL：SELECT * FROM A WHERE id IN (SELECT id FROM B)
很多人以为子查询和 JOIN 是一样的，其实执行方式完全不同！
理解子查询的执行方式，是避免慢查询的关键。
📊 子查询的两种执行方式
❌ 相关子查询（Dependent Subquery）— 通常很慢
SELECT * FROM order_info o
WHERE amount > (
SELECT AVG(amount)
FROM order_info
WHERE user_id = o.user_id
);
执行过程：
1. 外层取第1行 order_info → 拿到 user_id=101
2. 执行内层：SELECT AVG(amount) ... WHERE user_id=101
3. 外层取第2行 → 拿到 user_id=102
4. 再执行内层：SELECT AVG(amount) ... WHERE user_id=102
5. ... 重复 N 次！
外层有多少行，内层就执行多少次！外层10000行 → 内层执行10000次！
✅ 非相关子查询 — 通常较快
SELECT * FROM order_info
WHERE user_id IN (
SELECT id FROM user WHERE status = 'active'
);
执行过程：
1. 先执行内层：SELECT id FROM user WHERE status='active'
→ 结果集 (101, 103, 105)
2. 再执行外层：SELECT * FROM order_info WHERE user_id IN (101,103,105)
内层只执行一次！
内层独立执行一次，结果传给外层。效率高得多！
🔑 怎么判断是哪种子查询？
判断标准：内层有没有引用外层的字段？
内层引用了 o.user_id（来自外层）→ 相关子查询（慢）
内层完全独立，不依赖外层 → 非相关子查询（快）
EXPLAIN 里看到 Select_type: DEPENDENT SUBQUERY 就是相关子查询，要警惕！
🔄 子查询 → JOIN 改写（最常见的优化手法）
很多慢子查询可以改写为 JOIN，让 MySQL 优化器自由选择执行顺序，性能往往好很多。
| 慢子查询 | 改写为 JOIN | 效果 |
|---|
|
SELECT * FROM order_info
WHERE user_id IN (
SELECT id FROM user
WHERE status='active'
);
|
SELECT o.*
FROM order_info o
JOIN user u ON o.user_id = u.id
WHERE u.status='active';
|
优化器可选择驱动表
性能更好 |
|
SELECT * FROM order_info o
WHERE EXISTS (
SELECT 1 FROM user
WHERE id = o.user_id
AND status='active'
);
|
SELECT o.*
FROM order_info o
JOIN user u ON o.user_id = u.id
WHERE u.status='active';
|
同上，改写后更灵活 |
|
SELECT * FROM order_info
WHERE user_id NOT IN (
SELECT id FROM user
);
|
SELECT o.*
FROM order_info o
LEFT JOIN user u
ON o.user_id = u.id
WHERE u.id IS NULL;
|
NOT IN → LEFT JOIN
避免 NOT IN 的 NULL 陷阱 |
⚠️ 子查询常见的坑
坑1：NOT IN 遇到 NULL
-- 如果子查询结果里有 NULL
SELECT * FROM order_info
WHERE user_id NOT IN (101, NULL, 103);
-- 结果：空！一条都没有！
-- 因为 user_id != NULL 的结果是 NULL（不是true）
-- 整个 NOT IN 条件变成 NULL → 全部不匹配
解决：用 LEFT JOIN ... IS NULL 代替，或在子查询里加 WHERE id IS NOT NULL
坑2：IN 子查询结果集太大
-- 如果子查询返回10万条ID
SELECT * FROM order_info
WHERE user_id IN (
SELECT id FROM big_table
WHERE create_time > '2026-01-01'
);
-- MySQL 可能不走索引
-- 而是把10万个ID逐个匹配
解决：改写为 JOIN，让优化器选择最优执行计划
坑3：EXISTS vs IN 选错
子查询结果集小 → 用 IN（先执行子查询）
外层表小 → 用 EXISTS（先遍历外层）
不确定 → 用 JOIN（优化器自动选）
子查询实战总结：
① 看到 DEPENDENT SUBQUERY → 警惕，可能需要改写为 JOIN
② NOT IN 小心 NULL → 改用 LEFT JOIN ... IS NULL
③ MySQL 8.0 优化器很聪明，很多子查询会自动优化为 SEMI JOIN
④ 不确定性能时，用 EXPLAIN 看执行计划，对比子查询和 JOIN 的 rows 差异
🧠 子查询在 MySQL 内部是怎么跑的？（内存 vs 磁盘）
前面讲了"子查询分两种"和"改写为 JOIN"。但你可能还是纳闷：子查询的结果到底存在哪里？内存还是磁盘？
这就是子查询的物化（Materialization）机制。理解了它，你就能明白为什么有的子查询快、有的慢、有的直接报错。
类比：你帮同事查资料，查到后要把结果写下来交给同事。
· 结果少（比如3个ID）→ 写在便利贴上（内存），秒交
· 结果多（比如10万条）→ 写在A4纸上（磁盘临时表），慢一些
· 结果特别多（比如500万条）→ A4纸写不下，可能直接撑爆
MySQL 也是一样：子查询结果少→放内存；结果多→写磁盘；太多→报错或巨慢。
📦 一、物化（Materialization）— 子查询结果的"暂存区"
MySQL 执行非相关子查询时，一般分 两步：
第1步：先执行内层子查询，把结果存起来（这个过程叫"物化"）
第2步：外层查询去这个"暂存区"里找数据
关键问题：这个暂存区在内存还是磁盘？
✅ 结果小 → 放内存（快速）
条件：子查询结果 <
tmp_table_size（默认16MB）
存储方式：MySQL 在内存中创建一个
内部临时表
数据结构：默认用
哈希表（查找 O(1)，极快）
例子：
-- 内层返回3个ID → 放内存哈希表
SELECT * FROM order_info
WHERE user_id IN (
SELECT id FROM user WHERE status = 'active'
);
-- 内层结果：(101, 103, 105)
-- MySQL 在内存中建哈希表：{101, 103, 105}
-- 外层每取一行 order_info，查哈希表 O(1) → 极快
内存哈希查找：外层每行都只要 O(1) 就能判断"在不在里面"
⚠️ 结果大 → 写磁盘（变慢）
条件：子查询结果 >
tmp_table_size（默认16MB）
存储方式：内存装不下 → 写到磁盘
临时表（InnoDB 或 MyISAM）
数据结构：从哈希表变成
B+树（查找 O(logN)，慢不少）
例子：
-- 内层返回10万个ID → 内存装不下 → 写磁盘
SELECT * FROM order_info
WHERE user_id IN (
SELECT user_id FROM big_table
WHERE create_time > '2025-01-01'
);
-- 内层结果：10万条 → 超过 tmp_table_size
-- MySQL 在磁盘创建临时表（B+树索引）
-- 外层每行查 B+树 → 比内存哈希慢 10-100 倍
内存→磁盘的转换是自动的，但对性能影响巨大！
判断方法：EXPLAIN 的 Extra 列看到
Using temporary 就说明用了临时表。
如果想确认是内存还是磁盘，执行后查：
SHOW STATUS LIKE 'Created_tmp%';
SHOW STATUS LIKE 'Created_tmp%';
-- 结果：
-- Created_tmp_disk_tables: 2 ← 写磁盘临时表的次数（慢！）
-- Created_tmp_tables: 15 ← 创建临时表的总次数（含内存）
--
-- 如果 Created_tmp_disk_tables 很大 → 说明 tmp_table_size 不够，需要调大
📊 二、三种物化策略对比
| 策略 | 结果大小 | 存放位置 | 数据结构 | 查找速度 | 什么时候触发 |
|---|
| 内存物化 |
< tmp_table_size
（默认16MB） |
内存 |
哈希表 |
O(1) 极快 |
IN 子查询结果集小
（几百~几千条） |
| 磁盘物化 |
> tmp_table_size |
磁盘临时表
（/tmp 目录） |
B+树 |
O(logN) 较慢 |
IN 子查询结果集大
（几万~几十万条） |
不物化
（相关子查询） |
无暂存 |
不存！每行重新查 |
— |
O(N×M)
极慢 |
相关子查询
（DEPENDENT SUBQUERY） |
🔄 三、非相关子查询的完整执行流程图
第①步：执行内层子查询
↓
第②步：结果物化
· 结果 < 16MB → 内存哈希表 ✅
· 结果 > 16MB → 磁盘临时表（B+树）⚠️
↓
第③步：外层查询
· 扫描 order_info 的每一行
· 对每行 user_id，去"暂存区"查找
· 内存哈希 → O(1) 秒查
· 磁盘B+树 → O(logN) 稍慢
↓
第④步：匹配的行返回给客户端
🚨 四、数据量大时会发生什么？
💀 场景1：子查询结果撑爆 tmp_table_size
-- 500万条活跃用户
SELECT * FROM order_info
WHERE user_id IN (
SELECT id FROM user
WHERE create_time > '2024-01-01'
);
-- 内层返回 500万 个 ID
-- 每个 ID 8 字节 → 40MB → 超过 tmp_table_size(16MB)
--
-- MySQL 自动切换到磁盘临时表：
-- 1. 在 /tmp 目录创建临时文件（#sql_xxxx.MYD）
-- 2. 把 500万 个 ID 写入磁盘 B+树
-- 3. 外层每行都去磁盘查 B+树
--
-- 耗时：从 0.05s → 3-10s（慢 60-200 倍）
解决：改写为 JOIN，让优化器选择更优的执行计划
⚠️ 场景2：磁盘临时表占满 /tmp
-- 如果磁盘临时表文件很大
-- /tmp/#sql_xxxx.MYD → 2GB
-- 多个慢查询同时跑 → /tmp 目录被占满
-- 报错：ERROR 126 (ER_TMP_TABLE):
-- "Incorrect key file for table '/tmp/#sql_xxxx.MYI'"
--
-- 此时整个数据库响应变慢，甚至影响其他查询
解决：调大 tmp_table_size 和 max_heap_table_size，或改写为 JOIN
🔁 场景3：相关子查询 → 无物化 → 逐行扫描
-- 相关子查询：根本不会物化！
SELECT * FROM order_info o
WHERE amount > (
SELECT AVG(amount) FROM order_info
WHERE user_id = o.user_id
);
--
-- 执行过程（无暂存区！）：
-- 外层第1行 → 执行内层 → 算 AVG
-- 外层第2行 → 执行内层 → 算 AVG
-- 外层第3行 → 执行内层 → 算 AVG
-- ... 重复 10万次！
--
-- 每次内层都是一次完整的索引查找+计算
-- 10万行 × 每次0.1ms = 10秒
解决：改写为 JOIN + GROUP BY，AVG 只算一次
🛠️ 五、怎么调优？关键参数
-- 查看当前临时表相关参数
SHOW VARIABLES LIKE 'tmp_table_size'; -- 默认 16MB（内存临时表上限）
SHOW VARIABLES LIKE 'max_heap_table_size'; -- 默认 16MB（MEMORY表上限）
SHOW VARIABLES LIKE 'internal_tmp_disk_storage_engine'; -- MySQL 8.0: InnoDB
-- 调大内存临时表上限（让更多子查询结果留在内存）
SET GLOBAL tmp_table_size = 64 * 1024 * 1024; -- 16MB → 64MB
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024; -- 同步调大
-- 查看临时表使用情况
SHOW STATUS LIKE 'Created_tmp_disk_tables'; -- 写磁盘次数（越小越好）
SHOW STATUS LIKE 'Created_tmp_tables'; -- 总临时表次数
调优经验：
① tmp_table_size 和 max_heap_table_size 必须同时设置，取较小的那个生效
② 一般设 64MB~256MB 就够了，不建议太大（占用太多内存）
③ 如果 Created_tmp_disk_tables / Created_tmp_tables > 30% → 说明磁盘临时表太多，需要调大参数或优化SQL
④ 最根本的解决方案：把大子查询改写为 JOIN，减少临时表的使用
📋 六、MySQL 8.0 的优化器做了什么？
MySQL 8.0 的优化器比老版本聪明很多，会自动对子查询做以下优化：
| 优化策略 | 触发条件 | 做了什么 | EXPLAIN 特征 |
|---|
物化 + 半连接
(Semi-Join) |
IN 子查询
非相关 |
把子查询结果物化后，
与外层做半连接（找到匹配就停，不继续） |
select_type = SIMPLE
Extra 可见 FirstMatch
或 LooseScan |
首匹配
(FirstMatch) |
IN 子查询
外层有重复值 |
外层遇到第一个匹配就停，
不继续找后面的 |
Extra: FirstMatch(o) |
松散扫描
(LooseScan) |
IN 子查询
子查询有重复值 |
跳过子查询中重复的值，
减少外层匹配次数 |
Extra: LooseScan |
重复物化
(Duplicate Weedout) |
IN 子查询
可能产生重复 |
先做 JOIN，
再用临时表去重 |
Extra: Using temporary;
Start temporary |
-- MySQL 8.0 自动优化演示
EXPLAIN SELECT * FROM order_info
WHERE user_id IN (SELECT id FROM user WHERE status = 'active');
-- MySQL 5.7 的 EXPLAIN 结果（子查询是独立步骤）：
-- +----+-------------+-------+...+ select_type | Extra
-- | 1 | PRIMARY | o |...| PRIMARY | Using where
-- | 2 | SUBQUERY | u |...| SUBQUERY | Using index
--
-- MySQL 8.0 的 EXPLAIN 结果（优化器重写为 JOIN！）：
-- +----+-------------+-------+...+ select_type | Extra
-- | 1 | SIMPLE | u |...| SIMPLE | Using index
-- | 1 | SIMPLE | o |...| SIMPLE | Using where; FirstMatch(u)
--
-- 注意：select_type 从 PRIMARY+SUBQUERY 变成了两个 SIMPLE
-- 优化器自动把子查询改写为 Semi-Join，性能提升很大！
子查询内部原理总结：
① 非相关子查询 → 先物化（存结果），再匹配。结果小放内存（哈希），大放磁盘（B+树）
② 相关子查询 → 不物化！外层每行都重新执行内层，极慢
③ tmp_table_size 决定了内存临时表的上限（默认16MB），超过就写磁盘
④ 用 SHOW STATUS LIKE 'Created_tmp%' 监控临时表使用情况
⑤ MySQL 8.0 优化器会自动把 IN 子查询改写为 Semi-Join，性能提升很大
⑥ 最根本的优化：大子查询改写为 JOIN，让优化器灵活选择执行计划
📂 第五章：Filesort — 排序的代价
你让 MySQL 按 created_at 排序。两种情况：
情况A（走索引排序）：created_at 上有索引，B+树本身就是按 created_at 排好的，直接按顺序读出来就行了。就像字典正文已经按拼音排好了。
情况B（Filesort）：你要按 amount 排序，但 amount 上没有索引。MySQL 只能先把所有数据捞出来，在内存里（或磁盘上）做一次排序。就像你拿到一堆乱序的卡片，在桌上重新排列。
Filesort 不一定慢（小数据量在内存里排很快），但数据量大时 Filesort 会很恐怖——要写临时文件到磁盘，IO爆炸。
✅ 走索引排序（没有 Filesort）
💡 因为 idx_created 的B+树叶子节点已经是按 created_at 排好的，直接顺序读就行。
📖 Filesort 的两种排序算法
双路排序（老版本）：
第一次读：取出排序字段 + 主键 → 排序
第二次读：按排序后的主键回表取完整数据
→ 两次IO，老了去了
单路排序（新版本默认）：
一次性取出所有需要的字段 → 在 sort_buffer 里排序
→ 一次IO，但 sort_buffer 放不下就写磁盘，更恐怖
类比：双路排序像"先按笔画把所有字的页码排好，再逐页翻过去取解释"。单路排序像"一次性把所有页的解释都抄出来，铺在桌上排序"。如果桌子（sort_buffer）不够大，只能分批搬到隔壁房间（磁盘临时文件）。
面试速答："InnoDB和MyISAM区别" → InnoDB支持事务/行锁/外键，MyISAM只有表锁。现在默认InnoDB，MyISAM基本淘汰。
⚠️ sort_buffer 不够用怎么办？
MySQL 的 sort_buffer_size 默认 256KB。如果要排序的数据超过这个大小，就会分批排：
① 在 sort_buffer 里排一批 → 写入临时文件
② 再排下一批 → 写入临时文件
③ 最后对所有临时文件做归并排序
→ 磁盘IO 爆炸！
实战建议：与其调大 sort_buffer_size，不如让 ORDER BY 走索引（避免 Filesort）。
📋 什么时候 ORDER BY 能走索引？
| SQL | 能走索引？ | 原因 |
|---|
| ORDER BY created_at | ✅ | idx_created 索引天然有序 |
| ORDER BY created_at DESC | ✅ | B+树叶子可以反向扫描 |
| WHERE user_id=101 ORDER BY status | ✅ | idx_user_status 联合索引，user_id等值后status有序 |
| ORDER BY amount | ❌ | amount没有索引 |
| ORDER BY user_id, created_at | ❌ | 没有这个联合索引 |
| WHERE status='paid' ORDER BY created_at | ❌ | 索引是(user_id,status)，status不是最左列 |
| ORDER BY user_id DESC, status ASC | ❌ | 排序方向不一致 |
🖥️ 工作场景：发现 ORDER BY 慢查询
mysql> SHOW STATUS LIKE 'Sort_merge_passes';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 127 | ← 🔥 归并排序127次！sort_buffer严重不够
+-------------------+-------+
Sort_merge_passes > 0 说明 Filesort 溢出到磁盘了。解决方案不是调大 sort_buffer，而是让 ORDER BY 走索引。
🖥️ Java 中避免 Filesort 的实践
Filesort 的本质是排序没走索引。在 Java 开发中，最常见的场景就是"列表排序"。
// ========= 场景：订单列表按时间倒序 =========
// ❌ 错误：在 Java 内存里排序（数据量大时 OOM）
@Select("SELECT * FROM order_info WHERE user_id = #{userId}")
List findByUserId(@Param("userId") Long userId);
// Java端排序：
// orders.sort(Comparator.comparing(Order::getCreatedAt).reversed());
// 问题：如果有10万条 → 全部加载到内存 → 再排序 → OOM风险！
// ✅ 正确：让 MySQL 排序（利用索引避免 Filesort）
// 1. 建索引：CREATE INDEX idx_user_time ON order_info(user_id, created_at);
// 2. SQL 加 ORDER BY：
@Select("SELECT * FROM order_info "
+ "WHERE user_id = #{userId} "
+ "ORDER BY created_at DESC")
List findByUserIdOrderByTime(@Param("userId") Long userId);
// MySQL 用 idx_user_time: WHERE 用 user_id, ORDER BY 用 created_at
// 叶子节点按 created_at 有序 → 不需要 filesort ✅
// ========= 另一个场景：分页排序 =========
// PageHelper 自动处理 LIMIT + ORDER BY
@GetMapping("/orders")
public Result<PageInfo<OrderVO>> list(
@RequestParam Long userId,
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "20") int size,
@RequestParam(defaultValue = "created_at") String sort,
@RequestParam(defaultValue = "desc") String order) {
// ⚠️ 注意 sort/order 参数不能用 ${} 直接拼！
// 要用白名单校验
List<String> allowedSorts = List.of("created_at", "amount", "id");
List<String> allowedOrders = List.of("asc", "desc");
if (!allowedSorts.contains(sort) || !allowedOrders.contains(order)) {
throw new BusinessException("不支持的排序字段");
}
// 白名单校验后才能用 ${}
PageHelper.startPage(page, size);
List<OrderVO> list = orderMapper
.listSorted(userId, sort, order);
return Result.success(new PageInfo<>(list));
}
// Mapper：
// @Select("<script>"
// + "SELECT * FROM order_info "
// + "WHERE user_id = #{userId} "
// + "ORDER BY ${sort} ${order}"
// + "</script>")
// List<OrderVO> listSorted(
// @Param("userId") Long userId,
// @Param("sort") String sort,
// @Param("order") String order);
🔒 第六章：事务 & 隔离级别 — "提交事务"流程
一个典型的交易流程：
① 选择商品 → ② 付款 → ③ 扣库存 → ④ 生成订单
这4步要么全部成功，要么全部失败。付了钱但库存没扣？扣了库存但订单没生成？都不行！
这就是 事务（Transaction）：一组操作，要成功全成功，要失败全失败。
用 MySQL 的话说就是 ACID：
• A（原子性）：要么全做，要么不做 —— undo log 保证
• C（一致性）：数据从一个正确状态变到另一个正确状态 —— 由A/I/D共同保证
• I（隔离性）：多个事务互相不干扰 —— 锁 + MVCC 保证
• D（持久性）：提交了就不会丢 —— redo log 保证
📊 四种隔离级别
假设两个用户同时操作数据库（两个事务并发执行）：
READ UNCOMMITTED（读未提交）：
事务A刚改了某行数据（还没提交），事务B立刻看到了新值。万一事务A回滚了？事务B看到的就是"脏数据"。—— 脏读
READ COMMITTED（读已提交）：
只有事务A提交了，事务B才能看到新值。但事务A改了两次，事务B两次读到的值不一样。—— 不可重复读
REPEATABLE READ（可重复读，InnoDB默认）：
事务B在整个事务期间，不管事务A怎么改，读到的都是同一个值。—— InnoDB 还额外防止了幻读（通过 MVCC + 间隙锁）
SERIALIZABLE（串行化）：
所有事务排队一个一个来。最安全，但最慢。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | InnoDB默认 |
|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 最快 | — |
| READ COMMITTED | 不会 | 可能 | 可能 | 快 | — |
| REPEATABLE READ | 不会 | 不会 | 不会* | 中等 | ✅ 默认 |
| SERIALIZABLE | 不会 | 不会 | 不会 | 最慢 | — |
* InnoDB 的 RR 级别通过 MVCC + 间隙锁在很大程度防止了幻读，但某些特殊场景下仍可能出现。
📖 MVCC 是什么？（多版本并发控制）
MVCC = 让读写互不阻塞的秘密武器。
类比：数据库的每一行数据都有当前版本和历史版本（通过 undo log 链保存）。
• 写事务：直接修改当前数据，但旧版本通过 undo log 保留着
• 读事务：看到的是事务开始时刻的"快照"，不管当前数据怎么被别人改
每行数据都藏着两个隐藏列：
• trx_id：最后修改这行的事务ID
• roll_pointer：指向这行的上一个版本（undo log链）
读的时候，如果发现当前版本的事务ID > 自己的事务ID（说明是别人改的），就顺着 roll_pointer 往前找，找到一个"自己能看到"的版本。
MVCC 版本链示意（读取 amount 的过程）
当前版本
amount=35
trx_id=108
← roll_pointer
历史版本1
amount=22
trx_id=105
← roll_pointer
历史版本2
amount=18
trx_id=100
事务102来读时：当前trx_id=108 > 102，不可见 → 找到历史版本1 trx_id=105 > 102，不可见 → 找到历史版本2 trx_id=100 ≤ 102，✅ 可见！返回 amount=18
🖥️ 演示：不可重复读 vs 可重复读
mysql> BEGIN;
mysql> SELECT amount FROM order_info WHERE id=1;
+--------+
| amount |
+--------+
| 18.00 |
+--------+
mysql> SELECT amount FROM order_info WHERE id=1;
+--------+
| amount |
+--------+
| 18.00 | ← 🔥 依然是18.00！这就是RR（可重复读）
+--------+
🖥️ Spring Boot 中怎么用事务？(@Transactional)
上面是 MySQL 命令行的事务操作。但日常开发中，你99%的时间是在 Java 代码 里用事务。
Spring Boot 提供了 @Transactional 注解，一行搞定。
✅ 基本用法
@Service
public class OrderService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private ProductMapper productMapper;
@Autowired
private AccountMapper accountMapper;
// 下单 = 扣库存 + 创建订单 + 扣余额
// 三个操作要在一个事务里
@Transactional
public void createOrder(CreateOrderRequest req) {
// 1. 扣库存
productMapper.decreaseStock(
req.getProductId(), req.getQuantity());
// 2. 创建订单
orderMapper.insert(req.toOrder());
// 3. 扣余额
accountMapper.deduct(req.getUserId(),
req.getAmount());
// 如果第3步报异常 → 1、2自动回滚！
}
}
⚠️ @Transactional 常见坑
// 坑1：默认只对 RuntimeException 回滚
@Transactional
public void updateOrder() {
orderMapper.update(...);
// 如果抛 checked Exception（如 IOException）
// 默认不回滚！
// 解决：指定回滚类型
}
// ✅ 正确写法：
@Transactional(rollbackFor = Exception.class)
public void updateOrder() { ... }
// 坑2：同类方法调用，事务不生效！
// ❌ 因为 Spring AOP 基于代理，内部调用绕过代理
public void methodA() {
this.methodB(); // methodB的@Transactional不生效
}
@Transactional
public void methodB() { ... }
// 坑3：方法必须是 public
// @Transactional 只对 public 方法生效
📋 事务传播行为（Propagation）— 了解最常用的两种
| 传播行为 | 含义 | 什么时候用 |
|---|
REQUIRED（默认） | 有事务就加入，没有就新建 | 99%的场景用这个 |
REQUIRES_NEW | 总是新建事务，外层事务挂起 | 日志记录（不受主事务回滚影响） |
NESTED | 嵌套事务，外层回滚它也回滚，它回滚外层不受影响 | 子操作独立回滚 |
🔄 Spring 事务 + MySQL 隔离级别配合
// 可以在注解里指定隔离级别（一般用默认的就行）
@Transactional(
isolation = Isolation.REPEATABLE_READ, // MySQL默认RR，一般不改
rollbackFor = Exception.class,
timeout = 30 // 超时30秒自动回滚，防止长事务
)
public void createOrder(CreateOrderRequest req) { ... }
// application.yml 配置全局超时
// spring:
// transaction:
// default-timeout: 30
🔐 第七章：锁机制 — 两个人同时修改同一条数据怎么办？
商品库存只剩1件。两个用户同时下单要同一件商品。
没锁：两个事务都成功了，但库存变成 -1 💀
表锁：事务A修改时，整张表暂停服务，其他事务啥也干不了。安全但太慢。
行锁：只锁住被修改的那一行。其他事务操作不同行完全不受影响。✅ InnoDB 就是这样！
InnoDB 的锁类型：
• 共享锁（S锁，读锁）：允许多人同时读，但不能写。SELECT ... LOCK IN SHARE MODE
• 排他锁（X锁，写锁）：只有持锁者能读写。UPDATE/DELETE 自动加
• 意向锁：表级标记，快速判断表里有没有行锁。不用你管。
• 间隙锁（Gap Lock）：锁住两条记录之间的"间隙"，防止别人往这里插入。RR级别才有。
• 临键锁（Next-Key Lock）：行锁 + 间隙锁的组合，InnoDB 在 RR 级别默认的行锁算法。
📋 锁的分类和关系
InnoDB 锁体系
全局锁
FLUSH TABLES WITH READ LOCK
↓ 用于全库备份
表级锁
→
意向锁（IS/IX）
→
元数据锁（MDL）
↓ InnoDB 主要用行锁
行级锁
→
Record Lock
（锁单行）
+
Gap Lock
（锁间隙）
=
Next-Key Lock
（锁行+间隙）
🖥️ 实战演示：行锁的效果
mysql> BEGIN;
mysql> UPDATE order_info SET amount=20.00 WHERE id=1;
Query OK, 1 row affected
mysql> UPDATE order_info SET amount=25.00 WHERE id=1;
⏳ 阻塞！等待终端A释放锁...
mysql> COMMIT;
Query OK, 1 row affected
💡 两个事务同时修改同一行，后到的必须等前面的提交。这就是行锁在保护数据一致性。
⚠️ 锁升级：行锁 → 表锁的坑
如果没有走索引，行锁会变成表锁！
比如：UPDATE order_info SET status='cancelled' WHERE amount=18.00
amount 没有索引，MySQL 不知道该锁哪些行，只好把所有行都锁住。
类比：你只想锁一行，但因为没有索引定位，只好把整张表都锁了。
这就是为什么 UPDATE/DELETE 一定要走索引！
🖥️ Java 中的锁方案：乐观锁 vs 悲观锁
上面讲了 MySQL 的行锁机制。但在 Java 代码层面，你有更灵活的选择：
✅ 乐观锁（推荐，大部分场景）
不真的加锁。更新时检查"版本号有没有变"。
-- 1. 表加 version 字段
ALTER TABLE product ADD COLUMN version INT DEFAULT 0;
-- 2. 更新时带版本号
UPDATE product
SET stock = stock - 1, version = version + 1
WHERE id = 5 AND version = 3;
-- 如果 version 变了 → 影响行数 = 0 → 说明被别人改过了
-- 3. MyBatis 写法
// ProductMapper.java
@Update("UPDATE product SET stock = stock - #{qty}, "
+ "version = version + 1 "
+ "WHERE id = #{id} AND version = #{version}")
int decreaseStock(
@Param("id") Long id,
@Param("qty") int qty,
@Param("version") int version);
// Service 层：CAS 重试
public boolean tryDecreaseStock(Long id, int qty) {
int maxRetry = 3;
for (int i = 0; i < maxRetry; i++) {
Product p = productMapper.selectById(id);
int rows = productMapper.decreaseStock(
id, qty, p.getVersion());
if (rows > 0) return true;
// version 不匹配，重试
}
throw new BusinessException("库存扣减失败，请重试");
}
优点：不加锁，并发高。适合：读多写少、冲突概率低的场景。
🔒 悲观锁（高并发抢购）
先锁住，再操作。MySQL 层面用 SELECT ... FOR UPDATE。
@Transactional(rollbackFor = Exception.class)
public boolean decreaseStockWithLock(Long id, int qty) {
// 1. 先锁住这一行（SELECT ... FOR UPDATE）
// MySQL 会给这行加 X 锁
Product p = productMapper
.selectForUpdate(id);
// @Select("SELECT * FROM product "
// + "WHERE id = #{id} FOR UPDATE")
if (p.getStock() < qty) {
throw new BusinessException("库存不足");
}
// 2. 再更新
productMapper.decreaseStock(id, qty);
return true;
}
// 注意：FOR UPDATE 必须在事务里！
// 而且 WHERE 条件必须走索引，否则锁全表！
缺点：锁等待，并发低。适合：秒杀、抢购等高冲突场景。
选型指南：
• 99% 的场景用乐观锁（version字段 + CAS重试）
• 秒杀/抢购用悲观锁（SELECT FOR UPDATE）
• 跨服务用分布式锁（Redis SETNX / Redisson）
🖥️ 工作场景：排查死锁
mysql> BEGIN;
mysql> UPDATE order_info SET amount=20 WHERE id=1;
mysql> BEGIN;
mysql> UPDATE order_info SET amount=30 WHERE id=3;
mysql> UPDATE order_info SET amount=40 WHERE id=3;
mysql> UPDATE order_info SET amount=50 WHERE id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> SHOW ENGINE INNODB STATUS\G
...
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION: TRANSACTION 108
*** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 58 page no 4 ...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... id=3
*** (2) TRANSACTION: TRANSACTION 109
*** (2) HOLDS THE LOCK(S): RECORD LOCKS ... id=3
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... id=1
*** WE ROLL BACK TRANSACTION (2) ← MySQL自动回滚代价较小的事务
⚠️ 死锁排查四步法：
① SHOW ENGINE INNODB STATUS 看死锁日志
② 找到两个事务分别持有什么锁、等什么锁
③ 定位到具体的 SQL
④ 分析为什么会产生交叉等待（通常是更新顺序不一致）
🖥️ 死锁在 Java 代码中的体现 — 错误 vs 正确
上面的 MySQL 命令行演示了死锁是怎么产生的。但在实际开发中，你很少会手动开两个终端去 UPDATE。
真正的死锁发生在 Java 代码并发执行多个事务 时。下面用「批量更新订单状态」这个真实场景演示。
❌ 错误写法：随机顺序更新 → 死锁
场景：运营后台"批量取消过期订单"，一次传了多个订单ID。
@Service
public class OrderBatchService {
@Autowired
private OrderMapper orderMapper;
// ❌ 线程A传入 ids=[1, 3]
// 线程B传入 ids=[3, 1]
// → 跟上面MySQL演示的死锁一模一样！
@Transactional(rollbackFor = Exception.class)
public void batchCancel(List<Long> ids) {
for (Long id : ids) {
// 线程A: 先锁id=1，再锁id=3
// 线程B: 先锁id=3，再锁id=1
// → 交叉等待 → 死锁！
orderMapper.updateStatus(
id, "cancelled");
}
}
}
// 报错日志：
// Caused by: com.mysql.cj.jdbc.exceptions
// .MySQLTransactionRollbackException:
// Deadlock found when trying to get lock;
// try restarting transaction
✅ 正确写法1：按 ID 排序后再更新
所有线程都按 id 升序更新 → 锁的获取顺序一致 → 不会交叉等待。
@Service
public class OrderBatchService {
@Autowired
private OrderMapper orderMapper;
@Transactional(rollbackFor = Exception.class)
public void batchCancel(List<Long> ids) {
// ✅ 核心：排序！保证所有线程按同一顺序拿锁
List<Long> sortedIds = ids.stream()
.sorted()
.collect(Collectors.toList());
for (Long id : sortedIds) {
// 线程A ids=[1,3] → 锁id=1, 再锁id=3
// 线程B ids=[3,1] → 锁id=1, 再锁id=3
// → 顺序一致，不会死锁！
orderMapper.updateStatus(
id, "cancelled");
}
}
}
✅ 正确写法2：拆成小事务 + 单条更新
每条UPDATE单独一个事务 → 不会同时持有多个锁 → 不可能死锁。
@Service
public class OrderBatchService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private TransactionTemplate txTemplate;
public BatchResult batchCancel(List<Long> ids) {
int success = 0, fail = 0;
for (Long id : ids) {
try {
// ✅ 每条单独事务
txTemplate.execute(status -> {
orderMapper.updateStatus(
id, "cancelled");
return null;
});
success++;
} catch (Exception e) {
// 单条失败不影响其他
fail++;
log.warn("取消订单{}失败", id, e);
}
}
return new BatchResult(success, fail);
}
}
✅ 正确写法3：一条 SQL 批量更新
最简单：一条 UPDATE ... IN (...) 搞定，只加一次锁。
// Mapper
@Update("<script>" +
"UPDATE order_info SET status = #{status} " +
"WHERE id IN " +
"<foreach collection='ids' item='id' " +
" open='(' separator=',' close=')'>" +
" #{id}" +
"</foreach>" +
"</script>")
int batchUpdateStatus(
@Param("ids") List<Long> ids,
@Param("status") String status);
// Service — 一行搞定
@Transactional(rollbackFor = Exception.class)
public void batchCancel(List<Long> ids) {
// ✅ 一条SQL更新所有行
// MySQL 内部按id顺序加锁（因为WHERE id IN）
// 不会死锁
orderMapper.batchUpdateStatus(
ids, "cancelled");
}
// ⚠️ 注意：ids 数量不要超过500
// 否则SQL太长，且锁太多行影响并发
🖥️ 死锁发生后的 Java 端处理
// 死锁发生时 MySQL 会自动回滚其中一个事务
// Java 端会收到 MySQLTransactionRollbackException
// 实战建议：捕获死锁异常，自动重试
@Service
public class OrderService {
@Autowired
private OrderMapper orderMapper;
private static final int MAX_RETRY = 3;
public void updateWithRetry(Long id, String status) {
for (int i = 0; i < MAX_RETRY; i++) {
try {
doUpdate(id, status);
return; // 成功就退出
} catch (MySQLTransactionRollbackException e) {
// 死锁异常，可以重试
log.warn("死锁重试第{}次, id={}", i + 1, id);
if (i == MAX_RETRY - 1) {
throw new BusinessException(
"系统繁忙，请稍后再试");
}
// 短暂等待再重试
try { Thread.sleep(100); }
catch (InterruptedException ignored) {}
}
}
}
@Transactional(rollbackFor = Exception.class)
public void doUpdate(Long id, String status) {
orderMapper.updateStatus(id, status);
}
}
死锁预防总结：
① 按固定顺序更新（ID排序）→ 最有效的预防手段
② 大事务拆小→ 减少锁持有时间，降低交叉等待概率
③ 一条SQL批量操作→ 只加一次锁，不会死锁
④ Java端加重试→ 死锁不可避免但可以重试，用户体验不受影响
⑤ UPDATE 的 WHERE 必须走索引→ 否则行锁退化为表锁，死锁概率暴增
💾 第八章：Buffer Pool — MySQL 的内存缓存
MySQL 读数据不是每次都去硬盘拿。它有一个内存缓存叫 Buffer Pool。
类比：你有一个小书桌，上面放着最近常用的几页书。
• 要查 id=1 的数据 → 先看内存里有没有 → 有，直接用（缓存命中）
• 内存没有 → 去磁盘读取 → 放到 Buffer Pool → 下次不用再读磁盘
• 缓存满了 → 把最久没用的数据页淘汰（LRU淘汰）
Buffer Pool 默认128MB，线上一般调到物理内存的 60%~80%。
如果 Buffer Pool 太小，你的数据库会疯狂读磁盘，性能暴跌。
📐 Buffer Pool 内部长什么样？
Buffer Pool 结构
Buffer Pool
（默认128MB）
包含 ↓
数据页
Data Page
（16KB/页）
索引页
Index Page
自适应哈希
AHI
锁信息
Lock Info
LRU链表管理：热数据在前面，冷数据在后面被淘汰
📖 改进的 LRU 算法（冷热分离）
MySQL 没用普通 LRU，而是用了 冷热分离的改进 LRU：
• 新生代（young 区）：5/8 的空间，放经常访问的热数据
• 老生代（old 区）：3/8 的空间，放新读入的数据
新读入的数据先进 old 区，如果 在 old 区停留超过 1 秒 后再次被访问，才提升到 young 区。
为什么？ 防止一次全表扫描把所有热数据都挤出 Buffer Pool！
类比：新数据先进"冷区"，被访问超过1秒后才升到"热区"。避免一次全表扫描把所有热数据挤走。
🖥️ 监控 Buffer Pool 状态
Buffer Pool 命中率: 99.7% ← 正常！应该 > 95%
⚠️ 如果命中率 < 95%，说明 Buffer Pool 太小，频繁读磁盘。需要调大 innodb_buffer_pool_size。
📋 redo log 和 undo log
这两个 log 跟 Buffer Pool 紧密相关：
redo log（重做日志）：
• 你在 Buffer Pool 里改了数据，还没刷到磁盘。如果突然断电，改了白改？
• 所以修改的同时，先把"改了什么"写到 redo log 里（WAL = Write-Ahead Logging）。
• redo log 是顺序写，很快。断电后重启，通过 redo log 重做一遍，恢复数据。
undo log（回滚日志）：
• 你要回滚事务？得知道"改之前长什么样"。undo log 就是存旧版本的。
• MVCC 的版本链就是通过 undo log 实现的。
类比：
• redo log = "操作日志"（记录做了什么，断电后可以重做）
• undo log = "修改前快照"（记录改之前的样子，需要时可以撤回）
一次 UPDATE 的完整流程
① 读取数据页
到 Buffer Pool
→
② 写 undo log
（旧值）
→
③ 修改 Buffer Pool
中的数据
→
④ 写 redo log
（新值）
→
⑤ 提交事务
COMMIT
数据页不立即写磁盘（随机写慢），靠 redo log（顺序写快）保证不丢。后台线程异步刷盘。
🎬 第九章：10 个实战场景 — 原理在工作中怎么用
场景1索引失效明明建了索引，为什么还是全表扫描？
📅 周一早上，产品经理说："用户反馈'我的订单'页面打开要5秒！"
user_id + 0 = 101 → 对索引列做了运算！B+树里存的是 user_id 原始值，加了0之后MySQL不知道怎么查了。
索引失效的常见原因（都跟B+树原理有关）：
① 对索引列做运算/函数：WHERE YEAR(created_at)=2025 → 用 WHERE created_at >= '2025-01-01'
② 隐式类型转换：WHERE user_id='101'（user_id是int，传了string）
③ LIKE 左模糊：WHERE name LIKE '%珍珠'
④ OR 条件有一边没索引
⑤ 联合索引不满足最左前缀
🖥️ Java 中导致索引失效的常见写法
❌ MyBatis 中对索引列做运算
// 需求：查询 user_id + 0 等于某个值的订单
// （虽然实际很少这么写，但类似的错误更隐蔽）
// ❌ 在 Java 里拼接运算表达式
@Select("SELECT * FROM order_info "
+ "WHERE user_id + 0 = #{userId}")
List findByCalc(@Param("userId") int userId);
// MySQL: type=ALL, 全表扫描！
// ❌ 使用 MySQL 函数
@Select("SELECT * FROM order_info "
+ "WHERE DATE(created_at) = #{date}")
List findByDate(@Param("date") String date);
// DATE() 函数让 created_at 索引失效！
// ✅ 正确：用范围查询代替函数
@Select("SELECT * FROM order_info "
+ "WHERE created_at >= #{start} "
+ "AND created_at < #{end}")
List findByDateRange(
@Param("start") String start,
@Param("end") String end);
// 传入 start="2025-06-01", end="2025-06-02"
❌ 隐式类型转换（最容易踩的坑）
// phone 是 VARCHAR，有索引 idx_phone
// ❌ Mapper 参数类型不对
@Select("SELECT * FROM user "
+ "WHERE phone = #{phone}")
User findByPhone(@Param("phone") Long phone);
// Java 传 Long → MyBatis 传数字 → MySQL 做隐式转换
// → 索引失效！type=ALL
// ✅ 正确：参数类型匹配字段类型
@Select("SELECT * FROM user "
+ "WHERE phone = #{phone}")
User findByPhone(@Param("phone") String phone);
// Java 传 String → MyBatis 传字符串 → 走索引！
// 💡 规律：Java 参数类型要跟 MySQL 字段类型一致
// DB是VARCHAR → Java用String
// DB是INT → Java用Integer/Long
// DB是BIGINT → Java用Long
// DB是DECIMAL → Java用BigDecimal
场景2深翻页第10000页的数据怎么这么慢？
📅 运营后台要导出数据，翻到第10000页超时了。
为什么慢？ MySQL 不是直接跳到第100000行。它是从第1行开始读，读 100010 行，然后扔掉前 100000 行。
而且 SELECT * 要回表，100010 次回表！
类比：你要翻到笔记本的第100页，笨办法是从第1页逐页翻过去，而不是直接跳到第100页。
💡 方案2的子查询 SELECT id FROM order_info 走覆盖索引（不需要回表），只取10个ID再去回表，从100010次回表降到10次！
🖥️ Java 中的分页实现
方式一：MyBatis PageHelper（最常用）
// 1. 添加依赖
// <dependency>
// <groupId>com.github.pagehelper</groupId>
// <artifactId>pagehelper-spring-boot-starter</artifactId>
// </dependency>
// 2. Controller
@GetMapping("/orders")
public Result<PageInfo<OrderVO>> listOrders(
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "20") int size) {
// 一行搞定分页！
PageHelper.startPage(page, size);
List<OrderVO> list = orderService.list();
PageInfo<OrderVO> pageInfo = new PageInfo<>(list);
return Result.success(pageInfo);
// pageInfo 包含：list, total, pageNum, pageSize, pages
}
// 3. 底层自动改写 SQL：
// SELECT * FROM order_info
// LIMIT (page-1)*size, size
// 底层用 MySQL 的 LIMIT 实现分页
方式二：游标分页（大数据量推荐）
// App 端"下拉加载更多"的场景
// 不用 page/size，用 lastId
@GetMapping("/orders/cursor")
public Result<List<OrderVO>> loadMore(
@RequestParam Long lastId,
@RequestParam(defaultValue = "20") int size) {
// SELECT * FROM order_info
// WHERE id > #{lastId}
// ORDER BY id ASC LIMIT #{size}
List<OrderVO> list = orderService
.listAfterId(lastId, size);
return Result.success(list);
}
// MyBatis XML:
// <select id="listAfterId" resultType="OrderVO">
// SELECT * FROM order_info
// WHERE id > #{lastId}
// ORDER BY id ASC LIMIT #{size}
// </select>
场景3联合索引最左前缀为什么 WHERE status='paid' 不走索引？
📅 新同事写了条SQL，WHERE 条件里用了联合索引的第二列，发现没走索引。
为什么？ 联合索引 (user_id, status) 在B+树里是先按 user_id 排序，user_id 相同的再按 status 排序。
类比：电话簿先按姓排，同姓再按名排。你只按名查"叫小明的人"，电话簿帮不了你，因为小明可能分散在不同姓里。
🌳 最左前缀原理图解 — idx_user_status (user_id, status)
联合索引的排序：先 user_id，再 status
叶子节点排列：
(101, completed) → (101, paid) → (101, pending) → (102, completed) → (102, pending) → (103, paid)
↑ user_id=101的排一起了，status在其中有序
❌ WHERE status = 'paid'
你要找 status='paid' 的行。
但索引先排的是 user_id！
paid 分布在：
→ user_id=101 里有一个 paid
→ user_id=103 里有一个 paid
分散在不同 user_id 里，
B+树没法快速定位！
→ 只能扫全部叶子 = 全表扫描
✅ WHERE user_id = 101 AND status = 'paid'
先用 user_id=101：
→ B+树快速定位到 user_id=101 区间
在这个区间里 status 是有序的：
completed → paid → pending
再用 status='paid'：
→ 在小区间里直接找到 paid
B+树完美配合！
→ 走索引 ref
最左前缀原则：联合索引 (A, B, C) 的排序是 A → B → C。
能走索引：WHERE A / WHERE A, B / WHERE A, B, C
不能走索引：WHERE B / WHERE C / WHERE B, C（跳过了最左列A）
🖥️ Java 端实现：只查需要的字段
❌ SELECT * 浪费性能
// Entity 包含所有字段
@Data
public class Order {
private Long id;
private Integer userId;
private Integer productId;
private Integer storeId;
private BigDecimal amount;
private String status;
private LocalDateTime createdAt;
}
// ❌ 用 SELECT * → 查了所有字段
// 但前端列表页只需要 id, userId, status, createdAt
@Select("SELECT * FROM order_info "
+ "ORDER BY created_at DESC LIMIT 10")
List findRecent();
// → 回表取 amount, productId, storeId...
// → 白白浪费 IO！
✅ 只查需要的字段 + 覆盖索引
// 用轻量 VO 代替完整 Entity
@Data
public class OrderListVO {
private Long id;
private Integer userId;
private String status;
private LocalDateTime createdAt;
}
// ✅ 只查4个字段 → 如果有合适的索引就是覆盖索引
@Select("SELECT id, user_id, status, created_at "
+ "FROM order_info "
+ "ORDER BY created_at DESC LIMIT 10")
List findRecentList();
// → 如果 idx_created 覆盖了这些字段 → Using index
// → 不回表，快 10~100 倍！
// 💡 最佳实践：建联合索引
// CREATE INDEX idx_created_cover
// ON order_info(created_at, user_id, status);
// → created_at 排序 + user_id/status 覆盖 = 完美
场景4EXPLAIN type 详解这些 type 值分别代表什么？
面试必考！EXPLAIN 结果的 type 列从好到差排列：
| type | 含义 | 生活类比 | 性能 |
|---|
| system | 表中只有一行 | 表里只有一行 | 🟢 最快 |
| const | 主键/唯一索引等值查 | 按主键直接查 | 🟢 极快 |
| eq_ref | JOIN时主键/唯一索引 | JOIN时按主键匹配 | 🟢 很快 |
| ref | 普通索引等值查 | 按普通索引查，找到几条 | 🟢 快 |
| range | 索引范围查询 | BETWEEN 5 AND 10 | 🟡 还行 |
| index | 全索引扫描 | 遍历整棵索引树 | 🟠 勉强 |
| ALL | 全表扫描 | 把所有行扫一遍 | 🔴 最慢 |
💡 面试回答要点：至少要达到 ref 级别才算"走了索引"。出现 ALL 就要优化（加索引/改SQL）。
场景5事务回滚事务没包完整导致数据不一致？
📅 数据不一致：操作执行了一半就中断了！—— 典型的事务没包完整。
💡 这就是 ACID 中 A（原子性）的应用：要成功全成功，要失败全失败。undo log 保证了这一点。
🖥️ Java 代码对比：不加事务 vs 加事务
❌ 没有事务 — 中间异常导致数据不一致
@Service
public class OrderService {
@Autowired
private UserMapper userMapper;
@Autowired
private OrderMapper orderMapper;
// ❌ 没有 @Transactional
public void createOrder(CreateOrderRequest req) {
// 步骤1：扣用户等级
userMapper.deductLevel(req.getUserId());
// 💥 如果这里抛异常（如 OOM、NPE）
// 用户等级扣了，但订单没生成！
// 数据不一致了！
// 步骤2：创建订单
orderMapper.insert(req.toOrder());
}
}
// 结果：user 表 level 少了1，但 order_info 表没有新记录
// 客服接到投诉："我等级扣了但没看到订单！"
✅ 有事务 — 异常自动回滚
@Service
public class OrderService {
@Autowired
private UserMapper userMapper;
@Autowired
private OrderMapper orderMapper;
// ✅ 加了 @Transactional
@Transactional(rollbackFor = Exception.class)
public void createOrder(CreateOrderRequest req) {
// 步骤1：扣用户等级
userMapper.deductLevel(req.getUserId());
// 💥 即使这里抛异常
// Spring 会自动 ROLLBACK
// 步骤1的修改也会被撤销！
// 步骤2：创建订单
orderMapper.insert(req.toOrder());
}
// 要么两步都成功，要么两步都撤销
// ACID 的 A（原子性）保证！
}
场景6覆盖索引优化慢查询从2秒优化到20毫秒
📅 首页要展示"最近10条记录的 user_id 和 status"，原来要2秒。
💡 虽然都是走 idx_created，但优化后不需要回表（所有查询字段在二级索引 + 主键里都有）。从2秒 → 20毫秒！
⚡ 日常开发必会的 MySQL 技巧 — 避坑 + 实战
前面的章节讲了原理。但日常开发中，还有一些非常实用的技巧和常见的坑，面试和工作中都会用到。
下面按场景梳理，每个都是"踩过才知道"的经验。
高频坑 深分页优化 — LIMIT 100000, 10 为什么慢？
问题：分页查询到第10000页时，SQL 变成 LIMIT 100000, 10。
MySQL 的执行方式是：先扫描前100010行，再丢弃前100000行，只返回最后10行。
也就是说，越往后翻页，扫描的行数越多，速度越慢。
❌ 原始写法（越翻越慢）
SELECT * FROM order_info
WHERE user_id = 101
ORDER BY id DESC
LIMIT 100000, 10;
-- 扫描100010行，回表100010次
-- 第1页：扫描10行 ✅ 快
-- 第10000页：扫描100010行 ❌ 慢！
✅ 延迟关联（推荐）
-- 第一步：用覆盖索引取ID（不回表）
SELECT id FROM order_info
WHERE user_id = 101
ORDER BY id DESC
LIMIT 100000, 10;
-- Extra: Using index，快！
-- 第二步：拿ID去回表取完整数据
SELECT * FROM order_info
WHERE id IN (
SELECT id FROM order_info
WHERE user_id = 101
ORDER BY id DESC
LIMIT 100000, 10
);
-- 只回表10次！
原理：覆盖索引取 ID 很快（Using index），然后用少量 ID 回表取完整数据。
从 100010 次回表 → 只有 10 次回表，性能提升巨大！
面试必问 COUNT(*) vs COUNT(1) vs COUNT(字段) 的区别
| 写法 | 统计什么 | NULL算不算 | 性能 |
|---|
COUNT(*) | 总行数 | 算 | 最快（InnoDB专门优化） |
COUNT(1) | 总行数 | 算 | 和 COUNT(*) 一样 |
COUNT(id) | id 不为 NULL 的行 | 不算（但主键不可能NULL） | 走二级索引扫描 |
COUNT(status) | status 不为 NULL 的行 | 不算NULL！ | 最慢，要判断每行是否NULL |
结论：统计总行数一律用 COUNT(*)，MySQL 官方推荐，InnoDB 有专门优化（选最小的索引来扫描）。
⚠️ 注意：InnoDB 的 COUNT(*) 不能缓存结果（因为MVCC，不同事务看到的行数可能不同），每次都要扫描。
最隐蔽的坑 隐式类型转换 — 明明有索引却不走！
❌ 类型不匹配导致索引失效
-- phone 字段是 VARCHAR 类型，有索引
CREATE INDEX idx_phone ON user(phone);
-- 用字符串查 → 走索引 ✅
SELECT * FROM user WHERE phone = '13800138000';
-- type: ref, key: idx_phone
-- 用数字查 → 不走索引！❌
SELECT * FROM user WHERE phone = 13800138000;
-- type: ALL, key: NULL
-- 因为 MySQL 要把每行的 phone 转成数字来比较
-- → 索引失效 → 全表扫描！
✅ 常见场景和规律
| 字段类型 | 传入值 | 走索引？ |
|---|
| VARCHAR | '13800138000'（字符串） | ✅ 走 |
| VARCHAR | 13800138000（数字） | ❌ 不走 |
| INT | 123（数字） | ✅ 走 |
| INT | '123'（字符串） | ✅ 走（MySQL自动转字符串为数字） |
规律：字符串字段传数字 → 索引失效（要转每一行）
数字字段传字符串 → 索引OK（只转传入值一次）
生产必知 UPDATE 常见坑 — 别把线上锁死了
坑1：WHERE 不走索引 → 锁全表
-- amount 没有索引
UPDATE order_info SET status = 'cancelled'
WHERE amount < 10;
-- InnoDB 行锁靠索引实现
-- 没索引 → 退化为表锁！
-- 其他事务全部被阻塞！😱
原则：UPDATE 的 WHERE 条件必须有索引！
坑2：大事务 UPDATE 锁太久
BEGIN;
-- 更新100万行
UPDATE order_info SET status = 'expired'
WHERE create_time < '2025-01-01';
-- 还没 COMMIT，锁还没释放
-- 其他事务等着...等着...超时了！
COMMIT; -- 5分钟后才提交
原则：大批量 UPDATE 分批执行，每批1000条就 COMMIT
坑3：忘了写 WHERE
-- 😱 这会更新全表！
UPDATE order_info SET status = 'cancelled';
-- 永远先 SELECT 确认范围
SELECT COUNT(*) FROM order_info
WHERE create_time < '2025-01-01';
-- 确认数量合理后再 UPDATE
-- 或者用事务包裹，先看再提交
BEGIN;
UPDATE ... WHERE ...;
-- SELECT 确认 → COMMIT / ROLLBACK
小技巧 LIMIT 1 和 EXISTS — 微优化也有用
判断是否存在，不要用 COUNT(*)
-- ❌ 扫描全部符合条件的行
SELECT COUNT(*) FROM user WHERE phone = '13800138000';
-- 如果有100万条匹配，全扫完才知道 > 0
-- ✅ 找到第一条就返回
SELECT 1 FROM user WHERE phone = '13800138000' LIMIT 1;
-- 找到1条就结束，不继续扫描
-- ✅ 或者用 EXISTS（语义更清晰）
SELECT EXISTS(
SELECT 1 FROM user
WHERE phone = '13800138000'
);
-- 返回 1（存在）或 0（不存在）
WHERE 条件唯一时加 LIMIT 1
-- 如果能确定只返回一条（如按主键查）
SELECT * FROM user WHERE id = 123 LIMIT 1;
-- MySQL 找到后立即停止，不再继续扫
-- 按唯一索引查也一样
SELECT * FROM user WHERE phone = '13800138000' LIMIT 1;
-- phone 有唯一索引，只可能有一条
-- 加 LIMIT 1 让 MySQL 更早停止扫描
性能优化 INSERT 批量写入 — 别一条一条插
❌ 循环单条插入（Java中常见）
// Java 代码
for (Order order : orders) {
// 每次都：建立连接→解析SQL→写B+树→写redo log
mapper.insert(order);
}
// 1000条 → 1000次网络IO + 1000次提交
✅ 批量插入
-- 一条SQL插入多行
INSERT INTO order_info
(user_id, status, amount)
VALUES
(101, 'paid', 15.0),
(102, 'pending', 28.0),
(103, 'paid', 9.9);
-- 1000条 → 1次网络IO + 1次提交
-- 快 10~50 倍！
-- MyBatis 批量写法：
// mapper.xml
INSERT INTO order_info
(user_id, status, amount)
VALUES
(#{o.userId}, #{o.status}, #{o.amount})
原理：每次 INSERT 都要：网络往返 + 解析SQL + 写 B+ 树索引 + 写 redo log + 提交事务。
批量 INSERT 只需要做一次，省掉 N-1 次开销。
注意：一次批量不要超过 500~1000 条，否则 SQL 太长可能报错或撑爆内存。
📋 全文总结：MySQL 原理一张图
MySQL 核心原理关系图
存储引擎
InnoDB
→
数据组织
B+树索引
→
查询优化
回表/覆盖索引
索引下推
事务
ACID
→
隔离级别
MVCC
→
锁机制
行锁/间隙锁
Buffer Pool
内存管理
→
redo log
持久性保证
+
undo log
原子性保证
🔑 核心知识点速记
| 原理 | 一句话理解 | 工作中的应用 |
|---|
| InnoDB | 数据按主键B+树存储，支持事务行锁 | 99%场景用InnoDB |
| B+树 | 3-4层就能存千万行，范围查询靠叶子链表 | 理解索引为什么快 |
| 回表 | 二级索引只存索引列+主键，查其他字段要回聚簇索引 | SELECT * 导致大量回表 |
| 覆盖索引 | 查询的所有字段都在索引里，不用回表 | EXPLAIN Extra=Using index |
| 子查询 | 优先改写为JOIN，让优化器灵活选择 | 慢查询优化常见手法 |
| Filesort | ORDER BY没走索引就要额外排序 | 监控Sort_merge_passes |
| MVCC | 通过版本链实现读写不冲突 | 理解RR级别的可重复读 |
| 锁 | 行锁靠索引实现，没索引变表锁 | UPDATE必须走索引 |
| Buffer Pool | 内存缓存池，命中率应>95% | 调innodb_buffer_pool_size |
| redo/undo log | redo保持久性，undo保原子性 | 理解事务的底层保障 |
安全必知 MyBatis #{} vs ${} — SQL 注入就差这一个符号
✅ #{} — 预编译，安全
// Mapper
@Select("SELECT * FROM user WHERE id = #{id}")
User selectById(@Param("id") Long id);
// MyBatis 底层会用 PreparedStatement：
// SELECT * FROM user WHERE id = ?
// 参数通过 setLong() 设置，不拼接进 SQL
// → SQL注入不可能！
❌ ${} — 字符串拼接，危险！
// ❌ 危险写法
@Select("SELECT * FROM user WHERE name = '${name}'")
User selectByName(@Param("name") String name);
// 如果有人传入 name = "'; DROP TABLE user; --"
// 实际执行的SQL变成：
// SELECT * FROM user WHERE name = '';
// DROP TABLE user; --'
// 表被删了！！！😱
// ${} 唯一合理用途：动态表名/列名
@Select("SELECT * FROM ${tableName} LIMIT 10")
List
铁律：所有用户输入的值一律用 #{}。只有动态表名/列名/ORDER BY 列名才能用 ${}，且必须做白名单校验！
配置必知 HikariCP 连接池 — Java 连 MySQL 的"大门"
Java 程序不直接连 MySQL，而是通过连接池管理连接。
连接池就像一个"接线员"：预先建好若干连接放在池子里，谁要用就取一个，用完还回去。
Spring Boot 默认用 HikariCP（目前最快的连接池）。
# application.yml — HikariCP 常用配置
spring:
datasource:
url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4
username: root
password: your_password
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
# 连接池大小（推荐 = CPU核数 * 2 + 磁盘数）
maximum-pool-size: 10
# 最小空闲连接
minimum-idle: 5
# 连接超时时间（毫秒）
connection-timeout: 30000
# 空闲连接最大存活时间（毫秒）
idle-timeout: 600000
# 连接最大存活时间（毫秒），建议 < MySQL的wait_timeout
max-lifetime: 1800000
# 连接测试语句
connection-test-query: SELECT 1
| 配置项 | 推荐值 | 为什么 |
|---|
| maximum-pool-size | 10~20 | 不是越大越好！太多连接会让MySQL压力更大，反而变慢 |
| max-lifetime | 1800000（30分钟） | 必须 < MySQL 的 wait_timeout（默认8小时），否则MySQL端已断开但Java还以为连着 |
| connection-timeout | 30000（30秒） | 获取连接的超时时间。如果经常超时 → 连接池太小或SQL太慢 |
开发必知 MyBatis 常用写法速查
// ========= Mapper 接口常用写法 =========
// 1. 查询单条
@Select("SELECT * FROM order_info WHERE id = #{id}")
Order selectById(@Param("id") Long id);
// 2. 查询列表
@Select("SELECT * FROM order_info WHERE user_id = #{userId} "
+ "ORDER BY create_time DESC LIMIT #{limit}")
List listByUserId(
@Param("userId") Long userId,
@Param("limit") int limit);
// 3. 插入（返回自增主键）
@Insert("INSERT INTO order_info(user_id, status, amount) "
+ "VALUES(#{userId}, #{status}, #{amount})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(Order order);
// 插入后 order.getId() 就能拿到自增ID
// 4. 更新
@Update("UPDATE order_info SET status = #{status} "
+ "WHERE id = #{id} AND version = #{version}")
int updateStatus(
@Param("id") Long id,
@Param("status") String status,
@Param("version") int version);
// 乐观锁：返回 0 表示 version 不匹配
// 5. 批量插入（需要 XML）
// mapper.xml:
// <insert id="batchInsert">
// INSERT INTO order_info(user_id, status, amount)
// VALUES
// <foreach collection="list" item="o" separator=",">
// (#{o.userId}, #{o.status}, #{o.amount})
// </foreach>
// </insert>
// 6. 动态 SQL（需要 XML）
// <select id="search" resultType="OrderVO">
// SELECT * FROM order_info
// <where>
// <if test="userId != null">
// AND user_id = #{userId}
// </if>
// <if test="status != null">
// AND status = #{status}
// </if>
// </where>
// ORDER BY create_time DESC
// </select>
🗺️ 索引决策全景图 — 一张图搞懂"什么时候建什么索引"
🤔 问题：SQL 里这么多子句，索引到底是为谁建的？
你写一条 SQL：SELECT user_id, status FROM order_info WHERE user_id = 101 ORDER BY create_time DESC LIMIT 10;
这条 SQL 里出现了 SELECT、WHERE、ORDER BY、LIMIT —— 索引跟它们都有关系！
很多人只会想"WHERE 后面的字段要建索引"，但这远远不够。
下面这张图，把所有需要索引的场景统一梳理，让你一次性建立系统认知。
📊 SQL 各子句 & 索引关系全景图
一条 SQL 的完整生命周期 & 索引介入点
📝 先看一条完整 SQL：
SELECT
user_id, status, amount
FROM order_info
WHERE
user_id = 101 AND status = 'paid'
ORDER BY
create_time DESC
LIMIT 10
每种颜色的部分，都跟索引有关系！下面逐个说明 ↓
WHERE
过滤条件 — 索引最核心的作用
最重要
作用：WHERE 决定了 MySQL 要从表里找哪些行。
如果 WHERE 的字段没有索引 → 全表扫描（type=ALL）→ 100万行逐行检查。
有索引 → B+ 树二分查找 → 只扫描几行。
-- WHERE 条件决定用索引的"查找"能力
-- 这是最常见需要建索引的场景
SELECT * FROM order_info
WHERE user_id = 101;
-- 索引：idx_user_id(user_id)
-- EXPLAIN: type=ref ✅
-- 联合 WHERE 条件 → 联合索引
WHERE user_id = 101 AND status = 'paid'
-- 索引：idx_user_status(user_id, status)
-- EXPLAIN: type=ref, 用了联合索引 ✅
记忆点：WHERE 字段 → 索引用于"找到那几行"，是联合索引中排在最前面的列。
ORDER BY
排序 — 索引的第二个功能
经常被忽略
问题：没索引时，MySQL 找到数据后还要在内存里排一遍（filesort）。
100万行排序 → CPU飙升 → 慢查询。
关键：B+ 树的叶子节点本身就是有序的！
如果 ORDER BY 的字段在索引里 → 直接按索引顺序读取 → 天然有序 → 不需要额外排序。
-- 场景1：ORDER BY 字段有索引
SELECT * FROM order_info ORDER BY create_time DESC;
-- 索引：idx_create_time(create_time)
-- Extra: 没有 Using filesort ✅
-- 场景2：没索引
SELECT * FROM order_info ORDER BY create_time DESC;
-- Extra: Using filesort ❌ 要额外排序！
-- 场景3（最优）：WHERE + ORDER BY 联合索引
SELECT * FROM order_info
WHERE user_id = 101
ORDER BY create_time DESC;
-- 索引：idx_user_time(user_id, create_time)
-- WHERE 用 user_id 找到范围
-- 叶子节点已按 create_time 排好
-- Extra: 没有 filesort ✅✅ 一箭双雕！
记忆点：ORDER BY 字段放在联合索引的最后面，利用 B+ 树叶子节点天然有序的特性，避免 filesort。
SELECT
查询的列 — 决定要不要回表
决定回表
问题：索引的叶子节点只存了索引列的值 + 主键。如果你 SELECT 的字段不在索引里 → 必须回表。
覆盖索引：SELECT 的字段恰好都在索引里 → 不用回表 → Extra: Using index。
这就是为什么 SELECT * 通常是坏习惯：你很少建一个包含所有字段的索引。
-- 索引：idx_user_status(user_id, status)
-- 叶子节点存了：user_id, status, id
-- ❌ 需要回表：amount 不在索引里
SELECT user_id, status, amount
FROM order_info WHERE user_id = 101;
-- Extra: NULL（回表了）
-- ✅ 覆盖索引：只要 user_id, status, id
SELECT user_id, status, id
FROM order_info WHERE user_id = 101;
-- Extra: Using index（不回表！）
-- 💡 实战技巧：把常查的字段加入联合索引末尾
CREATE INDEX idx_user_status_time
ON order_info(user_id, status, create_time);
-- 现在 SELECT user_id, status, create_time
-- 也能覆盖索引了！
记忆点：SELECT 的字段决定了能不能用覆盖索引。联合索引可以把常查的字段加到末尾，让更多查询实现覆盖索引。
GROUP BY
分组 — 和 ORDER BY 类似
原理：GROUP BY 需要先排序再分组。如果分组字段没有索引 → MySQL 建临时表 + filesort。
Extra 出现 Using temporary; Using filesort → 最差组合。
和 ORDER BY 的关系：如果联合索引是 (a, b)，
WHERE a = ? ORDER BY b ✅ 叶子天然按 b 排好
WHERE a = ? GROUP BY b ✅ 叶子天然按 b 排好，分组也快
-- 没索引时 GROUP BY
SELECT status, COUNT(*) FROM order_info
GROUP BY status;
-- Extra: Using temporary; Using filesort ❌❌
-- 加索引
CREATE INDEX idx_status ON order_info(status);
-- Extra: 只有 Using index ✅
-- 更优：联合索引同时满足 WHERE + GROUP BY
SELECT status, COUNT(*) FROM order_info
WHERE user_id = 101 GROUP BY status;
-- 索引：idx_user_status(user_id, status)
-- WHERE 用 user_id 查找
-- 叶子按 status 有序 → GROUP BY 不用额外排序 ✅
记忆点：GROUP BY 和 ORDER BY 共享索引的有序性。放在联合索引的 WHERE 之后、合理位置即可。
JOIN
关联 — ON 字段必须建索引
原理：JOIN 时 MySQL 用 Nested Loop（嵌套循环）：
驱动表每取一行 → 去被驱动表找匹配行。
如果被驱动表的 ON 字段没索引 → 每行都全表扫描 → M×N 次！
规则：被驱动表的 JOIN 字段必须有索引。
SELECT o.*, u.name
FROM order_info o
JOIN user u ON o.user_id = u.id
WHERE u.status = 'active';
-- 被驱动表是 user
-- ON 条件：o.user_id = u.id
-- 需要索引：user.id（主键，已有 ✅）
-- 反过来也适用
SELECT o.*, u.name
FROM order_info o
JOIN user u ON o.user_id = u.id
WHERE o.status = 'paid';
-- 被驱动表可能是 order_info
-- 需要：order_info.user_id 有索引
-- CREATE INDEX idx_order_user ON order_info(user_id);
记忆点：JOIN 的 ON 字段 = 索引必建项。特别是被驱动表的字段。Extra 出现 Using join buffer 就说明缺索引。
🔄 索引设计决策流程（实战版）
拿到一条慢 SQL → 按这个顺序思考 ↓
1
WHERE 字段 → 联合索引的前面几列
最重要！决定了能不能快速找到数据。
等值查询的字段放前面，范围查询（>, <, BETWEEN）放后面。
2
ORDER BY / GROUP BY → 加在 WHERE 列后面
利用 B+ 树叶子节点天然有序，避免 filesort。
WHERE user_id = ? ORDER BY create_time → idx(user_id, create_time)
3
SELECT 的列 → 加在最后（可选，看是否要覆盖索引）
如果常查的字段都在索引里 → 覆盖索引（Using index）→ 不回表。
idx(user_id, status, create_time) → SELECT user_id, status, create_time 不回表
4
JOIN ON 字段 → 被驱动表必须建索引
否则 Nested Loop 每行都全表扫描。Extra 出现 Using join buffer 就该加索引了。
口诀：WHERE定范围 → ORDER排好序 → SELECT省回表 → JOIN要有路
🧪 6 个实战案例：从 SQL 到索引设计
用 order_info 表（id, user_id, status, amount, product_id, create_time）演示：
案例1：只查某个用户的订单
SELECT * FROM order_info WHERE user_id = 101;
分析：WHERE user_id → 需要在 user_id 上建索引
索引：CREATE INDEX idx_user_id ON order_info(user_id);
效果：type=ref，不用全表扫描
案例2：查用户订单并按时间排序
SELECT * FROM order_info
WHERE user_id = 101
ORDER BY create_time DESC
LIMIT 10;
分析：WHERE user_id（过滤）+ ORDER BY create_time（排序）→ 联合索引
索引：CREATE INDEX idx_user_time ON order_info(user_id, create_time);
效果：WHERE 用 user_id 找范围，叶子按 create_time 有序，不用 filesort ✅
⚠️ 如果只建 idx_user_id → ORDER BY create_time 还是要 filesort！
案例3：覆盖索引 — 不回表
SELECT user_id, status FROM order_info
WHERE user_id = 101 AND status = 'paid';
分析：WHERE user_id + status（过滤），SELECT user_id + status（只查这两列）
索引：CREATE INDEX idx_user_status ON order_info(user_id, status);
效果：叶子节点存了 user_id + status + id → SELECT 的字段全有 → 覆盖索引，不回表 ✅
Extra: Using index 🎉
案例4：用户订单分组统计
SELECT status, COUNT(*) AS cnt
FROM order_info
WHERE user_id = 101
GROUP BY status;
分析：WHERE user_id（过滤）+ GROUP BY status（分组）
索引：CREATE INDEX idx_user_status ON order_info(user_id, status);
效果：WHERE 用 user_id 找范围，叶子按 status 有序 → 分组不用建临时表 ✅
如果没有这个联合索引 → Extra: Using temporary; Using filesort ❌❌
案例5：三合一 — 过滤+排序+覆盖
SELECT user_id, status, create_time
FROM order_info
WHERE user_id = 101 AND status = 'paid'
ORDER BY create_time DESC
LIMIT 10;
分析：WHERE user_id + status（过滤）+ ORDER BY create_time（排序）+ SELECT 三个字段
索引：CREATE INDEX idx_user_status_time ON order_info(user_id, status, create_time);
效果：
WHERE user_id = 101 → 用第1列找到范围 ✅
AND status = 'paid' → 用第2列继续过滤 ✅
ORDER BY create_time → 叶子按 create_time 有序，不用 filesort ✅
SELECT 三个字段都在索引里 → 覆盖索引，不回表 ✅
一个索引满足四个需求！这就是联合索引的威力。
案例6：JOIN 关联查询
SELECT o.id, o.amount, u.name, u.phone
FROM order_info o
JOIN user u ON o.user_id = u.id
WHERE u.status = 'active'
ORDER BY o.create_time DESC
LIMIT 20;
分析：WHERE u.status（user表过滤）+ JOIN ON o.user_id（关联）+ ORDER BY o.create_time
索引：
user表：CREATE INDEX idx_user_status ON user(status);（WHERE 过滤）
order_info表：CREATE INDEX idx_order_user_time ON order_info(user_id, create_time);（JOIN + 排序）
效果：user 表走 status 索引找到 active 用户 → 拿 id 去 order_info 走联合索引 → JOIN + 排序都有索引 ✅
📋 速查表：SQL 子句 × 索引作用
| SQL 子句 | 索引的作用 | 在联合索引中的位置 | 没有索引的后果 |
|---|
| WHERE |
快速找到目标行 |
最前面（第1~N列） |
type=ALL 全表扫描 |
| ORDER BY |
利用 B+ 树有序性，避免额外排序 |
WHERE 列之后 |
Using filesort 额外排序 |
| GROUP BY |
利用 B+ 树有序性，避免临时表 |
WHERE 列之后 |
Using temporary + filesort |
| SELECT |
字段都在索引里 → 覆盖索引不回表 |
可以加到末尾 |
需要回表取完整行 |
| JOIN ON |
被驱动表快速匹配关联行 |
被驱动表独立索引 |
Using join buffer，M×N 扫描 |
一句话总结：
WHERE 定范围 +
ORDER/GROUP 保有序 +
SELECT 省回表 +
JOIN 有索引
= 一条联合索引覆盖全流程
📐 索引设计规则 — 什么时候该建索引？
✅ 该建索引的场景
1. WHERE 高频过滤字段
SELECT * FROM order_info WHERE user_id = 123
→ user_id 是高频查询条件，必须建索引
CREATE INDEX idx_user_id ON order_info(user_id);
2. JOIN 关联字段
SELECT * FROM order o JOIN user u ON o.user_id = u.id
→ JOIN 的 ON 字段必须有索引，否则 Nested Loop 极慢
CREATE INDEX idx_order_user_id ON order_info(user_id);
3. ORDER BY / GROUP BY 字段
SELECT * FROM order_info ORDER BY create_time DESC
→ 不建索引就要 filesort（额外排序），数据量大时很慢
CREATE INDEX idx_create_time ON order_info(create_time);
4. 高选择性字段（区分度高）
选择性 = DISTINCT(col) / COUNT(*)
→ 选择性越接近 1 越好。手机号、订单号很好；性别只有2种，很差
-- 查看选择性
SELECT COUNT(DISTINCT user_id) / COUNT(*) AS selectivity
FROM order_info;
-- 结果: 0.85 → 很好，值得建索引
❌ 不该建（或谨慎建）索引的场景
1. 数据量小的表（< 1000行）
小表全表扫描可能比走索引还快
→ 优化器可能自动忽略你的索引，建了也白建
2. 频繁更新的字段
每次 UPDATE 都要维护 B+ 树
→ 索引越多，INSERT/UPDATE/DELETE 越慢
3. 低选择性字段（性别、状态只有2-3种值）
WHERE gender = 'M' → 过滤不掉多少数据
→ 但可以放在联合索引的后面列，配合其他字段一起用
4. 很少出现在 WHERE 中的字段
用不到的索引 = 白占磁盘 + 拖慢写入
→ 用以下SQL找出从未使用过的索引
-- 查看索引使用统计
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';
🔧 联合索引设计原则
口诀：最左前缀 → 把"最常查询 + 最高选择性"的列放最左边
假设你的查询有这几种模式：
① WHERE user_id = ? AND status = ?
② WHERE user_id = ?
③ WHERE user_id = ? AND status = ? ORDER BY create_time
→ 一个联合索引就能全部覆盖：
CREATE INDEX idx_user_status_time ON order_info(user_id, status, create_time);
| 查询模式 | 能不能用 idx(user_id, status, create_time) | 为什么 |
|---|
| WHERE user_id = ? | ✅ 能用第1列 | 最左前缀匹配 |
| WHERE user_id = ? AND status = ? | ✅ 能用前2列 | 连续匹配 |
| WHERE user_id = ? AND status = ? ORDER BY create_time | ✅ 全部用上，还避免filesort | ORDER BY也走索引 |
| WHERE status = ? | ❌ 用不上 | 跳过了最左列 user_id |
| WHERE create_time = ? | ❌ 用不上 | 跳过了前两列 |
| WHERE user_id = ? AND create_time = ? | ⚠️ 只用第1列 | 中间跳了 status，create_time 不能走索引 |
📊 索引维护成本可视化
索引不是免费的！每次写入都要更新所有相关索引的 B+ 树。
0个索引
→
INSERT 只写一次数据
3个索引
→
INSERT 写1次数据 + 更新3棵 B+ 树
10个索引
→
INSERT 写1次数据 + 更新10棵 B+ 树 😱
经验：单表索引一般不超过 5-6 个，超过就要考虑合并或删除
实战建议：先用 EXPLAIN 确认慢查询缺什么索引 → 按需建联合索引（覆盖多个查询）→ 定期用 sys.schema_unused_indexes 清理无用索引 → 监控写入性能
🎉 恭喜你读完了 MySQL 原理全景！
记住：原理不是用来背的，是用来理解"为什么这条SQL慢"和"怎么优化"的。
遇到慢查询 → EXPLAIN → 看 type/key/Extra → 对照这篇原理 → 找到优化方向！
❓ 面试高频FAQ
为什么在索引列上使用函数会导致索引失效？
B+树索引存的是列的原始值。当你写 WHERE DATE(create_time) = '2024-06-14'，MySQL需要先对每一行执行DATE()函数再比较——B+树里存的是完整的datetime值，无法直接用树查找定位。正确写法：WHERE create_time >= '2024-06-14' AND create_time < '2024-06-15'，这样MySQL可以直接在B+树上范围查找。
回表一定会导致性能问题吗？
不一定。回表的代价取决于扫描多少行。如果通过索引筛选后只剩10行，回表10次完全没问题。但如果筛选后还有10万行，回表10万次就很慢。解决方案是覆盖索引——把查询需要的所有列都放进联合索引，这样在索引上就能拿到所有数据，不需要回表。
什么情况下SELECT * 反而比SELECT字段快？
几乎不会。SELECT * 通常更慢，因为：①传输更多数据 ②无法使用覆盖索引优化 ③如果表有很多大字段(TEXT/BLOB)，每行数据量巨大。唯一例外：查询本身就需要所有字段（比如导出全表数据），这时候SELECT * 和SELECT所有字段没区别。
为什么建议用自增主键而不是UUID？
InnoDB的聚簇索引按主键顺序存储数据。自增主键：新数据永远追加到最后，页的填充率高，插入快。UUID：随机值导致新数据可能插入到中间位置，触发页分裂（page split），大量数据搬移，写入性能下降50%+，且产生碎片。
Buffer Pool设置多大合适？
经验法则：专用MySQL服务器设置为物理内存的60-80%。比如32GB内存的服务器，innodb_buffer_pool_size = 20G。注意要预留内存给操作系统、连接数、排序缓存等。可以通过 SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests' vs Innodb_buffer_pool_reads 计算命中率（应>99%）。
怎么判断一个索引该不该建？
①看WHERE条件：频繁查询的列考虑建索引 ②看JOIN条件：连接字段建索引 ③看ORDER BY/GROUP BY：排序分组字段建索引 ④区分度：SELECT COUNT(DISTINCT col)/COUNT(*) FROM table，低于0.1的列建索引意义不大 ⑤用 sys.schema_unused_indexes 定期清理无用索引
🎯 全文一句话总结
存储引擎管怎么存 → B+树管怎么快速找 → 回表/覆盖索引管找几次 → 事务/锁管并发安全 → Buffer Pool管减少磁盘IO。慢查询排查链路：EXPLAIN → 看 type(访问类型) + key(用了哪个索引) + Extra(额外信息) → 对照原理找优化方向。