[譯] SQLite 底層查詢原理

boolchow發表於2019-03-04
SQLite

作者 ：bool周 原文連結：查詢規劃器（譯）

本文翻譯自 Query Planning

目錄

  1. 查詢

    1.1 無索引表查詢

    1.2 使用 rowid 查詢

    1.3 使用索引查詢

    1.4 多行內容查詢

    1.5 使用 AND 連結多個 WHERE 條件查詢

    1.6 多列查詢

    1.7 覆蓋索引查詢

    1.8 使用 OR 連結多個 WHERE 條件查詢

  2. 排序

    2.1 使用 rowid 排序

    2.2 使用索引排序

    2.3 覆蓋索引排序

  3. 查詢並排序

    3.1 通過多列索引查詢並排序

    3.2 通過覆蓋索引查詢並排序

    3.3 通過索引進行區域性排序

  4. 無 rowid 的表

概述

SQL 最主要的特徵 （在 所有 使用 SQL 語句的資料庫中，不只是 SQLite）在於它是一中 表述式程式語言，而不是一種 過程化語言。在使用 SQL 時，你只需要告訴系統你想要計算什麼，不需要描述如何去計算。計算結果的方式取決於 SQL 資料庫引擎的內部查詢規劃器。

對於一條 SQL 語句，可能有成百上千種執行演算法。所有的演算法都可以計算出正確的結果，但是有的計算的快，有的計算的慢。查詢規劃器相當於一個 AI，為每條 SQL 語句儘可能規劃最快的執行演算法。

多數情況下，查詢規劃器在 SQLite 中表現的十分出色。但是查詢規劃器需要使用索引進行協助。這些索引需要由開發者在設計資料庫時加上。有時候，查詢規劃器會選擇次優演算法，而不是最優的。這種情況下，需要開發者進行一些輔助操作來幫助查詢規劃器更好的工作。

這篇文章主要講解了 SQLite 查詢規劃器和查詢引擎背後的工作原理。有必要的時候，開發者可以根據這些原理更好地建立索引，幫助查詢規劃器高效地工作。

更多資訊可以檢視 SQLite query planner 和 next generation query planner.

1.查詢

1.1 無索引表查詢

在 SQLite 中，大多數表由一行或者多行組成，每一行都有一個獨一無二的 key (rowid 或者 INTEGER PRIMARY KEY)(WITHOUT ROWID 表是一個特例)。這些資料通常會按照遞增順序排列。例如，這篇文章使用的表為 "FruitsForSale"，主要儲存了各種各樣的水果以及水果的產地和價格資訊。表結構如下：

CREATE TABLE FruitsForSale(
  Fruit TEXT,
  State TEXT,
  Price REAL
);
複製程式碼

寫入一些資料之後，這張表將以 figure 1 圖中所示的形式儲存於磁碟中：

[譯] SQLite 底層查詢原理
Figure 1: "FruitsForSale" 表結構

在這個表裡，rowid 並不是連續，但卻是有序排列的。通常情況下，SQLite 建立一條資料時，這條資料的rowid 是在上一條 rowid 基礎上加 1。如果某一行被刪除，rowid 則會不連貫。如果有必要，建立一條資料時可以指定 rowid 的序號，並不是只能在末尾追加資料。但無論如何新增，每個 rowid 都是唯一的，有序排列的。

當你想查詢桃子的價格，查詢語句可能像下面這樣：

SELECT price FROM fruitsforsale WHERE fruit='Peach';
複製程式碼

為了滿足這條查詢，SQLite 會讀取表中每一行資料，首先檢索 'fruit' 這一列看是否有一條資料的值為 'Peach'，如果有的話，輸出這一條資料對應的 'price' 的值。檢索過程如圖 figure2 所示。這種演算法叫做 全表遍歷 —— 需要讀入整張表並檢索。這個表只有 7 條資料，檢索起來還好，但如果有 7 百萬條資料，為了檢索一條 8-byte 的資料需要讀入並遍歷 1M 的資料。為此，儘量避免全表遍歷。

[譯] SQLite 底層查詢原理
Figure 2: 全表遍歷

1.2 使用 rowid 查詢

