📖 相关阅读 — MySQL 专题系列

本文以15个MySQL问题驱动（慢查询→死锁→主从延迟等），问题→现象→原理→修复。与以下文件有话题重叠但角度不同：

🔧 MySQL 问题实战手册 — 遇到问题再学原理

15个真实问题 × 从现象到原因到修复 × 每个问题讲透背后的原理

慢查询索引失效死锁锁等待 数据不一致连接超时主从延迟OOM 大表DDL分页慢COUNT慢乱码
学习路径1. 理解概念类比+图解2. 看代码IDE窗口+实战3. 动手踩坑+FAQ
三步学习法：概念→代码→实战
P1 接口慢 P2 索引失效 P3 死锁 P4 不可重复读与幻读 P5 数据消失 P6 导出超时 P7 连接满 P8 大表加字段 P9 深翻页 P10 COUNT慢 P11 主从延迟 P12 隐式转换 P13 乱码 P14 事务超时 P15 磁盘爆满
1
接口突然变慢了，3秒才能返回
📅 周一早高峰，用户反馈"我的借书单"页面要3-5秒才能打开
⚠️ 线上问题 📚 索引原理 🔧 EXPLAIN

🔍 怎么发现的？

🍵 技术技术群（5人）
PM
产品经理-小美
@后端 用户反馈借书单列表打开巨慢，能看看吗？好几个客户投诉了
后
后端-小张
收到，我先看看接口响应时间
# 先看接口耗时 — 用 Arthas trace 追踪
$ trace com.milktea.service.OrderService getMyOrders
+---[2987ms] com.mysql.cj.jdbc.ClientPreparedStatement:executeQuery()
+---[2ms] java.util.ArrayList:<init>()
# 🔥 2987ms 全花在 SQL 查询上了！

🔎 定位SQL

# 查看最近的慢查询日志
mysql> SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 3\G
Query_time: 3.214507 seconds
Rows_examined: 895432
SQL: SELECT * FROM order_info WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20
这条SQL扫描了 89 万行！status 上没有索引，ORDER BY created_at 也没走索引。

📊 EXPLAIN 分析

问题SQL的执行计划
EXPLAIN SELECT * FROM order_info WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;
typekeyrowsExtra
ALL NULL 895432 Using where; Using filesort
🔥 type=ALL 全表扫描 + Using filesort 额外排序 = 89万行全扫一遍再排一遍！

💡 背后原理：为什么这么慢？

📚 原理：全表扫描 + Filesort
1) 为什么走了全表扫描？
WHERE status = 'paid'，但 status 列上没有索引。InnoDB 的 B+树索引是按列的值排好序的。没有索引，MySQL 不知道 'paid' 的数据在哪，只能从第1行扫到最后一行，逐行判断 status 是不是 'paid'。89万行全扫一遍。

2) 为什么还有 Using filesort？
ORDER BY created_at DESC。数据在磁盘上是按主键 id 排列的（聚簇索引），不是按 created_at 排的。MySQL 要先把扫出来的数据全放内存里（sort_buffer），再排序。89万行 sort_buffer 放不下 → 溢出到磁盘临时文件 → 磁盘IO爆炸。

3) 执行流程：全表扫描89万行 → 过滤出 status='paid' 的行 → 把这些行写入 sort_buffer → sort_buffer 放不下分批排 → 写磁盘临时文件 → 归并排序 → 取前20条 → 回表取完整数据（如果 SELECT * 的话）

✅ 修复方案

加联合索引
-- 创建联合索引：status 用于过滤，created_at 用于排序
ALTER TABLE order_info ADD INDEX idx_status_created (status, created_at);
修复后的 EXPLAIN
EXPLAIN SELECT * FROM order_info WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;
typekeyrowsExtra
ref idx_status_created 18234 Backward index scan
✅ type=ref 精确匹配索引，Using filesort 消失了！89万行 → 1.8万行，排序由索引天然完成
✅ 修复结果
$ trace com.milktea.service.OrderService getMyOrders
+---[23ms] com.mysql.cj.jdbc.ClientPreparedStatement:executeQuery()
3214ms → 23ms，提升 140 倍。索引让 MySQL 直接定位到 status='paid' 的数据（不用全扫），而且联合索引里 created_at 已经排好了（不用额外排序）。
2
明明建了索引，查询还是全表扫描
📅 新功能上线后，运营后台一个查询页面总是超时
⚠️ 线上问题 📚 索引失效原理 🔧 SQL改写

🔍 问题SQL

运营后台的查询
-- 查最近7天金额大于20的借书单
SELECT * FROM order_info
WHERE DATE(created_at) >= '2025-06-04'
AND amount + 0 > 20
ORDER BY created_at DESC;
typekeyrowsExtra
ALL NULL 895432 Using where; Using filesort
🤔 created_at 和 amount 上都有索引啊，为什么还是 ALL？？

💡 背后原理：索引为什么"失效"了？

📚 原理：对索引列做运算 → B+树找不到
B+树索引的查找原理：索引是一棵按列值排好序的树。查找时，MySQL 从根节点开始，用你要查的值和节点里的 key 比较，决定往左还是往右走。

但是！如果你对列做了运算（函数、加减乘除、类型转换），MySQL 就没法直接用索引里的值来比较了：

❌ DATE(created_at) >= '2025-06-04'
MySQL 要对每一行执行 DATE() 函数，才能判断结果。B+树里存的是原始的 datetime 值，没法用。

❌ amount + 0 > 20
虽然 +0 看起来没变，但 MySQL 要在每一行上执行加法运算。B+树里存的是 amount 的原始值，不是 amount+0 的结果。

类比：你的电话簿按姓名拼音排好了（索引），但你要找"名字第一个字是'小'的人"。你不能直接翻到"小"字开头的页，因为你要逐个检查每个人名字的第一字。这就是"索引失效"。

✅ 修复：改写SQL

不对索引列做运算
-- ✅ 改写：让条件是"列 = 常量"的形式
SELECT * FROM order_info
WHERE created_at >= '2025-06-04 00:00:00'
AND amount > 20
ORDER BY created_at DESC;
typekeyrowsExtra
range idx_created 45230 Using where
✅ type=range！created_at 索引走范围查询，89万行 → 4.5万行
💡 索引失效的7种常见写法（对照B+树原理理解）：
① WHERE YEAR(created_at) = 2025 → 函数破坏了B+树的有序性
② WHERE user_id + 0 = 101 → 对列做运算，B+树用不上
③ WHERE user_id = '101'（user_id是int）→ 隐式转换，字符串转数字要走每一行
④ WHERE name LIKE '%技术' → 左模糊，B+树不知道从哪个节点开始
⑤ WHERE user_id = 101 OR amount > 20 → OR 一边没索引
⑥ WHERE status = 'paid' → 联合索引(user_id,status)不满足最左前缀
⑦ WHERE CAST(id AS CHAR) = '5' → 类型转换破坏索引
3
线上报 Deadlock，两个接口互相卡死了
📅 高峰期收到告警：ERROR 1213 Deadlock found when trying to get lock
⚠️ 线上告警 📚 行锁 & 死锁原理 🔧 调整更新顺序

🔍 错误日志

2025-06-11 12:34:56 ERROR [OrderService] - Deadlock found when trying to get lock;
try restarting transaction
com.mysql.cj.jdbc.exceptions MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction

🔎 定位：两个接口的更新顺序不一致

