优化查询选择期间

鉴于下表:

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需要这个来自这个问题:优化使用between子句的SQL

我的解决方案有一点需要注意:

1)对此解决方案的警告是,您必须将MyISAM引擎用于事件表.如果您不能使用MyISAM,那么此解决方案将无法工作,因为空间索引仅支持MyISAM.

因此,假设上述内容对您来说不是问题,以下内容应该起作用并为您提供良好的性能:

该解决方案利用MySQL对空间数据的支持(参见此处的文档).虽然空间数据类型可以添加到各种存储引擎,但只有MyISAM支持Spatial R-Tree索引(请参阅此处的文档),以获得所需的性能.另一个限制是空间数据类型仅适用于数字数据,因此您不能将此技术用于基于字符串的范围查询.

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

基本前提是我们可以采用开始/结束并使用它们中的两个来创建四个不同的点,一个用于在xy网格上以0,0为中心的矩形的每个角,然后快速查找空间index用于确定我们关心的特定时间点是否在矩形内.如前所述,请参阅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将使用索引进行查找.如果一切顺利,上面的选择说明应该显示类似于以下内容:

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的分析.

如果您有任何疑问,请告诉我.

谢谢,

-蘸