1.插入数据
环境准备,创建表tb_test
create table tb_test
(
id int not null comment '主键' primary key,
name varchar(20) null comment '姓名',
gender char null comment '性别 默认1 表示男'
)comment '测试';
1.1. insert
思考:一次性往数据库表中插入多条记录,需要怎么优化?
可以从以下三方面进行优化:
1.1.1. 批量插入
优化方案一:批量插入数据
Insert into tb_test values(1,'Tom','1'),
(2,'Cat','1'),
(3,'Jerry','1');
1.1.2. 手动控制事务
优化方案二:手动控制事务
start transaction;
insert into tb_test values(4,'Tom','0'),(5,'Cat','0'),(6,'Jerry','0');
insert into tb_test values(7,'Tom','0'),(8,'Cat','0'),(9,'Jerry','0');
commit;
1.1.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.主键优化
前面我们说主键顺序插入的性能是要高于乱序插入的。现在具体说明一下原因,然后再分析一下主键又该如何设计。
2.1. 数据组织方式
InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)
行数据,都是存储在聚集索引的叶子节点上的。InnoDB的逻辑结构图:
在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。
也就是一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储 到下一个页中,页与页之间会通过指针连接。
2.2. 页分裂
页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列
2.2.1. 主键顺序插入效果
- 1.从磁盘中申请页, 主键顺序插入
- 2.第一个页没有满,继续往第一页插入
- 当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接
- 当第二页写满了,再往第三页写入
2.2.2. 主键乱序插入效果
- 假设 1#,2#页都已经写满了,存放了如图所示的数据
- 此时再插入id为50的记录,我们来看看会发生什么现象思考:会再次开启一个页,写入新的页中吗?
不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后
实际插入方式
但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#
但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。
移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1#的下一个页,应该是3#, 3#的下一个页是2#。 所以,此时,需要重新设置链表指针。
上面过程称之为 "页分裂",是比较耗费性能的操作
2.2.3. 页合并
假设表中已有数据的索引结构(叶子节点)如下
当我们对已有数据进行删除时,具体的效果如下: 当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用
当我们继续删除2#的数据记录
当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使
删除数据,并将页合并之后,再次插入新的数据21,则直接插入3#页
上面过程称之为 "页合并"
特别说明;MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。
标签:存储,顺序,优化,SQL,tb,插入,Mysql,数据,主键 From: https://blog.51cto.com/maguobin/8262047