使用 rowid 查詢可以避免全表遍歷 (等價於通過 INTEGER PRIMARY KEY 查詢)。查詢桃子的價格，直接檢索 rowid 為 4 的資料即可：

SELECT price FROM fruitsforsale WHERE rowid=4;
複製程式碼

因為每條資料是以 rowid 為順序儲存在表中的，SQLite 可以對這些資料進行二分查詢。如果表中含有 N 條資料，查詢一條資料的時間以 logN 為比例係數增長，而不是以 N 為比例係數增長。假如一個表中含有 1 千萬條資料，這意味遍歷全表操作時快了 N/logN 倍，也就是 1 百萬倍的速度。

[譯] SQLite 底層查詢原理
Figure 3: 通過 rowid 查詢

1.3 使用索引查詢

使用 rowid 查詢固然很快，但當你不知道 rowid 時怎麼辦？這時使用 rowid 查詢就不行了。

為提高查詢速度，我們可以將 "fruitsforsalt" 表中 "fruit" 這一列設定為索引，像下面這樣:

CREATE INDEX Idx1 ON fruitsforsale(fruit);
複製程式碼

索引表是與原來的 "fruitsforsale" 表相關聯的另外一張表，索引表中包含索引內容(這裡指 fruit 這一列)和 rowid 這兩列，其中索引內容在前，所有資料按照索引內容排序。Figure 4 中為索引表的結構。"fruit" 這一列作為主鍵，"rowid" 作為輔助索引，如果多條主鍵欄位值相同，則用輔助索引進行區別。在下面示例中，"Ornage"欄位值相同時，使用 rowid 區別。你可能注意到，在原始表中每條資料的 rowid 都是唯一的，所以通過 "fruit" 和 "rowid" 組成的複合鍵可以為每條資料確定一個唯一索引。

[譯] SQLite 底層查詢原理
Figure 4: 索引表

使用索引可以更快的查詢出 "桃子的價格" :

SELECT price FROM fruitsforsale WHERE fruit='Peach';
複製程式碼

執行這條時，SQLLite 先在索引表中進行二分查詢，找到 fruit='Peach'，然後取出這一行的 rowid。使用 rowid 在原始表 'FruitForSale' 中進行第二次二分查詢。找到對應行之後，取出 price 欄位值。檢索過程如圖 figure 5 所示。

[譯] SQLite 底層查詢原理
Figure 5: 通過索引查詢桃子的價格

為了查詢桃子的價格， SQLite 進行了兩次二分查詢。對於含有大量資料的表，這種方式仍然要快於全表遍歷。

1.4 多行查詢

在前面的查詢中，通過 fruit='Peach' 約束條件查詢出了一條資料。但是有時候一個約束條件可能對應多條資料。例如，我們要查詢橘子的價格，將會出現如下情況：

SELECT price FROM fruitsforsale WHERE fruit='Orange';
複製程式碼
[譯] SQLite 底層查詢原理
Figure 6: 通過索引查詢橘子價格

在這裡，SQLite 仍然是先進行一次二分查詢，找到索引為 fruit='Orange' 的資料。然後取出 rowid，使用這個 rowid 去原始表再進行一次二分查詢，找到對應的 price。之後 SQLite 並不會終止查詢，而是繼續去查詢下一條符合條件的資料。使用二分查詢，查詢下一條資料的消耗遠遠小於第一次，因為第二條資料和第一條資料一般會在同一頁內，就像上圖展示的那樣。這樣第二次查詢十分廉價，以致可以忽略不計。所以整個查詢大約進行了三次二分查詢。如果資料庫中有 K 條資料符合條件，整個表總共有 N 條資料，那麼一次查詢所消耗時間的比例係數大約為 （K+1)*logN.

1.5 使用 AND 連結多個 WHERE 條件查詢

接下來，你想要查詢 California 生產的橘子的價格。查詢條件如下所示：

SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA';
複製程式碼
[譯] SQLite 底層查詢原理
Figure 7: 使用索引查詢 California 生產的橘子的價格

