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?

推荐答案

一切正常.:)

索引是为了加快检索速度.他们使用索引查找来完成.

Indexes are there to speed up retrieval. They do it using index lookups.

在您第一次查询时未使用索引，因为您正在检索所有行，并且在这种情况下使用索引较慢(lookup index、get row、lookup index, get row... x 行数比 get all rows == table scan) 慢)

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.

编辑
您可能想阅读this之类的内容，以了解有关mysql 查询计划器.

EDIT
You might want to read something like this to get some concepts and keywords regarding mysql query planner.

这篇关于MySQL 日期时间索引不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！