mysql索引范围查询 之后的不生效_TIMESTAMP字段上的MYSQL索引不使用索引进行范围查询...

我在

mysql表中有以下行

+--------------------------------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------------------------+----------------------+------+-----+---------+----------------+

| created_at | timestamp | YES | MUL | NULL | |

该字段中存在以下索引

*************************** 6. row ***************************

Table: My_Table

Non_unique: 1

Key_name: IDX_My_Table_CREATED_AT

Seq_in_index: 1

Column_name: created_at

Collation: A

Cardinality: 273809

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

我正在尝试优化以下查询以使用IDX_My_Table_CREATED_AT索引作为范围条件

SELECT * FROM My_Table as main_table WHERE ((main_table.created_at >= '2013-07-01 05:00:00') AND (main_table.created_at <= '2013-11-09 05:59:59'))\G

当我在select查询中使用EXPLAIN时,我得到以下内容：

+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | main_table | ALL | IDX_My_Table_CREATED_AT | NULL | NULL | NULL | 273809 | Using where |

+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+

问题是IDX_My_Table_CREATED_AT索引未用于此范围条件,即使它是BTREE索引,因此应该适用于查询.

奇怪的是,如果我在列上尝试单个值查找,则使用索引.

EXPLAIN SELECT * FROM My_Table as main_table WHERE (main_table.created_at = '2013-07-01 05:00:00');

+----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+

| 1 | SIMPLE | main_table | ref | IDX_My_Table_CREATED_AT index | IDX_My_Table_CREATED_AT index | 5 | const | 1 | Using where |

+----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+

为什么索引不用于范围条件？我已经尝试更改查询以使用BETWEEN,但这并没有改变任何东西.