一種查詢路徑是，先通過 fruit='Orange' 條件找出所有橘子的資料，然後過濾掉產地不是 California 的資料。查詢過程如圖 Figure 7 所示。多數情況這是一種合理的途徑。但是，資料庫需要做一次額外的二分查詢來過濾掉產地為 Florida 的資料，並不是想象中那麼高效。

既然可以將 "fruit" 這一列設定為索引，也可以考慮將 "state" 這一列設定為索引。

CREATE INDEX Idx2 ON fruitsforsale(state);
複製程式碼
[譯] SQLite 底層查詢原理
Figure 8: 將 State 這一列設定為索引

這個索引表中的 "state" 這一列和 Idx1 中的 "fruit" 類似，"state" 一列作為主鍵，"rowid" 一列作為輔助索引。在這個 model 中，"state" 這一列也有很多重複項，還是需要使用 "rowid" 來區分。

使用索引表 Idx2，SQLite 有了新的查詢方式：先使用索引表找出 California 對應的行，然後過濾掉未生產橘子的行。

[譯] SQLite 底層查詢原理
Figure 9: 使用索引查詢 California 生產的橘子

這裡與使用 idx1 查詢最終得到的是相同的結果（使用索引是為了提高 SQLite 的查詢速度，不應改變查詢結果）。這兩種索引方式工作量是相同的，所以在查詢價格這個 case 上，使用 Idx2 並不能提高效能。

在本例中，最後這兩種查詢方式使用時間相同。我們應該使用哪種呢？如果 ANALYZE 命令開啟，SQLite 可以收集使用索引表的統計資訊。然後 SQLite 就會知道使用 Idx1 進行索引查詢，多數情況下只會查詢到一行資料（這個表中 fruit='Orange' 屬於一種特殊情況）；而使用 Idx2 進行所用查詢，很多情況會查詢到兩行資料。所以如果其他查詢情況相同，SQLite 會選擇 Idx1 進行索引查詢，以減少查詢到的行數。這種選擇是由 ANALYZE 提供的。如果 ANALYZE 沒有執行在資料庫上，SQLite 選擇每種查詢方式的概率是一樣的。

1.6 多列索引查詢

為最大化提高 "AND 連結多個 WHERE 條件查詢" 的效能，你需要設定根據 AND 的連結項建立一個多列索引表。在這裡我們為 FruitsForSale 表中的 "fruit" 和 "state" 兩列建立為一個索引表：

CREATE INDEX Idx3 ON FruitsForSale(fruit, state);
複製程式碼
[譯] SQLite 底層查詢原理
Figure 10: 兩列索引

多列索引表的形式和單列索引表的形式相同，都是索引列在前，rowid 列在後。最左一列用來確定要查詢的行數，第二列用來過濾不符合要求的行數。如果這裡有三列，第三列則用來過濾前兩列結果，以此類推。這種情況一般在我們這種簡單資料模型中不會出現。但也有特例，如過濾條件為 fruit='Orange' 時會有兩行資料，需要根據索引表中的第二列來過濾掉髒資料。因為 rowid 是唯一的，所以索引表中的每一行都是唯一的，儘管兩行內容一樣。

使用新的索引表 Idx3，SQLite 查詢 California 生產的橘子的價格只需要兩次二分查詢：

SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA';
複製程式碼
[譯] SQLite 底層查詢原理
Figure 11: 使用兩列索引的索引表查詢

在 Idx3 中使用 WHERE 約束進行查詢，SQLite 只需要做一次二分查詢就可以找出 "California 生產的橘子" 這一行對應的 rowid，然後再從原始的表中進行一次二分查詢，找出對應橘子的價格。這是一種非常高效的查詢方式。

既然 Idx3 中已經包含了 Idx1 中的所有資訊，那麼我們就不需要 Idx1 了。如果要查詢 "桃子的價格"，可以忽略掉 "state" 欄位，直接使用 Idx3 進行查詢：

SELECT price FROM fruitsforsale WHERE fruit='Peach';
複製程式碼
[譯] SQLite 底層查詢原理
Figure 12: 使用 Idx3 進行查詢

因此，在今後設計資料庫時最好遵循這樣一個原則：不要讓一個索引表包含另外一個索引表。雖然 SQLite 對於較長索引仍然可以進行高效查詢，但是在設計時儘可能減少索引表的列數。