问题复现 — 两个事务并发执行
-- 事务A（取消借书单）：先锁 order_info，再锁 product
BEGIN;
UPDATE order_info SET status='cancelled' WHERE id=1;  -- 锁住 order_info id=1
UPDATE product SET stock = stock + 1 WHERE id=5;  -- 等待 product id=5 的锁...
COMMIT;
事务B（下单）：先锁 product，再锁 order_info
-- 事务B（下单）：先锁 product，再锁 order_info
BEGIN;
UPDATE product SET stock = stock - 1 WHERE id=5;  -- 锁住 product id=5
UPDATE order_info SET status='paid' WHERE id=1;  -- 等待 order_info id=1 的锁...
COMMIT;
SHOW ENGINE INNODB STATUS — 死锁详情
SHOW ENGINE INNODB STATUS;
内容
LATEST DETECTED DEADLOCK
------------------------
Transaction 1 (ACTIVE 3 sec)
  mysql tables in use 1, locked 1
  LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
  HOLDS THE LOCK(S):
  RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table order_info
  record lock heap no 2 PHYSICAL RECORD • id=1
  WAITING FOR:
  RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table product
  record lock heap no 5 PHYSICAL RECORD • id=5

Transaction 2 (ACTIVE 2 sec)
  mysql tables in use 1, locked 1
  3 lock struct(s), heap size 1136, 2 row lock(s)
  HOLDS THE LOCK(S):
  RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table product
  record lock heap no 5 PHYSICAL RECORD • id=5
  WAITING FOR:
  RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table order_info
  record lock heap no 2 PHYSICAL RECORD • id=1

*** WE ROLL BACK TRANSACTION 1（回滚代价较小的事务）
🔥 死锁检测：事务A持有order_info.id=1等待product.id=5，事务B持有product.id=5等待order_info.id=1 → 环路等待！InnoDB回滚了事务A

💡 背后原理：死锁是怎么产生的？

📚 原理：行锁的交叉等待
InnoDB 的行锁：UPDATE/DELETE 执行时，InnoDB 会给匹配到的每一行加排他锁（X锁）。其他事务要修改同一行，必须等锁释放。

死锁条件：两个事务互相持有对方需要的锁，形成环路等待：

事务A
持有: order_info.id=1
等待: product.id=5 ⇄ 事务B
持有: product.id=5
等待: order_info.id=1
MySQL 怎么处理？：InnoDB 有死锁检测机制，发现环路后立刻回滚代价较小的事务（通常是修改行数少的那个），报错给应用。

类比：两辆车在窄路上面对面相遇。A车占着左边要往右走，B车占着右边要往左走。谁也过不去。只能让一辆车先退回去。
📊 InnoDB 锁层级体系
InnoDB 锁体系
┌───────────┘ └───────────┐
表级锁 行级锁
── 表级锁细分 ──
意向共享锁 (IS)
SELECT ... LOCK IN SHARE MODE 意向排他锁 (IX)
SELECT ... FOR UPDATE 元数据锁 (MDL)
自动加，防DDL冲突 自增锁 (AUTO-INC)
INSERT时保护自增列
── 行级锁细分 ──
Record Lock 记录锁
锁定单行记录 Gap Lock 间隙锁
锁定索引间隙(开区间) Next-Key Lock
Record + Gap (左开右闭) Insert Intention 插入意向锁
INSERT特殊间隙锁
💡 死锁本质：两个事务交叉持有不同行级锁（如 X锁 + Gap锁），形成环路等待。Next-Key Lock 是 RR 级别默认加锁方式，最容易引发死锁。

✅ 修复：统一更新顺序

✅ 所有接口都按相同顺序更新：先 product 再 order_info
-- 接口A修复后：也先改 product，再改 order_info
BEGIN;
UPDATE product SET stock = stock + 1 WHERE id=5;  -- 先锁 product
UPDATE order_info SET status='cancelled' WHERE id=1;  -- 再锁 order_info
COMMIT;
✅ 修复结果
统一更新顺序后，两个事务都是先锁 product 再锁 order_info，不会出现交叉等待。上线后死锁消失。

预防死锁的三条规则：
① 按固定顺序访问表和行（如按 id 升序）
② 保持事务短小，减少持锁时间
③ 合理使用索引，避免没走索引导致行锁升级为表锁
4
不可重复读 vs 幻读 — 你分得清吗？
📅 财务说"上午查了昨天营收是5200，下午再查变成5800了？" — 这到底是不可重复读还是幻读？
📚 事务隔离 & MVCC 🔧 隔离级别

🔍 Part A：不可重复读 — 同一事务两次读结果不同

不可重复读关注的是数据内容的变化：同一事务内两次读取同一行，结果不一样。RR级别通过 MVCC 快照读解决。
问题复现 — 查看当前隔离级别
-- 先确认当前事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
Variable_nameValue
transaction_isolation REPEATABLE-READ
MySQL 默认隔离级别是 RR（REPEATABLE-READ），会使用 MVCC 快照读
问题复现 — RR级别下的可重复读（快照读）
-- ===== 事务A：上午10:00 开启长事务 =====
BEGIN;
SELECT SUM(amount) FROM order_info WHERE created_at >= '2025-06-10';
SUM(amount)
5200.00
事务A的 ReadView 在此刻创建，记住这个快照
事务B：插入新借书单并提交
-- ===== 事务B：上午10:05 插入新借书单 =====
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(106, 5, 35.00, 'paid', '2025-06-10 15:00:00');
COMMIT;
事务B已提交，新行已持久化到磁盘
回到事务A：再查一次（RR级别，快照读）
-- ===== 事务A：上午10:10 再查一次 =====
SELECT SUM(amount) FROM order_info WHERE created_at >= '2025-06-10';
SUM(amount)
5200.00
🔥 还是5200！RR级别 + MVCC，事务A只看自己的 ReadView 快照，新插入的行对A不可见 — 这是解决"不可重复读"
对比：RC级别下的不可重复读
-- ===== 如果用 RC（READ COMMITTED）级别 =====
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT SUM(amount) FROM order_info WHERE created_at >= '2025-06-10';
SUM(amount)
5200.00
第一次查询结果
RC级别 — 其他事务提交后，再次查询结果变化
-- （此时事务B插入了新借书单并提交）
-- RC级别每次SELECT都生成新的ReadView
SELECT SUM(amount) FROM order_info WHERE created_at >= '2025-06-10';
SUM(amount)
5235.00
⚠️ 数据变了！这就是"不可重复读" — 同一事务内两次读结果不同。RC每次SELECT拍新快照，能看到最新已提交数据
📚 原理：MVCC 版本链 + ReadView 解决不可重复读
RR级别（可重复读）下，事务开始时拍一张"快照"（ReadView）。之后所有快照读（普通SELECT）都看这张快照里的数据版本。

每一行数据都有隐藏列：
• trx_id：最后修改这行的事务ID
• roll_pointer：指向上一个版本（undo log链）

读的时候，如果发现当前版本的 trx_id > 自己的 ReadView 能看到的范围，就顺着 roll_pointer 往前找，找到一个"自己能看到"的版本。

