SQL优化——order by、group by、limit、count、update语句优化

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(字段):
  1. 当没有not null约束时:InnoDB引擎会遍历整张表把每一行的字段都提取出来,返回给服务层,服务层判断是否为null,不为则计数增加。
  2. 当有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


  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值