SQL优化实战：大批量写入优化

CH-05-01-029·约 17 分钟·数据库与存储系统·更新 2026-08-10

假设你是一家搬家公司的调度员，今天要帮客户把五千件杂物从旧房搬进新公寓。最笨的做法是什么？每拿起一只杯子就下楼一趟，放进货车，再气喘吁吁上楼拿下一件——来回五千趟，门卫登记五千次，累到虚脱。稍微聪明一点，你会先把零碎装进纸箱，一箱装几十件，十来趟完成；更专业的做法，是提前联系物业、规划路线、按房间号顺序把大件家具一次就位。数据库的大批量写入，本质就是同一门“搬家调度”学问：逐条 INSERT 如同每次只搬一只杯子，批量 INSERT 如同装箱搬运，LOAD DATA 如同请来专业装卸队，而索引与约束的取舍、事务批次的节奏、写入顺序的规划，则是搬家前如何腾出走廊、如何控制每车吨位、如何按房间归置家具的智慧。读懂这门调度艺术，你就能把“数据库写入慢”的抱怨，变成一张漂亮的效率账单。

岗位关联

对 AI 从业者而言，特征工程、数据管道与模型训练都依赖高效的数据入库能力，掌握大批量写入优化是搭建高性能数据服务的基本功。

可视化演示
性能瓶颈分析：一趟只搬一只杯子的代价
批量INSERT策略：把散件装进大纸箱
LOAD DATA导入：请来专业装卸队
临时加速技巧：搬运前的清障工作
事务与锁平衡：控制每车货物的吨位
顺序写入优化：按房间号顺序摆放家具
AI 应用场景
Python 演示
常见误区
面试题

可视化演示

展示系统处理流水线、实时性能指标曲线及多方案对比分析，包含数据流转、吞吐延迟监控与方案评估。

本节配有交互动效（进入视口后自动加载，约 3KB）。

系统可视化处理流程：输入→处理→模型→输出性能监控：实时指标 P=L×T

性能瓶颈分析：一趟只搬一只杯子的代价

想象你是搬家公司的调度员，客户住在 20 楼，楼下停着货车。最笨的工人会怎么做？拿一只杯子下楼，放进车里，再上楼拿第二只杯子。每趟都要等电梯、按门铃、登记，电梯门开关的吱呀声里，大半天只搬了几十件。数据库里最朴素的逐条 INSERT，就是这种“一趟一件”的做法——每一条记录都是一次独立的电梯往返，门卫就是数据库的日志系统，每一次上下楼都要重新登记。

把这种笨办法翻译成数据库术语，总耗时可拆成三笔账：网络往返、事务提交、锁等待。网络往返是客户端与数据库服务器之间一次请求响应的来回；事务提交是数据库把本次写入的日志真正落盘的那一刹那，相当于门卫盖章；锁等待则是当你占着走廊时，其他搬家公司的人只能干等着。逐条插入时，这三笔账每条数据都要付一遍，n 条数据就是 n 次往返、n 次日志刷盘、n 次锁排队。

所以说，逐条插入慢并不怪“插入”这个动作本身，而是电梯门和门卫登记被重复了 n 次。搬家公司的老手看到这种干法，会直接摇头：把杯子装进箱子，一趟解决的问题，为什么要跑二十趟？

\displaystyle T_{\text{total}} = n \times (t_{\text{net}} + t_{\text{commit}} + t_{\text{lock}})

一个总耗时模型：每条数据都要独立付出网络、提交、锁等待三份成本，总耗时随数据条数线性放大。

拆解单条成本单条 INSERT 的耗时由三部分组成。
$\displaystyle c = t_{\text{net}} + t_{\text{commit}} + t_{\text{lock}}$
乘以总条数n 条数据每条都付一次单条成本。
$\displaystyle T_{\text{total}} = n \times c$
代入展开把 c 展开成三项，得到完整模型。
$\displaystyle T_{\text{total}} = n \times (t_{\text{net}} + t_{\text{commit}} + t_{\text{lock}})$

批量INSERT策略：把散件装进大纸箱