1.7 覆蓋索引

通過使用索引表 Idx3 查詢 "California 生產的橘子的價格" 已經十分高效。但還可以提高：將 "price" 這一列加入索引表，使用含有 3 列選項的索引表：

CREATE INDEX Idx4 ON FruitsForSale(fruit, state, price);
複製程式碼
[譯] SQLite 底層查詢原理
Figure 13: 覆蓋索引表

這個索引表中包含了 FruitesForSale 表中的所有欄位。我們稱這種查詢方式為 "覆蓋查詢"。因為所有的欄位資訊都被設定為了索引。SQLite 不需要再查詢原始表就可以查詢出對應水果的價格。

SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA';
複製程式碼
[譯] SQLite 底層查詢原理
Figure 14: 使用覆蓋索引查詢

將要查詢的結果的那一列資料也加入到索引表中，這樣就不用再與原始表相關聯，也使二分查詢次數減半。這種查詢雖然使效能有了提升（查詢大約速度提升一倍）。但是，這只是細微提升。在效能提升這一方面，提升一倍往往不如提升數百萬倍。所以對於大多數查詢來說，1 微秒與 2 微秒之間的的差異是微不足道的。

1.8 使用 OR 連結多個 WHERE 條件查詢

多列索引表只適用於用 AND 連線的 WHERE 條件的查詢。所以當約束條件為 California 生產和橘子 時 Idx3 和 Idx4 兩個索引表才有幫助；當約束條件變為 California 生產或橘子 時，這兩個索引表將不再有什麼幫助。

SELECT price FROM FruitsForSale WHERE fruit='Orange' OR state='CA';
複製程式碼

當面對使用 OR 連線 WHERE 條件時，SQLite 會先通過索引表查詢出每個條件對應行的 rowid。然後將這些 rowid 做一個並集，再去原始表中去查詢。下面是查詢過程：

[譯] SQLite 底層查詢原理
Figure 15: 使用 OR 連線的查詢

如上圖所示，SQLite 首先查詢出符合條件的 rowid，然後先將兩部分做並集，再使用這些 rowid 去原始表中查詢。這些 rowid 的排列是非常離散的，SQLite 使用索引查詢一次 rowid 之後，會記住遍歷過的索引，這樣可以減少下次查詢的計算量。當然，這只是其中一個實現細節。上圖中不能表示完整的檢索細節，但是展示了一個大概的過程。

上圖所示的 OR-by-UNION 技術是很適用的，前提索引表中必須有滿足條件的資料。如果索引表中沒有滿足 OR 連線的約束條件的資料，那麼 SQLite 會去原始表中進行全表遍歷。而不是通過 rowid 集合進行二分查詢，這將十分耗費效能。

我們可以看到，OR-by-UNION 這個技術進行多索引查詢時，實際上就是先通過索引表查詢符合條件的 rowid，再將這些 rowid 進行 並集 操作；類似的，通過 AND 連線的 WHERE 條件的查詢，也可以先通過索引表將符合條件的 rowid 查詢出來，然後取 交集，很多 SQL 型資料庫的原理就是這樣的。但是是用單列索引的索引表和 OR-by-INTERSECT 進行 AND 查詢，效能會比較差，所以一般都是使用多列索引進行 AND 查詢。隨著 SQLite 的不斷優化，後序可能支援 OR-by-INTERSECT 查詢。

2.排序

SQLite (像很多其他 SQL 資料庫引擎一樣) 可以使用索引進行 ORDER BY 查詢，不僅加快查詢速度。還可以加速排序速度。

如果沒有索引進行輔助，一個 ORDERT BY 查詢需要先進行排序。看一下下面這個語句：

SELECT * FROM fruitsforsale ORDER BY fruit;
複製程式碼

SQLite 首先檢索出所有結果，然後再通過使用一個 sorter 進行排序輸出。

[譯] SQLite 底層查詢原理
Figure 16: 無索引排序

