order by语句优化
Using filesort:通过表的索引或者全盘扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫做FileSort排序。
Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为Using index,不需要额外排序,操作效率高。
对于升序或者是降序排序,尽量给每一个相关的字段都设置上对应的索引,比如说一张表中,要给字段A和字段B进行排序,那么当我们需要A升序,并且当表中字段A出现相同时,再按照字段B进行升序,此时,我们在新建索引的时候,就需要这么来写创建索引的语句:create index tb_user_A_B_aa on the tb_user(A asc, B asc);这样,我们就新建了一个AB字段都是升序的索引,但我们需要A升序,B降序排序的时候,我们需要重新再新建一个B为降序的索引,不然SQL语句在排序的时候不会走索引,也就提升不了SQL性能,再新建一次索引:create index tb_user_A_B_ad on the tb_user(A asc, B dsc);即可
order by优化要点
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
- 如果不可避免地出现filesort,大数据量排序时,可以适当增大排序缓冲徐的大小sort_buffer_size(默认是256K)。
group by语句优化
group by语句优化其实也就是针对需要分组的字段建立索引
- 在分组操作时,可以通过索引来提高效率
- 分组操作时,索引的使用也是满足最左前缀法则的
limit语句优化
limit在大数据量下进行分页查询,会消耗很长的查询时间,越往后性能越低。比如说需要前2000010的记录,仅仅返回2000000—2000010条记录,其他记录都丢弃,这样查询排序的代价非常大。
优化的思路:一般分页查询,通过创建覆盖索引可以比较好的提高性能,也可以通过覆盖索引加子查询(多表联查)的形式进行优化。
比如说如下图所示,子查询的语句中设置好对应的索引,然后返回主键。
接下来再进行多表查询,将上图的查询到的结果,当做一张表来进行多表查询即可
count语句优化
count有几种用法:
- count(主键):InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层,服务层拿到主键后,直接按行进行累加
- count(字段):
- 当没有not null约束时:InnoDB引擎会遍历整张表把每一行的字段都提取出来,返回给服务层,服务层判断是否为null,不为则计数增加。
- 当有null约束:InnoDB引擎会遍历整张表,把每一行的字段值都取出来,返回给服务器,直接进行累加。
- count(1):InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,然后直接按行进行累加。count()里边放什么参数,服务层就会放什么数字进去0,-1都是一样的。
- count(*):InnoDB引擎并不会把全部字段取出来,而是做了专门的优化,不取值,服务层直接按行进行累加。
所以最终按效率来排序的话:count(*) ≈ count(1)>count(主键id)>count(字段),所以应尽量使用count(*)。
update语句优化
我们都知道InnoDB的三大特性:主键、事务、行级锁
在对数据进行更新的时候,一定要根据索引字段来更新,因为当你where后面的字段如果说没有索引,那么行锁将会升级成表锁,从而降低并发性能。
InnoDB的行锁是针对索引加的锁,而不是针对记录加锁,并且索引不能失效,否则会从行级锁升级为表级锁。
总结
本篇文章仅作为学习笔记,供大家复习或者参考,学习源来自于B站黑马程序员下的MySQL数据库教程39. 进阶-SQL优化-小结_哔哩哔哩_bilibili