MySQL - 进阶篇 - 3. SQL优化

最新推荐文章于 2025-11-24 15:06:53 发布

原创最新推荐文章于 2025-11-24 15:06:53 发布 · 977 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#android

MySQL 专栏收录该内容

13 篇文章

订阅专栏

MySQL - 进阶篇 - 3. SQL优化

3.1 插入数据

在日常业务中，数据插入的性能优化常常被忽视。但在大规模数据导入、批量写入、日志归档等场景中，插入性能的差异可能直接决定任务能否在规定时间内完成。本节我们从 普通插入、多条插入、事务控制以及大批量数据加载 四个维度来探讨 MySQL 的优化手段。

3.1.1 insert

如果我们需要一次性向数据库表中插入多条记录，可以从以下三个方面进行优化：

1) 优化方案一：批量插入数据

传统写法：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

批量插入写法：

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

👉 优势：减少了 SQL 解析、执行计划生成的次数；网络通信也更少，性能显著提升。

2) 优化方案二：手动控制事务

MySQL 默认情况下，每条 SQL 都是一个独立事务，会产生大量的事务开销。如果能在批量插入时显式开启事务，将多条插入语句合并到同一个事务中，可以大幅减少提交的次数。

示例：

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

👉 优势：显著减少事务提交次数，提升写入性能。

3) 优化方案三：主键顺序插入

在 InnoDB 中，主键索引是聚簇索引。如果插入数据时主键是乱序的，B+树频繁发生分裂与页移动，会大幅降低写入性能。

例如：

乱序插入：8, 1, 9, 21, 88, 2, 4, 15, 89, 5, 7, 3
顺序插入：1, 2, 3, 4, 5, 7, 8, 9, 15, 21, 88, 89

👉 结论：主键顺序插入性能要高于乱序插入。

3.1.2 大批量插入数据

如果一次性需要插入大批量数据（例如：百万级、千万级），单纯使用 insert 效率较低，这时可以使用 MySQL 提供的 LOAD DATA 语句。

A. 创建表结构

create table tb_user(
  id int primary key auto_increment,
  name varchar(50),
  age int
);

B. 客户端连接

mysql --local-infile -u root -p

C. 开启参数

set global local_infile = 1;

D. 使用 LOAD DATA 导入数据

load data local infile '/root/sql1.log'
into table tb_user
fields terminated by ','
lines terminated by '\n';

实验效果

插入 100w 条记录，只需要 17 秒 就能完成。
在 load 的过程中，同样遵循 主键顺序插入性能高于乱序插入 的规律。

✅ 小结：

批量插入 + 事务控制，可以显著提升小规模数据写入性能；
对于大批量数据导入，推荐使用 LOAD DATA，远快于 insert；
插入时保持 主键有序，可以避免聚簇索引频繁分裂，进一步提升性能。

理解

1. 理论理解

插入数据的性能优化，本质上围绕 减少额外开销、顺应存储结构 两个方面展开。

批量插入：减少 SQL 解析、执行计划生成与网络交互的次数。单条多值 insert 与多条单值 insert 的差距，就在于避免了重复的解析和通信成本。
事务合并：InnoDB 每次提交事务都涉及日志写入（redo/undo log）、刷盘（fsync），这些是昂贵的操作。显式开启事务后，能让多条插入共享一次事务提交，从而降低日志与磁盘写入成本。
主键顺序插入：InnoDB 聚簇索引的叶子节点存储整行数据，插入主键时会顺序写入页中。如果主键乱序，会触发页分裂与页移动，导致额外的磁盘 I/O 和锁竞争，从而显著降低性能。
LOAD DATA：相比 insert，LOAD DATA 直接以批量方式从文件加载数据，绕过了部分 SQL 层解析与逐条插入的开销，其性能接近于底层文件写入，是 MySQL 中最快的批量导入方式。

👉 理论上的核心要点是：批量化 + 顺序化 + 避免重复日志写入，这是优化插入性能的基本规律。

2. 大厂实战理解

在大厂业务中，数据写入场景普遍存在于 日志归档、用户行为数据落库、订单流水存储、历史数据迁移 等。此时，写入性能不再是“可选优化”，而是直接影响业务可用性与稳定性。

日志与埋点系统：在字节跳动的埋点系统中，用户行为数据是按天批量导入的，规模往往在千万到亿级。如果单纯使用 insert，导入时间可能是小时级，而使用 LOAD DATA 可以缩短到分钟级甚至秒级。
电商订单系统：阿里系在订单库的优化实践中，会强制规定主键必须是趋势递增（如雪花算法生成的 ID），避免随机 UUID 导致 B+ 树频繁分裂，确保高并发写入时的稳定性能。
金融交易系统：银行、支付类业务需要写入数亿条交易流水。为了保证高效与安全，往往采取 批量事务提交 + 主从分库分表 的组合方式。一次事务承载上万条写入，并通过顺序主键降低索引开销。
数据迁移与清洗：在大规模离线数据迁移中（如数据仓库同步到 OLTP 数据库），几乎所有大厂都会选择 LOAD DATA 或 批量批次写入，并临时关闭二级索引或外键约束，在导入完成后再重建索引，从而极大提升导入效率。

