sql - 优化查询选择期间

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。在任何一个字段上都有索引仍然需要检查一个范围。此外，两个字段的索引不会有任何区别(只会使用第一个)。

我可以向表中添加字段/索引(因此添加一个包含两个字段信息的索引构造字段是可以接受的)。

附言对此的需求来自这个问题:Optimize SQL that uses between clause

Answer 1

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

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

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

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

我不会详细介绍空间类型如何工作以及空间索引如何有用的理论细节，但您应该看看 Jeremy Cole's explanation here关于如何使用空间数据类型和索引进行 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 函数将它们转换为数字(请参阅 documentation here 了解其工作原理)。

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): The used table type doesn't support SPATIAL indexes” ).

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 的分析，详细了解此方法与 between 子句相比的性能优势。

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

谢谢，

-地平