⚠️ 重要：MVCC 只解决了"不可重复读"问题（快照读层面），并没有解决"幻读"问题！
幻读针对的是当前读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE），需要 Next-Key Lock 来防止。
🔍 Part B：幻读 — 同一事务两次范围查询，结果集行数不同
幻读关注的是结果集行数的变化：同一事务内两次范围查询，中间有其他事务插入了新行，导致结果集多了"幻影行"。MVCC 的快照读看不到新行 ≠ 防止了幻读，幻读是针对当前读的问题。
幻读演示 — RC级别下，当前读出现幻读
-- ===== RC级别下，当前读的幻读问题 =====
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务A：第一次范围查询（当前读）
BEGIN;
SELECT * FROM order_info WHERE amount > 20 FOR UPDATE;
iduser_idamountstatus
310228.00 paid
5104 35.00 paid
7106 52.00 paid
3行 — RC级别下 FOR UPDATE 只加行锁，不加间隙锁
事务B：在间隙中插入一条新记录（RC级别下成功）
-- ===== 事务B：插入 amount=25 的新记录 =====
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(108, 3, 25.00, 'paid', '2025-06-10 16:00:00');
COMMIT;
✅ RC级别没有间隙锁，事务B的INSERT成功！新行已提交
事务A：第二次同样的范围查询 — 出现幻读！
-- ===== 事务A：再查一次同样的范围 =====
SELECT * FROM order_info WHERE amount > 20 FOR UPDATE;
iduser_idamountstatus
310228.00 paid
910825.00 paid
510435.00 paid
7106 52.00 paid
🔥 幻读！4行 — 多了一行 id=9, amount=25.00 的"幻影行"！同一事务内两次当前读，结果集行数变了
防幻读 — RR级别下，Next-Key Lock 阻止插入
-- ===== RR级别下，当前读自动加 Next-Key Lock =====
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务A：第一次范围查询（当前读）
BEGIN;
SELECT * FROM order_info WHERE amount > 20 FOR UPDATE;
iduser_idamountstatus
310228.00 paid
5104 35.00 paid
7106 52.00 paid
3行 — RR级别下 FOR UPDATE 加 Next-Key Lock = 行锁 + 间隙锁，锁住了 (20, +∞) 的间隙
事务B：尝试插入 — 被阻塞！
-- ===== 事务B：尝试插入 amount=25 的新记录 =====
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(108, 3, 25.00, 'paid', '2025-06-10 16:00:00');

-- ⏳ 阻塞中... 事务A的 Next-Key Lock 锁住了间隙，INSERT 无法获取插入意向锁
🚫 被阻塞！事务B的 INSERT 等待事务A释放间隙锁。直到事务A COMMIT 或 ROLLBACK，B才能继续
事务A提交后，事务A第二次查询不会出现幻读
-- ===== 事务A：提交事务 =====
COMMIT;

-- （事务B的INSERT此时才会执行成功）
-- 由于RR级别 + Next-Key Lock，事务A在其事务期间
-- 不会看到新插入的行，防止了幻读
✅ RR级别通过 Next-Key Lock（行锁 + 间隙锁）阻止其他事务在范围内插入新行，从根本上防止了幻读

💡 背后原理：不可重复读 vs 幻读 的本质区别

📚 原理：MVCC 解决不可重复读，Next-Key Lock 解决幻读
不可重复读：同一事务内两次读取同一行数据，内容不同。解决方式：MVCC 快照读。

幻读：同一事务内两次范围查询，结果集行数不同（多了"幻影行"）。解决方式：Next-Key Lock 当前读。

关键区别：
• 快照读（普通 SELECT）→ MVCC 通过 ReadView 保证可重复读，看不到其他事务新插入的行 → 这只是解决了"不可重复读"
• 当前读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE / UPDATE / DELETE）→ 读取最新已提交数据，MVCC 的快照机制不生效 → 必须靠 Next-Key Lock 锁住间隙来防止幻读

Next-Key Lock = Record Lock（行锁）+ Gap Lock（间隙锁）
• Record Lock：锁住索引上的已有行
• Gap Lock：锁住索引之间的间隙，阻止其他事务在间隙中插入新行
• 在 RR 级别下，当前读会自动使用 Next-Key Lock，从而防止幻读

常见误区：很多人以为"RR级别下快照读看不到新插入的行就是防止了幻读" — 这是错的！快照读看不到新行是解决"不可重复读"，幻读是当前读层面的问题。即使RR级别下，如果你用快照读查了一次，然后执行 UPDATE 语句（当前读）修改了那行，再快照读就能看到了 — 这才是真正可能发生幻读的场景。
📊 MVCC 版本链 + ReadView 可见性判断（解决不可重复读）
── 版本链（通过 roll_pointer 串联） ──
当前版本
trx_id = 108
amount = 35.00 ← roll_pointer ← 版本1
trx_id = 105
amount = 28.00 ← roll_pointer ← 版本2
trx_id = 100
amount = 52.00
── 事务A 的 ReadView（RR级别，事务开始时创建） ──
min_trx_id = 102
活跃事务最小ID max_trx_id = 110
下一个分配ID creator_trx_id = 102
当前事务ID
── 可见性判断规则 ──
trx_id=100 < min_trx_id=102
✅ 已提交，可见 | trx_id=108 在 [102,110) 范围内
且在活跃列表中
❌ 未提交，不可见
➡️ 结果：顺着版本链，跳过 trx_id=108（不可见）→ 跳过 trx_id=105（不可见）→ 找到 trx_id=100（✅可见）→ 返回该版本数据
📊 Next-Key Lock 如何防止幻读（RR级别当前读）
── 索引上的记录与间隙 ──
行记录
amount=20 间隙
(20,28) 行记录
amount=28 间隙
(28,35) 行记录
amount=35 间隙
(35,52) 行记录
amount=52 间隙
(52,+∞)
── 事务A执行 SELECT * FROM order_info WHERE amount > 20 FOR UPDATE 加锁范围 ──
amount=20
不满足条件 🔒 间隙锁
(20,28) 🔒 行锁
amount=28 🔒 间隙锁
(28,35) 🔒 行锁
amount=35 🔒 间隙锁
(35,52) 🔒 行锁
amount=52 🔒 间隙锁
(52,+∞)
🚫 事务B尝试 INSERT amount=25 → 落在间隙 (20,28) → 被间隙锁阻塞 → 无法插入 → 防止了幻读！
✅ RC级别下 FOR UPDATE 只加行锁（Record Lock），不加间隙锁 → 事务B可以在 (20,28) 间隙插入 → 出现幻读！
📊 不可重复读 vs 幻读 关键对比
对比维度 不可重复读 幻读
关注点 同一行数据内容变化 结果集行数变化（多了幻影行）
产生原因 其他事务 UPDATE/DELETE 了已有行 其他事务 INSERT 了新行
读类型 快照读（普通 SELECT） 当前读（SELECT FOR UPDATE 等）
RR级别解决方式 MVCC + ReadView（快照读看旧版本） Next-Key Lock（锁间隙阻止INSERT）
RC级别 ❌ 可能发生 ❌ 可能发生
RR级别 ✅ 防止 ✅ 防止*
* RR级别下快照读通过 MVCC 防止不可重复读；当前读通过 Next-Key Lock 防止幻读。但快照读后执行 UPDATE（当前读）仍可能触发特殊场景的幻读
📊 四种事务隔离级别对比
隔离级别 脏读 不可重复读 幻读 InnoDB 防止方式 适用场景
READ UNCOMMITTED
读未提交		 ❌ 可能 ❌ 可能 ❌ 可能 无任何防护 几乎不用
READ COMMITTED
读已提交 (RC)		 ✅ 防止 ❌ 可能 ❌ 可能 每次 SELECT 生成新 ReadView Oracle默认 / 需要实时数据
REPEATABLE READ
可重复读 (RR)		 ✅ 防止 ✅ 防止 ✅ 防止* 快照读：MVCC + ReadView 防不可重复读
当前读：Next-Key Lock 防幻读		 MySQL默认 / 大多数业务
SERIALIZABLE
串行化		 ✅ 防止 ✅ 防止 ✅ 防止 所有读加共享锁，完全串行 对一致性要求极高 / 性能差
* InnoDB 在 RR 级别：快照读通过 MVCC 防不可重复读；当前读通过 Next-Key Lock 防幻读。快照读后执行 UPDATE 再查可能触发特殊场景的幻读
5
数据被误删了！能不能恢复？
📅 新同事手抖执行了 DELETE 没加 WHERE，把整个表清空了
⚠️ 生产事故 📚 undo log & 事务
问题复现 — DELETE 没加 WHERE
-- 新同事在 DataGrip 里执行了这条...
DELETE FROM order_info;  -- 😱 没加 WHERE！全表删除！
结果
Query OK, 895432 rows affected (12.34 sec)
89万行全部删除！因为 autocommit=1，执行完就已经自动 COMMIT 了
尝试回滚 — 但已经晚了
-- 发现后紧急尝试回滚
ROLLBACK;
结果
Query OK, 0 rows affected
😭 没用！DataGrip 默认 autocommit=1，DELETE 已经自动提交了。ROLLBACK 回滚的是一个空的新事务
✅ 正确做法：先 BEGIN，确认后 COMMIT
-- 安全操作流程：先开启事务
BEGIN;

