當 rowID 是索引的第二列時，SQLite 是否選擇了錯誤的查詢計劃？

我正在測試我的數據庫的性能，發現 SQLite 沒有為我的查詢選擇正確的查詢計劃。

我的架構：

CREATE TABLE event (
   ID       INTEGER PRIMARY KEY NOT NULL,
   netAddr  INTEGER             NOT NULL,
   date     INTEGER             NOT NULL,
   value    INTEGER             NOT NULL);
INSERT INTO event ( netAddr, date, value )
   WITH RECURSIVE cnt(x) as (
       VALUES(1)
       UNION ALL
       SELECT x+1 FROM cnt LIMIT 1000000
   ) SELECT 250, x, x+1000 FROM cnt;
CREATE INDEX myIdx ON event ( netAddr, ID );

查詢：

SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

它執行得太慢了，我檢查了查詢計劃：

EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

這給了我

0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=?)

SQLite 拒絕使用索引的第二列，即使它是按照查詢希望的方式排序的。

然後我用不同的索引進行了測試：

CREATE INDEX dateIdx ON event ( netAddr, date );
EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND date > 100 AND date < 110
ORDER BY date DESC LIMIT 1;

這給了我

0|0|0|SEARCH TABLE event USING INDEX dateIdx (netAddr=? AND date>? AND date<?)

查詢計劃現在正如我所期望的那樣。

1. 為什麼 SQLite 不使用整個索引 ie SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)？
2. 將 rowID 作為索引中的第二列有什麼問題？
3. 有沒有辦法讓它工作而不在表或索引中添加另一列？（我的數據庫儲存空間很小。）

編輯：

嘗試使用ypercubeᵀᴹ 建議 - 結果與“慢”查詢相同SELECT value FROM event WHERE id = (SELECT id FROM event WHERE netAddr = 250 AND id > 100 AND id < 110 ORDER BY id DESC LIMIT 1) LIMIT 1;。SELECT value FROM event WHERE netAddr = 250 AND ID > 100 AND ID < 110 ORDER BY netAddr DESC, ID DESC LIMIT 1;

試圖將 ID 列的名稱更改為其他名稱 - 結果是相同的。

編輯2：

SQLite 版本 3.8.2 和 3.8.8.1

理論上，將 rowid 作為索引中的第二列沒有任何問題。

在實踐中，使用目前的 SQLite 版本，它可以按照您的預期工作：

sqlite> .eqp on
sqlite> 從事件中選擇值
...> 其中 netAddr = 250 AND ID > 100 AND ID < 110
...> 按 ID DESC 限制 1 排序；
--EQP-- 0,0,0,SEARCH TABLE 事件 USING INDEX myIdx (netAddr=?AND ID>?AND ID<?)

實際上，我發現 usingCREATE INDEX myIdx ON event ( netAddr );有效。

EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

給

0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=? AND rowid>? AND rowid<?)

即使它沒有寫在 SQLite 文件的任何地方，索引中的 rowid 列也會保持排序。因此不需要顯式創建索引( columnXXX, rowid )，創建它就足夠了( columnXXX )

引用自：https://dba.stackexchange.com/questions/149004