mysql - Mysql 中的日期时间索引

Question

这是表格

+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| datecolumn | datetime | YES  | MUL | NULL    |       |
+------------+----------+------+-----+---------+-------+

表格中的数据

+---------------------+
| datecolumn          |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-01-03 00:00:00 |
| 2008-01-03 00:00:00 |
| 2009-01-03 00:00:00 |
| 2010-01-01 00:00:00 |
| 2010-01-02 00:00:00 |
| 2010-01-03 00:00:00 |
+---------------------+



EXPLAIN select * from test_date_index use key (datecolumn) where datecolumn>='2010-01-02 00:00:00';
+----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table           | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_date_index | index | datecolumn    | datecolumn |  9     | NULL |    7 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

为什么mysql用7行取1行？

提前致谢!

Answer 1

它确实使用了索引。

它(很可能)不使用范围访问，因为表中的记录太少以至于搜索范围的第一条记录实际上比遍历索引和过滤值更昂贵。

当您向表中添加更多值时，将应用范围访问(因为现在认为它更便宜)并且 COUNT 显示 1，因为您只有一个记录满足条件。