-- 执行删除（此时可以回滚）
DELETE FROM order_info WHERE status = 'cancelled' AND created_at < '2025-01-01';

-- 先 SELECT 确认影响范围
SELECT COUNT(*) FROM order_info WHERE status = 'cancelled' AND created_at < '2025-01-01';

-- 确认无误后提交
COMMIT;  -- 如果有问题，用 ROLLBACK; 回滚
✅ 事务里执行可以 ROLLBACK。或者开启 sql_safe_updates=1 防止无 WHERE 的 UPDATE/DELETE

💡 背后原理：为什么 ROLLBACK 没用？

📚 原理：autocommit 和 undo log
InnoDB 的 undo log：每次修改数据前，先把旧值记到 undo log 里。如果执行 ROLLBACK，就按 undo log 恢复。

但关键是：MySQL 默认 autocommit=1，每条 SQL 自成一个事务。执行完就自动 COMMIT 了。COMMIT 之后 undo log 就没用了（purge 线程会清理）。

所以：在 DataGrip 里执行 DELETE，默认自动提交了。你 ROLLBACK 回滚的是一个空的、新的事务，不是之前的 DELETE。

类比：你把文件扔进回收站（undo log还在），可以撤销。但你点了"清空回收站"（COMMIT），就真的没了。

✅ 能恢复吗？怎么预防？

如果还没提交（在BEGIN事务里）：直接 ROLLBACK，undo log 会恢复所有数据。✅

如果已经提交了：
① 从 binlog 恢复（如果开了 binlog）—— 最可靠
② 从备份恢复 + binlog 重做（推荐方案）
③ 使用第三方工具如 mysqlbinlog 解析 binlog

预防措施：
① DataGrip/Navicat 设置"安全模式"：UPDATE/DELETE 没有 WHERE 不让执行
② 生产环境用 sql_safe_updates=1
③ 重要操作先 BEGIN → 确认结果 → COMMIT
6
导出100万行数据，接口超时了
📅 运营要求导出全部借书单做月报，API 超时60秒
⚠️ 功能问题 📚 回表 & 临时表

🔍 问题代码

// Java 代码：一次查全部
List<Order> all = orderMapper.selectAll();  // 😱 100万行全加载到内存
ExcelUtil.export(all);  // 内存溢出
问题复现 — SELECT * 全表扫描
-- 实际执行的 SQL：100万行全加载
SELECT * FROM order_info ORDER BY created_at DESC;
iduser_idproduct_idamountstatuscreated_at
1 101 5 18.00 paid 2025-06-10 09:00:00
... 895432 rows returned • 执行时间: 58.2 sec • 内存溢出!
🔥 100万行 × 每行6个字段 = 全部加载到JVM堆内存 → OOM
EXPLAIN 分析 — 全表扫描 + filesort
EXPLAIN SELECT * FROM order_info ORDER BY created_at DESC;
typekeyrowsExtra
ALL NULL 895432 Using filesort
type=ALL 全表扫描89万行 + Using filesort 排序。100万行排序需要巨大的 sort_buffer，溢出到磁盘

💡 背后原理：为什么一次查100万行会崩？

📚 原理：回表 + 内存 + 网络传输
1) SELECT * 的回表问题：如果走了二级索引，每一条都要回聚簇索引取完整数据。100万行 = 100万次回表。

2) 内存问题：100万个 Java 对象，每个对象假设500字节 = 500MB 堆内存。很可能 OOM。

3) 网络问题：MySQL 把100万行从磁盘读到 Buffer Pool → 通过网络传给 Java 应用。网络带宽被打满。

类比：你要把技术10年的所有借书单都搬到新店。不能一次全搬（搬不动），应该分批搬。

✅ 修复：分批导出 + 流式写入

分批查询
-- 每次查5000条，基于游标（id）分页
-- 第1批
SELECT id, user_id, product_id, amount, status, created_at
FROM order_info WHERE id > 0 ORDER BY id LIMIT 5000;

-- 第2批（id > 上次最大的id）
SELECT id, user_id, product_id, amount, status, created_at
FROM order_info WHERE id > 5000 ORDER BY id LIMIT 5000;

-- ... 一直循环到没有数据
💡 游标分页（基于 id）比 OFFSET 分页快很多：不用跳过前N行，直接从上次的位置继续。SELECT 指定字段而不是 *，减少回表数据量。
7
应用报"连接池耗尽"，接口全部报错
📅 高峰期所有接口开始返回500错误，日志满是"Could not get JDBC Connection"
⚠️ 线上故障 📚 连接管理 & 长事务
com.alibaba.druid.pool.GetConnectionTimeoutException:
wait millis 60000, active 50, maxActive 50
# 🔥 50个连接全被占了，新请求等60秒还拿不到连接

🔍 排查：谁占着连接不放？

问题复现 — SHOW PROCESSLIST
-- 查看当前所有连接
SHOW PROCESSLIST;
IdUserHostdbCommandTimeStateInfo
12 app 10.0.1.5:52341 milk Sleep 2847 NULL
13 app 10.0.1.5:52342 milk Sleep 1923 NULL
14 app 10.0.1.5:52343 milk Query 892 Sending data SELECT * FROM order_info...
... (50 rows total, 几乎全部 Sleep 或慢查询)
🔥 Sleep 2847秒=47分钟！有连接占着47分钟没释放。慢查询跑了892秒。50个连接全部被占满
✅ 紧急修复 — KILL 长时间 Sleep 的连接
-- 紧急处理：杀掉长时间 Sleep 的连接释放资源
KILL CONNECTION 12;  -- 杀掉 Sleep 2847秒的连接
KILL CONNECTION 13;  -- 杀掉 Sleep 1923秒的连接

-- 查找所有 Sleep 超过 60 秒的连接
SELECT Id, User, Host, Time, Command
FROM information_schema.PROCESSLIST
WHERE Command = 'Sleep' AND Time > 60;
IdUserTimeCommand
15 app 1245 Sleep
18 app 456 Sleep
✅ KILL后连接释放，新请求可以获取连接了
查找长事务
-- 找出运行时间最长的事务
SELECT trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
  trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;
trx_idtrx_staterunning_secondstrx_query
38512 RUNNING 2847 NULL
这个事务跑了2847秒（47分钟）还在 RUNNING 状态！很可能就是忘了 COMMIT

💡 背后原理

📚 原理：MySQL连接 & 连接池
MySQL 的连接模型：每个连接对应一个线程。连接不是免费的——要分配内存、创建线程、鉴权。

连接池：应用启动时建好 N 个连接（如50个），请求来了从池里借，用完还回去。避免反复创建/销毁连接的开销。

