MySQL高级 SQL优化【插入数据&主键优化】

目录

1：SQL优化

1.1：插入数据

1.1.1：insert

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

2). 优化方案二（手动控制事务）

3). 优化方案三 （主键顺序插入，性能要高于乱序插入。）

1.1.2：大批量插入数据

1.2：主键优化

1). 数据组织方式

2). 页分裂

3). 页合并

 4). 索引设计原则

---

1：SQL优化

1.1：插入数据

1.1.1：insert

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

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

每次插入一条数据都会与数据库建立一次连接进行网络传输，如果插入次数很多就会造成很大程度的消耗

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

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

示例， 优化方式为：

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

2). 优化方案二（手动控制事务）

由于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;

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

示例：数据有序插入

insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

优化后

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(4,'Tim');
insert into tb_test values(5,'Rose');

1.1.2：大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使 用MySQL数据库提供的load指令进行插入。操作如下：

在大批量插入数据时：可以进行三个方面的小优化

1：关闭mysql的数据唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

2：关闭事务的自动提交

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

 

3：如果导入的文件中的主键数据是有序的话也可以减少导入的效率（主键顺序插入）

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

示例：

脚本文件介绍 :
	sql1.log  ----> 主键有序
	sql2.log  ----> 主键无序

插入ID顺序排列数据：

插入ID无序排列数据：

指令实例：

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

 实例演示：

A:首先要先创建一个对应的表结构

CREATE TABLE `tb_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`birthday` DATE DEFAULT NULL,
`sex` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

B. 设置参数:

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

 C. load加载数据

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

 

我们看到，插入100w的记录，17s就完成了，性能很好。 

在load时，主键顺序插入性能高于乱序插入 

1.2：主键优化

在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的。 这一小节，就来介绍一下具体的 原因，然后再分析一下主键又该如何设计。

1). 数据组织方式

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

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

InnoDB的逻辑结构图：

 在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。 那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储 到下一个页中，页与页之间会通过指针连接。

2). 页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行 溢出)，根据主键排列。

A. 主键顺序插入效果

①. 从磁盘中申请页， 主键顺序插入

②. 第一个页没有满，继续往第一页插入 

 ③. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

 ④. 当第二页写满了，再往第三页写入

B. 主键乱序插入效果

①. 加入1#,2#页都已经写满了，存放了如图所示的数据 

②. 此时再插入id为50的记录，我们来看看会发生什么现象 会再次开启一个页，写入新的页中吗？ 

 

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

 

但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。 

 

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

移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个 页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。 

 

上述的这种现象，称之为 "页分裂"，是比较耗费性能的操作。 

3). 页合并

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

当我们对已有数据进行删除时，具体的效果如下:

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

当我们继续删除2#的数据记录

 

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

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

这个里面所发生的合并页的这个现象，就称之为 "页合并"。 

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

 4). 索引设计原则

1：满足业务需求的情况下，尽量降低主键的长度。

2：插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

3：尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

4：业务操作时，避免对主键的修改。