如何为这种查询索引两个日期列

Question

我有一个如下的 MySQL 表：

CREATE TABLE `dates` (
`id`  int UNSIGNED NULL AUTO_INCREMENT ,
`object_id`  int UNSIGNED NOT NULL ,
`date_from`  date NOT NULL ,
`date_to`  date NULL ,
`time_from`  time NULL ,
`time_to`  time NULL ,
PRIMARY KEY (`id`)
);

这通常是通过以下方式进行查询：

SELECT object_id FROM `dates`
WHERE NOW() BETWEEN date_from AND date_to

我应该如何对表进行索引？是创建一个针对 date_from 和另一个针对 date_to 的索引，还是使用两列的组合索引更好？

- acme

---

我觉得单独使用date_from创建索引比联合索引更好。 - Sanjay Goswami

你[可能]感觉不对。假设某个对象有10行数据，其中8行的结束日期已经过去，1行是“当前”，1行是“未来”。在“NOW()>date_from”条件下，有多少行数据被过滤掉了（答案：只有1行）？在“NOW()<date_to”条件下，有多少行数据被过滤掉了（答案：8行）？索引的作用是什么？是尽可能长时间地保留尽可能多的行数据，还是尽可能早地过滤掉尽可能多的行数据？只有当“未来行数”可能比“过去行数”更高时，你的“感觉”才是正确的。 - Erwin Smout

Answer 1

针对此查询：

WHERE NOW() >= date_from 
  AND NOW() <= date_to

一个复合索引 (date_from, date_to) 是无用的。

创建两个索引：(date_from) 和 (date_to)，让SQL优化器每次决定使用哪一个。根据值和选择性，优化器可能选择其中一个索引或者都不使用。没有简单的方法可以创建一个同时考虑这两个条件的索引。

（如果你能将日期转换为纬度和经度，则可以使用空间索引来优化这种情况。）

更新：

我的错误。对于这个查询，(date_from, date_to, object_id)上的索引在某些情况下确实可以使用。如果NOW() <= date_from的选择性足够高，则优化器选择使用此索引，而不是对表进行完整扫描或使用其他索引。这是因为它是一个覆盖索引，意味着不需要从表中获取任何数据，只需要从索引数据中读取即可。

小注（与性能无关，仅涉及查询正确性）。您的条件等效于：

WHERE CURRENT_DATE() >= date_from 
  AND ( CURRENT_DATE() + INTERVAL 1 DAY <= date_to
       OR  ( CURRENT_DATE() = NOW() 
         AND CURRENT_DATE() = date_to
           )
      )

您确定要那个还是想要这个：

WHERE CURRENT_DATE() >= date_from 
  AND CURRENT_DATE() <= date_to

< p > NOW()函数返回一个DATETIME，而CURRENT_DATE()返回一个DATE，不包含时间部分。

- ypercubeᵀᴹ

---

感谢您的答复-基本上我有两种查询方式：一种是通过选择object_id来获取我的对象的所有相关日期，或者通过选择日期范围，其中所选日期（无论是NOW()还是任何其他日期）位于date_from和date_to之间，以便我获得发生在那天的所有行。 - acme

Answer 2

按照ypercube的解释，您应该创建一个索引来覆盖date_from、date_to和object_id字段。这个索引中字段的顺序依赖于过去或未来数据的数量。正如Erwin在回应Sanjay的评论时指出的那样，如果您在过去有更多日期，则date_to字段将更具选择性，反之亦然。

CREATE INDEX ON (date_to, date_from, object_id);

- nnichols

Answer 3

你的查询结果相对于表大小有多少行？如果超过了10％，我就不会费心去创建索引，在这种情况下，你已经非常接近表扫描了。如果低于10％，那么在这种情况下，我会使用包含（date_from，date_to，object_id）的索引，以便查询结果可以完全从索引中的信息构建，而无需数据库回溯到表数据以获取object_id的值。

根据你的表大小，这可能会占用很多空间。如果你能够承受这个，可以试一试。

- Mithrandir

---

我不知道所选字段是从索引中获取的，现在知道了！我猜行数有五位数字，匹配项少于10％。所以这似乎是正确的方法。 - acme

Answer 4

创建一个以(date_from,date_to)为索引的单一索引，这样该索引就可以用于WHERE条件。

如果你创建了分离的索引，MySQL就必须使用其中一个而不是两个。

- Simon at My School Portal