SQL中有序索引与order by有什么联系

116次阅读

没有评论

共计 1863 个字符，预计需要花费 5 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍了 SQL 中有序索引与 order by 有什么联系，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

一般情况下，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 等。