代码之家 › 专栏 › 技术社区 › Dominic Rodger

sqlite查询优化

sqlite sql

Dominic Rodger · 技术社区 · 16 年前

我有一张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)

有没有更好的方法来进行这种查询?更好的桌子布局?目前,这种查询在我的开发盒上花费了大约400毫秒,如果我能减少100毫秒左右就好了(我的目标时间实际上是100毫秒,但我怀疑这是否可控)。

FWIW action

2 回复 | 直到 16 年前

Cade Roux 16 年前

正确先于速度——你的问题:

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)

不执行您描述的任务——内部选择可能会返回 uuid AND

Alex Martelli 16 年前

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(actiondate)和actiondate DESC、用户名ASC、INCLUNE(status)的索引上表现良好,但显然要看看查询计划。

如果不增加uuid,您将需要某种规则来确保为某人选择最新的操作,因为除非用户名、操作日期是唯一的,否则您的原始ORDER BY actiondate DESC限制1中没有任何内容可以确保您每次都选择正确的行。如果用户名和操作日期是唯一的,那么您可以使用以下内容:

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