聪明的搬家工不会一只杯子跑一趟。他们会先把一箱、两箱、十箱的杂物装进一只大纸箱，再用小推车一次运下楼。SQL 里的多值 INSERT 就是这只大纸箱：一条 INSERT 语句的 VALUES 后面可以跟多组括号，一次写入几十几百行数据。

纸箱也不是越大越好。经验上每条 SQL 控制在 100 到 1000 行比较稳妥，像 MySQL 还会受 max_allowed_packet 限制。纸箱太大，一个人根本抬不动——单个事务过大时，回滚日志膨胀、锁持有时间拉长，反而把性能拖垮。批量提交的时机同样有讲究：每装满一车（比如每 1000 行）就发车一次，而不是每箱发一车、也不是把所有贵重大件全堆在一辆超长卡车里。吞吐量随批大小呈现先升后降的山丘形状，山丘顶部那片平坦区域，就是我们要找的最优区间。

\displaystyle T_{\text{batch}} = \frac{n}{B} \times (t_{\text{net}} + B \times t_{\text{row}} + t_{\text{commit}})

批大小为 B 时，网络往返从 n 次降到 n/B 次；写入行的总时间仍是 n × t_row，不会消失但被摊薄。

批次数B 行装一箱，总共只需要 n/B 趟。
$\displaystyle \frac{n}{B}$
单批耗时一趟包含一次网络往返、B 行写入、一次提交。
$\displaystyle c_{\text{batch}} = t_{\text{net}} + B \times t_{\text{row}} + t_{\text{commit}}$
总耗时趟数乘以单趟耗时。
$\displaystyle T_{\text{batch}} = \frac{n}{B} \times (t_{\text{net}} + B \times t_{\text{row}} + t_{\text{commit}})$

LOAD DATA导入：请来专业装卸队

批量 INSERT 再快，也还是“人工装箱”。搬家行业里真正的效率革命，是直接请来带传送带的专业装卸队：货车尾门打开，整板整板的货物顺着滑轨滑进仓库，不需要工人一件件拆包扫码。MySQL 的 LOAD DATA 就是这个角色。它让数据库服务端直接读取数据文件，绕过了逐条 SQL 的语法解析和逐次网络传输，像传送带一样把文件里的行流式灌入表中。

LOAD DATA 的另一个设计心思是“索引延迟维护”：装卸队先把家具堆进房间，等全部堆完再统一布置书架和梳妆台。数据库同样在导入完成后一次性构建索引，而不是每插一行就维护一次 B+ 树。加上它默认在单个事务内完成全部导入，失败时统一回滚，省心又利落。实际项目中，LOAD DATA 往往比逐条 INSERT 快 20 到 30 倍。

不过要注意，装卸队不等于没有规矩。LOAD DATA 默认仍会执行约束检查、仍要写事务日志，只是把“逐件扫码”变成了“整板过磅”。它最适合的场景，是离线批量导入——日志归档、数据迁移、训练集落库，而不是业务高峰期里的在线写入。

\displaystyle \frac{T_{\text{INSERT}}}{T_{\text{LOAD DATA}}} \approx 20 \sim 30

经验倍数：两者差距主要来自省掉的 n 次网络往返与逐条 SQL 解析，索引与日志差异是次要因素。

逐条总耗时逐条 INSERT 的总耗时。
$\displaystyle T_{\text{INSERT}}$
LOAD DATA 总耗时文件直读导入的总耗时。
$\displaystyle T_{\text{LOAD DATA}}$
经验倍数两类做法约差 20 到 30 倍。
$\displaystyle \frac{T_{\text{INSERT}}}{T_{\text{LOAD DATA}}} \approx 20 \sim 30$

临时加速技巧：搬运前的清障工作

搬家师傅有个经验：搬大家具前，先把走廊里的装饰矮柜挪到阳台，把门上挂的感应门铃暂时摘掉。否则三人抬着大沙发，每挪一步都要被矮柜挡住、被门铃吵得停手。数据库的索引和约束检查，就是走廊里的矮柜与门铃——它们平时保护秩序，搬大件时却处处碍事。

