一文搞懂SQL优化——如何高效添加数据

一、SQL优化

1.高效添加数据的几种方式

普通插入（Insert语句）

先回顾一下向数据库中添加数据的基本操作：

当我们想要向数据库中的表tb中插入一条数据时，可以采用insert into语句：

insert into tb values(1,'value1');

当我们想要向数据库插入多条数据时，可以执行多条insert into语句：

insert into tb1 values(1,'value1');
insert into tb2 values(2,'value2');
insert into tb3 values(3,'value3');
.....

但是当想插入数据很多时，行数会非常密集，而且代码要多次请求数据库，每次请求都会消耗一定的性能，要怎样进行优化呢？

优化方案1：批量插入

• 一般情况下都采用批量插入来使得添加数据更高效
• 批量插入的思想就是把多行数据压缩成一行，只需要远程请求一次数据库，且代码更加简洁
• 但是一次性批量插入的数据建议控制在500条之内，如果多于500条，则应该分多个批次处理

Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');

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

• 通过手动控制事务添加数据有多种好处
• 一般情况下，MySQL自动为每条插入语句创建一个事务，这样可能会导致大量的日志记录，从而降低系统性能。通过手动控制事务，可以将多条执行单元合并为一个事务，从而避免了多个事务的开销。
• 手动控制事务可以帮助我们保证数据的完整性和一致性。

反例：

Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');
Insert into tb values(4,'value1'),(5,'value2'),(6,'value3');
Insert into tb values(7,'value1'),(8,'value2'),(9,'value3');

正例：

start transaction;
Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');
Insert into tb values(4,'value1'),(5,'value2'),(6,'value3');
Insert into tb values(7,'value1'),(8,'value2'),(9,'value3');
commit;

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

• 不管数据量如何，推荐采用主键顺序插入来添加数据。
• 主键顺序插入，性能要高于乱序插入。原理见后续的主键优化部分。

#主键乱序插入 : 6 2 9 7 2  
#主键顺序插入 : 1 2 4 6 8

优化方案4：load指令添加数据

如果一次性需要插入大批量数据(比如几百万的记录)，使用insert语句可能需要花费几十分钟，此时可以使用MySQL数据库提供的load指令，这个过程只需要花费几十秒。

如何采用load指令大批量添加数据？

• 如果是常规的连接数据库，只需要输入以下指令：

mysql -u root -p

• 如果需要用load指令，需要额外添加-local-infile参数：

mysql –-local-infile -u root -p

• 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关：

set global local_infile = 1;

• 我们也可以事先通过以下指令来查看local_infile全局参数是否开启：
• 如果local_infile显示为0，则表示开关并未开启，则需要手动设置为1。

select @@local_infile;

• 结果显示如下：

举个栗子：

• 假设要上传100万条数据，要上传的文件路径是'/root/load_user_100w_sort.sql'，则往表tb1中添加数据的完整load指令是：

load data local infile '/root/load_user_100w_sort.sql' into table tb1 fields terminated by ',' lines terminated by '\n' ;

• 其中，
• load data local infile是固定格式；
• into table tb1表示向表tb1添加数据；
• fields terminated by ','表示每一个字段之间采用逗号分割；
• lines terminated by '\n'表示每一行之间采用换行符分割。

2.主键优化的原理

为什么主键顺序插入的性能要大于乱序插入？

• 首先了解在InnoDB存储引擎中数据的组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
• 在索引组织表中，页是InnoDB磁盘管理的最小单元，其固定大小为16K。页可以为空，也可以填充一半，也可以填充满。每个页至少包含2行数据(如果只包含一行数据则是链表结构；如果一行数据过大超过阈值会导致行溢出)，根据主键排列。

页分裂与页合并现象

主键顺序添加数据时的过程是怎样的？

• 主键顺序插入，就是先在第一个页中填写数据，如果第一个页满了就写第二个页中，依此类推
• 从磁盘中申请一页，主键按照顺序进行插入

• 当第一页存满之后，会继续申请第二页，页与页之间通过双向指针进行连接；当第二页也存满，就会申请第三页；
  

主键乱序添加数据时的过程是怎样的？

• 主键乱序插入，不是依此往后插入，因为叶子节点主键之间是有序的，所以就产生了页分裂现象
• 假设1页和2页都已经存满，而此时主键50想要插入，不会直接写入新的页，因为索引结构的叶子节点是有顺序的。
• 按照顺序，主键50应该存储在47之后。

• 但1页显然已经存满，随后会进行“页分裂”的过程，即先开启第三页，然后将1页后一半的数据，移动到3页，然后在3页中插入50。

• 最后2#和3#两页位置互换，以符合主键排序规则。最终过程如下：

与页分裂相对，还有页合并现象：

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

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

最后2#中被标记的数据删除，同时2#和3#进行合并：

3.总结

• 插入多条数据时，尽量选择批量插入
  • 因为批量插入只需要远程请求一次数据库，且代码更加简洁
• 插入多条数据时，尽量选择手动控制事务插入
  • 因为通过手动控制事务，可以将多条执行单元合并为一个事务，从免了多个事务的开销，同时保证数据的完整性和一致性。
• 插入大量数据时，选择MySQL提供的load指令插入的效率要大于Insert语句
• 插入数据时，尽量选择主键顺序插入，选择使用AUTO_INCREMENT自增主键。
  • 因为当主键乱序插入时，会产生“页分裂”，消耗性能
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  • 因为每次生成的UUID之间无序，插入时为主键乱序插入，会产生“页分裂”，消耗性能
• 业务操作时，避免对主键的修改。
  • 因为修改主键后还需对索引结构进行修改，花费代价较大。
• 满足业务需求的情况下，尽量降低主键的长度。