SQL 优化 - 插入数据与主键优化

写入侧的两条主线：插入数据要减少事务提交次数与磁盘 IO，主键决定了 InnoDB 表数据的物理组织方式。

一、SQL 优化总览

#优化点核心手段详见
1插入数据批量插入、手动提交事务、主键顺序插入；大批量数据用 load data local infile本篇
2主键优化主键尽量短、顺序插入用 AUTO_INCREMENT、避免 UUID 与自然主键本篇
3order by 优化Using index 直接返回有序数据，避免 Using filesortorder by 与 group by 优化
4group by 优化用索引分组，多字段分组满足最左前缀法则同上
5limit 优化覆盖索引 + 子查询limit / count / update 优化
6count 优化性能：count(字段) < count(主键 id) < count(1) ≈ count(*)同上
7update 优化按主键 / 索引字段更新，避免行锁升级为表锁同上

二、插入数据

2.1 批量插入

一条 insert 插入多行，减少与服务器的交互次数：

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

2.2 手动提交事务

批量插入时事务会被反复提交，改成手动提交可显著减少开销：

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;

2.3 主键顺序插入

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

主键顺序插入的性能高于乱序插入：乱序插入会触发页分裂（见第三节），顺序插入只在页写满时追加新页。

2.4 大批量插入数据

一次性插入大批量数据时，insert 性能较低，此时使用 MySQL 提供的 load 指令：

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

# 设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 执行 load 指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table `tb_user`
fields terminated by ',' lines terminated by '\n';

数据文件按分隔符逐行准备，例如：

1,jdThmKQlwu1,jdThmKQlwu,jdThmKQlwu,2020-10-13,1
2,BTJ0cWjrWi2,BTJ0cWjrWi,BTJ0cWjrWi,2020-06-12,2
3,waQTJII1HI3,waQTJII1HI,waQTJII1HI,2020-06-02,0

fields terminated by 与 lines terminated by 必须与数据文件的分隔符一致；路径为服务端可见的路径。

三、主键优化

3.1 数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table，IOT）。

逻辑存储结构自顶向下逐层包含：

层级说明
TableSpace表空间
Segment段
Extent区，1M
Page页，16K
Row行

一个区（1M）包含 64 个页（16K）；每个页中按主键顺序排列若干行，行内包含 Trx id、Roll pointer、col1、col2 … 等。

3.2 页分裂

页可以为空、可以填充一半、也可以填充 100%，每个页包含 2-N 行数据（一行数据过大时会行溢出），并根据主键排列。

  • 主键顺序插入：1# page 写入 1~8，页满后新开 2# page 写入 9~13，再开 3# page 写入 14。
  • 主键乱序插入：数据按主键应落在已满的页中间，需要申请新页、搬运部分数据、重新建立页间指针，即发生页分裂，性能较差。
主键乱序插入后的物理页（页号与主键区间不一致）：
1# page(1,5,9)  ←→  3# page(23,47,50)  ←→  2# page(55,67,89,101,107)

3.3 页合并

删除一行记录时，记录并没有被物理删除，只是被标记（flagged）为删除，其空间允许被其他记录声明使用。

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%）时，InnoDB 会开始寻找最靠近的页（前或后），判断是否可以将两个页合并以优化空间使用。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定。

3.4 主键设计原则

#原则说明
1主键尽量短满足业务需求的情况下，尽量降低主键的长度
2顺序插入插入数据时尽量顺序插入，选择 AUTO_INCREMENT 自增主键
3不用 UUID / 自然主键尽量不要使用 UUID 做主键或其他自然主键（如身份证号）
4不修改主键业务操作时避免对主键的修改

主键即聚集索引的叶子节点数据，主键越长，整棵 B+ 树的非叶子节点占用越大、越占磁盘 IO。

速查

-- 批量插入：一条 insert 插多行
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

-- 手动提交事务：减少事务反复提交的开销
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;

-- 大批量导入：客户端连接时加 --local-infile
mysql --local-infile -u root -p
set global local_infile = 1;                                  -- 开启本地文件导入开关
load data local infile '/root/sql1.log' into table `tb_user`  -- 按分隔符批量加载
fields terminated by ',' lines terminated by '\n';
场景关键点
单条 SQL 写多行insert into 表 values(...),(...),(...)
多条 SQL 批量写start transaction → 多条 insert → commit
海量数据导入load data local infile（需 --local-infile + local_infile = 1）
主键选择尽量短、顺序插入（AUTO_INCREMENT）、不用 UUID / 自然主键、不修改主键

相关

最近更新:
Contributors: liujw