前言
本文仅个人的学习所记随笔,如有不对之处,还有各位大佬斧正!谢谢!
1 插入数据
-
少量数据
-
批量插入数据
-
手动控制
-
主键顺序插入,性能要高于乱序插入
-
-
大量数据(几百万)
-
使用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'
在load时,主键顺序插入性能高于乱序插入
-
2 主键优化
-
数据组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表 (index organized table IOT)
在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。那也就意味着, 一个页中所存储的行也是有限的,如果插入的数据行 row 在该页存储不小,将会存储到下一个页中,页与页之间会通过指针连接
1 页分裂
页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列
-
主键顺序插入效果
-
从磁盘中申请页,主键顺序插入
-
第一个没有满,继续往第一页插入
-
当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接
-
当第二页写满了,再往第三页写入
-
-
主键乱序插入效果
-
加入1#,2#页都已经写满了,存放了如图所示的数据
-
此时再插入id为50的记录,因为索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后
但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#
但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50
移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1#的下一个页,应该是3#,3#的下一个页是2#。 所以,此时,需要重新设置链表指针
上述的这种现象,称之为 "页分裂",是比较耗费性能的操作。
-
2 页合并
目前表中已有数据的索引结构(叶子节点)如下:
当我们对已有数据进行删除时,具体的效果如下:
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间 变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
删除数据,并将页合并之后,再次插入新的数据20,则直接插入3#页
这个里面所发生的合并页的这个现象,就称之为 "页合并"。
MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。
3 order by优化
MySQL的排序,有两种方式:
-
Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
-
Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高
对于以上的两种排序方式,Using index的性能高,而Using filesort的性能低,我们在优化排序操作时,尽量要优化为 Using index。
order by优化原则:
-
根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
-
尽量使用覆盖索引
-
多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
-
如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)
4 group by优化
group by优化原则:
-
在分组操作时,可以通过索引来提高效率
-
分组操作时,索引的使用也是满足最左前缀法则的
5 limit优化
当在进行分页查询时,如果执行 limit 2000000,10 ,此时需要MySQL排序前2000010记 录,仅仅返回 2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大。
优化思路:
-
一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
select *
from a_table a, (select id from a_table order by id limit 2000000,10) b
where a.id = b.id;
6 count优化
-
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;但是如果是带条件的count,MyISAM也慢
-
InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数
count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加,最后返回累计值
用法:count(*)、count(主键)、count(字段)、count(数字)
count用法 | 含义 |
---|---|
count(主键) | InnoDB 引擎会遍历整张表,把每一行的主键id 值都取出来,返回给服务层。 服务层拿到主键后,直接按行进行累加(主键不可能为null) |
count(字段) | 没有not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加 有not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加 |
count(数字) | InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字 “1” 进去,直接按行进行累加 |
count(*) | InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加 |
InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
7 update优化
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