优化查询选择时间段

Question

给出下表：

Table events
id
start_time
end_time

有没有办法快速查找一个常数？

例如

SELECT *
FROM events
WHERE start_time<='2009-02-18 16:27:12' 
AND     end_time>='2009-02-18 16:27:12'

我正在使用MySQL。 在任一字段上建立索引仍然需要检查范围。 此外，两个字段上的索引不会产生影响（仅使用第一个字段）。

我可以将字段/索引添加到表中（因此添加包含这两个字段信息的索引构造字段是可以接受的）。

PS对此的需要来自这个问题：Optimize SQL that use Between Clause

Answer 1

我的解决方案有一个警告：

1) 该解决方案的警告是您必须对事件表使用 MyISAM 引擎。 如果您无法使用 MyISAM，则此解决方案将不起作用，因为空间索引仅支持 MyISAM。

因此，假设上述内容对您来说不是问题，那么以下内容应该可以工作并为您提供良好的性能：

此解决方案利用 MySQL 对空间数据的支持（请参阅 此处的文档）。 虽然空间数据类型可以添加到各种存储引擎中，但空间 R 树索引仅支持 MyISAM（请参阅 此处的文档），这是获得所需性能所必需的。 另一个限制是空间数据类型仅适用于数字数据，因此您不能将此技术用于基于字符串的范围查询。

我不会详细介绍空间类型如何工作以及空间索引如何有用的理论细节，但您应该查看 Jeremy Cole 的解释 关于如何使用 GeoIP 的空间数据类型和索引查找。 如果您需要原始性能并且可以放弃一些准确性，还要查看评论，因为它们提出了一些有用的观点和替代方案。

基本前提是我们可以采用起点/终点并使用它们中的两个来创建四个不同的点，一个点对应 xy 网格上以 0,0 为中心的矩形的每个角，然后快速查找空间索引来确定我们关心的特定时间点是否在矩形内。 如前所述，请参阅 Jeremy Cole 的解释，以更全面地了解其工作原理。

在您的特定情况下，我们需要执行以下操作：

1）将表更改为 MyISAM 表（请注意，除非您完全意识到此类更改的后果（例如缺少事务和表），否则不应该这样做与 MyISAM 相关的锁定行为）。

alter table events engine = MyISAM;

2) 接下来我们添加将保存空间数据的新列。 我们将使用多边形数据类型，因为我们需要能够保存完整的矩形。

alter table events add column time_poly polygon NOT NULL;

3) 接下来，我们用数据填充新列（请记住，任何更新或插入表事件的进程都需要进行修改，以确保它们也填充新列）。 由于开始和结束范围是时间，我们需要使用 unix_timestamp 函数将它们转换为数字（请参见 此处的文档了解其工作原理）。

update events set time_poly := LINESTRINGFROMWKB(LINESTRING(
    POINT(unix_timestamp(start_time), -1),
    POINT(unix_timestamp(end_time), -1),
    POINT(unix_timestamp(end_time), 1),
    POINT(unix_timestamp(start_time), 1),
    POINT(unix_timestamp(start_time), -1)
  ));

4）接下来我们将空间索引添加到表中（如前所述，这仅适用于MyISAM表，并且会产生错误“ERROR 1464（HY000）：使用的表类型不支持SPATIAL索引”）。

alter table events add SPATIAL KEY `IXs_time_poly` (`time_poly`);

5) 接下来，您将需要使用以下选择，以便在查询数据时使用空间索引。

SELECT * 
FROM events force index (IXs_time_poly)
WHERE MBRCONTAINS(events.time_poly, POINTFROMWKB(POINT(unix_timestamp('2009-02-18 16:27:12'), 0)));

强制索引是为了 100% 确保 MySQL 将使用该索引进行查找。 如果一切顺利，在上面的 select 上运行解释应该显示类似以下内容：

