`
liulanghan110
  • 浏览: 1061256 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

order by与索引

阅读更多

ORDER BY 通常会有两种实现方法,一个是利用有序索引自动实现,也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。

用有序索引这种,当然是最快的,不过有一些限制条件,来看下面的测试。

 

测试数据:student表有两个字段id ,sid ,id是主键。一共有20W条记录,id从1到200000,sid也是从1到200000的数据。

 

第一种情况 :

 

order by的字段不在where条件也不在select中

 

select sid from zhuyuehua.student where sid < 50000 order by id;


 

第二种情况 :

 

order by的字段不在where条件但在select中。

 

select id,sid from zhuyuehua.student where sid < 50000 order by id;


 

第三种情况 :

 

order by的字段在where条件但不在select中。

 

select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id;


 

 

第四种情况 :

 

order by的字段在where条件但不在select中。倒序排列

 

select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc;

 


 

测试结果:

 

order by的字段不在where条件不在select中     有排序操作

 

order by的字段不在where条件但在select中     有排序操作

 

order by的字段在where条件但不在select中     无排序操作

 

order by的字段在where条件但不在select中(倒序)     无排序操作

 

结论:

 

当order by 字段出现在where条件中时,才会利用索引而无需排序操作。其他情况,order by不会出现排序操作。

 

分析:

 

为什么只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。这要说到数据库如何取到我们需要的数据了。

一条SQL实际上可以分为三步。

1.得到数据

 

2.处理数据

 

3.返回处理后的数据

 

比如上面的这条语句select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc

第一步:根据where条件和统计信息生成执行计划,得到数据。

 

第二步:将得到的数据排序。

 

当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则排序操作。

 

第三步:返回排序后的数据。

 

另外:

 

上面的5万的数据sort只用了25ms,也许大家觉得sort不怎么占用资源。可是,由于上面的表的数据是有序的,所以排序花费的时间较少。如果 是个比较无序的表,sort时间就会增加很多了。另外排序操作一般都是在内存里进行的,对于数据库来说是一种CPU的消耗,由于现在CPU的性能增强,对 于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上百万条以上时,你需要注意是否一定要这么做了,大记录集排序不仅增加了 CPU开销,而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降。

 

注:ORACLE或者DB2都有一个空间来供SORT操作使用(上面所说的内存排序),如ORACLE中是用户全局区(UGA),里面有SORT_AREA_SIZE等参数的设置。如果当排序的数据量大时,就会出现排序溢出(硬盘排序),这时的性能就会降低很多了。

 

总结:

 

当order by 中的字段出现在where条件中时,才会利用索引而不排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。

 

这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。

0
2
分享到:
评论

相关推荐

    MySQL Order By索引优化方法

    在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序

    MySQL利用索引优化ORDER BY排序语句的方法

    创建表&创建索引 ...MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT

    MySQL 通过索引优化含ORDER BY的语句

    关于建立索引的几个准则: 1、合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度。 2、索引越多,更新数据的速度越慢。 3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以...

    MYSQL order by排序与索引关系总结1

    1.如果索引了多列,要遵守最左前缀法则 2.当MySQL一旦估计检查的行数可能会”太多”,范围查找优化将不会被 3.索引列不应该作为表达式的一部分,即也不能在索

    sql学习 索引特性之有序优化order by.sql

    sql学习 索引特性之有序优化order by.sql

    【mysql知识点整理】— order by 、group by 出现Using filesort原因详解

    group by什么时候会出现Using filesort — 理论3 order by 和 group by什么时候会出现Using filesort — 实践3.1 不会出现 Using filesort的情况 — 符合最佳左前缀法则3.1.1 order by也满足索引最佳左前缀法则的...

    mysql中提高Order by语句查询效率的两个思路分析

    因为可能需要对数据库的记录进行重新排序。在这篇文章中,笔者就谈谈提高Order By语句查询效率的两个...在条件允许的情况下,笔者建议最好使用一个索引来满足Order By子句。如此的话,就可以避免额外的排序工作。这里笔

    MySQL优化GROUP BY(松散索引扫描与紧凑索引扫描)

    满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后... 由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组

    MySQL order by性能优化方法实例

     当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 rang,ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。...

    覆盖索引及案例

    覆盖索引 技巧 1. 创建覆盖索引的方法 2. 使用Order by和distinct后,如何创建覆盖索引

    mysql索引优化分享

    关于mysql索引一些优化介绍与创建原则,还有对order by排序的算法的介绍等等

    MySQL中索引优化distinct语句及distinct的多字段操作

    MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个...

    【MySQL】经验:索引使用场景

    一、适合用索引的场景 ...这里要注意,order by的字段出现在where条件中才能使用索引,否则索引失效。 5、查询中的统计、分组字段 group by和union也属于需要排序的操作,这里也要注意字段出现在whe

    在sql中不指定Order by排序是按照主键吗

    在sql中不指定Order by,排序是按照主键吗?答案是不一定。举个例子: 查询AttendanceEmpRank表,主键是AttendanceEmployeeRankId,而且是聚集索引 执行下面的语句,发现第一句不指定Order by的结果跟第二句不一样...

    Mysql面试过关!(详解:索引+常用引擎+常见问题+sql调优)

    一、Mysql索引 1、添加索引sql语句 2、查看MySQL中建立的索引是否生效 3、索引失效场景(补充:以下在实际应用中并不会一定导致索引失效,基于mysql不同版本的优化规则) ...3.11 当查询条件涉及到order by、l

    MySQL中利用索引对数据进行排序的基础教程

    当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序。如果查询是连接多个表,仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用...

    索引优化原则及Oracle中索引总结

     · 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引  · 用于联接的列(主健/外健)上建立索引;  · 在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定;  ·...

    MySQL Order by 语句用法与优化详解

    MySQL Order By keyword是用来给记录中的数据进行分类的。MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的。 代码如下:SELECT column_name(s) FROM table_name ORDER BY ...

    MySQL ORDER BY 的实现分析

    总的来说,在 MySQL 中的ORDER BY有两种排序实现方式,一种是利用有序索引获取有序数据,另一种则是通过相应的排序算法,将取得的数据在内存中进行排序

Global site tag (gtag.js) - Google Analytics