mysql - 搜索日期范围时如何减少 MySQL DB 中的搜索时间？

Question

我在具有以下列和索引的下表中对 MySQL 执行了搜索:

查询如下，注意天数为6:

SELECT * FROM data WHERE TEMP<"3000" AND TEMP>"2600" AND Date_Time >"2016-05-05 %" and Date_Time <"2016-05-11 %";

此查询在 0.59s 内返回结果

如果我将天数更改为超过此查询中的天数，那么我将在更多时间2.15s

中得到结果

SELECT * FROM data WHERE TEMP<"3000" AND TEMP>"2600" AND Date_Time >"2016-05-04 %" and Date_Time <"2016-05-11 %";

我该如何解决这个问题？我觉得很荒谬，仅仅一天的时间就会对速度产生如此大的影响!我的目标是在更广泛的时间范围内进行搜索，所以我有点需要知道这背后的原因。

Answer 1

事实 1:在您的情况下，MySQL 不能使用超过 1 个索引
正如这篇博客文章中所解释的那样 https://www.percona.com/blog/2009/09/12/3-ways-mysql-uses-indexes/

if you have index on (A,B) This index can be used to lookup rows for WHERE clauses like A=5 ; A BETWEEN 5 AND 10 ; A=5 AND B BETWEEN 5 AND 10 it however will NOT be able to help lookup rows for B BETWEEN 5 AND 10 predicate because it is not index prefix.

因此，在您拥有 TEMP 和 Date_Time 列并按范围搜索的情况下，范围索引将用于 TEMP 或 Date_Time，查询的第二部分将使用 where 执行。这是 BTREE 索引的结构限制。

事实 2:索引未覆盖您的查询，这意味着在不读取行本身的情况下无法仅使用索引中的数据执行查询

A full table scan is performed using reads from the index to look up data rows > in index order. Uses index does not appear in the Extra column. http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range

那么在您的第一个查询中会发生什么:
1. MySQL 将在 Date_Time 索引中查找 Date_Time 并选择符合 'Date_Time >"2016-05-05 %"和 Date_Time <"2016-05-11 %"'
的索引行 2. 索引中没有TEMP信息，MySQL需要从数据表中收集所有的index_rows。
3. MySQL 将对这些行中的每一行评估条件 'TEMP<"3000"AND TEMP>"2600"'。

第二个查询发生了什么？
MySQL 决定不使用任何可用索引 (possible_keys) 并执行全表扫描以返回值。这意味着将扫描表的每一行以检查是否匹配条件 TEMP<"3000"AND TEMP>"2600"AND Date_Time >"2016-05-04 %"和 Date_Time <"2016-05- 11%”。这可能是因为没有前缀为“2016-05-11”或“2016-05-04”的值。