连接池耗尽的根因：
① 长事务：一个方法里 @Transactional 包了太多逻辑（调远程服务、发邮件...），连接被占着几十秒
② 慢查询：SQL 太慢，连接被占着等结果
③ 忘记关闭：代码bug，拿到了连接但没有 finally 里 close

类比：技术有50个收银台。如果一个客人占着收银台打电话（长事务），其他人就只能排队。50个都占满了，新客人就进不来了。
修复方案：
① 紧急处理：KILL CONNECTION 12; 杀掉长时间 Sleep 的连接
② 根本解决：缩小 @Transactional 范围，只在数据库操作部分加事务
③ 加监控：Druid 连接池配置 removeAbandoned=true，超时自动回收
④ 排查慢查询并优化
8
给500万行的表加字段，线上写入全部阻塞
📅 执行 ALTER TABLE 加字段后，线上接口全部超时
⚠️ 线上故障 📚 DDL原理 & Online DDL
问题复现 — ALTER TABLE 阻塞线上写入
-- 执行 ALTER TABLE 加字段
ALTER TABLE order_info ADD COLUMN remark VARCHAR(200) DEFAULT NULL;
😱 这条语句跑了3分钟！期间所有 INSERT/UPDATE 被阻塞
SHOW PROCESSLIST — 其他会话全部 Waiting for table metadata lock
-- 在另一个终端查看连接状态
SHOW PROCESSLIST;
IdCommandTimeStateInfo
5 Query 187 Waiting for table metadata lock ALTER TABLE order_info ADD...
8 Query 165 Waiting for table metadata lock INSERT INTO order_info...
9 Query 152 Waiting for table metadata lock UPDATE order_info SET status...
12 Sleep 5420 NULL
🔥 会话12有一个长事务（Sleep 5420秒）持有 metadata lock，ALTER TABLE 在等它释放，后续所有DML又在等ALTER TABLE → 级联阻塞！

💡 背后原理：ALTER TABLE 在做什么？

📚 原理：DDL的COPY vs INPLACE
老版本（COPY算法）：
① 创建一个新表（带新字段）
② 把旧表数据逐行拷贝到新表
③ 删除旧表，新表改名为旧表名
④ 全程锁表！500万行拷贝可能要几分钟

新版本（MySQL 5.6+ Online DDL, INPLACE算法）：
① 在原表上直接修改表结构（不拷贝）
② 同时记录期间的 DML 变更到"在线日志"
③ 修改完后，把日志里的变更回放
④ 只有最后很短的时间需要锁（毫秒级）

但有些操作不能用 INPLACE：
• 修改列类型（如 INT → BIGINT）→ 必须COPY
• 修改字符集 → 必须COPY
• 删除主键 → 必须COPY

类比：COPY = 把整栋楼拆了重建（居民全搬走）。INPLACE = 在现有楼层上加一层（居民继续住，只有最后封顶时暂停一下）。
📊 DDL 算法对比：COPY vs INPLACE
❌ COPY 算法（旧版）
① 创建临时表（含新列结构）
↓
② 逐行拷贝旧表数据到临时表
↓
③ 删除旧表，将临时表改名为原表
④ 重建所有索引
🔒 全程锁表！500万行需数分钟
DML 全部阻塞
✅ INPLACE 算法（MySQL 5.6+）
① 在原表上直接修改表结构定义
↓
② 同时记录期间 DML 变更到在线日志
↓
③ 修改完后回放日志中的变更
④ 极短时间加锁完成元数据更新
🔓 几乎不锁表！仅毫秒级锁定
DML 正常执行
解决方案：
① MySQL 5.6+ 自动用 INPLACE，确认：ALTER TABLE ... , ALGORITHM=INPLACE, LOCK=NONE;
② 超大表（千万级）用工具：pt-online-schema-change 或 gh-ost
③ 在低峰期执行
④ 执行前检查：SHOW PROCESSLIST; 确认没有长事务
✅ 方案1：MySQL 5.6+ Online DDL
-- 指定 INPLACE 算法，不锁表
ALTER TABLE order_info ADD COLUMN remark VARCHAR(200) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;
结果
Query OK, 0 rows affected (0.85 sec)
✅ 0.85秒完成！INPLACE 不拷贝数据，仅毫秒级锁定。DML几乎不受影响
✅ 方案2：pt-online-schema-change（千万级大表推荐）
-- pt-online-schema-change 工具原理：
-- ① 创建一张影子表（带新字段）
-- ② 在原表上创建触发器，同步期间的DML变更
-- ③ 分批拷贝数据到影子表（每批1000行，不锁表）
-- ④ 拷贝完成后，原子性地交换表名

-- 实际命令行执行：
-- pt-online-schema-change \
-- --alter "ADD COLUMN remark VARCHAR(200) DEFAULT NULL" \
-- --host=127.0.0.1 --user=dba --ask-pass \
-- D=milk_tea_shop,t=order_info \
-- --chunk-size=1000 --max-load=Threads_running=25 \
-- --execute
✅ pt-osc 不锁表！分批拷贝，期间线上读写正常。适合千万级大表
9
翻到第500页就超时，越往后越慢
📅 后台管理系统，翻到第500页（LIMIT 5000, 10）要10秒
📚 深翻页 & 回表

🔍 问题SQL

问题复现 — 深翻页扫描
SELECT * FROM order_info ORDER BY id LIMIT 50000, 10;
iduser_idamountstatus
50001.........
10 rows • 执行时间: 10.23 sec
EXPLAIN 分析 — 扫描50010行全部回表
EXPLAIN SELECT * FROM order_info ORDER BY id LIMIT 50000, 10;
typekeyrowsExtra
index PRIMARY 50010 Backward index scan
虽然走了主键索引（type=index），但扫描了50010行！SELECT * 每一行都要回表取完整数据，50010次回表 → 10秒

💡 背后原理：为什么 LIMIT 50000,10 这么慢？

📚 原理：OFFSET 的代价
LIMIT 50000, 10 的意思是：从第50001行开始取10条。

但 MySQL 不是直接跳到第50001行！它的执行流程是：
① 从索引的第1条开始扫描
② 扫描前50010条
③ 扔掉前50000条
④ 返回最后10条

如果 SELECT *，每一条都要回表。扫描50010条 = 回表50010次！

类比：你要翻到一本书第500页。不能直接翻过去，必须从第1页开始，一页一页翻，翻到第500页。越往后翻越慢。

✅ 修复：延迟关联

✅ 解决方案 — 延迟关联：先用覆盖索引查出id，再回表
SELECT o.* FROM order_info o
INNER JOIN (
  SELECT id FROM order_info ORDER BY id LIMIT 50000, 10
) tmp ON o.id = tmp.id;
iduser_idamountstatus
5000110528.00paid
10 rows • 执行时间: 0.05 sec — 10.23s → 0.05s，提升200倍！
EXPLAIN — 子查询走覆盖索引，外层只回表10次
EXPLAIN SELECT o.* FROM order_info o
INNER JOIN (
  SELECT id FROM order_info ORDER BY id LIMIT 50000, 10
) tmp ON o.id = tmp.id;
idselect_typetypekeyrowsExtra
1 PRIMARY ALL NULL 10
1 PRIMARY eq_ref PRIMARY 1
2 DERIVED index PRIMARY 50010 Using index
✅ 子查询 Using index（覆盖索引，不回表），外层 eq_ref 只回表10次。50010次回表 → 10次回表！
✅ 或者用游标分页（更好）
-- 让前端记住上一页最后的 id
SELECT * FROM order_info WHERE id > 50000 ORDER BY id LIMIT 10;
iduser_idamountstatus
5000110528.00paid
10 rows • 执行时间: 0.002 sec
EXPLAIN — 游标分页走 range 扫描
EXPLAIN SELECT * FROM order_info WHERE id > 50000 ORDER BY id LIMIT 10;
typekeyrowsExtra
range PRIMARY 10 Using where
✅ type=range，直接从 id=50000 的位置开始，只扫描10行！无需跳过任何行。完美的分页方案
10
COUNT(*) 越来越慢了
📅 借书单表500万行，COUNT(*) 要4秒，以前只要0.1秒
📚 InnoDB MVCC & COUNT
问题复现 — COUNT(*) 全表扫描
-- 500万行的 order_info 表，COUNT(*) 越来越慢
SELECT COUNT(*) FROM order_info;
COUNT(*)
895432
1 row • 执行时间: 4.12 sec — 以前只要0.1秒，数据量涨了5倍后慢了40倍
EXPLAIN 分析 — 遍历二级索引计数
EXPLAIN SELECT COUNT(*) FROM order_info;
typekeyrowsExtra
index idx_status 895432 Using index
type=index 全索引扫描！InnoDB 选了最小的二级索引（idx_status）遍历计数。Using index 表示覆盖索引（不回表），但89万行全扫一遍还是要4秒

