提高 sqlite3 中的 GROUP BY 查詢性能

我有一個使用 sqlite3 作為數據庫的小型 Web 應用程序（數據庫相當小）。

現在，我正在使用以下查詢生成一些要顯示的內容：

SELECT dbId,
       dlState,
       retreivalTime,
       seriesName,
       <snip irrelevant columns>
       FROM DataItems
       GROUP BY seriesName
       ORDER BY retreivalTime DESC
       LIMIT ?
       OFFSET ?;

其中limit通常為 ~200，並且offset為 0（它們驅動分頁機制）。

無論如何，現在，這個查詢完全扼殺了我的表現。在大約 67K 行的表上執行大約需要 800 毫秒。

seriesName我在和上都有索引retreivalTime。

sqlite> SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;
<snip irrelevant indexes>
DataItems_seriesName_index
DataItems_time_index           // This is the index on retreivalTime. Yeah, it's poorly named

但是，EXPLAIN QUERY PLAN似乎表明它們沒有被使用：

sqlite> EXPLAIN QUERY PLAN SELECT dbId, 
                                 dlState, 
                                 retreivalTime, 
                                 seriesName 
                                 FROM 
                                     DataItems 
                                 GROUP BY 
                                     seriesName 
                                 ORDER BY 
                                     retreivalTime 
                                 DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

上的索引seriesName是COLLATE NOCASE，如果相關的話。

如果我放棄GROUP BY，它將按預期執行：

sqlite> EXPLAIN QUERY PLAN SELECT dbId, dlState, retreivalTime, seriesName FROM DataItems ORDER BY retreivalTime DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems USING INDEX DataItems_time_index

基本上，我的天真假設是執行此查詢的最佳方法是從 中的最新值向後走retreivalTime，每次seriesName看到新值時，將其附加到臨時列表，最後返回該值。對於較大的情況，這會導致性能稍差OFFSET，但在此應用程序中這種情況很少發生。

如何優化此查詢？如果需要，我可以提供原始查詢操作。

插入性能在這裡並不重要，所以如果我需要創建一個或兩個額外的索引，那很好。

---

我目前的想法是一個送出掛鉤，它更新了一個單獨的表，該表僅用於跟踪唯一項目，但這似乎有點矯枉過正。

這是一個建議：添加索引(seriesName, retreivalTime)並嘗試此查詢。它不會超級快，但可能比你擁有的更有效：

SELECT d.dbId,
      d.dlState,
      d.retreivalTime,
      d.seriesName,
       <snip irrelevant columns>
FROM DataItems AS d
 JOIN
   ( SELECT seriesName, 
            MAX(retreivalTime) AS max_retreivalTime
     FROM DataItems
     GROUP BY seriesName
     ORDER BY max_retreivalTime DESC
     LIMIT ?
     OFFSET ?
   ) AS di
   ON  di.seriesName = d.seriesName
   AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;

---

或者（變體）也使用 PK，帶有索引(seriesName, retreivalTime, dbId)和查詢：

SELECT d.dbId,
      d.dlState,
      d.retreivalTime,
      d.seriesName,
       <snip irrelevant columns>
FROM DataItems AS d
 JOIN
   ( SELECT dbId
     FROM DataItems
     GROUP BY seriesName
     ORDER BY MAX(retreivalTime) DESC
     LIMIT ?
     OFFSET ?
   ) AS di
   ON  di.dbId = d.dbId
ORDER BY d.max_retreivalTime ;

---

查詢背後的邏輯是僅使用索引進行派生表計算（找到每個 seriesName 的 max(retreival-time) 然後排序並執行偏移限制的事情。）

然後表本身將僅用於獲取要顯示的那 200 行。

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