您的位置: 翼速应用 > 业内知识 > 数据库 > 正文

看完这篇教会你优化sql中order By语句

在sql中如何优化orderBy语句?我们在使用数据库进行数据查询时,难免会遇到基于某些字段对查询的结果集进行排序的需求。在sql中通常使用orderby语句来实现。下面一起来看一下具体的优化方法。


看完这篇教会你优化sql中order By语句


看完这篇教会你优化sql中order By语句


将需要排序的字段放到 该关键词后,如果有多个字段的话,就用","分割。


select * from table t order by t.column1,t.column2;


上面的sql表示查询表table中数据,然后先按照column1排序,如果column1相同的话,在按照column2排序,排序的方式默认是降序。当然排序方式也是可以指定的。在被排序字段后添加 DESC,ASE,分别表示降序和升序。


使用该orderby可以很方便的实现日常的排序操作。使用的多了,不知道你有没有遇到过这种场景:有时候使用orderby后,sql执行效率非常慢,有时候却比较快,由于整天被curd缠身,也没有时间研究,反正就是觉得很神奇。趁这个周末比较闲,就来研究下,mysql中orderby是怎么实现的。


为了方便描述,我们先建立一个数据表 t1,如下:


CREATE TABLE `t1` (
  `id` int(11) NOT NULL not null auto_increment,
  `a` int(11)  DEFAULT NULL,
  `b` int(11)  DEFAULT NULL,
  `c` int(11)  DEFAULT NULL,
  PRIMARY KEY (`id`) ,
  KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB;


并插入数据:


insert into t1 (a,b,c) values (1,1,3);
insert into t1 (a,b,c) values (1,4,5);
insert into t1 (a,b,c) values (1,3,3);
insert into t1 (a,b,c) values (1,3,4);
insert into t1 (a,b,c) values (1,2,5);
insert into t1 (a,b,c) values (1,3,6);


为了使索引生效,插入10000行 7,7,7,无关数据,数据量少的情况下,会直接全表扫描


insert into t1 (a,b,c) values (7,7,7);


我们现在需要查找 a=1的所有记录,然后按照b字段进行排序。


查询sql为


select a,b,c from t1 where a = 1 order by b limit 2;


为了防止在查询过程中全表扫描,我们在字段a上添加了索引。


首先我们先通过语句


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


查看sql的执行计划,如下所示:


查看sql的执行计划


在extra中我们可以看到出现了Using filesort,这个表示 该sql执行过程中,执行了排序操作,排序操作在 sort_buffer中完成,sort_buffer是mysql分配给每个线程的一个内存缓冲区,该缓冲区专门用来完成排序,大小默认是1M,其大小由变量 sort_buffer_size 进行控制。


mysql在对orderby进行实现时,根据放入到sort_buffer中的字段内容不同,进行了两种不同实现方式:全字段排序和rowid排序。


全字段排序


首先我们先通过一张图整体看一下sql执行过程:


sql执行过程


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中的数据是需要输出的全部字段,所以这种排序被称为全排序。


看到这里不知道你是否会有疑问?如果需要排序的数据量很大的话,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中的字段个数。


具体优化方式,就是下面的rowId排序


RowId 排序


在全字段排序实现中,排序的过程中,要把需要输出的字段全部放到sort_buffer中,当输出的字段比较多的时候,可以放到sort_buffer中的数据行就会变少。也就增大了sort_buffer无法容纳数据的风险,直至出现基于文件的排序。


rowId排序对全字段排序的优化手段,主要是减少了放到sort_buffer中字段个数。


在rowId排序中,只会将需要排序的字段和主键Id放到sort_buffer中。


select a,b,c from t1 where a = 1 order by b limit 2;


在rowId的排序中的执行流程如下:


1.初始化并创建sort_buffer,并确认要放入的的字段,id和b。


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


3.回表主键索引id,取出整行数据,从整行数据中取出id和b,存入sort_buffer中。


4.从索引a中取出下一条满足a=1的 记录的主键id。


5.重复步骤3和4,直到最后一个满足a=1的主键id,也就是a=6。


6.对sort_buffer中的数据,按照字段b排序。


7.从sort_buffer中的有序数据集中,取出前2个,因为此时取出的数据只有id和b,要想获取a和c字段,需要根据id字段,回表到主键索引中取出整行数据,从整行数据中获取需要的数据。


根据rowId排序的执行步骤,可以发现:相比全字段排序,rowId排序的实现方式,减少了存放到sort_buffer中的数据量,降低了基于文件的外部排序的可能性。


那rowid排序有不足的地方吗?肯定有的,要不然全字段排序就没有存在的意义了。rowid排序不足之处在于,在最后的步骤7中,增加了回表的次数,不过这个回表的次数,取决于limit后的值,如果返回的结果集比较小的话,回表的次数还是比较小的。


mysql是如何在全字段排序和rowId排序的呢?其实是根据存放的sort_buffer中每行字段的长度决定的,如果mysql认为每次放到sort_buffer中的数据量很大的话,那么就用rowId排序实现,否则使用全字段排序。那么多大算大呢?这个大小的阈值有一个变量的值来决定,这个变量就是 max_length_for_sort_data。如果每次放到sort_buffer中的数据大小大于该字段值的话,就使用rowId排序,否则使用全字段排序。


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;


查看sql的执行计划


通过查看执行计划,我们发现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语句之详细教程,翼速应用平台内有更多相关资讯,欢迎查阅!



我来说两句

0 条评论

推荐阅读

  • 响应式布局CSS媒体查询设备像素比介绍

    构建响应式网站布局最常见的是流体网格,灵活调整大小的站点布局技术,确保用户在使用的幕上获得完整的体验。响应式设计如何展示富媒体图像,可以通过以下几种方法。

    admin
  • 提升网站的性能快速加载的实用技巧

    网站速度很重要,快速加载的网站会带来更好的用户体验、更高的转化率、更多的参与度,而且在搜索引擎排名中也扮演重要角色,做SEO,网站硬件是起跑线,如果输在了起跑线,又怎么跟同行竞争。有许多方法可提升网站的性能,有一些技巧可以避免踩坑。

    admin
  • 织梦CMS TAG页找不到标签和实现彩色标签解决方法

    织梦cms是我们常见的网站程序系统的一款,在TAG标签中常常遇到的问题也很多。当我们点击 tags.php 页的某个标签的时候,有时会提示:“系统无此标签,可 能已经移除!” 但是我们检查程序后台,以及前台显示页面。这个标签确实存在,如果解决这个问题那?

    admin
  • HTML关于fieldset标签主要的作用

    在前端开发html页面中常用的标签很多,今天为大家带来的是关于HTML中fieldset标签主要的作用说明,根据技术分析HTML

    admin

精选专题