MySQL日期时间索引不起作用 [英] MySQL datetime index is not working
问题描述
表结构：
+-------------+----------+------+-----+---------+----------------+
| 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使用索引。
The real query is much more longer with more table joins, the point is, I can't get the table to use the datetime index. This is going to be hard for me if I want to select all data until certain date. However, I noticed that I can get MySQL to use the index if I select a smaller subset of data.
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将使用索引，无论我放置什么日期？
So, what can I do to make sure MySQL will use the index no matter what date that I put?
推荐答案
一切都按照原样运行。 ：）
Everything works as it is supposed to. :)
索引可以加快检索速度。他们使用索引查找。
Indexes are there to speed up retrieval. They do it using index lookups.
在第一次查询中，索引不被使用，因为您正在检索所有行，在这种情况下使用索引较慢（查找索引，获取行，查找索引，获取行 ... x行数慢，然后获取所有行 ==表扫描）
In you first query the index is not used because you are retrieving ALL rows, and in this case using index is slower (lookup index, get row, lookup index, get row... x number of rows is slower then get all rows == table scan)
在第二个查询中，您仅检索一部分数据，在这种情况下，表扫描速度要慢得多。
In the second query you are retrieving only a portion of the data and in this case table scan is much slower.
优化器的作业是使用RDBMS保持索引的统计信息来确定最佳方案。在第一种情况下，索引被考虑，但计划者（正确地）将其丢弃。
The job of the optimizer is to use statistics that RDBMS keeps on the index to determine the best plan. In first case index was considered, but planner (correctly) threw it away.
编辑
您可能想阅读类似此的内容，以获取有关mysql查询计划程序的一些概念和关键字。
EDIT
You might want to read something like this to get some concepts and keywords regarding mysql query planner.
这篇关于MySQL日期时间索引不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
