插入数据 insert
如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。
1). 优化方案一
批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
2). 优化方案二
手动控制事务
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
语句插入性能较低,此时可以使
用
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' ;
主键优化
在上一小节,我们提到,主键顺序插入的性能是要高于乱序插入的。 这一小节,就来介绍一下具体的原因,然后再分析一下主键又该如何设计。
1). 数据组织方式
在
InnoDB
存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表
(index organized table IOT)
。
2). 页分裂
页可以为空,也可以填充一半,也可以填充
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#
页
索引设计原则
满足业务需求的情况下,尽量降低主键的长度。
插入数据时,尽量选择顺序插入,选择使用
AUTO_INCREMENT
自增主键。
尽量不要使用
UUID
做主键或者是其他自然主键,如身份证号。
因为身份证号是无序的。
业务操作时,避免对主键的修改。
order by优化
Using filesort :
通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区
sort
buffer
中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫
FileSort
排序。
Using index :
通过有序索引顺序扫描直接返回有序数据,这种情况即为
using index
,不需要
额外排序,操作效率高。
A.
根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
B.
尽量使用覆盖索引。在selcet的时候避免使用*,选择where后面有的数据。
C.
多字段排序
,
一个升序一个降序,此时需要注意联合索引在创建时的规则(
ASC/DESC
)。
D.
如果不可避免的出现
filesort
,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认
256k)
。
group by优化
分组操作,我们主要来看看索引对于分组操作的影响。
A.
在分组操作时,可以通过索引来提高效率。
B.
分组操作时,索引的使用也是满足最左前缀法则的。
limit优化
覆盖索引:
优化思路
:
一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查
询形式进行优化。
count优化
MyISAM
引擎把一个表的总行数存在了磁盘上,因此执行
count(*)
的时候会直接返回这个数,效率很高; 但是如果是带条件的count
,
MyISAM
也慢。
InnoDB
引擎就麻烦了,它执行
count(*)
的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
如果说要大幅度提升
InnoDB
表的
count
效率,主要的优化思路:自己计数
(
可以借助于
redis
这样的数 据库进行,
但是如果是带条件的
count
又比较麻烦了。
按照效率排序的话,
count(
字段
) < count(
主键
id) < count(1) ≈ count(*)
,所以尽量使用 count(*)
。