MySQL日期时间索引不起作用

Question

表结构：

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| total       | int(11)  | YES  |     | NULL    |                |
| thedatetime | datetime | YES  | MUL | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

行总数：137967

mysql> explain select * from out where thedatetime <= NOW();
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | out         | ALL  | thedatetime   | NULL | NULL    | NULL | 137967 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+

对于更多的表联接，真正的查询要长得多，重点是，我无法让表使用datetime索引。如果我想选择所有的数据，直到确定的日期，这对我来说将是很困难的。但是，我注意到，如果我选择一个较小的数据子集，我可以让MySQL使用索引。

mysql> explain select * from out where thedatetime <= '2008-01-01';
+----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
|  1 | SIMPLE      | out         | range | thedatetime   | thedatetime | 9       | NULL | 15826 | Using where |
+----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+

mysql> select count(*) from out where thedatetime <= '2008-01-01';
+----------+
| count(*) |
+----------+
|    15990 |
+----------+

那么，我能做些什么来确保MySQL使用索引，不管我写的日期是什么？

Answer 1

每件事都是按理说的。:)

索引是用来加速检索的。他们使用索引查找来完成这项工作。

在第一次查询中，由于要检索所有行，所以不使用索引，在本例中，使用索引比较慢(lookup index、get row、lookup index、get row.X行数比get all rows ==表扫描慢)

在第二个查询中，您只检索一部分数据，在这种情况下，表扫描要慢得多。

优化器的工作是使用RDBMS在索引上保存的统计信息来确定最佳计划。在第一种情况下，考虑索引，但规划者(正确)放弃它。

编辑

您可能希望阅读类似这的内容，以获得有关mysql的一些概念和关键字。

Answer 2

这里有两件事-

1. 索引没有足够的选择性--如果索引覆盖的范围超过了大约。30%的行，MySQL将决定一个完整的表扫描是更有效的。当您缩小范围时，索引就会开始生效。
2. 连接中每个表的一个索引

真正的查询要长得多，有更多的表连接，重点是.

关键是因为它有连接，所以它可能不能使用该索引。MySQL可以在联接中使用每个表的一个索引(除非它符合索引-合并优化的条件)。如果主键已经用于连接，则不会使用日期时间。为了使用它，您需要按照正确的顺序在join键+ thedatetime索引上创建多列索引。

检查实际查询的说明，以查看MySQL用于连接的键。修改该索引以包括该日期时间列，或者从这两种索引中创建一个新的多列索引(取决于您使用连接键的内容)。