💡 背后原理

📚 原理：InnoDB 的 COUNT 要遍历索引
为什么不能直接存一个总数？
因为 MVCC！每个事务看到的数据不一样。事务A可能看不到事务B刚删的那行，也可能看不到事务C刚插的那行。所以没有一个"绝对的总数"，每个事务都得自己数一遍。

InnoDB 怎么 COUNT？：选一个最小的二级索引，遍历所有叶子节点数一遍。因为二级索引比聚簇索引小（每页能存更多条目），所以遍历更快。

MyISAM 为什么快？：MyISAM 不支持事务，没有 MVCC 问题，直接存了一个精确的行数。O(1) 返回。

类比：InnoDB 像一个有多本"快照"的技术——不同时间点看，书架上的书数量不一样，所以每次都要现数。MyISAM 只有一本，门口计数器记了总数。
优化方案：
① 维护一张单独的统计表，每次 INSERT/DELETE 时 +1/-1（业务层保证）
② 用 Redis 缓存总数（定时同步，允许微小误差）
③ 用 EXPLAIN SELECT COUNT(*) 确认走了最小索引
④ 如果只要"大概多少"，用 SHOW TABLE STATUS 的 Rows 列（估算值，秒返）
✅ 方案1：用 SHOW TABLE STATUS 快速估算
-- 只要大概数量（秒返，不精确）
SHOW TABLE STATUS LIKE 'order_info';
NameRowsData_lengthUpdate_time
order_info 894120 478M 2025-06-11 12:34:56
✅ 秒返！Rows 是 InnoDB 估算值（误差5-10%），适合展示"约X条"
✅ 方案2：维护统计表
-- 创建统计表
CREATE TABLE table_stats (
  table_name VARCHAR(64) PRIMARY KEY,
  row_count BIGINT NOT NULL DEFAULT 0
);

-- 业务代码中 INSERT/DELETE 时同步更新
INSERT INTO table_stats VALUES('order_info', 0) ON DUPLICATE KEY UPDATE row_count = row_count + 1;

-- 查总数：直接查统计表（O(1)）
SELECT row_count FROM table_stats WHERE table_name = 'order_info';
row_count
895432
✅ 精确且秒返！统计表只有1行，查询极快。业务层保证 INSERT/DELETE 时 +1/-1
✅ 方案3：条件计数走索引
-- 如果只要某个条件的 COUNT
SELECT COUNT(*) FROM order_info WHERE status = 'paid';
COUNT(*)
45230
1 row • 执行时间: 0.05 sec — 走 idx_status 索引，不需要全扫
11
写入后立刻读，读不到数据
📅 用户刚下完单，去借书单列表查不到，刷新几次才出来
⚠️ 线上问题 📚 主从复制 & binlog
问题复现 — 写入主库后立刻读从库
-- ===== 主库：用户下单 =====
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(201, 5, 35.00, 'paid', NOW());
-- 主库立即返回成功，借书单id=900

-- ===== 从库：立刻查询（读请求路由到从库） =====
SELECT * FROM order_info WHERE user_id = 201;
iduser_idamountstatus
Empty set — 借书单在主库已经有了，但从库还没同步过来！用户看到"下单成功"但借书单列表为空
SHOW SLAVE STATUS — 查看主从延迟
-- 在从库上执行
SHOW SLAVE STATUS\G
字段值
Slave_IO_Running Yes
Slave_SQL_Running Yes
Seconds_Behind_Master 12
Master_Log_File binlog.000152
Relay_Master_Log_File binlog.000149
🔥 Seconds_Behind_Master = 12秒！从库落后主库12秒，还差3个binlog文件没重放完

💡 背后原理

📚 原理：主从复制是异步的
主从架构：写请求到主库（Master），读请求到从库（Slave）。

复制流程：
① 主库执行SQL
写入binlog → ② 从库IO线程
拉取binlog → ③ 写入relay log
（中继日志） → ④ SQL线程
重放relay log

延迟产生的原因：主库写入 binlog 后，从库要拉取、写入、重放。这个过程是异步的。如果主库写入量大（大促/批量操作），从库可能落后好几秒。

用户下单后立刻去从库读：主库已经有了这条借书单，但从库还没来得及重放 → 读不到！

类比：你在总部下了借书单（主库），分店（从库）收到通知需要时间。你立刻打电话到分店问，分店还没收到通知，说"没有这个借书单"。
解决方案：
① 强制路由到主库：写完之后的第一次读，走主库（ShardingSphere等中间件支持）
② 半同步复制：主库等至少一个从库确认收到 binlog 才返回成功
③ 并行复制：MySQL 5.7+ 多线程重放 binlog
④ 监控延迟：SHOW SLAVE STATUS\G 看 Seconds_Behind_Master
✅ 方案1：写后读强制走主库
-- 应用层代码逻辑：
-- 1. 写入主库
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(201, 5, 35.00, 'paid', NOW());

-- 2. 写完后的第一次查询，强制走主库（而非从库）
-- ShardingSphere: HintManager.getInstance().setMasterRouteOnly();
SELECT * FROM order_info WHERE user_id = 201;
iduser_idamountstatus
900 201 35.00 paid
✅ 强制走主库，数据立即可见！后续读请求可以继续走从库
✅ 方案2：开启半同步复制
-- 主库上安装半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 等从库1秒

-- 从库上安装
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
✅ 半同步复制：主库写入后，至少等一个从库确认收到binlog才返回成功。数据更安全，延迟更小
12
用 MyBatis 传参，查询变全表扫描
📅 Java代码传了String类型参数给INT列，查询突然变慢
📚 隐式类型转换

🔍 MyBatis 映射的坑

<!-- MyBatis Mapper -->
<select id="getByPhone" resultType="User">
  SELECT * FROM user WHERE phone = #{phone}
</select>

