如何編寫更好的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_idFROM 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_stockFROM itemWHERE i_id = N;

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

二次時間：O（n ^ 2）

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

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

SELECT *
FROM item, authorWHERE 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

轉載請注明出自：葡萄城控件