優化嵌套 SQLite 查詢

我有一個名為 SQLite 的表hashbands，如下所示：

hashband | file_id | window_id
------------------------------
potato   | 0       | 0
potato   | 1       | 0

該表有 100M+ 行（大聲笑）。我需要找到具有兩個或多個不同 file_id 值的所有雜湊帶，然後我需要獲取包含這些雜湊帶的所有行並按雜湊帶對結果進行排序。現在我正在使用這個：

 WITH file_id_counts AS (
   SELECT hashband, COUNT(DISTINCT(file_id)) as count
   FROM hashbands
   GROUP BY hashband
   HAVING COUNT > 1
 ) SELECT hashband, file_id, window_id
   FROM hashbands
   WHERE hashband IN (SELECT hashband from file_id_counts)
   ORDER BY hashband

有沒有人看到加快這個查詢的方法？任何指針都會有所幫助！

您看到的大部分性能問題可能只是 SQLite 數據限制的正常約束。您的查詢是 合理的，我不相信除了將WHERE謂詞重寫為更有效地與INNER JOIN這樣的關係之外，您可以做很多事情來優化它：

WITH file_id_counts AS (
   SELECT hashband, COUNT(DISTINCT(file_id)) as count
   FROM hashbands
   GROUP BY hashband
   HAVING COUNT > 1
 ) SELECT hashband, file_id, window_id
   FROM hashbands
   INNER JOIN file_id_counts
       ON hashbands.hashband = file_id_counts.hashband
   ORDER BY hashband

這在邏輯上是等效的，並且它可能更快的推理是因為該IN子句是一堆子句的語法糖，OR其效率可能低於INNER JOIN上面所做的直接單個相等運算符。

此外，確保您的hashbands表至少(hashband)或可能有一個索引(hashband, file_id)應該會有所幫助（如果一個不存在的話）。

最後，如果可能的話，刪除您的ORDER BY子句，而是在您的消費應用程序中進行排序可能也會有所幫助。雖然這主要只是將排序的責任轉移到呼叫堆棧的不同部分，但通常在數據庫中排序會增加一些額外的複雜性，有時可以解決這些複雜性，並且在消費應用程序中執行效率會更高。另外，在我看來，排序實際上是一種表示邏輯（至少在不用於查詢的功能目的時）。

這個 SQLite 數據庫在哪裡？…一個移動應用程序，還是在它自己的伺服器上？

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