// Java 代码：phone 字段是 String，但传了数字
userMapper.getByPhone(13812341234);  // 😱 传了 long 类型
问题复现 — user_id 是 INT，传了字符串（不影响索引）
-- user_id 是 INT 类型，传了字符串（INT = STRING）
SELECT * FROM order_info WHERE user_id = '101';
iduser_idamountstatus
5 101 18.00 paid
正常返回。INT=STRING 时 MySQL 把字符串转数字（只转一次），索引不失效
问题复现 — status 是 VARCHAR，传了数字（索引失效！）
-- status 是 VARCHAR(20)，但 Java 代码传了枚举的 int 值
-- 实际执行的SQL：
SELECT * FROM order_info WHERE status = 0;
-- MySQL 对每一行执行 CAST(status AS SIGNED) 再比较 → 索引失效！
结果
895432 rows • 执行时间: 12.8 sec — 全表扫描！
EXPLAIN 分析 — VARCHAR=INT 导致索引失效
EXPLAIN SELECT * FROM order_info WHERE status = 0;
typekeyrowsExtra
ALL NULL 895432 Using where
🔥 type=ALL 全表扫描！status 是 VARCHAR，传了数字 0，MySQL 要对每一行 CAST(status AS SIGNED) → 索引失效
✅ 修复：传字符串参数
-- 正确：传字符串
SELECT * FROM order_info WHERE status = 'pending';
iduser_idamountstatus
2 102 25.00 pending
修复后 EXPLAIN — 索引恢复
EXPLAIN SELECT * FROM order_info WHERE status = 'pending';
typekeyrowsExtra
ref idx_status 45230
✅ type=ref！走 idx_status 索引，89万行 → 4.5万行。参数类型匹配后索引恢复

💡 背后原理

📚 原理：隐式类型转换 = 函数调用 = 索引失效
MySQL 的类型转换规则：
• 字符串列 = 数字 → MySQL 把每一行的字符串转成数字再比较 → 索引失效
• 数字列 = 字符串 → MySQL 把字符串转成数字（只转一次）→ 索引不失效

所以 WHERE user_id = '101'（INT=STRING）不会失效，但 WHERE phone = 13812341234（VARCHAR=INT）会失效。

类比：电话簿按名字拼音排好了（索引），但你要用编号查人。MySQL 要先把所有人的名字翻成编号，再和你的编号比。相当于对每行做了函数处理。
13
中文存进去变成 ??? 或乱码
📅 新服务上线，存了条"编程入门书"到数据库，查出来变成 "???"
📚 字符集 & 编码
问题复现 — 乱码现象
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(301, 5, 35.00, '已支付', NOW());

SELECT id, status FROM order_info WHERE user_id = 301;
idstatus
900 ???
🔥 中文"已支付"存进去变成 ??? 了！数据库字符集不支持中文
排查 — 检查字符集设置
SHOW VARIABLES LIKE 'character_set%';
Variable_nameValue
character_set_server latin1
character_set_client latin1
character_set_database latin1
character_set_connection latin1
🔥 全链路都是 latin1！latin1 不支持中文，中文字符被截断为 ???
排查 — 检查表和列的字符集
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'milk_tea_shop';
TABLE_NAMETABLE_COLLATION
order_info latin1_swedish_ci
表级别也是 latin1。需要从 server → database → table → column 全链路修改

💡 背后原理

📚 原理：字符集不匹配
字符集链条（三个环节要一致）：
Java 应用
(UTF-8) → JDBC 连接
(characterEncoding) → MySQL Server
(character_set_server) → 数据库/表/列
(CHARSET)

任何一个环节字符集不一致，都会导致乱码。

常见问题：
• 数据库/表用了 latin1（MySQL老版本默认），不支持中文
• JDBC 连接串没加 characterEncoding=utf8
• 表用了 utf8 而不是 utf8mb4 → 存不了emoji 😂

utf8 vs utf8mb4：MySQL的 utf8 只支持3字节UTF8字符，utf8mb4 支持4字节（emoji、生僻字）。永远用 utf8mb4。
修复：
ALTER DATABASE milk_tea_shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE product CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
JDBC URL 加 ?characterEncoding=utf-8
✅ 修复：全链路设为 utf8mb4
-- 1. 修改数据库默认字符集
ALTER DATABASE milk_tea_shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. 修改表的字符集（含所有列）
ALTER TABLE order_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 3. 修改MySQL服务器默认字符集（my.cnf）
-- [mysqld]
-- character_set_server = utf8mb4
-- collation_server = utf8mb4_unicode_ci

-- 4. JDBC连接串加上字符编码
-- jdbc:mysql://host:3306/milk_tea_shop?characterEncoding=utf-8
修复后验证 — 中文正常存储
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(302, 5, 35.00, '已支付', NOW());

SELECT id, status FROM order_info WHERE user_id = 302;
idstatus
901 已支付
✅ 中文正常存储和显示！utf8mb4 支持中文、emoji、生僻字
14
@Transactional 里调外部接口，事务超时了
📅 下单接口里调了支付服务和消息通知，高峰期全部超时
📚 事务 & 锁持有时间
// ❌ 问题代码：事务里包含了远程调用
@Transactional
public void placeOrder(OrderDTO dto) {
  orderMapper.insert(order);       // 1. 插入借书单
  productMapper.updateStock(..);  // 2. 扣库存 ← 加了行锁
  payService.pay(dto);         // 3. 调支付接口 ← 😱 外部HTTP调用，可能3秒！
  notifyService.sendMsg(..);   // 4. 发消息 ← 又是外部调用
}
// 整个方法都在事务里！步骤3、4占用外部调用，但数据库锁一直没释放！
问题复现 — 对应的SQL执行序列
-- Spring @Transactional 实际执行的SQL序列：
BEGIN;  -- 方法开始，Spring自动开启事务

INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(201, 5, 35.00, 'pending', NOW());  -- 1. 插入借书单

UPDATE product SET stock = stock - 1 WHERE id = 5;  -- 2. 扣库存（加行锁）

-- 3. payService.pay() ← HTTP调用外部支付服务... 等待3秒...
-- 4. notifyService.sendMsg() ← HTTP调用消息服务... 等待2秒...
-- 这5秒里，product id=5 的行锁一直被持有！

COMMIT;  -- 方法结束，Spring自动提交
🔥 事务持有了5秒锁！期间其他用户下单扣同一商品库存必须排队等待
SHOW PROCESSLIST — 高峰期大量事务等待锁
SHOW PROCESSLIST;
IdCommandTimeStateInfo
20 Query 5.2 Waiting for lock metadata UPDATE product SET stock...
21 Query 4.8 Waiting for lock metadata UPDATE product SET stock...
22 Query 3.1 Sending data INSERT INTO order_info...
🔥 高峰期大量事务在等锁！前一个事务还在调支付接口（HTTP），后续事务全部排队

💡 背后原理

📚 原理：事务越长，锁持有越久
Spring 的 @Transactional 在方法开始时 BEGIN，方法结束时 COMMIT。

问题：
① 步骤2扣库存时，对 product 行加了行锁
② 步骤3调支付接口（HTTP远程调用），可能要2-5秒
③ 这2-5秒里，行锁一直被持有！其他事务要扣同一商品的库存，只能等！
④ 如果支付服务更慢（比如10秒），大量事务排队等锁 → 连接池耗尽 → 雪崩

类比：你占着收银台（事务），付钱的时候发现支付宝要等5秒。5秒里别人都用不了这个收银台。
修复原则：事务里只放数据库操作，不放外部调用
① 步骤1、2放事务里（纯DB操作）
② 事务提交后，再调支付接口和发消息
③ 支付结果通过回调接口更新借书单状态（另一个小事务）
④ 或者用消息队列（RabbitMQ）异步处理支付和通知
✅ 修复后：事务只包含DB操作
-- ✅ 修复后的事务：只包含数据库操作（耗时 < 50ms）
BEGIN;
INSERT INTO order_info(user_id, product_id, amount, status, created_at)
VALUES(201, 5, 35.00, 'pending', NOW());
UPDATE product SET stock = stock - 1 WHERE id = 5;
COMMIT;  -- 立即提交！锁只持有几毫秒