于是有了“临时加速”这套组合拳：导入前先 DROP 非唯一索引，导入完成后重建；导入期间 SET foreign_key_checks=0 关闭外键检查、SET unique_checks=0 禁用唯一约束检查。这样插入一行时不再需要同步维护每一个索引和约束，速度自然飞起。插入一行数据的基本成本，被压到了只剩写行本身。

这套清障动作的关键词是“临时”。矮柜搬走，是为了沙发进门；门铃摘掉，是为了大件通行，搬完必须物归原位。生产环境里关闭唯一约束和外键检查会让脏数据有机会溜进表里，所以一定要安排在离线导入窗口，并在导入完成后立即恢复检查、重建索引、验证数据完整性。低峰期配合备份执行，才是一门有节奏的调度艺术。

\displaystyle T_{\text{insert}} = t_{\text{row}} + \sum_{i=1}^{m} O(\log N_i)

带索引的插入耗时 = 写行本身 + 每个索引的 B+ 树维护代价。暂时去掉索引，就是让求和项归零。

写一行数据把行写入表文件的基础耗时。
$\displaystyle t_{\text{row}}$
维护每个索引m 个二级索引，每个都要付出对数级更新代价。
$\displaystyle \sum_{i=1}^{m} O(\log N_i)$
总写入耗时基础写入与索引维护相加。
$\displaystyle T_{\text{insert}} = t_{\text{row}} + \sum_{i=1}^{m} O(\log N_i)$

事务与锁平衡：控制每车货物的吨位

搬家公司调度员最怕什么？一辆超长卡车满载出发，却在小区门口转弯处卡住，进退两难；好不容易挪进去，路上颠了一下，整车玻璃器皿碎了大半——这一车货全赔。数据库里，超大事务就是那辆超长卡车：锁要一路捏在手里，谁也别想碰；undo 日志不断膨胀，记录着回滚所需的所有现场；一旦中途出错要回滚，代价与事务大小成正比。

反过来，事务太小也不行。每 100 行的提交，虽然一车很轻，但跑 50 趟意味着 50 次日志刷盘，门卫登记的时间比搬货还长。最优解是“适中且分批”：常见的做法是每 1000 到 5000 行提交一个事务，让每趟车都有足够吨位，又不会压垮小区道路。锁的持有时间正比于事务内处理的数据量，这是一个硬约束。

控制吨位之外，还要规划路线：多个写入任务尽量按相同顺序处理数据，避免互相占道形成死锁闭环。把大事务拆成中批次的短事务，像是把一趟长途货运拆成几段短途接驳——每段出问题只损失这一段，重来也快。这就是批量提交的折中智慧：既不吃逐条的苦，也不担整车的险。

\displaystyle T_{\text{hold}} = B \times t_{\text{row}}

锁持有总时间 = 批大小 × 每行写入耗时。事务越大，别人等待的时间越长，死锁概率也越高。

批大小一个事务包含 B 行。
$\displaystyle B$
每行写入耗时写一行数据平均花费的时间。
$\displaystyle t_{\text{row}}$
锁持有总时间事务持有锁的总时长，随 B 线性增长。
$\displaystyle T_{\text{hold}} = B \times t_{\text{row}}$

顺序写入优化：按房间号顺序摆放家具

经验丰富的搬家队还有个细节：每件家具搬进新居前，先看一眼房间号。一号房的沙发先搬进去，二号房的书架接着进，三号房的床垫跟在后面——物业的楼层布局图几乎不用改。如果反过来，三号房的床垫先到了，先把图纸改了；一会儿一号房沙发来了，又得撕下那页重写，楼层图被涂改得乱七八糟。数据库的 B+ 树页分裂，正是这种涂改。

B+ 树把数据按主键顺序存放在“页”里，每个页像一个房间的登记表。顺序插入时，新数据总是追加在最后一页，登记表翻到最后一页就够用；乱序插入时，数据可能落在任意一页，页满了就要现场“撕页重写”——把一个满页拆成两个半页，并更新所有指向它的索引。页分裂不仅带来额外写入，还让每页留下空洞，空间利用率下降。随机主键（如 UUID）正是页分裂的头号诱因，而自增主键天然保证顺序追加。

