Mysql索引未使用

Question

我有一个表格如下:

描述日志；

+-------------------+-------------------------------+------+-----+-------------------+-------+
| Field             | Type                          | Null | Key | Default           | Extra |
+-------------------+-------------------------------+------+-----+-------------------+-------+
| time              | timestamp                     | NO   | MUL | CURRENT_TIMESTAMP |       |
| severity          | enum('0','1','2','3','4','5') | NO   |     | 1                 |       |
| user_id           | bigint(20)                    | YES  | MUL | NULL              |       |
| user_ip           | varchar(15)                   | YES  | MUL | NULL              |       |
| module            | varchar(30)                   | NO   |     | NULL              |       |
| message           | text                          | NO   |     | NULL              |       |
| blog_id           | bigint(20)                    | NO   | MUL | NULL              |       |
| event_type        | varchar(255)                  | YES  | MUL | NULL              |       |
| post_id           | bigint(20)                    | YES  |     | NULL              |       |
| data              | longtext                      | YES  |     | NULL              |       |
| data_integer      | tinyint(4)                    | YES  |     | NULL              |       |
| data_integer_type | varchar(100)                  | YES  |     | NULL              |       |
+-------------------+-------------------------------+------+-----+-------------------+-------+

该表有以下索引:

show indexes from log;
+-------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| log |          1 | log_event_type_idx |            1 | event_type  | A         |          30 |     NULL | NULL   | YES  | BTREE      |         |
| log |          1 | log_user_id_idx    |            1 | user_id     | A         |       10938 |     NULL | NULL   | YES  | BTREE      |         |
| log |          1 | log_blog_id_idx    |            1 | blog_id     | A         |       24064 |     NULL | NULL   |      | BTREE      |         |
| log |          1 | log_user_ip_idx    |            1 | user_ip     | A         |       24064 |     NULL | NULL   | YES  | BTREE      |         |
| log |          1 | log_time_idx       |            1 | time        | A         |      240647 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

当我查询 8 月或 9 月的数据时，它使用索引并且工作正常。但对于 Oct 数据，它不使用索引。在表中，最大日期是“2012-10-15”。我添加了解释计划。

explain
select *
from log
where time >= '2012-08-01 00:00:00'  AND time < '2012-09-01 00:00:00'

+----+-------------+-------------+-------+----------------------+----------------------+---------+------+-------+-------------+
| id | select_type | table       | type  | possible_keys        | key                  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+-------+----------------------+----------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | log | range | log_time_idx | log_time_idx | 4       | NULL | 57306 | Using where |
+----+-------------+-------------+-------+----------------------+----------------------+---------+------+-------+-------------+
1 row in set (0.04 sec)

explain
select *
from log
where time >= '2012-09-01 00:00:00'  AND time < '2012-10-01 00:00:00'

+----+-------------+-------------+-------+----------------------+----------------------+---------+------+-------+-------------+
| id | select_type | table       | type  | possible_keys        | key                  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+-------+----------------------+----------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | log | range | log_time_idx | log_time_idx | 4       | NULL | 42569 | Using where |
+----+-------------+-------------+-------+----------------------+----------------------+---------+------+-------+-------------+

explain
select *
from log
where time >= '2012-10-01 00:00:00'  AND time < '2012-11-01 00:00:00'

+----+-------------+-------------+------+----------------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys        | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+----------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | log | ALL  | log_time_idx | NULL | NULL    | NULL | 481300 | Using where |
+----+-------------+-------------+------+----------------------+------+---------+------+--------+-------------+

你知道问题出在哪里吗？我正在使用 Mysql 5.1.48。

Answer 1

MySQL 似乎讨厌具有高基数的范围。当使用 DATETIME 值时，这会很快显示出来。

您可以通过向查询表中添加一个DATE 列来减少基数。基数会低很多，MySQL会更有可能使用索引。