首頁  >  文章  >  資料庫  >  【SQL學習】有序索引與order by的聯繫

【SQL學習】有序索引與order by的聯繫

little bottle
little bottle轉載
2019-04-18 10:43:104430瀏覽

一般情況下,order by 有兩種實現方法,一個是利用有序索引自動實現,也就是說利用有序索引的有序性就不再另做排序操作了。另一個是把結果選好之後再排序。 用有序索引這種,當然是最快的,不過有一些限制條件,來看下面的測試。 

測試資料:student表有兩個欄位id ,sid ,id是主鍵。一共有20W筆記錄,id從1到200000,sid也是從1到200000的資料。

第一種情況 :

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

select sid from zhuyuehua.student where sid
【SQL學習】有序索引與order by的聯繫
##第二個情況:

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

select id,sid from zhuyuehua.student where sid

【SQL學習】有序索引與order by的聯繫
第三種情況 :

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

select sid from zhuyuehua.student where sid

【SQL學習】有序索引與order by的聯繫
第四種情況 :

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

select sid from zhuyuehua.student where sid

【SQL學習】有序索引與order by的聯繫
測試結果:

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影片教學


  • 【SQL學習】有序索引與order by的聯繫
  • #: 16 KB
  • 【SQL學習】有序索引與order by的聯繫
  • ##大小: 16 KB
  • 【SQL學習】有序索引與order by的聯繫
  • 大小: 20.6 KB
  • 【SQL學習】有序索引與order by的聯繫
  • : 21 KB

    #

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

    陳述:
    本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除