所以大批量写入时，设计表结构就要想好主键策略：能自增就别用 UUID，能用雪花算法保证趋势递增就别让业务随机数当主键。如果表里已经积累了碎片，低峰期跑一次 OPTIMIZE TABLE 或重建表，等于把涂改得乱七八糟的楼层图重新誊写一遍。顺序，是数据库世界里最廉价的免费加速器。

\displaystyle \eta_{\text{random}} \approx \frac{5}{6}, \qquad \eta_{\text{ordered}} \approx 1

乱序插入时 B+ 树页平均利用率约 5/6，顺序插入时接近 1，相当于白省约 17% 的存储和 IO。

随机顺序的页利用率乱序插入不断触发页分裂，平均每页只用到六分之五。
$\displaystyle \eta_{\text{random}} \approx \frac{5}{6}$
顺序插入的页利用率顺序追加时数据紧挨着排，页面几乎不浪费。
$\displaystyle \eta_{\text{ordered}} \approx 1$
空间差异两者相差约六分之一，即约 17% 的空间与 IO。
$\displaystyle 1 - \frac{5}{6} = \frac{1}{6} \approx 16.7\%$

AI 应用场景

离线特征宽表回填

阶段：数据准备与特征工程

解决的问题：推荐系统每天要把亿万级用户日志加工成特征，写回特征宽表。旧方案用 ORM 逐条 INSERT，每晚要跑 4 小时且频繁与线上读写抢锁；改用分批批量 INSERT（每批约 800 行、每 3000 行提交一次），导入前删除非唯一索引、导入后重建，耗时从 4 小时降到 25 分钟，线上服务也不再被打扰。

具体案例：某推荐团队在凌晨低峰期执行特征回填任务，先 SET unique_checks=0 与 foreign_key_checks=0，卸掉全部非唯一索引，然后按 session_id 排序后分批灌入百万级特征行，导入完成重新建索引并校验唯一性，整个过程像调度搬家队一样有条不紊。

训练数据集批量入库

阶段：模型训练数据准备

解决的问题：CV 团队要把千万级样本的元数据从文本文件导入 MySQL 供训练脚本查询。若逐条 INSERT 需要数小时；用 LOAD DATA 配合临时关闭约束检查与先删后建索引，一千万行在数分钟内完成，且单事务保证失败可整体回滚。

具体案例：某 MLOps 平台将标注结果文件定时导出，再通过 LOAD DATA 灌入训练样本表，导入完成后统一重建倒排索引，支撑多轮训练迭代的样本读取，整个过程比旧方式提速约 25 倍。

Python 演示

python34 行

# -*- coding: utf-8 -*-"""比较逐条插入与批量插入的性能差异, 使用内存版 SQLite"""import sqlite3import time  def run_inserts(conn, batch_size=1, commit_every=1000):    cur = conn.cursor()    cur.execute("DROP TABLE IF EXISTS items")    cur.execute("CREATE TABLE items (id INTEGER PRIMARY KEY, val TEXT)")    total = 5000    t0 = time.perf_counter()    count = 0    for start in range(0, total, batch_size):        rows = [(i, "row_%d" % i)                for i in range(start, min(start + batch_size, total))]        sql = "INSERT INTO items (id, val) VALUES " + ", ".join(            "(?, ?)" for _ in rows)        cur.execute(sql, [v for pair in rows for v in pair])        count += len(rows)        if count % commit_every == 0:            conn.commit()    conn.commit()    return time.perf_counter() - t0  conn = sqlite3.connect(":memory:")t_single = run_inserts(conn, batch_size=1)t_batch = run_inserts(conn, batch_size=400)t_small = run_inserts(conn, batch_size=50)print("逐条插入耗时: %.3fs" % t_single)print("每批50行耗时: %.3fs" % t_small)print("每批400行耗时: %.3fs" % t_batch)print("批量相对提速: %.1f倍" % (t_single / t_batch))

目的：用 SQLite 内存库实测逐条插入与批量插入的耗时差距，验证“减少往返、批量提交”的优化原理。