👉 大厂结论：

小规模写入：批量 insert + 手动事务 + 顺序主键 是标配；
大规模导入：LOAD DATA + 顺序主键 + 索引延迟创建 才是最优解。

大厂面试题

问题 1：

为什么在插入大量数据时，insert into ... values(...),(...),(...); 的性能高于多条单独的 insert？

参考答案：
单条多值 insert 可以减少 SQL 解析、执行计划生成和网络通信的开销。例如，插入 1000 条记录，如果写成 1000 条 insert，就要进行 1000 次语法解析、执行计划生成和 TCP 通信，而批量 insert 只需要一次。这种减少额外开销的方式，使得性能提升往往在数量级上。

问题 2：

为什么显式控制事务能提升批量插入性能？

参考答案：
InnoDB 默认情况下，每条 SQL 都是一个独立事务，每次执行都会产生一次 redo log 和 binlog 刷盘操作，代价昂贵。显式使用 START TRANSACTION ... COMMIT 可以把多条 insert 放到同一个事务里，所有日志和刷盘只在 commit 时进行一次，极大降低了 I/O 次数。

问题 3：

为什么主键顺序插入性能高于乱序插入？

参考答案：
InnoDB 的聚簇索引是按主键顺序组织数据的。如果主键顺序插入，数据会顺序写入页，磁盘 I/O 命中率高；而乱序插入会导致频繁的页分裂和数据移动，带来额外的 CPU 和磁盘开销。在大规模写入时，乱序插入可能导致 TPS 下降一个数量级。

问题 4：

在面对上千万条数据需要一次性插入时，为什么推荐使用 LOAD DATA 而不是 insert？

参考答案：
LOAD DATA 是 MySQL 提供的批量装载机制，它绕过了逐条 insert 的 SQL 解析与事务开销，直接将数据文件批量写入表中。其底层机制接近文件导入，因此性能远超 insert。在实际测试中，插入 100 万条数据，insert 可能需要数分钟，而 LOAD DATA 可以在十几秒内完成。

问题 5：

在大批量数据导入时，除了使用 LOAD DATA，还能做哪些优化？

参考答案：

关闭自动提交：set autocommit=0;，避免每条记录单独提交。
暂时关闭二级索引和外键约束，导入完成后再重建。
使用顺序主键，避免 B+ 树频繁分裂。
增大 innodb_buffer_pool_size 和 innodb_log_buffer_size，提高缓冲区利用率。
分批导入，避免单次事务过大导致回滚代价过高。

大厂场景题

场景一：电商订单批量导入

某电商平台在 双十一 活动中，需要将第三方支付网关返回的 1000 万条支付流水 导入订单数据库。开发最初采用逐条 insert 的方式，导入耗时接近 8 小时，影响了订单对账。

问题：如何优化？
解析：

使用批量 insert 替代逐条 insert，将 SQL 数量从千万级降低到百万级。
显式控制事务，减少频繁 commit 带来的 I/O 开销。
如果数据已经在文件中，可直接使用 LOAD DATA，将导入时间从小时级缩短到分钟级。
👉 在阿里、京东等大厂中，类似场景几乎都采用 LOAD DATA + 顺序主键，保证账务处理能在 T+0 时间完成。

场景二：金融系统交易流水写入

银行系统每天产生 数亿条交易流水，存入交易表 transaction_log。主键采用随机 UUID，导致写入时聚簇索引频繁分裂，TPS（每秒事务数）骤降。

问题：为什么会出现性能瓶颈？如何解决？
解析：

随机 UUID 破坏了 B+ 树的顺序性，每次写入都会导致页分裂与重排。
优化方案：改用趋势递增的雪花算法 ID 或自增主键，保证插入顺序，提升 I/O 命中率。
👉 在蚂蚁金服、银联等金融业务场景中，主键顺序写入是硬性规范，否则在高并发场景下会造成严重写入阻塞。

场景三：社交平台批量导入历史数据

某社交平台在上线前，需要将 1 亿条历史聊天记录 导入消息表 message。如果逐条 insert，迁移需要数天，无法满足上线窗口。

问题：如何快速完成数据迁移？
解析：

使用 LOAD DATA 批量导入消息文本文件，速度提升百倍。
在导入前暂时关闭二级索引和外键约束，导入完成后再重建索引。
分批提交事务，避免单事务过大导致回滚开销过高。
👉 字节、腾讯在 IM 系统中导入历史消息时，都会采用 LOAD DATA + 分区分表 + 批量事务 的组合方案。

场景四：日志系统埋点写入

某视频平台每天产生 几十亿条埋点日志，需要实时落库。若直接单条 insert，每秒几千 QPS 就会拖垮数据库。

问题：如何设计写入方案？
解析：

应用层合并日志，使用批量 insert（每次写入上千条）。
数据库端使用分库分表，避免单表写入成为瓶颈。
对离线埋点数据，统一以文件形式落盘，然后定时用 LOAD DATA 导入。
👉 在大厂中，埋点数据通常不会直接写入核心 MySQL，而是先入 Kafka，再通过 Flink 等 ETL 工具批量落地，最终写入 MySQL 或数仓。

✅ 总结：