加快对日期部分位于不同列的表的select查询

Question

运行以下查询需要很长时间。

logins表有10M条记录，并且有一个关于月、日和年的索引。可以做些什么来加快查询速度？

SELECT
cast(logins.month || '/' || logins.day || '/' || logins.year as date) as loginDt, logins.person
FROM logins
LEFT JOIN MIN_LUNCH
    ON MIN_LUNCH.person = logins.person
    AND MIN_LUNCH.date = cast(logins.month || '/' || logins.day || '/' || logins.year as date)
WHERE
    cast(logins.month || '/' || logins.day || '/' || logins.year as date) between '01/01/2010' and '03/01/2010'

Answer 1

要修复查询，必须修改表结构。考虑使用BIGINT列存储unix-timestamp或DATE列。这将使查询数据库变得更容易、更快。

更改结构后，您的查询可能如下所示：

SELECT
  from_unixtime(logins.login_date, '%m-%d') as loginDt, 
  logins.person
FROM 
  logins
  LEFT JOIN MIN_LUNCH ON MIN_LUNCH.person = logins.person AND MIN_LUNCH.date = logins.date
WHERE 
  logins.date between 1262332800 and 1267430400;

Answer 2

对于您提供的日期间隔，应该可以简化WHERE子句：

WHERE logins.month IN (1, 2, 3) AND logins.year = 2010

查询的这一部分应该能够使用现有的索引，但是仍然需要使用JOIN条件，其中需要将date数据类型与包含日期部分的三列相匹配：

MIN_LUNCH.date = cast(logins.month || '/' || logins.day || '/' || logins.year as date)

在这里，对于每条记录，RDBMS都需要执行强制转换操作；这会破坏现有的索引。

出于这个原因以及使用SQL数据类型的原因，我建议您修复数据库结构，并在logins表中将日期存储为dates。

您可以简单地添加一个新列，并从现有数据填充它：

ALTER TABLE logins ADD login_date DATE; -- or the relevant date datatype for your RDBMS
UPDATE logins SET login_date =
    CAST(logins.month || '/' || logins.day || '/' || logins.year as date);

从那时起，您可以在两个表之间使用简单的连接。查询应从以下索引中受益：

logins(person, login_date)
min_lunch(person, date)

Answer 3

假设您不能更改表，我认为您可以通过使用日期部分将登录表限制为指定的日期范围来提高查询速度，这样至少您不会对表中的每一行执行这种强制转换，并且您的索引也不会完全无用。

SELECT
  loginRange.loginDt, loginRange.person
FROM
  (SELECT
    cast(logins.month || '/' || logins.day || '/' || logins.year as date) as loginDt, logins.person 
    FROM logins
    WHERE logins.month IN ('01','02','03') AND logins.year = '2010') as loginRange
  LEFT JOIN MIN_LUNCH ON
     MIN_LUNCH.person = logins.person
     AND MIN_LUNCH.date = loginRange.loginDt

显然，如果表使用了正确的数据类型，那么它就不会像预期的那样好，如果您可以修改表，那么就应该对其进行修复。