在MySQL中搜索日期范围最有效的方法是什么？ [英] What is the most efficient way to search in date ranges in MySQL?

问题描述

我想问一下在两个日期之间搜索数据的最有效率（最快）的方式是什么？

I'd like to ask what is the most efficient (and fastest) way to search for data between 2 dates?

让我们考虑下面的简单查询： / p>

Let's consider having following simple query:

SELECT Date_,counter1,counter2,...
WHERE (date range condition)

Date_列是DATETIME类型。

The "Date_" column is a DATETIME type.

选项如：

WHERE Date_ >= '2012-12-01 00:00:00' AND Date_ <= '2012-12-05 00:00:00'

和

WHERE Date_ BETWEEN '2012-12-01 00:00:00' AND '2012-12-05 00:00:00'

和

WHERE Date_ = '2012-12-01 00:00:00' OR Date_ = '2012-12-02 00:00:00' OR
Date_ = '2012-12-03 00:00:00' OR Date_ = '2012-12-04 00:00:00' OR
Date_ = '2012-12-05 00:00:00'

实际上，这个Select查询更复杂（加入） s和更多条件）。这只是简化版本。根据EXPLAIN，没有什么区别，查询是如何执行的。我需要说，Date_列是索引的。不幸的是，我无法测试真正的查询速度，因为我无法避免OS缓存，但至少MySQL缓存未被使用（SQL_NO_CACHE）。

In fact this Select query is a lot more complicated (with joins and more conditions). This is only the simplified version. According to EXPLAIN there is no difference, how the query is executed. I need to say that Date_ column is indexed. Unfortunately, I cannot test the real query speed, because I cannot avoid OS caching, but at least MySQL cache was not used (SQL_NO_CACHE).

是否按对于您的经验，以更快的方式在日期间隔搜索（无功能）？

三种方法之一更快（如果有的话）是不一样的）？

非常感谢！

推荐答案

效率的主要因素将是适当的指标的可用性，以及生成有效的执行计划。

The big factor in efficiency is going to be the availability of suitable indexes, and the generation of an efficient execution plan.

我们的规范模式搜索日期时间范围是使用大于等于和小于（但不等于）的比较。

Our normative pattern for searching a datetime range is to use a greater than and equal to, and a less than (but not equal to) comparisons.

要获取一天的所有数据时间，例如，2012-12-01，我们通常将代码如下：

To get all datetimes for a single day, for example, '2012-12-01', we typically code this like:

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-02'

- 或 -

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-01' + INTERVAL 1 DAY

同样，要获得五天的范围示例：

Similarly, to get the five day range in your example:

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-06'

-or -

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-01' + INTERVAL 5 DAY

---

---

To answer your question, "is there a faster way?" No. A predicate of that form enables MySQL to perform a range scan on an index (given a suitable index available.) It doesn't get any more efficient than that.

这篇关于在MySQL中搜索日期范围最有效的方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！