如果要輸出的行數為 K 條，那麼排序所需時間的比例係數為 KlogK.如果 K 的值比較小，那麼排序時間無足輕重。但是像上圖所示那樣 K==N,排序時間遠遠大於需要遍歷全表的時間。此外，所有的檢索結果都需要先放在臨時快取區（可能是運存或者硬碟快取，依賴於編譯時和執行時的設定），這意味著在語句執行完之前需要佔據一塊很大的快取。

2.1 使用 rowid 排序

排序操作是十分昂貴的，SQLite 很難將 ORDER BY 轉化為一個非耗時操作。如果 SQLite 要輸出的資料已經排序好了，這樣就不用進行排序了。例如，你如果你按照 rowid 的排序輸出結果，就不需要進行排序：

SELECT * FROM fruitsforsale ORDER BY rowid;
複製程式碼
[譯] SQLite 底層查詢原理
Figure 17: 使用 rowid 進行排序檢索

你也可以進行倒序檢索：

SELECT * FROM fruitsforsale ORDER BY rowid DESC;
複製程式碼

這樣 SQLite 雖然不會進行排序。但是為了進行倒序輸出，SQLite 需要從 table 最後一條開始向前遍歷，而不是從前往後遍歷。如圖 Figure 17 所示。

2.2 使用索引排序

然而，在實際使用中，很少直接通過 rowid 進行有序輸出。一般都是通過其他條件進行有序檢索。如果一個索引可以適用於進行 ORDER BY 查詢，那麼這個索引也可以用來進行排序。例如，對 "fruit" 這一列排序進行輸出：

SELECT * FROM fruitsforsale ORDER BY fruit;
複製程式碼
[譯] SQLite 底層查詢原理
Figure 18: 使用索引進行排序

首先從上向下遍歷 Idx1 索引表（如果查詢語句為 "ORDER BY fruit DESC" 則從下向上遍歷），按順序檢索出每個 fruit 對應的 rowid。然後通過 rowid 在原始表中進行二分查詢並輸出對應的資料。因為從索引中檢索 rowid 時已經排好順序，所以直接按照 rowid 的排列順序在原始表中將資料檢索並輸出即可，不需要將所有檢索結果再次排序。

但是這樣做真的節省時間嗎？在本節開始時所描述的方式中，先對資料查詢再排序，所需要的時間比例係數為 NlogN，因為這需要對 N 條資料進行排序。而通過 Idx 索引表進行有序查詢，我們需要對 N 個 rowid 進行二分查詢，每個查詢時間為 logN，總時間的比例係數同樣為 NlogN.

SQLite 的查詢規劃器遵循 "低成本原則"。當有兩種甚至有更多種查詢方式時，SQLite 會先對每一種查詢方式進行時間預估，然後選擇成本最低的那種方式。成本的高低大多數情況下由預估時間決定，所以最終選擇哪種方式取決於要查詢的表的大小和 WHERE 條件的複雜度。通常情況下，使用索引進行有序查詢一般作為首選。主要原因在於，使用索引查詢不需要額外的臨時儲存空間來對資料進行排序，可以減少記憶體消耗。

2.3 使用覆蓋索引排序

如果覆蓋索引可以用於查詢，那麼查詢 rowid 這一步則可以省去，這樣消耗成本急劇降低。

[譯] SQLite 底層查詢原理
Figure 19: 使用覆蓋索引進行有序查詢

使用覆蓋索引，SQLite 可以簡單的對所有資料進行遍歷，然後將結果輸出，所需時間比例係數問 N。而且不需要額外開闢臨時快取區對資料進行排序。

3.同時進行查詢和排序

前面針對查詢和排序兩個主題分別作了講解。但是在實際使用中，開發者需要將查詢和排序同時進行。幸運的是，通過單個索引就可以完成這個操作。

3.1 通過多列索引進行同時查詢和排序操作

假如我們有這樣一個需求：我們想要查詢所有橘子的價格，並且按照橘子產地進行排序輸出。查詢語句如下：

SELECT price FROM fruitforsale WHERE fruit='Orange' ORDER BY state;
複製程式碼

這條查詢語句中，既包含了查詢，又包含了排序。使用索引表 Idx3 中的兩列索引，可以將滿足這兩個條件的資料查詢出來.

[譯] SQLite 底層查詢原理
Figure 20: 使用多行索引進行查詢並排序

