我有一個在文本列上索引的表，我希望我的所有查詢都回傳按名稱排序的結果，而不會影響性能。如果重要的話，表格大約有 100 萬行。
桌子 -
CREATE TABLE table (Name text)
指數 -
CREATE INDEX "NameIndex" ON "Files" (
"Name" COLLATE nocase ASC
);
查詢 1 -
select * from table where Name like "%a%"
查詢計劃，如預期的全盤掃描——
SCAN TABLE table
時間 -
Result: 179202 rows returned in 53ms
查詢 2，現在使用 order by 從索引中讀取 -
select * from table where Name like "%a%" order by Name collate nocase
查詢計劃，使用索引掃描 -
SCAN TABLE table USING INDEX NameIndex
時間 -
Result: 179202 rows returned in 672ms
用于DB Browser for SQLite獲取上面的資訊，帶有默認的 Pragmas。
我假設掃描索引與掃描表一樣高效，不是這樣還是我做錯了什么？
我注意到的另一件有趣的事情可能是相關的 -
問題 3 -
select * from table where Name like "a%"
Result: 23026 rows returned in 9ms
問題 4 -
select * from table where name like "a%" order by name collate nocase
Result: 23026 rows returned in 101ms
兩者都有相同的查詢計劃 -
SEARCH TABLE table USING INDEX NameIndex (Name>? AND Name<?)
這是預期的嗎？如果計劃相同，我會假設性能相同。
謝謝！
編輯 - 查詢速度較慢的原因是因為我使用select *而不是select name，導致 SQLite 在表和索引之間移動。
解決方案是使用聚集索引，感謝@Tomalak 幫助我找到它 -
create table mytable (a text, b text, primary key (a,b)) without rowid
默認情況下，該表將使用 a b 組合進行排序，這意味著全掃描查詢將更快（現在為 90 毫秒）。
uj5u.com熱心網友回復：
以 開頭的 LIKE 模式%永遠不能使用索引。它總是會導致全表掃描（或索引掃描，如果查詢可以被索引本身覆寫）。
當你考慮它時，這是合乎邏輯的。索引不是魔術。它們是排序的值串列，就像書中的關鍵字索引一樣，這意味著只有在您知道給定單詞的開頭時，它們才能快速查找單詞。如果您要搜索單詞的中間部分，則還必須查看書中的每個索引條目。
評論中隨后討論的結論：
獲得始終按非唯一列排序而不會降低性能的表的最佳做法是在沒有性能損失的情況下創建它ROWID，并將其轉換為相關列上的聚集索引以及使組合唯一的第二列：
CREATE TABLE MyTable (
Name TEXT COLLATE NOCASE,
Id INTEGER,
Other TEXT,
Stuff INTEGER,
PRIMARY KEY(Name, Id) -- this will sort the whole table by Name
) WITHOUT ROWID;
這將導致 INSERT/UPDATE/DELETE 操作的性能損失，但作為交換排序將是免費的，因為表已經排序。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/417996.html
標籤：