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

最新推荐文章于 2024-07-24 14:50:40 发布

学个Jvav

最新推荐文章于 2024-07-24 14:50:40 发布

阅读量1.5k

点赞数

分类专栏： SQL优化文章标签： sql 数据库

本文链接：https://blog.csdn.net/Fover_Night/article/details/128357495

版权

SQL优化专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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(*)。