如何編寫更好的SQL查詢：終極指南-第三部分

dbasdk發表於2017-11-24
SQL

本次我們學習《如何編寫更好的SQL查詢》系列的最後一篇文章。

 

時間複雜度和大O符號

通過前兩篇文章，我們已經對查詢計劃有了一定了解。接下來，我們還可以藉助計算複雜度理論，來進一步深入地挖掘和思考效能的提升。理論電腦科學這一領域聚焦於：根據難度來對計算問題進行分類。這些計算問題可以是演算法問題，也可以是查詢問題。

對於查詢，我們可以不按照難度進行分類，而是按照執行查詢並得到結果所需的時間來進行分類。這種方式也被稱為按照時間複雜度進行分類。

使用大O符號，可以根據輸入的增長速度來表示執行時間，因為輸入可以任意大。大O符號不包括係數和低階項，以便可以專注於查詢執行時間的重要部分：增長率。使用這種方式時，會丟棄係數和低階項，時間複雜度是逐漸描述出的，這意味著輸入會變為無窮大。

在資料庫語言中，複雜性衡量了查詢執行時間的長短。

請注意，資料庫的大小不僅隨著表中儲存資料的增加而增加，資料庫中的索引也會影響資料庫大小。

 

估算查詢計劃的時間複雜性

執行計劃定義了每個操作所使用的演算法，這也使得每個查詢的執行時間可以在邏輯上表示為查詢計劃中資料表大小的函式。換句話說，可以使用大O符號和執行計劃來估算查詢的複雜性和效能。

在下面的小結中，我們將會了解四種型別的時間複雜度概念。

通過這些示例，可以看到查詢的時間複雜度會根據執行的查詢內容不同而有所不同。

對於不同的資料庫，需要考慮不同的索引方式、不同的執行計劃和不同的實現方式。

因此以下所列出的時間複雜度概念非常普遍。

O（1）：恆定時間

有一種查詢演算法，不論輸入的大小如何，都需要相同的時間來執行，這種方式就是恆定時間查詢。這些型別的查詢並不常見，下面是一個例子： 

SELECT TOP 1 t.*
FROM t

這種演算法的時間複雜度是一個常數，因為只是從表中選擇任意一行。因此，時間長度與表的大小無關。

線性時間：O（n）

如果一個演算法的時間執行與輸入大小成正比，那麼演算法的執行時間會隨著輸入大小的增加而增加。對於資料庫，這意味著查詢執行時間與表大小成正比：隨著表中資料行數的增加，查詢時間也會相應增加。

一個示例就是在非索引列上使用WHERE子句進行查詢：這就需要使用全表掃描或順序掃描，這將導致O（n）的時間複雜度。這意味著需要讀取表中的每一行，以便找到正確ID的資料。即使第一行就查詢到了正確的資料，查詢還是會對每一行資料進行讀取。

如果沒有索引，那麼這個查詢的複雜度為O（n）i_id： 

SELECT i_id FROM item;
  • 這也意味像COUNT(*) FROM TABLE這樣的計數查詢，具有O（n）的時間複雜度，除非儲存了資料表的總行數，否則就會進行全表掃描。此時，複雜度將更像是O（1）。

與線性執行時間密切相關的是，所有線性執行計劃的時間總和。下面是一些例子：

  • 雜湊連線（hash join）的複雜度為O（M + N）。兩個內部資料表連線的經典雜湊連線演算法是，首先為較小的資料表準備一個雜湊表。雜湊表的入口由連線屬性和行組成。通過將hash函式應用於join屬性，來實現雜湊表的訪問。一旦構建了雜湊表，就會掃描較大的表，並通過檢視雜湊表來查詢較小表中的相關行。
  • 合併連線（merge join）的複雜度為O（M + N），但是這種連線嚴重依賴於連線列上的索引，並且在沒有索引的情況下，會根據連線中使用的key對行先進行排序：
    • 如果根據連線中使用的key，對兩個表進行了排序，那麼查詢的複雜度為O（M + N）。
    • 如果兩個表都有連線列上的索引，則索引會按順序維護這些列，同時也不需要進行排序。此時複雜度為O（M + N）。
    • 如果兩個表都沒有連線列上的索引，則需要先對兩個表進行排序，因此複雜度會是O（M log M + N log N）。
    • 如果一個表的連線列上有索引，而另一個表沒有，則需要先對沒有索引的表進行排序，因此複雜度會是O（M + N log N ）。
  • 對於巢狀連線，複雜度通常為O（MN）。當一個或兩個表非常小（例如，小於10個記錄）時，這種連線方式特別有效。

請記得：巢狀連線是將一個表中的每個記錄與另一個表中的每個記錄進行比較的連線方式。

對數時間：O（log（n））

如果演算法的執行時間與輸入大小的對數成比，則演算法被稱為對數時間演算法; 對於查詢，這意味著執行時間與資料庫大小的對數成正比。

執行索引掃描（index Scan）或聚集索引掃描的查詢計劃時間複雜度，就是對數時間。聚集索引是索引的葉級別包含表的實際資料行的索引。聚集與其他索引非常相似：它是在一個或多個列上定義的。這也形成了索引主鍵。聚集主鍵是是聚集索引的主鍵列。聚集索引掃描是聚集索引中RDBMS從頭到尾一行一行讀取的基本操作。

以下的示例中存在一個i_id的索引，這也導致O（log（n））的複雜度： 

SELECT i_stock FROM item WHERE i_id = N;

如果沒有索引，則時間複雜度是O（n）。

二次時間：O（n ^ 2）

如果演算法的執行時間與輸入大小的平方成正比，則演算法被稱為對數時間演算法。對於資料庫，這意味著查詢的執行時間與資料庫大小的平方成正比。

具有二次時間複雜度的查詢的示例如下： 

SELECT *
FROM item, author WHERE item.i_a_id=author.a_id

最小複雜度為O（n log（n）），但是基於連線屬性的索引資訊，最大複雜度會是O（n ^ 2）。

下圖是一張根據時間複雜度來估算查詢效能的圖表，通過圖表可以檢視每個演算法的效能表現。

 

SQL調優

可以從以下方面衡量查詢計劃和時間複雜性，並進一步調優SQL查詢：

  • 用索引掃描替換不必要的大資料表的全表掃描;
  • 確保表的連線順序為最佳順序;
  • 確保以最佳方式使用索引;
  • 將小資料表的全表掃描快取起來。

《如何編寫更好的SQL查詢》教程的所有內容就介紹到這裡，希望通過本教程的介紹，能夠幫助大家編寫出更好、更優的SQL查詢。

原文連結：https://www.datacamp.com/community/tutorials/sql-tutorial-query#importance

轉載請註明出自：葡萄城控制元件

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28298702/viewspace-2144781/，如需轉載，請註明出處，否則將追究法律責任。

相關文章