【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

##第二個情況:

order by的欄位不在where條件但在select中。

select id,sid from zhuyuehua.student where sid


第三種情況 :

order by的欄位在where條件但不在select中。

select sid from zhuyuehua.student where sid


第四種情況 :

order by的欄位在where條件但不在select中。倒序排列

select sid from zhuyuehua.student where sid


測試結果：

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
#第一步：根據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等。

想知道更多有關sql的教程，請關注PHP中文網

sql影片教學！

• 
• #: 16 KB

• 
##大小: 16 KB

• 
大小: 20.6 KB

• 
: 21 KB
• 
  

#

以上是【SQL學習】有序索引與order by的聯繫的詳細內容。更多資訊請關注PHP中文網其他相關文章！