小巧、快速、可靠。
任選三項。
任選三項。
本文件概述 SQLite 的查詢規劃器和最佳化器如何運作。
針對單一 SQL 陳述式，可能有數十、數百，甚至數千種方法可以實作該陳述式，具體取決於陳述式本身和基礎資料庫架構的複雜性。查詢規劃器的任務是選擇可將磁碟 I/O 和 CPU 負載降至最低的演算法。
索引教學 文件中提供了其他背景資訊。
在 3.8.0 版（2013-08-26）中，SQLite 查詢規劃器已重新實作為 下一代查詢規劃器 或「NGQP」。本文檔中描述的所有功能、技術和演算法都適用於 3.8.0 版之前的舊版查詢規劃器和 NGQP。如需進一步瞭解 NGQP 與舊版查詢規劃器之間的差異，請參閱 NGQP 的詳細說明。
查詢中的 WHERE 子句會分成「項目」，其中每個項目都由 AND 運算子與其他項目分隔。如果 WHERE 子句由 OR 運算子分隔的約束所組成，則整個子句會被視為一個「項目」，並套用 OR 子句最佳化。
WHERE 子句的所有項目都會經過分析，以查看是否可以使用索引來滿足這些項目。要能使用索引，項目通常必須符合下列其中一種形式
column = expression column IS expression column > expression column >= expression column < expression column <= expression expression = column expression > column expression >= column expression < column expression <= column column IN (expression-list) column IN (subquery) column IS NULL column LIKE pattern column GLOB pattern
如果使用類似下列陳述來建立索引
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
如果索引的初始欄位（欄位 a、b 等）出現在 WHERE 子句項目中，則可能會使用該索引。索引的初始欄位必須與 = 或 IN 或 IS 運算子一起使用。最右邊使用的欄位可以使用不等式。對於所使用的索引的最右邊欄位，最多可以有兩個不等式，必須將欄位的允許值夾在兩個極值之間。
索引的每一欄位都不一定要出現在 WHERE 子句條件中，才能使用該索引。不過，索引中所使用的欄位不能有間隔。因此，對於上述範例索引，如果沒有 WHERE 子句條件限制欄位 c，則限制欄位 a 和 b 的條件可以使用索引，但限制欄位 d 到 z 的條件則不能。同樣地，如果索引欄位在僅由不等式限制的欄位右側，則通常不會使用索引欄位（用於索引目的）。（請參閱以下的跳躍掃描最佳化，以了解例外情況。）
對於表達式索引，每當在上述文字中使用「欄位」一詞時，都可以替換為「已編製索引的表達式」（意指出現在CREATE INDEX 陳述式中的表達式副本），而且所有內容都會以相同方式運作。
對於上述索引和 WHERE 子句如下所示
... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'
索引的前四個欄位 a、b、c 和 d 會可用，因為這四個欄位形成索引的前置詞，而且都受到等式限制約束。
對於上述索引和 WHERE 子句如下所示
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
只有索引的欄位 a、b 和 c 會可用。d 欄位不會可用，因為它出現在 c 的右側，而 c 僅受不等式限制約束。
對於上述索引和 WHERE 子句如下所示
... WHERE a=5 AND b IN (1,2,3) AND d='hello'
只有索引的欄位 a 和 b 會可用。d 欄位不會可用，因為欄位 c 沒有受到限制，而且索引可用的欄位組中不能有間隔。
對於上述索引和 WHERE 子句如下所示
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'
索引完全不可用，因為索引的最左邊欄位（欄位「a」）沒有受到限制。假設沒有其他索引，上述查詢將導致完整資料表掃描。
對於上述索引和 WHERE 子句如下所示
... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello'
索引無法使用，因為 WHERE 子句中的字詞是使用 OR 而不是 AND 連接。此查詢將導致完整表格掃描。但是，如果新增三個額外的索引，其中包含 b、c 和 d 欄位作為最左邊的欄位，則 OR 子句最佳化 可能適用。
如果 WHERE 子句中的字詞具有下列形式
expr1 BETWEEN expr2 AND expr3
則會新增兩個「虛擬」字詞，如下所示
expr1 >= expr2 AND expr1 <= expr3
虛擬字詞僅用於分析，不會導致產生任何位元組碼。如果兩個虛擬字詞都用作索引上的限制條件，則會省略原始的 BETWEEN 字詞，且不會對輸入列執行對應的測試。因此，如果 BETWEEN 字詞最終用作索引限制條件，則永遠不會對該字詞執行任何測試。另一方面，虛擬字詞本身永遠不會導致對輸入列執行測試。因此，如果 BETWEEN 字詞未用作索引限制條件，而必須用於測試輸入列，則僅會評估一次 expr1 運算式。
使用 OR 而不是 AND 連接的 WHERE 子句限制條件可以用兩種不同的方式處理。如果一個字詞包含多個子字詞，其中包含一個共通的欄位名稱，並以 OR 分隔，如下所示
column = expr1 OR column = expr2 OR column = expr3 OR ...
則該字詞會改寫如下
column IN (expr1,expr2,expr3,...)
改寫後的字詞接著可能會使用 IN 運算子的正常規則來限制索引。請注意，column 必須是每個 OR 連接的子字詞中相同的欄位，儘管該欄位可以出現在 = 運算子的左側或右側。
如果且僅當先前描述的 OR 轉換為 IN 運算子無法運作時，才會嘗試第二個 OR 子句最佳化。假設 OR 子句包含多個子項，如下所示
expr1 OR expr2 OR expr3
個別子項可能是單一比較式，例如 a=5 或 x>y，或可能是 LIKE 或 BETWEEN 式，或子項可以是 AND 連接的子子項的括號清單。每個子項都會分析，彷彿它本身就是整個 WHERE 子句，以查看子項本身是否可索引。如果 OR 子句的每個子項都可個別索引，則 OR 子句可能會編碼，以便使用個別索引來評估 OR 子句的每個項目。思考 SQLite 如何針對每個 OR 子句項目使用個別索引的方法之一，就是想像 WHERE 子句改寫如下
rowid IN (SELECT rowid FROM table WHERE expr1
UNION SELECT rowid FROM table WHERE expr2
UNION SELECT rowid FROM table WHERE expr3)
上述改寫的式是概念性的；包含 OR 的 WHERE 子句並不會真正這樣改寫。OR 子句的實際實作使用一種更有效率的機制，而且適用於 WITHOUT ROWID 表格或「rowid」無法存取的表格。儘管如此，實作的精髓已由上述陳述捕捉：個別索引用於從每個 OR 子句項目中找出候選結果列，而最終結果是那些列的聯集。
請注意，在大部分情況下，SQLite 只會對查詢的 FROM 子句中的每個表格使用單一索引。這裡描述的第二個 OR 子句最佳化是該規則的例外。對於 OR 子句，每個 OR 子句中的子項可能會使用不同的索引。
對於任何給定的查詢，在此描述的 OR 子句最佳化可用並不保證會使用它。SQLite 使用基於成本的查詢規劃器，估計各種競爭查詢規劃的 CPU 和磁碟 I/O 成本，並選擇它認為最快的規劃。如果 WHERE 子句中有許多 OR 項目，或者個別 OR 子句子項目的某些索引不夠具選擇性，則 SQLite 可能決定使用不同的查詢演算法，甚至是全表掃描。應用程式開發人員可以在陳述上使用 EXPLAIN QUERY PLAN 前置詞，以取得所選查詢策略的高階概觀。
使用 LIKE 或 GLOB 運算子的 WHERE 子句項目有時可以用索引來執行範圍搜尋，幾乎就像 LIKE 或 GLOB 是 BETWEEN 運算子的替代方案。此最佳化有許多條件
LIKE 運算子有兩種模式，可透過 pragma 設定。預設模式是 LIKE 比較對於拉丁字母字元的字元大小寫差異不敏感。因此，預設情況下，下列表達式為真
'a' LIKE 'A'
如果 case_sensitive_like pragma 如下啟用
PRAGMA case_sensitive_like=ON;
則 LIKE 運算子會注意大小寫，而上述範例會評估為假。請注意，不區分大小寫只適用於拉丁字母字元，基本上是 ASCII 較低 127 位元組代碼中英文的大寫和小寫字母。除非提供考慮非 ASCII 字元的應用程式定義 校對順序 和 like() SQL 函數，否則 SQLite 中的國際字元集會區分大小寫。如果提供了應用程式定義的校對順序和/或 like() SQL 函數，則不會進行此處描述的 LIKE 最佳化。
LIKE 運算子預設不區分大小寫，因為這是 SQL 標準的要求。您可以使用編譯器的 SQLITE_CASE_SENSITIVE_LIKE 命令列選項在編譯時變更預設行為。
如果運算子左側指定的欄位使用內建的 BINARY 校對順序建立索引，且 case_sensitive_like 已開啟，則可能會發生 LIKE 最佳化。或者，如果欄位使用內建的 NOCASE 校對順序建立索引，且 case_sensitive_like 模式已關閉，則可能會發生最佳化。這是 LIKE 運算子會最佳化的唯一兩種組合。
GLOB 運算子始終區分大小寫。GLOB 運算子左側的欄位必須始終使用內建的 BINARY 校對順序，否則不會嘗試使用索引最佳化該運算子。
LIKE 最佳化僅會在 GLOB 或 LIKE 算子的右側是字串常數或已 繫結 至字串常數的 參數 時嘗試。字串常數不得以萬用字元開頭；如果右側以萬用字元開頭，則不會嘗試此最佳化。如果右側是繫結至字串的 參數，則僅在包含表達式的 已準備好陳述式 是使用 sqlite3_prepare_v2() 或 sqlite3_prepare16_v2() 編譯時，才會嘗試此最佳化。如果右側是 參數 且陳述式是使用 sqlite3_prepare() 或 sqlite3_prepare16() 準備的，則不會嘗試 LIKE 最佳化。
假設 LIKE 或 GLOB 算子右側非萬用字元的初始順序是 x。我們使用單一字元表示此非萬用字元前綴，但讀者應了解此前綴可能包含多於 1 個字元。令 y 為與 /x/ 長度相同但比較大於 x 的最小字串。例如，如果 x 是 'hello'，則 y 會是 'hellp'。LIKE 和 GLOB 最佳化包含新增兩個虛擬項目，如下所示
column >= x AND column < y
在大部分情況下，即使虛擬項目用於限制索引，原始 LIKE 或 GLOB 算子仍會針對每個輸入列進行測試。這是因為我們不知道 x 前綴右側的字元可能會施加哪些額外的限制。不過，如果 x 右側只有一個全域萬用字元，則會停用原始 LIKE 或 GLOB 測試。換句話說，如果模式如下所示
column LIKE x% column GLOB x*
則當虛擬項目限制索引時，會停用原始 LIKE 或 GLOB 測試，因為在這種情況下，我們知道索引選取的所有列都會通過 LIKE 或 GLOB 測試。
請注意，當 LIKE 或 GLOB 算子的右側是一個 參數，且陳述句使用 sqlite3_prepare_v2() 或 sqlite3_prepare16_v2() 準備時，如果自上次執行後與右側參數的繫結已變更，則陳述句會在每次執行的第一次 sqlite3_step() 呼叫時自動重新剖析和重新編譯。此重新剖析和重新編譯基本上與架構變更後發生的動作相同。重新編譯是必要的，以便查詢規劃器可以檢查繫結至 LIKE 或 GLOB 算子右側的新值，並確定是否採用上述最佳化。
一般規則是，索引僅在索引的最左邊欄位有 WHERE 子句約束時才有用。然而，在某些情況下，即使 WHERE 子句省略了索引的前幾欄，但包含了後面的欄，SQLite 仍能使用索引。
考慮下表
CREATE TABLE people(
name TEXT PRIMARY KEY,
role TEXT NOT NULL,
height INT NOT NULL, -- in cm
CHECK( role IN ('student','teacher') )
);
CREATE INDEX people_idx1 ON people(role, height);
people 表格中每個人員在大型組織中都有各自的項目。每個人都是「學生」或「老師」，由「角色」欄位決定。表格中也記錄每個人的身高（公分）。角色和身高都有索引。請注意，索引的最左邊欄位選擇性不高，它僅包含兩個可能的值。
現在考慮一個查詢，找出組織中所有身高 180 公分或更高的人員姓名
SELECT name FROM people WHERE height>=180;
由於索引的最左欄位未出現在查詢的 WHERE 子句中，因此有人會認為索引在此處無法使用。然而，SQLite 能夠使用索引。理論上，SQLite 使用索引的方式就像查詢更類似於以下內容
SELECT name FROM people WHERE role IN (SELECT DISTINCT role FROM people) AND height>=180;
或這樣
SELECT name FROM people WHERE role='teacher' AND height>=180 UNION ALL SELECT name FROM people WHERE role='student' AND height>=180;
上面顯示的替代查詢公式僅為概念性。SQLite 實際上並未轉換查詢。實際的查詢計畫如下：SQLite 找到「role」的第一個可能值，它可以透過將「people_idx1」索引倒回開頭並讀取第一個記錄來做到這一點。SQLite 將這個第一個「role」值儲存在我們在此稱為「$role」的內部變數中。然後，SQLite 執行類似以下的查詢：「SELECT name FROM people WHERE role=$role AND height>=180」。此查詢在索引的最左欄位上有一個等式約束，因此可以利用索引來解析該查詢。一旦查詢完成，SQLite 便使用「people_idx1」索引找到「role」欄位的下一個值，使用邏輯上類似於「SELECT role FROM people WHERE role>$role LIMIT 1」的程式碼。這個新的「role」值會覆寫 $role 變數，並且這個程序會重複，直到檢查完所有可能的「role」值。
我們稱這種索引使用方式為「跳躍掃描」，因為資料庫引擎基本上對索引執行完整掃描，但它會透過偶爾跳到下一個候選值來最佳化掃描（使其小於「完整」）。
如果 SQLite 知道第一個或多個欄位包含許多重複值，則它可能會在索引上使用跳躍掃描。如果索引的最左欄位中重複值太少，那麼直接跳到下一個值會比在索引上執行二元搜尋來找到下一個左欄位值更快，因此執行完整資料表掃描會更快。
SQLite 唯一知道索引最左欄位中有許多重複項目的方式，就是資料庫已執行 ANALYZE 指令。在沒有 ANALYZE 結果的情況下，SQLite 必須猜測資料表中資料的「形狀」，而預設猜測是索引最左欄位中每個值的平均重複項目為 10 個。只有當重複項目數目約為 18 或更多時，略過掃描才會變得有利可圖（它只會比完整資料表掃描更快）。因此，未分析過的資料庫絕不會使用略過掃描。
在 第 2.0 段落中描述的 WHERE 子句分析之前，內部聯結的 ON 和 USING 子句會轉換為 WHERE 子句的其他項目。因此，使用 SQLite 時，較新的 SQL92 聯結語法與較舊的 SQL89 逗號聯結語法相比，沒有計算優勢。它們最終在內部聯結上完成完全相同的事情。
對於外部聯結，情況更為複雜。下列兩個查詢並不等效
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.y; SELECT * FROM tab1 LEFT JOIN tab2 WHERE tab1.x=tab2.y;
對於內部聯結，以上兩個查詢將會相同。但是，特殊處理套用於外部聯結的 ON 和 USING 子句：具體來說，如果聯結的右表在空值列上，則 ON 或 USING 子句中的約束不適用，但約束在 WHERE 子句中適用。淨效應是將 LEFT JOIN 的 ON 或 USING 子句表達式放入 WHERE 子句中，有效地將查詢轉換為一般的 INNER JOIN - 儘管是執行速度較慢的內部聯結。
SQLite 的目前實作僅使用迴圈聯結。也就是說，聯結實作為巢狀迴圈。
在連接中嵌套迴圈的預設順序是，FROM 子句中最左邊的表格形成外迴圈，最右邊的表格形成內迴圈。然而，如果這樣做有助於選擇更好的索引，SQLite 將會以不同的順序嵌套迴圈。
內部連接可以自由重新排序。然而，左外部連接既不是交換律也不是結合律，因此不會重新排序。如果最佳化器認為有利，則外部連接左右兩側的內部連接可能會重新排序，但外部連接總是按照它們出現的順序進行評估。
SQLite 特別處理 CROSS JOIN 運算子。理論上，CROSS JOIN 運算子是交換律的。然而，SQLite 選擇從不重新排序 CROSS JOIN 中的表格。這提供了一種機制，讓程式設計師可以強制 SQLite 選擇特定的迴圈巢狀順序。
在選擇連接中表格的順序時，SQLite 使用一種有效的多項式時間演算法。因此，SQLite 能夠在幾微秒內規劃 50 或 60 路徑連接的查詢
連接重新排序是自動的，通常工作得很好，以至於程式設計師不必考慮它，特別是如果 ANALYZE 已用於收集有關可用索引的統計資料，儘管偶爾需要程式設計師提供一些提示。例如，考慮以下架構
CREATE TABLE node( id INTEGER PRIMARY KEY, name TEXT ); CREATE INDEX node_idx ON node(name); CREATE TABLE edge( orig INTEGER REFERENCES node, dest INTEGER REFERENCES node, PRIMARY KEY(orig, dest) ); CREATE INDEX edge_idx ON edge(dest,orig);
上面的架構定義了一個有向圖，可以在每個節點儲存一個名稱。現在考慮對此架構的查詢
SELECT *
FROM edge AS e,
node AS n1,
node AS n2
WHERE n1.name = 'alice'
AND n2.name = 'bob'
AND e.orig = n1.id
AND e.dest = n2.id;
此查詢要求提供從標籤為「alice」的節點到標籤為「bob」的節點的所有邊緣資訊。SQLite 中的查詢最佳化器在如何實作此查詢方面基本上有兩種選擇。（實際上有六種不同的選擇，但我們在此只考慮其中兩種。）以下偽代碼展示這兩種選擇。
選項 1
foreach n1 where n1.name='alice' do:
foreach n2 where n2.name='bob' do:
foreach e where e.orig=n1.id and e.dest=n2.id
return n1.*, n2.*, e.*
end
end
end
選項 2
foreach n1 where n1.name='alice' do:
foreach e where e.orig=n1.id do:
foreach n2 where n2.id=e.dest and n2.name='bob' do:
return n1.*, n2.*, e.*
end
end
end
在兩個實作選項中，使用相同的索引來加速每個迴圈。這兩個查詢計畫的唯一差異在於迴圈的巢狀順序。
那麼哪個查詢計畫比較好？答案取決於節點和邊緣表格中找到的資料類型。
假設 Alice 節點數為 M，Bob 節點數為 N。考慮兩種情況。在第一種情況中，M 和 N 都是 2，但每個節點都有數千條邊緣。在這種情況下，選項 1 較佳。使用選項 1 時，內部迴圈會檢查一對節點之間是否存在邊緣，並在找到時輸出結果。由於每個 Alice 和 Bob 節點只有 2 個，因此內部迴圈只需執行四次，查詢速度非常快。選項 2 在這裡會花費更長的時間。選項 2 的外部迴圈只執行兩次，但由於每個 Alice 節點都有大量的邊緣，因此中間迴圈必須執行數千次。它的速度會慢很多。因此在第一種情況下，我們偏好使用選項 1。
現在考慮 M 和 N 都為 3500 的情況。Alice 節點很多。這一次假設這些節點中的每個節點只連接一條或兩條邊緣。現在選項 2 較佳。使用選項 2 時，外部迴圈仍然必須執行 3500 次，但中間迴圈只為每個外部迴圈執行一次或兩次，而內部迴圈只會為每個中間迴圈執行一次（如果有的話）。因此，內部迴圈的總迭代次數約為 7000。另一方面，選項 1 必須執行外部迴圈和中間迴圈各 3500 次，導致中間迴圈進行 1200 萬次迭代。因此在第二種情況下，選項 2 比選項 1 快將近 2000 倍。
因此，您可以看到，根據資料在資料表中的結構方式，查詢計畫 1 或查詢計畫 2 可能會比較好。SQLite 預設會選擇哪個計畫？截至版本 3.6.18，在未執行 ANALYZE 的情況下，SQLite 會選擇選項 2。如果執行 ANALYZE 指令以收集統計資料，如果統計資料指出替代方案可能會執行得更快，則可能會做出不同的選擇。
SQLite 提供進階程式設計人員控制最佳化程式所選取查詢計畫的能力。執行此操作的方法之一是修改 ANALYZE 在 sqlite_stat1、sqlite_stat3 和/或 sqlite_stat4 資料表中的結果。這不建議在大部分情況下使用。
程式設計人員可以使用 CROSS JOIN 營運子（而非僅使用 JOIN、INNER JOIN、NATURAL JOIN 或「,」連接）強制 SQLite 對連接使用特定的迴圈巢狀順序。儘管理論上 CROSS JOIN 是可交換的，但 SQLite 選擇絕不重新排序 CROSS JOIN 中的資料表。因此，CROSS JOIN 的左資料表相對於右資料表將永遠位於外迴圈中。
在下列查詢中，最佳化程式可以自由地重新排序 FROM 子句的資料表，視其認為合適的方式
SELECT *
FROM node AS n1,
edge AS e,
node AS n2
WHERE n1.name = 'alice'
AND n2.name = 'bob'
AND e.orig = n1.id
AND e.dest = n2.id;
在以下邏輯等效的相同查詢公式中，將「CROSS JOIN」替換為「,」表示表格的順序必須為 N1、E、N2。
SELECT *
FROM node AS n1 CROSS JOIN
edge AS e CROSS JOIN
node AS n2
WHERE n1.name = 'alice'
AND n2.name = 'bob'
AND e.orig = n1.id
AND e.dest = n2.id;
在後者查詢中，查詢計畫必須為 選項 2。請注意，您必須使用關鍵字「CROSS」才能停用表格重新排序最佳化；INNER JOIN、NATURAL JOIN、JOIN 和其他類似組合就像逗號連接一樣，最佳化程式可以自由地重新排序表格，視其需要而定。（表格重新排序也會在外部連接上停用，但這是因為外部連接不是關聯的或可交換的。重新排序外部連接中的表格會變更結果。）
請參閱「化石 NGQP 升級案例研究」，以取得使用 CROSS JOIN 手動控制連接巢狀順序的另一個實際範例。稍後在同一個文件中找到的 查詢計畫檢查清單 提供了進一步的指導，說明如何手動控制查詢計畫。
查詢 FROM 子句中的每個表格最多可以使用一個索引（除非 OR 子句最佳化 發揮作用），而 SQLite 努力在每個表格上至少使用一個索引。有時，兩個或多個索引可能是單一表格上可用的候選索引。例如
CREATE TABLE ex2(x,y,z); CREATE INDEX ex2i1 ON ex2(x); CREATE INDEX ex2i2 ON ex2(y); SELECT z FROM ex2 WHERE x=5 AND y=6;
對於上述 SELECT 陳述，最佳化程式可以使用 ex2i1 索引來查詢包含 x=5 的 ex2 列，然後針對 y=6 項目測試每一列。或者，它可以使用 ex2i2 索引來查詢包含 y=6 的 ex2 列，然後針對 x=5 項目測試每一列。
在面臨兩個或更多索引的選擇時，SQLite 會嘗試估計使用每個選項執行查詢所需的總工作量。然後，它會選擇估計工作量最少的選項。
為了幫助最佳化器更準確地估計使用各種索引所涉及的工作量，使用者可以選擇執行 ANALYZE 命令。ANALYZE 命令會掃描資料庫的所有索引，其中可能在兩個或更多索引之間進行選擇，並收集這些索引選擇性的統計資料。此掃描收集的統計資料會儲存在名稱開頭皆為「sqlite_stat」的特殊資料庫表格中。這些表格的內容不會隨著資料庫變更而更新，因此在進行重大變更後，重新執行 ANALYZE 可能會比較明智。ANALYZE 命令的結果只會提供給在 ANALYZE 命令完成後開啟的資料庫連線。
各種 sqlite_statN 表格包含有關各種索引選擇性的資訊。例如，sqlite_stat1 表格可能指出，對欄位 x 的等值約束平均會將搜尋範圍縮小到 10 列，而對欄位 y 的等值約束平均會將搜尋範圍縮小到 3 列。在這種情況下，SQLite 會偏好使用索引 ex2i2，因為該索引更具選擇性。
WHERE 子句的條件可以透過在欄位名稱前加上單元 + 算子，手動取消使用索引。單元 + 為無作用運算，且不會在已準備好的陳述式中產生任何位元組碼。但是，單元 + 算子會阻止條件約束索引。因此，在上述範例中，如果查詢重寫為
SELECT z FROM ex2 WHERE +x=5 AND y=6;
x 欄位上的 + 算子會阻止該條件約束索引。這會強制使用 ex2i2 索引。
請注意，單元+運算子也會移除表達式的類型關聯性，在某些情況下，這可能會導致表達式的含義發生細微的變化。在上面的範例中，如果欄位x具有TEXT 關聯性，則比較「x=5」將會以文字進行。+運算子會移除關聯性。因此，比較「+x=5」會將欄位x中的文字與數字值 5 進行比較，且永遠會為 false。
考慮一個稍微不同的場景
CREATE TABLE ex2(x,y,z); CREATE INDEX ex2i1 ON ex2(x); CREATE INDEX ex2i2 ON ex2(y); SELECT z FROM ex2 WHERE x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;
進一步假設欄位 x 包含在 0 到 1,000,000 之間散布的值，而欄位 y 包含在 0 到 1,000 之間的值。在這種場景中，對欄位 x 的範圍約束應將搜尋空間減少 10,000 倍，而對欄位 y 的範圍約束應僅將搜尋空間減少 10 倍。因此，應優先選擇 ex2i1 索引。
SQLite 將會做出此決定，但前提是它已使用 SQLITE_ENABLE_STAT3 或 SQLITE_ENABLE_STAT4 編譯。 SQLITE_ENABLE_STAT3 和 SQLITE_ENABLE_STAT4 選項會導致 ANALYZE 命令收集 sqlite_stat3 或 sqlite_stat4 表中欄位內容的直方圖，並使用此直方圖對要使用的最佳查詢進行更好的猜測，例如上述範圍約束。STAT3 和 STAT4 之間的主要區別在於，STAT3 僅針對索引的最左欄位記錄直方圖資料，而 STAT4 則針對索引的所有欄位記錄直方圖資料。對於單一欄位索引，STAT3 和 STAT4 的運作方式相同。
直方圖資料僅在約束的右側是一個簡單的編譯時間常數或參數（而非表達式）時才有用。
直方圖資料的另一個限制是，它僅適用於索引最左邊的欄位。考慮以下情況
CREATE TABLE ex3(w,x,y,z); CREATE INDEX ex3i1 ON ex2(w, x); CREATE INDEX ex3i2 ON ex2(w, y); SELECT z FROM ex3 WHERE w=5 AND x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;
此處的不等式在 x 和 y 欄位，它們不是最左邊的索引欄位。因此，收集到的直方圖資料沒有最左邊的索引欄位，對於選擇 x 和 y 欄位的範圍約束沒有幫助。
在對列進行索引查詢時，通常的步驟是在索引上執行二元搜尋以尋找索引項目，然後從索引中提取 rowid，並使用該 rowid 對原始表格執行二元搜尋。因此，典型的索引查詢涉及兩次二元搜尋。但是，如果要從表格擷取的所有欄位都已在索引本身中提供，SQLite 將使用索引中包含的值，而不會查詢原始表格列。這為每一列節省一次二元搜尋，並可以讓許多查詢執行速度快兩倍。
當索引包含查詢所需的所有資料，且從不需查詢原始表格時，我們稱該索引為「覆蓋索引」。
SQLite 會嘗試使用索引來滿足查詢的 ORDER BY 子句（如果可能）。在面對使用索引來滿足 WHERE 子句約束或滿足 ORDER BY 子句的選擇時，SQLite 會執行上述相同的成本分析，並選擇它認為將產生最快答案的索引。
SQLite 也會嘗試使用索引來協助滿足 GROUP BY 子句和 DISTINCT 關鍵字。如果可以安排聯結的巢狀迴圈，使對於 GROUP BY 或 DISTINCT 等效的列是連續的，則 GROUP BY 或 DISTINCT 邏輯可以透過將目前列與前一列進行比較，來判斷目前列是否屬於同一組或是否相異。這可能會比將每一列與所有前一列進行比較來得快很多。
如果查詢包含具有多個項目的 ORDER BY 子句，SQLite 可能可以使用索引讓列依據 ORDER BY 中項目的某個前綴順序輸出，但 ORDER BY 中較後面的項目並未滿足。在這種情況下，SQLite 會區塊排序。假設 ORDER BY 子句有四個項目，而查詢結果的自然順序會讓列依據前兩個項目的順序顯示。當查詢引擎輸出每一列並進入排序器時，會將目前列中與 ORDER BY 前兩個項目對應的輸出與前一列進行比較。如果它們已變更，則會完成目前的排序並進行輸出，並開始新的排序。這會讓排序速度略快。更大的優點是需要儲存在記憶體中的列更少，減少了記憶體需求，而且輸出可以在核心查詢執行完畢之前開始顯示。
當子查詢出現在 SELECT 的 FROM 子句中時，最簡單的行為是將子查詢評估為暫時性表格，然後針對暫時性表格執行外部 SELECT。這樣的計畫可能不是最佳的，因為暫時性表格不會有任何索引，而外部查詢（可能是聯結）將被迫對暫時性表格執行完整表格掃描。
為了克服此問題，SQLite 會嘗試扁平化 SELECT 的 FROM 子句中的子查詢。這包括將子查詢的 FROM 子句插入外部查詢的 FROM 子句中，並改寫外部查詢中參考子查詢結果集的表達式。例如
SELECT t1.a, t2.b FROM t2, (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
將使用查詢扁平化改寫為
SELECT t1.x+t1.y AS a, t2.b FROM t2, t1 WHERE z<100 AND a>5
必須滿足很長一串條件才能進行查詢扁平化。有些限制會以斜體文字標記為已過時。文件保留這些額外的限制以保留其他限制的編號。
休閒讀者預期無法理解所有這些規則。本節的主要重點是，判斷查詢扁平化是否安全或不安全的規則很微妙且複雜。多年來，過度積極的查詢扁平化已造成多項錯誤。另一方面，如果查詢扁平化較保守，則複雜查詢和/或涉及檢視的查詢效能往往會受到影響。
當使用檢視時，查詢扁平化是一種重要的最佳化，因為每次使用檢視都會轉譯成子查詢。
在 SQLite 3.7.15 (2012-12-12) 之前，FROM 子句中的子查詢會扁平化到外部查詢中，否則子查詢會在外部查詢開始之前執行完畢，子查詢的結果集會儲存在暫時表中，然後暫時表會用於外部查詢。較新的 SQLite 版本有第三個選項，即使用協程來實作子查詢。
協程類似於子常式，因為它在與呼叫者相同的執行緒中執行，並最終將控制權傳回呼叫者。不同之處在於協程也有能力在完成之前傳回，然後在下一次呼叫時從中斷處繼續執行。
當子查詢作為協程實作時，會產生位元組碼來實作子查詢，就像它是一個獨立查詢一樣，只不過它並未將結果列傳回應用程式，而是在計算完每一列後，協程會將控制權讓回給呼叫者。呼叫者接著可以使用計算出的那一列作為其計算的一部分，然後在準備好下一列時再次呼叫協程。
協程比將子查詢的完整結果集儲存在暫時表中更好，因為協程使用的記憶體較少。使用協程時，只需要記住結果的一列，而暫時表必須儲存結果的所有列。此外，由於協程不需要執行到完成才能讓外部查詢開始其工作，因此第一列的輸出可以提早出現，而且如果在完成之前放棄整體查詢，整體上執行的作業較少。
另一方面，如果必須多次掃描子查詢的結果（例如，它只是聯結中的其中一個表格），那麼最好使用暫時表來記住子查詢的完整結果，以避免多次計算子查詢。
從 SQLite 版本 3.21.0（2017-10-24）開始，查詢規劃器將總是偏好使用協程來實作包含 ORDER BY 子句且不屬於聯結一部分的 FROM 子句子查詢，當外部查詢的結果集為「複雜」時。此功能允許應用程式將昂貴的計算從排序器之前轉移到排序器之後，這可能會導致更快的運作。例如，考慮這個查詢
SELECT expensive_function(a) FROM tab ORDER BY date DESC LIMIT 5;
此查詢的目標是計算表格中最近五個條目的某個值。在上述查詢中，「expensive_function()」在排序之前呼叫，因此會在表格的每一列上呼叫，即使最終會因為 LIMIT 子句而略過的列也是如此。可以使用協程來解決這個問題
SELECT expensive_function(a) FROM ( SELECT a FROM tab ORDER BY date DESC LIMIT 5 );
在修改後的查詢中，由協程實作的子查詢計算「a」的五個最新值。這五個值從協程傳遞到外部查詢中，其中「expensive_function()」僅呼叫應用程式關心的特定列。
未來版本的 SQLite 中的查詢規劃器可能會變得夠聰明，可以自動進行上述轉換，無論是正向或反向。也就是說，未來版本的 SQLite 可能會將第一種形式的查詢轉換為第二種形式，或將第二種形式的查詢轉換為第一種形式。截至 SQLite 版本 3.22.0（2018-01-22），如果外部查詢在其結果集中未使用任何使用者定義函式或子查詢，查詢規劃器會將子查詢扁平化。然而，對於上述範例，SQLite 會實作每個查詢，就像寫出來的那樣。
查詢中包含單一 MIN() 或 MAX() 聚集函式，其引數是索引的最左邊欄位，可能會透過執行單一索引查詢，而不是掃描整個表格來滿足。範例
SELECT MIN(x) FROM table; SELECT MAX(x)+1 FROM table;
當沒有索引可用於協助評估查詢時，SQLite 可能會建立一個自動索引，僅持續單一 SQL 陳述式的時間。由於建構自動索引的成本為 O(NlogN)（其中 N 是表格中的項目數），而執行完整表格掃描的成本僅為 O(N)，因此僅當 SQLite 預期在 SQL 陳述式期間執行查詢的次數會超過 logN 時，才會建立自動索引。考慮一個範例
CREATE TABLE t1(a,b); CREATE TABLE t2(c,d); -- Insert many rows into both t1 and t2 SELECT * FROM t1, t2 WHERE a=c;
在上面的查詢中，如果 t1 和 t2 都大約有 N 列，那麼在沒有任何索引的情況下，查詢將需要 O(N*N) 時間。另一方面，建立 t2 表的索引需要 O(NlogN) 時間，而使用該索引來評估查詢需要額外的 O(NlogN) 時間。在沒有 ANALYZE 資訊的情況下，SQLite 會猜測 N 為一百萬，因此它認為建立自動索引會是較便宜的方法。
自動索引也可能用於子查詢
CREATE TABLE t1(a,b); CREATE TABLE t2(c,d); -- Insert many rows into both t1 and t2 SELECT a, (SELECT d FROM t2 WHERE c=b) FROM t1;
在此範例中，t2 表用於子查詢來轉換 t1.b 欄位的數值。如果每個表都包含 N 列，SQLite 預期子查詢將執行 N 次，因此它會認為先建立 t2 的自動暫時索引，然後使用該索引來滿足子查詢的 N 個執行個體會比較快。
可以使用 automatic_index pragma 在執行階段停用自動索引功能。預設會開啟自動索引，但可以使用 SQLITE_DEFAULT_AUTOMATIC_INDEX 編譯時間選項來變更，讓自動索引預設關閉。可以使用 SQLITE_OMIT_AUTOMATIC_INDEX 編譯時間選項來完全停用建立自動索引的功能。
在 SQLite 版本 3.8.0（2013-08-26）及後續版本中，每當準備使用自動索引的陳述式時，都會傳送 SQLITE_WARNING_AUTOINDEX 訊息至 錯誤記錄。應用程式開發人員可以而且應該使用這些警告來識別架構中需要新的持續索引。
不要將自動索引與內部索引（名稱類似「sqlite_autoindex_table_N」）混淆，後者有時會建立來實作PRIMARY KEY 約束或UNIQUE 約束。此處所述的自動索引僅存在於單一查詢期間，絕不會保留至磁碟，且僅對單一資料庫連線可見。內部索引是 PRIMARY KEY 和 UNIQUE 約束實作的一部分，具有長效性且會保留至磁碟，且對所有資料庫連線可見。「autoindex」一詞出現在內部索引的名稱中，是出於傳統原因，並不表示內部索引和自動索引相關。
自動索引與雜湊聯結幾乎是同一回事。唯一的差別在於使用 B 樹取代雜湊表。如果您願意說，為自動索引建構的暫時 B 樹實際上只是一個精緻的雜湊表，那麼使用自動索引的查詢只是一個雜湊聯結。
在這個範例中，SQLite 建構暫時索引而不是雜湊表，是因為它已經具備強健且效能優異的 B 樹實作，而雜湊表則需要另外新增。為了處理這個案例而新增一個獨立的雜湊表實作，會增加函式庫的大小（設計用於低記憶體嵌入式裝置），但效能提升卻很小。SQLite 未來可能會強化雜湊表實作，但目前看來，在客戶端/伺服器資料庫引擎可能使用雜湊聯結的案例中，繼續使用自動索引似乎比較好。
如果無法將子查詢扁平化到外部查詢中，仍然有可能透過將外部查詢中的 WHERE 子句項目「下推」到子查詢中來提升效能。請考慮以下範例
CREATE TABLE t1(a INT, b INT); CREATE TABLE t2(x INT, y INT); CREATE VIEW v1(a,b) AS SELECT DISTINCT a, b FROM t1; SELECT x, y, b FROM t2 JOIN v1 ON (x=a) WHERE b BETWEEN 10 AND 20;
無法扁平化檢視 v1，因為它是 DISTINCT。必須以子查詢執行，並將結果儲存在暫時性資料表中，然後在 t2 與暫時性資料表之間執行聯結。下推最佳化會將「b BETWEEN 10 AND 20」項目下推到檢視中。這會縮小暫時性資料表，如果 t1.b 中有索引，則有助於子查詢更快執行。產生的評估結果如下
SELECT x, y, b FROM t2 JOIN (SELECT DISTINCT a, b FROM t1 WHERE b BETWEEN 10 AND 20) WHERE b BETWEEN 10 AND 20;
無法總是使用下推最佳化。例如，如果子查詢包含 LIMIT，則下推外部查詢中 WHERE 子句的任何部分可能會變更內部查詢的結果。還有其他限制，在實作此最佳化的 pushDownWhereTerms() 常式的原始碼中以註解說明。
有時可以簡化 OUTER JOIN（LEFT JOIN、RIGHT JOIN 或 FULL JOIN）。LEFT 或 RIGHT JOIN 可以轉換為一般（INNER）JOIN，或 FULL JOIN 可以轉換為 LEFT 或 RIGHT JOIN。如果 WHERE 子句中有一些項目在簡化後保證結果相同，就會發生這種情況。例如，如果 LEFT JOIN 右手邊資料表中的任何欄位在 WHERE 子句為 true 時一定是非 NULL，則 LEFT JOIN 會降級為一般 JOIN。
判定聯結是否可以簡化的定理證明器並不完美。有時會回傳假陰性。換句話說，有時它無法證明降低 OUTER JOIN 強度是安全的，即使事實上是安全的。例如，證明器不知道datetime() SQL 函數如果其第一個引數為 NULL，則總是會回傳 NULL，因此它不會辨識出下列查詢中的 LEFT JOIN 可以強度降低
SELECT urls.url
FROM urls
LEFT JOIN
(SELECT *
FROM (SELECT url_id AS uid, max(retrieval_time) AS rtime
FROM lookups GROUP BY 1 ORDER BY 1)
WHERE uid IN (358341,358341,358341)
) recent
ON u.source_seed_id = recent.xyz OR u.url_id = recent.xyz
WHERE
DATETIME(recent.rtime) > DATETIME('now', '-5 days');
未來對證明器的加強有可能讓它辨識出某些內建函式的 NULL 輸入永遠會產生 NULL 答案。然而，並非所有內建函式都有此特性（例如 coalesce()），而且證明器永遠無法推論出 應用程式定義的 SQL 函式。
有時 LEFT 或 RIGHT JOIN 可以完全從查詢中省略，而不會改變結果。如果符合以下所有條件，就會發生這種情況
OUTER JOIN 消除通常發生在 OUTER JOIN 用於檢視內部時，然後檢視的使用方式是 LEFT JOIN 的右表或 RIGHT JOIN 的左表上的欄位都沒有被參考到。
以下是省略 LEFT JOIN 的簡單範例
CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1); CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2); CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3); SELECT v1, v3 FROM t1 LEFT JOIN t2 ON (t1.ipk=t2.ipk) LEFT JOIN t3 ON (t1.ipk=t3.ipk)
t2 表在上面的查詢中完全未被使用，因此查詢規劃器可以將查詢實作為以下寫法
SELECT v1, v3 FROM t1 LEFT JOIN t3 ON (t1.ipk=t3.ipk)
在撰寫本文時，只有 LEFT JOIN 被消除。這個最佳化尚未廣泛化到 RIGHT JOIN，因為 RIGHT JOIN 是 SQLite 相對新的新增功能。這種不對稱性可能會在未來的版本中修正。
當 WHERE 子句包含兩個或更多個由 AND 運算子連接的等號限制，且各種限制的 親和性 都相同時，SQLite 可能會使用等號的遞移性質來建構新的「虛擬」限制，這些限制可用於簡化表達式和/或改善效能。這稱為「常數傳播最佳化」。
例如，考慮以下的結構和查詢
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT); SELECT * FROM t1 WHERE a=b AND b=5;
SQLite 檢視「a=b」和「b=5」約束，並推論出如果這兩個約束為真，那麼「a=5」也必須為真。這表示可以使用 INTEGER PRIMARY KEY 的值 5 快速查詢所需的列。
此頁面最後修改於 2024-03-18 23:31:48 UTC