mysql> explain SELECT *
    -> FROM events force index (IXs_time_poly)
    -> on MBRCONTAINS(events.time_poly, POINTFROMWKB(POINT(unix_timestamp('2009-02-18 16:27:12'), 0)));
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | B     | range | IXs_time_poly | IXs_time_poly | 32      | NULL |    1 | Using where | 
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)

请参阅 Jeremy Cole 的分析，详细了解此方法与 Between 子句相比的性能优势。

如果您有任何疑问，请告诉我。

谢谢，

-迪平

Answer 2

在 MySQL 中没有有效的方法来精确执行此查询。

不过，如果您的范围不重叠，您可以仅使用 start_time <= const 以及 ORDER BY start_time DESC LIMIT 1 并进一步检查 end_time > = 常量。

您需要在函数中执行此操作，因为 MySQL 由于某种原因不会在子查询中对 ORDER BY 使用 INDEX RANGE SCAN如果范围条件取自超级查询。

CREATE UNIQUE INDEX ux_b_start ON b (start_date);

CREATE FUNCTION `fn_get_last_b`(event_date TIMESTAMP) RETURNS int(11)
BEGIN
  DECLARE id INT;
  SELECT b.id
  INTO id
  FROM b
  FORCE INDEX (ux_b_start)
  WHERE b.start_time <= event_date
  ORDER BY
    b.start_time DESC
  LIMIT 1;
  RETURN id;
END;

SELECT COUNT(*) FROM a;

1000


SELECT COUNT(*) FROM b;

200000

SELECT *
FROM (
  SELECT fn_get_last_b(a.event_time) AS bid,
         a.*
  FROM a
) ao, b FORCE INDEX (PRIMARY)
WHERE b.id = ao.bid
  AND b.end_time >= ao.event_time

1000 rows fetched in 0,0143s (0,1279s)

Answer 3

我对 MySQL 没有太多经验，但在 MS SQL Server 上，在两列上添加索引允许在 1M 行表上进行索引查找和返回时间，响应时间从 1-2 秒缩短到毫秒。

您似乎看到了不同的结果。 我想知道是否有限制因素造成了影响。 我有一个检查约束来强制 start_time < 时间结束。

Answer 4

您基本上得到了一个具有 2 个截然不同的范围条件的查询。 您正在使用 >=，对于 MySQL 来说，这始终是范围扫描。 此处有文档用于优化范围扫描。

最重要的是，MySQL 会执行额外的检查来过滤掉满足范围条件的行，然后满足 WHERE 子句的其余部分，在您的情况下，这是另一个范围条件。

Answer 5

我本来想问一个关于优化事件搜索（具有开始和停止时间的项目）的类似问题，并且我已经在使用不同的方法，所以我将把它扔在那里。

基本上，如果您知道事件永远不会大于给定的持续时间，则可以搜索大于最大持续时间的有界范围，然后添加限制以消除匹配的额外内容。 因此，要获取与搜索时间相交的时间：

SELECT *
FROM events
WHERE 
   ( start_time BETWEEN ( 'search_start' - INTERVAL 2 DAY ) and 'search_end' )
   AND end_time >= 'search_start'

...您需要在 start_time 上建立索引。

（注意——我的表中有数百万个事件分布在 4 年的时间里，没有超过 24 小时的记录......我不知道这相对于空间搜索方法的表现如何，因为我必须自己去尝试一下.)

Answer 6

在一张桌子上你能做的事情并不多。 如果优化这些查询 1) 是必要的 2) 必须在 SQL 级别完成，那么您需要创建一个派生表：

Table event_times
id
event_id
mark_time

并为每个事件跨越的每个时间单位向其中添加一条记录。 然后你

SELECT *
FROM events
LEFT JOIN event_times ON event_id = events.id
WHERE mark_time = '2009-02-18 16:27:12'

可以通过如何定义“时间单位”来使这个表变得不那么荒谬，即如果你将mark_time的分辨率限制为分钟或小时而不是秒。