for start in range(0, total, batch_size):每次处理一批数据而不是一行，是批量写入的核心循环。
sql = "INSERT INTO items (id, val) VALUES " + ", ".join("(?, ?)" for _ in rows)动态拼接多值 INSERT 语句，一个 SQL 带 B 组参数。
cur.execute(sql, [v for pair in rows for v in pair])一条语句一次发送整批数据，网络往返从 n 次降为 n/B 次。
if count % commit_every == 0: conn.commit()攒够 1000 行才提交一次，控制事务规模与刷盘频率的平衡。
t_batch = run_inserts(conn, batch_size=400)用 400 行每批的配置与逐条对比，可直观看到数量级差距。

常见误区

误区反正都是批量，那把十万行塞进一条 INSERT 不是更省事吗？一条 SQL 写完，一次提交搞定，应该最快。

正解批大小和吞吐量是倒 U 形关系。批太小时网络往返占主导；批太大时，事务锁持有时间变长、redo/undo 日志膨胀、回滚代价飙升，吞吐反而下降。可视化演示曲线已经展示，最优区间通常在每批 100 到 1000 行附近，具体值要靠压测决定。

为何常见：只看到了“减少往返”这一层收益，忽略了锁争用和日志压力这两层隐藏成本。

误区LOAD DATA 之所以快，是因为它根本不检查外键和唯一约束，所以可以把脏数据直接塞进表里。

正解LOAD DATA 默认仍会逐行检查约束，它的提速来自省去逐条 SQL 解析和网络传递，并把索引维护推迟到导入完成后统一执行。只有在显式执行 SET foreign_key_checks=0 和 SET unique_checks=0 之后，约束检查才会被临时关闭。

为何常见：把“索引延迟维护”误读成“跳过约束检查”，两者在概念上相近但实际机制完全不同。

误区既然索引拖慢插入，那建表时就别建索引，写入永保最快。

正解索引是为查询服务的，删除所有索引后写入虽快，但线上查询会退化成全表扫描。正确做法是区分场景：离线大批量导入时先删索引、导入后重建；在线业务表必须保持必要的索引结构，不能因噎废食。

为何常见：把“临时导入优化手段”错当成“长期表结构设计原则”，忽略了读写场景的差异。

误区主键用什么类型都一样，数据库会自动维护索引，UUID 全局唯一看起来更酷。

正解UUID 完全随机，插入位置在主键索引的任意页，导致 B+ 树频繁页分裂、页利用率掉到约 5/6、碎片大量产生，写入速度可能慢一个数量级。趋势递增的雪花 ID 或自增主键才是大多数业务表的稳妥选择。

为何常见：初学阶段看不到物理存储层的页分裂机制，容易忽略“逻辑上等价”背后的“物理上不等价”。

误区一个事务包住全部一百万行，要么全成要么全败，原子性最好，应该最安全。

正解原子性和事务大小是两回事。事务越大，undo 日志越膨胀，回滚代价 O(B) 增长，锁被攥在手里越久，死锁概率越高。分批提交（如每 5000 行一事务）既保留“失败可控”的边界，又避免长期持锁。

为何常见：只从逻辑层面理解原子性，没从物理层面考虑日志、锁和回滚的实际成本。

面试题

Q1. 下列哪一项是逐条 INSERT 性能低下的最主要原因？

单选题 · easy

A.SELECT查询缓存是否命中
B.网络往返与事务提交次数← 正确答案
C.索引列的数据类型长度
D.主键是否为数值类型

答案：B。逐条 INSERT 的每条语句都被当作独立请求和事务处理，n 条数据就要发生 n 次网络往返、n 次日志提交，类似于搬一件物品就下楼一趟。查询缓存与写入无关，数据类型和主键类型是次要因素，因此选 B。

Q2. 批量插入的核心收益是什么？

单选题 · easy

A.完全不提交事务
B.减少提交与网络往返次数← 正确答案
C.提交次数越多吞吐越大
D.每次恰好提交一行数据

答案：B。批量插入把多条数据放进一条 SQL，网络往返从 n 次降到 n/B 次，并按批次提交，从而减少日志刷盘次数。完全不提交会丢数据，提交越多开销越大，每次一行等于退化回逐条插入，因此选 B。

