SQL中有序索引與order by有什么聯系

發布時間：2021-01-15 09:40:55 來源：億速云閱讀：125 作者：小新欄目：數據庫

這篇文章主要介紹了SQL中有序索引與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;

SQL中有序索引與order by有什么聯系

第二種情況 :

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;

SQL中有序索引與order by有什么聯系

第四種情況 :

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等。