插入优化
- 批量插入 在进行批量插入时,建议单次的数据量在500-1000左右即可。如果数据量比较大,最好将其分割成多条语句进行分批插入。
- 手动提交事务
- 主键顺序插入
- 大批量插入数据 如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MVSOL数据库提供的l0ad指令进行插入。操作如下:
#在连接数据库时,添加--local-infile
mysql --loacl-infile -u root -p
#连接数据库成功后,设置全局参数local_infile为1,开启从本地加载文件导入数据开关
set global local_infile =1
#执行load命令将准备好的数据插入到数据库中
load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n'
主键优化
数据组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table loT)
页分裂
页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据多大,会行溢出),根据主键排列。
页合并
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(faged)为删除并目它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
主键设计原则
- 满足业务需求的情况下,尽量降低主键的长度。
- 插入数据时,尽量选择顺序插入,选择使用AUTOINCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
- 业务操作时,避免对主键的修改。
order by 优化
using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 filesort排序。
using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。
在分析sql的执行效率时,最好是using index。
在创建索引的时候,或默认制定排序方式为升序,所以在进行查询的时候,包含索引中查询的字段,最好都是统一升序,或者 降序。如果一升一降就会出现filesort情况。
解决方法是:在mysql8版本的时候,在创建索引时可以在字段后面指定排序方式,来进行解决。
总结
:
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort buffer size(默认256k)。
group by 优化
注意:分组查询同样需要遵循最左前缀法则,但是当创建的联合索引时,不满足最左前缀法则,也会使用索引,不过会引用一个临时表,导致性能被影响。
分页优化
在大数据量的情况下,使用分页查询时,需要考虑优化问题。
一个常见又非常头疼的问题就是limit 2000000,10,此时需要MVSOL排序前2000010记录,仅仅返回2000000-2000010 的记录,其他记录丢弃,查询排序的代价非常大
count 优化
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*)的时候会直接返回这个数,效率很高;
InnoDB 引擎就麻烦了,它执行 count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
count的几种用法
count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count 函数的参数不是 NULL,累计值就加 1,否则不加,最后返回累计值。
- count(*)
InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
- count(主键)
InnoDB 引擎会遍历整张表,把每一行的 主键id 值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
- count(字段)
没有not null 约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
有not null 约束:InnoD8 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。
- count(1)
InnoD8 引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。
按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以尽量使用 count(*)。
update 优化
在执行更新语句的时候,如果添加的条件在索引中,那么该次锁为行锁。如果不存在就会升级为表锁。
对于并发的性能就会造成影响。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