Q3. 关于 LOAD DATA 的说法，正确的是？

单选题 · easy

A.完全跳过所有约束检查
B.省去逐句解析且索引延后构建← 正确答案
C.它不需要写任何事务日志
D.它直接覆盖表数据文件

答案：B。LOAD DATA 由服务端直读文件，省去逐条 SQL 的语法解析和网络传递，并在导入完成后统一构建索引，这就是它快的主因。约束检查默认仍然执行，日志也照常写，更不会直接覆盖表文件，因此选 B。

Q4. 大批量导入时，批大小经验上常控制在什么范围？

单选题 · mid

A.每条 1 到 10 行
B.每条 100 到 1000 行← 正确答案
C.每条 10 万行以上
D.单条 SQL 行数无上限

答案：B。100 到 1000 行是工程上常见的平衡区间：批太小接近逐条插入，浪费往返；批太大会让事务与锁压力上升、日志膨胀、回滚困难，类似于纸箱太大搬不动。因此选 B。

Q5. 大批量导入前，可临时关闭哪些检查以加速？

多选题 · mid

A.外键检查← 正确答案
B.唯一约束检查← 正确答案
C.SELECT权限校验
D.归档日志检查

答案：AB。SET foreign_key_checks=0 与 SET unique_checks=0 是导入加速的经典清障动作，相当于搬家前摘掉门铃移走矮柜。权限校验和归档日志策略不能随意关闭，否则会引入安全与容灾风险，因此选 A、B。

Q6. 事务过大的主要风险是什么？

单选题 · mid

A.内存不足以容纳索引
B.日志膨胀且锁等待变长← 正确答案
C.SQL 解析流程彻底失败
D.客户端字符集出现混乱

答案：B。单个事务包含行数越多，undo 日志越膨胀，锁持有时间也越长，一旦出错回滚代价极大，相当于一辆满载卡车翻车全赔。内存索引、SQL 解析、字符集都与事务大小无直接联系，因此选 B。

Q7. 使用 UUID 作主键导致写入变慢，根本原因是什么？

单选题 · hard

A.UUID占用的存储空间过低
B.无序插入加剧页分裂与碎片← 正确答案
C.UUID无法建立任何索引
D.UUID需要徒增网络传输量

答案：B。UUID 随机无序，新行可能落在 B+ 树的任意页，频繁触发页分裂并制造碎片，页利用率降至约 5/6。空间大小不是主因，UUID 完全可以建索引，与网络传输量也无直接关系，因此选 B。

Q8. 关于 B+ 树随机插入与顺序插入，下列说法正确的是？

多选题 · hard

A.平均页利用率约为六分之五← 正确答案
B.顺序插入时页利用率接近一← 正确答案
C.随机插入会产生更多页分裂← 正确答案
D.页利用率与插入顺序没有关系

答案：ABC。随机插入导致新键散落在各处，页满即分裂，平均页利用率约 5/6；顺序插入总是落在最右页追加，利用率接近 1，分裂次数少得多。页利用率与插入顺序高度相关，因此 D 错，选 A、B、C。

Q9. 分批提交策略的核心思想是什么？

单选题 · mid

A.认为事务完全不需要提交
B.减少提交次数且控制规模← 正确答案
C.提交次数越多吞吐越大
D.每一行数据都单独提交

答案：B。分批提交既要减少日志刷盘次数，又要避免单事务过大，每 1000 到 5000 行提交一次是常见经验值。完全不提交不可取，提交越多开销越大，逐行提交等于放弃优化，因此选 B。

Q10. 导入一千万行数据时，下列哪些组合策略是合理的？

多选题 · extreme

A.关闭唯一与外键检查← 正确答案
B.导入前删非唯一索引再重建← 正确答案
C.每五千行左右提交一次事务← 正确答案
D.使用 UUID 主键顺序导入

答案：ABC。关闭约束检查能减少行级校验开销，先删后建非唯一索引能避免每行维护索引，每 5000 行提交一次能平衡日志刷盘与回滚代价。使用 UUID 主键会破坏顺序追加、加剧页分裂，与“顺序写入”目标冲突，因此 D 不合理，选 A、B、C。