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

155次閱讀

沒有評論

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