通過直接查詢索引獲取不同的值

我正在建構一個相對較大的 SQLite 數據庫。該數據庫的一般訪問配置文件是：

1. 初始載入 3 億行。
2. 每行的初始讀取，以及大約 3000 萬行的更新
3. 通過帶有 MyDatabaseReader 類的 python 腳本進行持續的只讀訪問。

資料結構是一個扁平的非規範化表，查詢的形式為：

WHERE chromosome=TEXT, position=INT, reference_sequence=TEXT, alternate_sequence=TEXT

我想根據數據庫的內容驗證傳入查詢是否請求有效的染色體名稱。請求染色體=‘chr13’ 是有效的，但在 ‘13’ 或 ‘chr31’ 的情況下，我希望我的 python 腳本拋出錯誤，而不是默默地返回零行。為了實現這一點，我在我的 python 類的初始化期間，它會進行初始查詢以獲取一組有效的染色體名稱：

SELECT DISTINCT chromosome from dbsnp;

此查詢需要很長時間，並且會阻礙執行。我已經嘗試了（染色體、位置、reference_sequence、alternate_sequence）上的複合索引以及染色體上的單個索引，並通過 EXPLAIN QUERY PLAN 驗證了在這兩種情況下都使用了索引。

sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT chromosome FROM dbsnp;
order|from|detail
0|0|TABLE dbsnp WITH INDEX chromosome ORDER BY

我的問題：是否有一些 SQL 技巧可以直接從染色體索引中查詢。我不關心行中的其他任何內容，並且索引似乎是我要返回的數據的預建構版本。

或者，我正在考慮建構一個染色體名稱表，我在數據庫載入和更新後使用上面的 SELECT DISTINCT 查詢填充該表。因為它讓我害怕建構一個可能與主表不同步的靜態表，所以我正在考慮在主表更改時更新染色體名稱表的觸發器。但是，我擔心如果我更新主表中的行，這可能會導致嚴重的流失，更重要的是，我正在重新發明索引中基本包含的內容。

有沒有一種好方法可以直接從索引中獲取我的不同值查詢，或者，如果染色體的查詢值超出包含的值集（注意：位置、參考、並且 alt 序列有時會查詢意外值，因此返回的零行錯誤將不起作用）。

謝謝

我從來沒有使用過 SQLite，所以請耐心等待。但似乎這個問題在許多 RDBMS 平台中很常見。

當您從列中選擇不同的值時，您最終會掃描索引中的所有行：

如果表中的行不多或列沒有很多重複值，這可能是一個很好的策略。但是，如果每個不同的值都有數百萬行，那麼您將掃描數百萬行只是為了返回一個唯一值。對於這樣的數據集，有時最好獲取第一個不同的值，然後跳到下一個值，依此類推。這可以通過某些平台中的遞歸來實現。您還可以一次執行一個查詢，每個查詢都獲得下一個不同的值。例如，您可以使用以下查詢獲取第一個值：

SELECT MIN(chromosome) FROM dbsnp;

然後使用此查詢獲取下一個值（用第一個查詢的值替換過濾器）：

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;

接下來：

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;

等等。對於這些查詢，我得到了索引搜尋：

對於相對較小的數據集，單個不同查詢大約需要 320 毫秒，而一系列LIMIT 1查詢只需要 4 毫秒。您當然需要編寫更多程式碼才能使用此解決方案，但可能值得一試。

分貝小提琴

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