sql - sqlite查询优化

Question

我有一个sqlite表actions看起来像这样：

uuid varchar (36)
actiondate int
username varchar (16)
mood int
bonus int
status varchar (80)
... bunch of other similar fields (all short varchar or int fields)

这种设计对于大多数类型的查询似乎都足够有效，但是在特定情况下有点挣扎，在这种情况下，我需要获取有关每个用户在给定日期执行的最新操作的一些数据。

我希望能够做这样的事情：

SELECT status, actiondate
FROM actions WHERE actiondate < 20061231
GROUP BY username
ORDER BY actiondate DESC

但是，未针对order子句进行汇总，order子句仅确定结果按什么顺序返回，这很有意义。

所以，我有这个：

SELECT actiondate, status FROM actions
WHERE actiondate < 20061231 and
uuid = (SELECT uuid from actions as alt
        WHERE alt.username = actions.username
        ORDER BY actiondate DESC LIMIT 1)

有没有更好的方法来进行这种查询？更好的表格布局？目前，这种查询在我的开发箱上花费了大约400ms，如果我可以将其减少100ms左右（我的目标时间实际上是100ms，但我对此是否可疑表示怀疑），那将是一个很好的选择。

我显然有关于用户名和日期的索引（我实际上有几个：一个似乎很适合慢查询的索引；一个关于用户名；一个关于日期ASC；一个关于日期DESC和一个关于uuid）。

FWIW， action表中可能包含100到30,000行。

Answer 1

您的索引应涵盖查询中使用的所有列，以实现最佳性能。

在这种情况下，我不确定嵌套查询的性能。如果执行计划未显示其将其转换为良好的嵌套联接，则我希望加入子查询。

对于这样的事情，如果可能，我可能会避免使用UUID，否则，我会确保UUID在增加，因此您可以这样写：

SELECT actiondate
    ,status
FROM actions
INNER JOIN (
    SELECT username
        ,MAX(uuid) as last_uuid from actions
    WHERE actiondate < 20061231
    GROUP BY username
) AS last_occur
    ON last_occur.username = actions.username
    AND last_occur.last_uuid = actions.uuid
WHERE actiondate < 20061231

我认为这应该对用户名ASC，uuid DESC，INCLUDE（操作日期）和操作日期DESC，用户名ASC，INCLUDE（状态）进行索引，效果很好，但是显然要考虑查询计划。

如果不增加uuid，您将需要某种规则来确保您选择一个人的最新操作，因为除非用户名，actiondate是唯一的，否则原始ORDER BY actiondate DESC限制1中没有任何内容可以确保您选择了每次更正一行。如果用户名，actiondate是唯一的，则可以使用以下命令：

SELECT actiondate
    ,status
FROM actions
INNER JOIN (
    SELECT username
        ,MAX(actiondate) as last_actiondate from actions
    WHERE actiondate < 20061231
    GROUP BY username
) AS last_occur
    ON last_occur.username = actions.username
    AND last_occur.last_actiondate = actions.actiondate
WHERE actiondate < 20061231

如果不是唯一的，它将仍然有效，但是您将在一个人的上次操作日期为他执行多个操作。在这种情况下（更好），推荐索引也将有所不同，因为不需要大的uuid。