-- 事务提交后，再异步调支付接口（不在事务里）
-- payService.payAsync(orderId);  ← 放在事务外面
-- 支付完成后回调更新状态：
-- UPDATE order_info SET status = 'paid' WHERE id = 900;
✅ 事务从5秒缩短到50ms内。行锁只持有几毫秒，其他用户几乎不用等待
15
MySQL磁盘快满了，怎么清理？
📅 监控报警：MySQL 数据目录使用率 92%，即将爆满
⚠️ 运维问题 📚 存储空间 & binlog
$ du -sh /var/lib/mysql/*
4.2G  /var/lib/mysql/milk_tea_shop/
12.8G  /var/lib/mysql/binlog.*  ← 🔥 binlog占了12.8G！
1.1G  /var/lib/mysql/ibdata1
256M  /var/lib/mysql/ib_logfile*

💡 背后原理：磁盘空间被什么吃了？

📚 原理：binlog + undo log + 数据文件
MySQL 磁盘空间的组成：

1) binlog（归档日志）：记录所有数据变更操作，用于主从复制和数据恢复。默认永不过期！你的数据库运行1年，binlog 可能几百GB。

2) 数据文件（.ibd）：InnoDB 每个表的数据存在 .ibd 文件里。DELETE 删了数据，文件不会自动缩小（只是标记为可复用）。这就是"碎片空间"。

3) undo log：事务的回滚日志。如果有长事务一直不提交，undo log 就会越来越大（因为要保留旧版本给这个长事务读）。

4) 临时文件：大排序（Filesort）、大 GROUP BY 会产生临时表，写磁盘。

类比：技术的话费账单（binlog）从开业到现在全部保留（12GB）、翻页笔记本删了几页但没撕掉（碎片）、有个老读者三个月前开始看索引卡还没决定（长事务 undo log）。
清理方案（从大到小排列）：
① 清理 binlog：PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY); 只保留7天
② 设置自动过期：SET GLOBAL expire_logs_days = 7;
③ 清理碎片：OPTIMIZE TABLE order_info;（会锁表，用 pt-online-schema-change 替代）
④ 杀长事务：SELECT * FROM information_schema.innodb_trx; 找到跑了几小时的事务
⑤ 清理慢查询日志：RESET MASTER;（慎用！会清空所有binlog）
查看表碎片
SELECT table_name, DATA_FREE/1024/1024 AS '碎片空间(MB)',
DATA_LENGTH/1024/1024 AS '数据大小(MB)'
FROM information_schema.TABLES
WHERE table_schema = 'milk_tea_shop'
ORDER BY DATA_FREE DESC;
table_name碎片空间(MB)数据大小(MB)
order_info890.51200.3
product2.115.8
order_info 有 890MB 碎片空间！OPTIMIZE TABLE 可以回收，但要锁表。

📋 15个问题对应的原理速查表

问题核心原理关键词
1. 接口慢全表扫描 + FilesortEXPLAIN → ALL → 加索引
2. 索引失效对列做运算破坏B+树查找函数/类型转换/LIKE左模糊
3. 死锁行锁交叉等待统一更新顺序
4. 数据不一致MVCC 版本链 + ReadViewRR vs RC 隔离级别
5. 数据误删autocommit + undo logBEGIN → 确认 → COMMIT
6. 导出超时回表 + 内存溢出分批查询 + 游标分页
7. 连接池耗尽长事务占连接缩小事务范围
8. 大表加字段DDL: COPY vs INPLACEOnline DDL / gh-ost
9. 深翻页OFFSET + 回表代价延迟关联 / 游标分页
10. COUNT慢MVCC无法预存总数统计表 / Redis缓存
11. 读写不一致主从复制异步延迟写后读走主库
12. MyBatis传参隐式类型转换 = 函数调用参数类型要匹配
13. 中文乱码字符集不匹配utf8mb4 全链路
14. 事务超时事务里调外部接口事务只包DB操作
15. 磁盘爆满binlog + 碎片 + undo log定期清理 binlog

🔍 EXPLAIN Extra 字段完整参考

Extra 列告诉你 MySQL 到底怎么执行的

EXPLAIN 输出中，Extra 列是最有信息量的字段之一。它告诉你 MySQL 在找到数据后还做了哪些额外操作。以下是所有常见值的含义。
Extra 值含义好坏示例 / 建议
Using index 覆盖索引，查询的所有字段都在索引里，不需要回表 ✅ 最优 SELECT user_id, status FROM order_info WHERE user_id = 1
索引 idx(user_id, status) 包含了全部查询列
Using where MySQL 从存储引擎取到数据后，还在 Server 层做了过滤 ⚠️ 一般 说明索引没有完全覆盖 WHERE 条件，有些条件在索引里没有，需要回表后再过滤
Using index condition 索引下推(ICP)，在索引层面就做了部分 WHERE 过滤，减少回表次数 ✅ 较好 MySQL 5.6+ 特性。WHERE 条件的一部分可以在索引里直接判断，不用每条都回表
Using temporary MySQL 创建了临时表来处理查询 ❌ 差 常见于 GROUP BY / DISTINCT / UNION 没有合适索引时。
→ 优化：给 GROUP BY 字段加索引
Using filesort MySQL 做了额外的排序操作（ORDER BY 没走索引） ❌ 差 数据量大时非常消耗 CPU 和内存。
→ 优化：给 ORDER BY 字段加索引，或放在联合索引末尾
Using temporary; Using filesort 最差组合：既建了临时表又做了额外排序 ❌ 最差 GROUP BY + ORDER BY 都没索引。必须优化！
→ 建联合索引覆盖 GROUP BY + ORDER BY
Using join buffer JOIN 时被驱动表没有索引，用了连接缓冲区 ⚠️ 一般 被驱动表扫描次数太多。
→ 优化：给 JOIN 的 ON 字段加索引
Impossible WHERE WHERE 条件永远为假（如 WHERE 1=0） — 无影响 通常出现在动态拼接 SQL 的 bug 中
Select tables optimized away 优化器直接从索引里取到了结果（如 MAX/MIN） ✅ 最优 SELECT MAX(id) FROM t → B+ 树最右节点直接返回
Using filesort (B) 使用了增量排序（MySQL 8.0+） ⚠️ 比 filesort 好 部分排序已经由索引完成，只有少量额外排序

📋 EXPLAIN 看的重点顺序

1️⃣ type
扫描方式 → 2️⃣ key
用了哪个索引 → 3️⃣ rows
预估扫描行数 → 4️⃣ Extra
额外操作
type 从好到差：system > const > eq_ref > ref > range > index > ALL
✅ 出现 const/ref/range 算及格  |  ❌ 出现 ALL 必须优化

🧪 Extra 实战对比

✅ 好的 EXPLAIN
-- 查询：有索引覆盖 EXPLAIN SELECT user_id, status FROM order_info WHERE user_id = 1; -- 结果： -- type: ref ✅ 走了索引查找 -- key: idx_user_id ✅ 用了索引 -- rows: 10 ✅ 只扫描10行 -- Extra: Using index ✅ 覆盖索引，不用回表
❌ 差的 EXPLAIN
-- 查询：没索引 + GROUP BY + ORDER BY EXPLAIN SELECT status, COUNT(*) FROM order_info GROUP BY status ORDER BY COUNT(*) DESC; -- 结果： -- type: ALL ❌ 全表扫描 -- key: NULL ❌ 没走索引 -- rows: 1000000 ❌ 扫描100万行 -- Extra: Using temporary ❌ 建了临时表 -- Using filesort ❌ 额外排序
🎉 遇到MySQL问题不要慌！
发现 → EXPLAIN → 看 type/key/Extra → 对照这个手册 → 找原理 → 修复
每个问题背后都是一个原理，理解了原理，一通百通。