使用多个表提高SQL性能

Question

我有两张表：Log（id，user，action，date）和ActionTypes（action，type）。给定一个动作A0和一个类型T0，我想要计算每个用户在使用A0后立即使用其他动作Ai的次数，但跳过不属于类型T0的Log动作。例如：

Log:

id   user   action        date
----------------------------------------
1    mary   start   2012-07-16 08:00:00
2    mary   open    2012-07-16 09:00:00
3    john   start   2012-07-16 09:00:00
4    mary   play    2012-07-16 10:00:00
5    john   open    2012-07-16 10:30:00
6    mary   start   2012-07-16 11:00:00
7    mary   jump    2012-07-16 12:00:00
8    mary   close   2012-07-16 13:00:00
9    mary   delete  2012-07-16 14:00:00
10   mary   start   2012-07-16 15:00:00
11   mary   open    2012-07-16 16:00:00

动作类型：

action  type
--------------
start   0
open    1
play    1
jump    2
close   1
delete  1

因此，针对操作“start”和类型“1”，答案将是：

user   action    ntimes
------------------------
mary   open      2
mary   close     1
john   open      1

我的任务是

SELECT b.user,b.action, count(*)
FROM log a, log b
WHERE a.action='start' AND b.date>a.date AND a.user=b.user AND
      1=(select type from ActionTypes where action=b.action) AND
      not exists (SELECT c.action FROM log c where c.user=a.user AND                  
                  c.date>a.date and c.date<b.date and                            
                  1=(select type from ActionTypes where action=c.action))
GROUP BY b.user,b.action

我们的日志表有大约1百万个元组，查询功能可以使用，但是速度太慢了。我们正在使用SQLServer。有什么提示可以使它更快吗？谢谢。

- SAL PIMIENTA

---

这是哪个版本的Sql Server？ - Nikola Markovinović

Answer 1

你可以试着运行这个查询吗？它使用exists函数来测试前一个时间记录是否是所请求的类型。相信它比自连接更快速。 我在Sql Fiddle上放了一个演示。

select log.[user], log.action, count(*) ntimes
  from log
 inner join actiontype t
    on log.action = t.action
 where t.type = 1
   and exists (select *
                 from 
                   (select top 1 t1.type
                      from log l1
                     inner join actiontype t1
                        on l1.action = t1.action
                     where l1.[user] = log.[user]
                       and l1.date < log.date
                       and t1.type in (0, 1)
                     order by l1.date desc
                   ) prevEntry
                where prevEntry.type = 0
               )
 group by log.[user], log.action

我不明白为什么结果列表中会出现mary\ close。前面的记录是类型为2的跳跃，不应该被跳过以到达开头。

- Nikola Markovinović

---

我们必须考虑（对于每个用户），在每次“开始”之后，第一个类型为1的动作（“跳跃”被跳过，因为它不是类型1，因此元组＃6之后的第一个类型1的动作是“关闭”）。 - SAL PIMIENTA

在这种情况下，对先前输入进行测试应该过滤掉不在（A0，Ai）之间的任何内容-请参见修订后的答案。 - Nikola Markovinović

非常感谢您的回答。我现在会尝试使用真实数据。 - SAL PIMIENTA

Answer 2

在借鉴@Nikola Markovinović的设置后，我想出了以下解决方案：

WITH ranked AS (
  SELECT
    L1.[user],
    L2.action,
    rnk = ROW_NUMBER() OVER (PARTITION BY L1.id ORDER BY L2.date)
  FROM Log L1
    INNER JOIN Log L2 ON L2.[user] = L1.[user] AND L2.date > L1.date
    INNER JOIN ActionType at ON L2.action = at.action
  WHERE L1.action = @Action
    AND at.type   = @Type
)
SELECT
  [user],
  action,
  ntimes = COUNT(*)
FROM ranked
WHERE rnk = 1
GROUP BY
  [user],
  action
;

基本上，这个查询从Log表中选择所有具有指定操作的用户记录，然后将该子集连接回Log以检索在第一个子集中跟随那些指定类型的所有操作，沿途按date的升序排列它们（使用ROW_NUMBER()函数）。然后查询仅检索1的排名行，按user和action对它们进行分组并计算组中的行数。

您可以在SQL Fiddle中查看（并尝试）一个工作示例。

- Andriy M

Answer 3

如果您的操作查询和所有关系字段都是整数而不是字符串，那么速度将会更快。

要加快查询速度的唯一方法是更改数据库结构。关系必须被索引并且必须是整数而不是字符串。例如：

id   user   action        date
----------------------------------------
1    mary   1   2012-07-16 08:00:00
2    mary   2   2012-07-16 09:00:00
3    john   3   2012-07-16 09:00:00
4    mary   1   2012-07-16 10:00:00
5    john   3   2012-07-16 10:30:00
6    mary   4   2012-07-16 11:00:00
7    mary   5   2012-07-16 12:00:00
8    mary   6   2012-07-16 13:00:00
9    mary   1   2012-07-16 14:00:00
10   mary   3   2012-07-16 15:00:00
11   mary   1   2012-07-16 16:00:00

这将解决你的问题。

另外，如果你有1到9个操作类型，你可以将操作转换成tinyint类型，如果你添加了一个带有主键的id和tinyint，将会使查询更加容易（使用简单的连接），并且你的数据库将更加灵活以适应未来的变化。例如：

id action  type
--------------
1  start   0
2  open    1
3  play    1
4  jump    2
5  close   1
6  delete  1

在“日志”表中，“操作”具有对该id的外键，而id是主键。

我认为主要问题在于您没有创建索引和外键关系。

- John Skoumbourdis

---

感谢您的回答。不幸的是，我们不允许更改表格的结构。 - SAL PIMIENTA

Answer 4

我略微不同意这些陈述：

1. ...作为整数而不是字符串会更快

   这并不完全正确，一旦列 action 被索引，整数或字符串之间的差异很小。

2. ...使查询更快的唯一方法是改变数据库的结构

   在这种情况下，可以通过以下方式优化查询：

   • 避免在连接数据集（Log x ActionTypes）上进行过滤，并尝试尽早进行过滤（在下面的示例中，在内部子选择中进行过滤）。
   • 避免重复的过滤条件（where）。即使 SQL Server 在内部优化这些查询，但重复通常表示您正在进行几次计算，大多数情况下，您可以找到只能将条件放置一次的解决方案（在下面的示例中，您可以在 group by 之前放置 where 条件）。
   • 您最好的朋友是“SQL 查询分析器（优化器）”。它是 Sql Server Manager Studio 中的内置工具，它将考虑数据量显示 sql 查询执行成本。它是一个非常好的工具，有助于找到查询中的瓶颈。

   这是一个简化的查询，它将生成您需要的结果（它是在 Oracle 上编写和测试的，因为我已经有一段时间没有使用 ms sql server）：

select
  "user",
  action,
  count(*)
from action_log
where action not in ( --exclusion criteria
    select action_type."action"from action_type where action_type."type" = 1
)
group by "user", action

- Petro Semeniuk