- insert
如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。
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');
- 优化方案二
手动控制事务
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;
- 优化方案三
主键顺序插入,性能要高于乱序插入。
主键乱序插入 : 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语句插入性能较低,此时可以使用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' ;
主键顺序插入性能高于乱序插入
实例演示:
- 创建表结构
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 ;
- 设置参数
-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
- load加载数据
load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
mysql> load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
Query OK, 1000000 rows affected (15.47 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.31 sec)
我们看到,插入100w的记录,15.47s就完成了,性能很好。
在load时,主键顺序插入性能高于乱序插入
主键优化
主键顺序插入的性能是要高于乱序插入的。我们来介绍一下具体的原因,然后再分析一下主键又该如何设计。
- 数据组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)。
行数据,都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图:
在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。那也就意味着, 一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储到下一个页中,页与页之间会通过指针连接。
- 页分裂
页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列。
-
主键顺序插入效果
-
从磁盘中申请页, 主键顺序插入
-
第一个页没有满,继续往第一页插入
-
当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接
-
- 当第二页写满了,再往第三页写入
-
主键乱序插入效果
- 加入1#,2#页都已经写满了,存放了如图所示的数据
-
此时再插入id为50的记录,我们来看看会发生什么现象
会再次开启一个页,写入新的页中吗?
不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后。
但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。
移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1#的下一个 页,应该是3#, 3#的下一个页是2#。 所以,此时,需要重新设置链表指针。
上述的这种现象,称之为 "页分裂",是比较耗费性能的操作。
-
页合并
- 目前表中已有数据的索引结构(叶子节点)如下:
-
当我们对已有数据进行删除时,具体的效果如下:
-
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
- 当我们继续删除2#的数据记录
- 当页中删除的记录达到
MERGE_THRESHOLD
(默认为页的50%),InnoDB会开始寻找最靠近的页(前 或后)看看是否可以将两个页合并以优化空间使用。
- 删除数据,并将页合并之后,再次插入新的数据21,则直接插入3#页
- 这个里面所发生的合并页的这个现象,就称之为 "页合并"。
知识小贴士:
MERGE_THRESHOLD
:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。