MySQL 实战技巧与常见陷阱
汇总 MySQL 日常开发中的关键实战技巧：join 该不该用、临时表的作用、Memory 引擎的特殊行为、自增主键为什么不连续、误删数据如何恢复，以及分区表的利弊。
MySQL 系列：MySQL 简介 · MySQL 锁机制 · MySQL 性能问题排查 · MySQL 事务与 MVCC
目录
| 章节 | 说明 |
|---|---|
| join 的使用 | 小表驱动大表、NLJ vs BNL |
| 临时表 | 内部临时表的触发条件 |
| Memory 引擎 | 与 InnoDB 的关键差异 |
| 自增主键不连续的原因 | 4 种常见场景 |
| 表空洞与碎片整理 | 空洞的成因、自增主键的影响、OPTIMIZE TABLE |
| 自增 id 用完怎么办 | 各类自增 id 的上限 |
| 误删数据恢复 | binlog 闪回、Flashback |
| 分区表 | 适合场景、MDL 锁的问题 |
join 的使用
两种 join 算法
| 算法 | 触发条件 | 原理 |
|---|---|---|
| Index Nested-Loop Join（NLJ） | 被驱动表有索引 | 驱动表每行，走被驱动表索引，类似嵌套循环，但利用索引，效率高 |
| Block Nested-Loop Join（BNL） | 被驱动表无索引 | 驱动表按 join_buffer 分块，与被驱动表全量数据做对比，可能很慢 |
小表驱动大表
-- 驱动表（外层循环）选择小表，被驱动表走索引
-- STRAIGHT_JOIN 强制左表为驱动表
SELECT t1.* FROM t1 STRAIGHT_JOIN t2 ON t1.id=t2.id WHERE t2.a=1;
-- 一般不需要手动指定，优化器会自动选择行数少的表作为驱动表
BNL 的优化
如果被驱动表上没有合适的索引，BNL 会导致多次全表扫描：
-- 方案1：给被驱动表的关联字段加索引
ALTER TABLE t2 ADD INDEX idx_a(a);
-- 方案2：使用临时表 + 索引（被驱动表数据量太大时）
CREATE TEMPORARY TABLE tmp_t (id INT, a INT, INDEX(id));
INSERT INTO tmp_t SELECT id, a FROM t2 WHERE b=1;
SELECT * FROM t1 JOIN tmp_t ON t1.id=tmp_t.id;
什么时候不该用 join
- 被驱动表无法使用索引（产生 BNL）
- 两个大表做 join（超过百万行的 BNL）
- 这种情况下宜在应用层做 join
临时表
内部临时表触发场景
MySQL 在以下情况自动使用内部临时表（EXPLAIN 中 Extra = "Using temporary"）：
| 场景 | 说明 |
|---|---|
GROUP BY 无索引 | 需要在临时表中聚合 |
ORDER BY + LIMIT 有文件排序 | 将中间结果放临时表 |
UNION | 去重需要临时表存放中间结果（UNION ALL 不需要） |
DISTINCT | 需要临时表去重 |
| 复杂子查询 |
内存临时表 vs 磁盘临时表
- 内存临时表超过
tmp_table_size（默认 16 MB）后，转为磁盘临时表（InnoDB） - 尽量避免产生大型临时表
显式临时表
-- 显式临时表：仅在当前 session 可见，session 结束自动删除
CREATE TEMPORARY TABLE tmp_result (id INT, val VARCHAR(100));
-- 同一 session 中可以和主表同名（各自独立）
-- 不同 session 可以建同名临时表（互不干扰）
-- 用途：存储 join 的中间结果，分步处理复杂查询
Memory 引擎
与 InnoDB 的关键差异
| 对比维度 | InnoDB | Memory |
|---|---|---|
| 数据结构 | B+ 树（聚簇索引） | 哈希表（默认） |
| 范围查询 | ✅ 高效（有序） | ❌ 低效（哈希无序，需全表扫描） |
| 数据持久化 | ✅ 持久化到磁盘 | ❌ 重启后数据丢失 |
| varchar 存储 | 变长 | 定长（按 varchar 最大长度分配空间） |
| 行锁 | ✅ 支持 | ❌ 只有表锁 |
为什么不推荐 Memory 引擎做业务表
- 重启数据丢失（即使可以靠 binlog 重建，也很麻烦）
- 只有表锁，并发性能差
- varchar 用定长存储，可能浪费大量空间
Memory 引擎适用场景
- MySQL 内部用于存放临时数据（内部临时表默认 Memory 引擎）
- 少量的临时配置数据（能接受重启丢失）
自增主键不连续的原因
InnoDB 的自增主键 AUTO_INCREMENT 并不保证连续，以下场景会产生"空洞"：
| 场景 | 原因 |
|---|---|
| 事务回滚 | INSERT 分配了自增 id，但事务回滚，该 id 不再使用 |
| INSERT ... SELECT 批量插入 | 预分配一批 id，实际使用的少于分配的 |
| INSERT 失败 | 已分配 id，但 INSERT 因唯一键冲突等失败 |
| AUTO_INCREMENT 锁策略 | innodb_autoinc_lock_mode=2 时，并发 INSERT 可能产生 id 空洞 |
结论：自增主键只保证单调递增，不保证连续。业务不应该依赖自增 id 的连续性。
AUTO_INCREMENT 锁模式
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
| 值 | 模式 | 特点 |
|---|---|---|
| 0 | 传统模式 | 语句级 AUTO-INC 锁，insert 完才释放 |
| 1 | 连续模式（默认） | 简单 insert 使用轻量互斥锁，批量 insert 仍用语句级锁 |
| 2 | 交错模式 | 所有 insert 用轻量锁，并发性最好，但主从 id 可能不同（row 格式 binlog 不受影响） |
表空洞与碎片整理
什么是表空洞
对表执行 DELETE 后，InnoDB 并不立即释放磁盘空间，被删除的位置变成"空洞"。
两种级别的空洞：
| 空洞类型 | 产生原因 | 能否自动复用 |
|---|---|---|
| 页内空洞（部分删除） | 页中部分记录被删除，槽位进入页内 free list | ❌ 自增插入去末尾页，不会回填中间页 |
| 整页空洞（整页清空） | 整页记录全部删除，页回到段的 free list | ✅ 新分配页时优先复用，但页序变得不连续 |
自增主键让空洞"有进无出"
初始状态：
页1: [0][5][10][15] 页2: [20][25]
DELETE id=5 后：
页1: [0][_][10][15] ← 空洞，但自增插入 id=26 不会来这里
页2: [20][25][26] ← 新记录追加到末尾
→ 页1 的空洞永久保留，除非 OPTIMIZE TABLE
整页被清空时，页会回到 free list 可被复用，但 B+ 树的页号顺序变得不连续，全表扫描需要跳跃读取，随机 I/O 增多。
例外：删除的是最后一行，且紧接着有新 INSERT——新记录刚好落在同一页，空洞被填上。但这只是巧合，不是机制保证。
消除空洞
-- 重建表，消除空洞，恢复页填充率（~93%）
-- ⚠️ 会锁表，建议低峰期执行
OPTIMIZE TABLE t;
-- 等价写法
ALTER TABLE t ENGINE=InnoDB;
重建过程按主键顺序重新插入所有数据，页填充率恢复到 innodb_fill_factor 默认值（约 93%）。
自增 id 用完怎么办
各类自增 id 的上限
| 类型 | 最大值 | 说明 |
|---|---|---|
INT UNSIGNED 自增主键 | 42.9 亿（2³²-1） | 用完后 INSERT 会报错"Duplicate entry" |
BIGINT UNSIGNED 自增主键 | 1.8×10¹⁹ | 实际上用不完 |
| row_id（无主键表） | 2⁴⁸（约 281 万亿） | 用完后从 0 开始，覆盖旧数据（非常危险！） |
Xid（事务 ID） | 2⁶⁴ | 实际不会用完 |
InnoDB 事务 id | 2⁴⁸ | 持久化，用完后重新从 0 开始 |
thread_id | 2³² | 用完后从 0 开始 |
⚠️ 最危险的是无主键表的 row_id：用完后从 0 循环，会覆盖旧数据且不报错！每张表必须有主键，且建议用 BIGINT。
int 主键用完后会怎样
-- INT UNSIGNED 最大值 4294967295，用完后尝试插入会报错：
-- ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
-- 原因：申请到最大值后，下一次还是最大值，导致主键冲突
误删数据恢复
误删行数据（binlog flashback）
-- 要求：binlog_format=row（记录了每行的 before/after）
-- 工具：myflashback、binlog2sql
-- 原理：找到误删的 DELETE 操作对应的 binlog event，
-- 将其逆转为 INSERT，重新执行
mysqlbinlog --start-datetime="2024-01-01 12:00:00" \
--stop-datetime="2024-01-01 12:30:00" \
binlog.000001 | binlog2sql --flashback
误删库/表（drop table / truncate）
binlog 中 row 格式不记录 DROP/TRUNCATE 的前镜像，无法直接 flashback。
恢复步骤：
- 找到最近一次全量备份
- 恢复全量备份到临时库
- 从备份时间点开始，重放 binlog 到误删操作前一刻
- 将表数据从临时库导入线上
预防措施
# 对 DROP TABLE / TRUNCATE / DELETE without WHERE 做延时处理
# 推荐：DBA 账号权限最小化，业务账号禁止执行 DDL
# 推荐：gh-ost / pt-osc 工具而非直接 ALTER
分区表
是什么
将一张逻辑表的数据按照规则分散存储到多个物理文件（分区）中。
-- 按 RANGE 分区
CREATE TABLE t (
id INT,
created DATE
) ENGINE=InnoDB
PARTITION BY RANGE(YEAR(created)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
分区表的优势
- 历史数据清理方便（
ALTER TABLE t DROP PARTITION p2022） - 查询时可以做分区裁剪（partition pruning），只扫描相关分区
分区表的 MDL 锁问题
分区表的 DDL 操作（如 DROP PARTITION）会对整张表加 MDL 写锁，影响所有分区的读写。
实际使用建议
| 场景 | 建议 |
|---|---|
| 数据量大、按时间清理 | ✅ 考虑分区表 |
| 普通业务数据 | ❌ 不推荐，用分库分表代替 |
| 跨分区查询多 | ❌ 分区裁剪失效，全表扫 |
实际项目中，分区表不如按日期/ID 拆分多张表（应用层分表）来得灵活。
参考资料
- 《MySQL 实战 45 讲》— 第 34 讲：到底可不可以使用 join？
- 《MySQL 实战 45 讲》— 第 35 讲：join 语句怎么优化？
- 《MySQL 实战 45 讲》— 第 37 讲：什么时候会使用内部临时表？
- 《MySQL 实战 45 讲》— 第 38 讲：都说 InnoDB 好，那还要不要使用 Memory 引擎？
- 《MySQL 实战 45 讲》— 第 39 讲：自增主键为什么不是连续的？
- 《MySQL 实战 45 讲》— 第 45 讲：自增 id 用完怎么办？
- 《MySQL 实战 45 讲》— 第 31 讲：误删数据后除了跑路，还能怎么办？
- 《MySQL 实战 45 讲》— 第 43 讲：要不要使用分区表？
评论 (0)