查詢過程中，SQLite 先進行一次二分查詢，找到 fruit='Orange' 對應的 rowid。（因為 fruit 是最左端的一列，所以整個索引表就是按照 furit 的拼寫順序進行排序的，因此兩個相同的 fruit 在表中也是相鄰的。）然後使用 rowid 在原始表中進行二分查詢，找出對應的水果的價格。

你可能注意到，這裡沒有任何排序過程。沒有特意過程去執行 ORDER BY 操作。沒有排序過程，是因為在 index 表中查出資料的時候就已經按照 state 排好順序了。在一個索引表中，如果第一列的值相同（例如上圖中的 'Orange')，那麼其對應的第二列的值也會像第一列那樣按照順序進行排列。所以，如果我們在一個索引表中遍歷 fruit 值相同的兩行，那麼這兩行資料的 state 列一定是按照順序排列的。

3.2 使用覆蓋索引進行查詢和排序

覆蓋索引也可以用來查詢和排序，例如下面這樣：

SELECT * FROM fruitforsale WHERE fruit='Orange' ORDER BY state;
複製程式碼
[譯] SQLite 底層查詢原理
Figure 21: 使用覆蓋索引進行查詢並排序

按照之前說的，為滿足 WHERE 條件約束，SQLite 會進行一次二分查詢，從上向下遍歷索引表，以找到符合條件的資料。如果 WHERE 條件所約束的值在索引表中有多條資料，那麼這些條資料一定是相鄰排列的。遍歷時是按照從上向下順序遍歷的。因為 fruit 這一列後面一列就是 state，所以當 fruit 值相等時就會按照 state 這一列進行排列，以此類推。根據這個原理，查詢出來的資料直接就是已經排好順序的，十分高效。

SQLite 同樣也可以進行降序查詢：

SELECT * FROM fruitforsale WHERE fruit='Orange' ORDER BY state DESC;
複製程式碼

基本原理是類似的，只不過這次是從下向上遍歷，這樣查詢出來的資料也是降序排列的。

3.3 使用索引進行區域性排序

有些情況下，索引表只能滿足部分屬性的排序。例如下面這個查詢：

SELECT * FROM fruitforsale ORDER BY fruit, price;
複製程式碼

如果使用覆蓋索引表進行遍歷，fruit 這一列肯定是按照順序排列的，但是如果表中有多條 fruit 欄位值相同的資料，它們的 price 欄位值就不一定按照順序排列了。當出現這種狀況時，SQLite 會進行很多區域性排序操作，每次只針對某個 fruit 進行排序，而不是針對整個表排序。Figure 22 展示了這一過程：

[譯] SQLite 底層查詢原理
Figure 22: 使用索引進行區域性排序

在這個示例中，並不是對 7 條資料進行整體排序，而是進行了 5 次單條排序（其實不用排）和 1 次兩條排序（fruit='Orange' 這兩條資料）。

進行多次區域性排序，而不是進行整體排序的優點在於：

  1. 相對於一次整體排序，多個區域性排序同時進行可以減少 CPU 的時鐘週期。
  2. 每個區域性排序可以很快執行完畢，這意味著不用將大量資訊暫存到記憶體快取中，減少記憶體的佔用。
  3. 有些 sort key 已經在索引表中排好順序了，寫 SQL 的可以省略，這樣可以減少記憶體佔用和 CPU 執行時間。
  4. 每當一次區域性排序完成，便會將資料返回給應用；整體查詢需要遍歷完整表才會將資料返回。前者更好。
  5. 如果使用了 LIMIT 條件，還可以避免遍歷整個表。

因為這些優點，SQLite 經常使用索引進行區域性排序，而不是進行整體排序。

4.無 rowid 的表

以上描述的這些基本原則，同時適用於含有 rowid 的表和無 rowid 的表。唯一的不同就是，有 rowid 的表，rowid 這一列一般會作為一個表的鍵。建立索引表之後，rowid 會在所以表中最右端用來關聯索引表和原始表，在索引表中它的位置會被主鍵代替。

參考文獻

  • https://www.sqlite.org/queryplanner.html

相關文章