当表有其他字段时，Mysql 不使用 DATETIME 索引

Question

我需要一些帮助来解决这个问题。我试图让 Mysql 在 DATETIME 字段上使用索引。

如果表中有其他（未使用的）字段，Mysql 决定不使用索引。考虑下面的两种情况：

一个包含 2 个字段的简单表格可以正常工作：

DROP TABLE IF EXISTS datetime_index_test;
CREATE TABLE  datetime_index_test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
created DATETIME NOT NULL ,
PRIMARY KEY (id) ,
INDEX (created)
) ENGINE = InnoDB ;

INSERT INTO datetime_index_test (created) VALUES
('2011-04-06 00:00:00'),
('2011-04-06 01:00:00'),
('2011-04-06 02:00:00'),
('2011-04-06 03:00:00'),
('2011-04-06 04:00:00'),
('2011-04-06 05:00:00'),
('2011-04-06 06:00:00'),
('2011-04-06 00:00:00');

EXPLAIN SELECT * FROM datetime_index_test
WHERE created <= '2011-04-06 04:00:00';

+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | datetime_index_test | range | created       | created | 4       | NULL |    4 | Using where; Using index |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+

一个包含 3 个字段的简单表格无法正常工作：

DROP TABLE IF EXISTS datetime_index_test;
CREATE TABLE  datetime_index_test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
created DATETIME NOT NULL ,
user int(10) unsigned DEFAULT 0,
PRIMARY KEY (id) ,
INDEX (created)
) ENGINE = InnoDB ;

INSERT INTO datetime_index_test (created) VALUES
('2011-04-06 00:00:00'),
('2011-04-06 01:00:00'),
('2011-04-06 02:00:00'),
('2011-04-06 03:00:00'),
('2011-04-06 04:00:00'),
('2011-04-06 05:00:00'),
('2011-04-06 06:00:00'),
('2011-04-06 00:00:00');

EXPLAIN SELECT * FROM datetime_index_test
WHERE created <= '2011-04-06 04:00:00';

+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | datetime_index_test | ALL  | created       | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+

最后，我的问题； 谁能向我解释一下为什么Mysql决定不使用索引？

Answer 1

这是由于我所说的基于关键群体（元组基数）的 5% 规则。

如果您对存在不平衡基数的表建立索引，MySQL 查询优化器将始终选择阻力最小的路径。

示例：如果表有性别列，则基数为两个，M 和 F。

您对这样的性别列建立什么索引？？？你本质上会得到两个巨大的链表。

如果将一百万行加载到包含性别列的表中，则可能会得到 50% M 和 50% F。

如果关键组合（我所说的关键群体）的基数更多，则在查询优化期间索引将变得毫无用处。超过表总数的 5%。

现在，就您的示例而言，为什么有两个不同的 EXPLAIN 计划？？？我的猜测是 MySQL 查询优化器和 InnoDB 作为一个标签组。

在第一个 CREATE TABLE 中，表和索引虽然很小，但大小大致相同，因此它决定通过执行索引扫描而不是全表扫描来支持索引。请记住，非唯一索引在其索引条目中携带每行的内部主键 (RowID)，从而使索引几乎与表本身的大小相同。

在第二个 CREATE TABLE 中，由于引入了另一列 user，您现在使查询优化器看到完全不同的场景：表现在大于索引。因此，查询优化器在解释如何使用可用索引方面变得更加严格。这就是我之前提到的5%规则。该规则惨遭失败，查询优化器决定支持全表扫描。