加入收藏 | 设为首页 | 会员中心 | 我要投稿 湘西站长网 (https://www.0743zz.cn/)- 科技、图像技术、AI硬件、数据采集、智能营销!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

怎么优化sql中order By语句

发布时间:2023-12-29 23:28:08 所属栏目:MySql教程 来源:DaWei
导读: 这篇文章主要介绍“如何优化sql中order By语句”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“如何优化sql中order By语句&rdqu
这篇文章主要介绍“如何优化sql中order By语句”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“如何优化sql中order By语句”文章能帮助大家解决问题。

全字段排序

mysql先根据查询条件确定需要排序的数据集,也就是表中 a=1的数据集,即主键id从1到6的这些记录。

整个sql的执行的过程如下:

1.创建并初始化sort_buffer,并确定需要放到该缓冲区中的字段,也就是a,b,c这三个字段。

2.从索引树a中找到第一个满足a=1的主键id,也就是id=1。

3.回表到id索引,取出整行数据,然后从整行数据中,取出a,b,c的值,放入到sort_buffer中。

4.从索引a中按照顺序找到下一个a=1的主键id。

5.重复步骤3和步骤4,直到获取到最后一个a=1的记录,也就是主键id=5。

6.此时满足条件a=1的所有记录的 a,b,c字段,全部读放到了sort_buffer中,然后,对这些数据按照b的值进行进行排序,排序的方式是快速排序。就是那个面试经常面到的快速排序,时间复杂度为log2n的快速排序。

7.然后从排序后的结果集中取出前2行数据。

上面是就是msql中orderby的执行流程。因为放入到sort_buffer中的数据是需要输出的全部字段,所以这种排序被称为全排序。

的确,如果a=1的数据行特别多,且需要存放到sort_buffer中的字段比较多,可能不止a,b,c三个字段,有些业务可能需要输出更多字段。那么默认大小只有1M的sort_buffer很可能容纳不下。

当sort_buffer容纳不下的时候,mysql会创建一批临时的磁盘文件来辅助排序。默认情况下会创建12个临时文件,将需要排序的数据分成12份,每一份单独排序,形成12个内部数据有序的文件,然后把这12个有序文件在合并成一个有序的大文件,最终完成数据的排序。

基于文件的排序,相比基于内存的排序,排序效率要低很多,为了提高排序的效率,应该尽量避免基于文件的排序,要想避免基于文件排序,就需要让sort_buffer可以容纳需要排序的数据量。

所以对于sort_buffer容纳不下的情况,mysql进行了优化。就是在排序时候,降低存放到sort_buffer中的字段个数。

orderby的优化

上面讲述了orderby的两种排序的方式,以及一些优化策略,优化的目的主要就是避免基于磁盘文件的外部排序。因为基于磁盘文件的排序效率要远低于基于sort_buffer的内存排序。

但是当数据量比较大的时候,即使sort_buffer比较大,所有数据全部放在内存中排序,sql的整体执行效率也不高,因为排序这个操作,本身就是比较消耗性能的。

试想,如果基于索引a获取到所有a=1的数据,按照字段b,天然就是有序的,那么就不用执行排序操作,直接取出来的数据,就是符合结果的数据集,那么sql的执行效率就会大幅度增长。

其实要实现整个sql执行过程中,避免排序操作也不难,只需要创建一个a和b的联合索引即可。

alter table t1 add index a_b (a,b);
添加a和b的联合索引后,sql执行流程就变成了:

1.从索引树(a,b)中找到第一个满足a=1的主键id,也就是id=1。

2.回表到主键索引树,取出整行数据,并从中取出a,b,c,直接作为结果集的一部分返回。

3.从索引树(a,b)上取出下一个满足a=1的主键id。

4.重复步骤2和3,直到找到第二个满足a=1的主键id,并回表获取字段a,b,c。

此时我们可以通过查看sql的执行计划,来判断sql的执行过程中是否执行了排序操作。

explain select a,b from t1 where a = 1 order by b lmit 2;

通过查看执行计划,我们发现extra中已经没有了using filesort了,也就是没有执行排序操作了。

其实还可以通过覆盖索引,对该sql进一步优化,通过在索引中覆盖字段c,来避免回表的操作。

alter table t1 add index a_b_c (a,b,c);
添加索引a_b_c后,sql的执行过程如下:

1.从索引树(a,b,c)中找到第一个满足a=1的索引,从中取出a,b,c。直接作为结果集的一部分直接返回。

2.从索引(a,b,c)中取出下一个,满足a=1的记录作为结果集的一部分。

3.重复执行步骤2,直到查到第二个a=1或者不满足a=1的记录。

此时通过查看执行sql的的还行计划可以发现 extra中只有 Using index。

explain select a,b from t1 where a = 1 order by b lmit 2;
如何优化sql中order By语句

关于“如何优化sql中order By语句”的内容就介绍到这里了,感谢大家的阅读。

(编辑:湘西站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章