php - 我想改进非常具体的 MySQL 查询

Question

这是我的场景:我有一个包含事件的表，每个事件都有一个名为“created”的字段，其中包含创建该事件的时间戳。现在我需要将事件从最新到最旧排序，但我不希望 MySQL 将它们全部返回。我只需要给定时间间隔内的最新信息，例如 24 小时范围内的信息(编辑:我想要一个灵活的解决方案，不仅适用于 24 小时范围内，而且可能每隔几个小时)。我只需要最后 10 天。我已经做到了这一点，但我确信以最低效的方式，即类似的方式:

$timestamp = time();

for($i = 0; $i < 10; $i++) {
    $query = "SELECT * FROM `eventos` WHERE ... AND `created` < '{$timestamp}' ORDER BY `created` DESC LIMIT 1";    
    $return = $database->query( $query );

    if($database->num( $return ) > 0) {
        $event = $database->fetch( $return );
        $events[] = $event;

        $timestamp = $timestamp - 86400;
    }
}

希望我说得足够清楚。谢谢，耶稣。

Answer 1

如果您有一个以 created 作为前导列的索引，MySQL 可能会进行反向扫描。如果您有 24 小时的时间段没有任何事件，您可能会返回不是来自该时间段的行。为了确保你在那个时期得到一行，你真的需要在 created 列上也包含一个下限，就像这样:

SELECT * FROM `eventos`
 WHERE ... 
   AND `created` <  FROM_UNIXTIME( {$timestamp} )
   AND `created` >= DATE_ADD(FROM_UNIXTIME( {$timestamp} ),INTERVAL -24 HOUR)
 ORDER BY `created` DESC
 LIMIT 1

我认为此处性能的重要关键是以 created 作为前导列的索引，以及 WHERE 子句中引用的所有(或大部分)其他列，并确保该索引由您的查询使用。

如果您需要不同的时间间隔，精确到秒，这种方法可以很容易地推广。

SELECT * FROM `eventos`
 WHERE ... 
   AND `created` <  DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL  0*{$nsecs} SECOND)
   AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*{$nsecs} SECOND)
 ORDER BY `created` DESC
 LIMIT 1

从您的代码来看，24 小时周期似乎在任意时间有界...如果时间函数返回，例如1341580800('2012-07-06 13:20')，那么你的十个时间段就是当天的13:20到次日的13:20。

(注意:如果您的参数是一个 unix 时间戳整数，请确保它被数据库正确解释。)

在单个查询中提取十行可能更有效。如果可以保证“时间戳”是唯一的，那么就可以制作这样的查询，但查询文本将比您现在拥有的复杂得多。我们可能会弄乱在每个时间段内获取 MAX(timestamp_)，然后将其连接回去以获取该行……但这真的很麻烦。

如果我要尝试拉出所有十行，我可能会尝试使用 UNION ALL 方法，不是很漂亮，但至少可以对其进行调整。

SELECT p0.*
  FROM ( SELECT * FROM `eventos` WHERE ... 
            AND `created` <  DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL  0*24 HOUR)
            AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*24 HOUR)
          ORDER BY `created` DESC LIMIT 1
       ) p0 
 UNION ALL           
SELECT p1.*
  FROM ( SELECT * FROM `eventos` WHERE ... 
            AND `created` <  DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*24 HOUR)
            AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -2*24 HOUR)
          ORDER BY `created` DESC LIMIT 1
       ) p1 
 UNION ALL           
SELECT p2.*
  FROM ( SELECT * FROM `eventos` WHERE ... 
            AND `created` <  DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -2*24 HOUR)
            AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -3*24 HOUR)
          ORDER BY `created` DESC LIMIT 1
       ) p2 
 UNION ALL           
SELECT p3.*
  FROM ...

同样，这可以概括为以秒数作为参数传递。将 HOUR 替换为 SECOND，并将“24”替换为具有秒数的绑定(bind)参数。

它相当冗长，但应该可以正常运行。

---

另一种非常困惑和复杂的方法是使用内联 View 获取十个周期的结束时间戳，如下所示:

     SELECT p.period_end
       FROM (SELECT DATE_ADD(t.t_,INTERVAL -1 * i.i_* {$nsecs} SECOND) AS period_end
               FROM (SELECT FROM_UNIXTIME( {$timestamp} ) AS t_) t
               JOIN (SELECT 0 AS i_
                     UNION ALL SELECT 1
                     UNION ALL SELECT 2
                     UNION ALL SELECT 3
                     UNION ALL SELECT 4
                     UNION ALL SELECT 5
                     UNION ALL SELECT 6
                     UNION ALL SELECT 7
                     UNION ALL SELECT 8
                     UNION ALL SELECT 9
                    ) i
            ) p

然后将其加入到您的表中...

  ON `created` < p.period_end
 AND `created` >= DATE_ADD(p.period_end,INTERVAL -1 * {$nsecs} SECOND)

然后为每个周期 GROUP BY p.period_end 拉回 MAX(created)，将其包装在内联 View 中。

然后将其连接回您的表格以获取每一行。

但这确实非常困惑、难以理解，而且不太可能比您已经在做的事情更快(或更有效)。您可以做出的最大改进是运行 9 个查询所需的时间。

---