MySQL中插入数据时的简单优化

写在前面：笔者这里只是将我在学习MySQL时遇到的DML和DQL语句的几种简单优化列了出来，如果以后工作了优化肯定不会这么简单。

插入优化

正常我们插入数据都是一条一条的插入，比如下面这种：

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....

这种插入是效率最低的，因为这意味着多次与数据库建立连接。但是这样一来，就会增加服务器的负荷，因为，执行每一次SQL服务器都要同样对SQL进行分析、优化等操作。

第一种优化方案：使用MySQL特有的插入方式

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

这是MySQL提供了另一种解决方案，就是使用一条INSERT语句来插入多条记录。这并不是标准的SQL语法，因此只能在MySQL中使用。

这样可以避免程序和数据库建立多次连接，从而增加服务器负荷。

第二种优化方案：使用事务进行插入多条数据

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;

这样效率高的原因是因为进行一个INSERT操作时，mysql内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。

第三种优化方案：主键顺序插入

说到主键顺序插入，就不得不提InnoDB引擎中的逻辑存储结构

如图：

在InnoDB引擎中，数据行是记录在Page页中的，而每一个Page页又是固定大小的，默认是16KB，那也就意味着，如果数据行在Page页中因为页容量不足，会新开一个页来存储，页与页之间用指针来连接。

了解了数据行的存储形式，那回到之前的问题，为什么建议主键顺序插入数据呢？

首先看看主键乱序插入的效果：

先说结论，会出现页分裂

1. 假如1#和2#页都已经写满了，存放了如图所示的数据
   

2. 此时如果我们再插入一条id为50的记录，会出现什么现象呢，会新开一个页，写入新的页中吗？

   

   不会这样的，因为索引结构的叶子节点都是有顺序的，按照顺序，应该存放在47之后。

   

   但是这时候就有一个问题，47所在的 1#页已经写满了啊，存储不了50对应的数据了，这时候就会新开辟一个新的页 3#页。

   

   但是并不会直接把50存入到3#中，而是会将1#页3后一半的数据，移动到3#页中，然后在3#页中，插入50。

   

   但此时，这三个页之间的数据顺序是有问题的。1#的下一个页，应该是3#，3#的下一个页应该是2#。所以这个时候需要重新设置链表指针。

   

上面的这种现象，称为”页分裂“，是比较耗费性能的操作。

当然，有页分裂就会有页合并，我们顺便来看看页合并的过程

页合并

目前表中已有数据的索引结构（叶子节点）如下：

当我们删除一行数据的时候，实际上记录并没有被物理删除，只是被标记为删除状态，并且它的空间变得允许其他记录声明使用。

当我门继续删除2#的时候的数据记录的时候

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

删除数据，并将页合并之后，再次插入新的数据20，则直接插入到3#页中

上面的这种现象，称为页合并

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

经过上述的页分裂和页合并，主要是页分裂，主键顺序插入效率高的原因我们就找到了，注意啊，主键是肯定有索引的啊，这是默认有的。

第四种优化方案：MySQL数据库提供的load指令进行插入

这种情况一般用于大批量数据，也就是数据存放再sql文件中，如下：

这五个sql文件中，总共存放了1000W的数据，我们需要执行的大体流程如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–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' ;

这样1000W的数据就存放到tb_user中了。