主页 / dba / 问题 / 72358
Accepted
Fake Name
Asked: 2014-07-25 22:25:29 +0800 CST

提高 sqlite3 中的 GROUP BY 查询性能

  • 772

我有一个使用 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，但在此应用程序中这种情况很少发生。

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

插入性能在这里并不重要，所以如果我需要创建一个或两个额外的索引，那很好。

我目前的想法是一个提交挂钩，它更新了一个单独的表，该表仅用于跟踪唯一项目，但这似乎有点矫枉过正。

performance optimization

2 个回答

  1. 索引可用于优化 GROUP BY，但如果 ORDER BY 使用不同的列，则排序不能使用索引（因为索引只有在数据库能够按排序顺序读取表中的行时才有用)。

    如果您在查询中使用不同的排序规则，则 COLLATE NOCASE 索引没有帮助。添加一个“正常”索引，或使用GROUP BY seriesName COLLATE NOCASE（如果允许）。

    使用 OFFSET 子句进行分页不是很有效，因为数据库仍然必须对所有行进行分组和排序，然后才能开始遍历它们。最好使用滚动光标。

    注意：不能保证dbId和dlState值来自任何特定行；SQLite 允许在聚合查询中使用非聚合列，只是为了与 MySQL 兼容。

    • 5
  2. Best Answer

    这是一个建议：添加索引(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 行。

    • 4

相关问题