我有以下 MySQL 表(简化):

CREATE TABLE `track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_active` (`is_active`, `id`)
) ENGINE=MyISAM AUTO_INCREMENT=7495088 DEFAULT CHARSET=utf8

“is_active”列标记了我想在大多数(但不是全部)查询中忽略的行。我有一些查询会定期从该表中读取 block 。其中一个看起来像这样:

SELECT id,title from track where (track.is_active=1 and track.id > 5580702) ORDER BY id ASC LIMIT 10;

执行此查询需要一分钟多的时间。这是执行计划:

> EXPLAIN SELECT id,title from track where (track.is_active=1 and track.id > 5580702) ORDER BY id ASC LIMIT 10;
+----+-------------+-------+------+----------------+--------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys  | key    | key_len | ref   | rows    | Extra       |
+----+-------------+-------+------+----------------+--------+---------+-------+---------+-------------+
|  1 | SIMPLE      | t     | ref  | PRIMARY,is_active | is_active | 1       | const | 3747543 | Using where |
+----+-------------+-------+------+----------------+--------+---------+-------+---------+-------------+

现在，如果我告诉 MySQL 忽略“is_active”索引，查询会立即发生。

> EXPLAIN SELECT id,title from track IGNORE INDEX(is_active) WHERE (track.is_active=1 AND track.id > 5580702) ORDER BY id ASC LIMIT 10;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL | 1597518 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+

现在，真正奇怪的是，如果我强制 MySQL 使用“is_active”索引，查询又会立即发生!

+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | t     | range | is_active     |is_active| 5       | NULL | 1866730 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+

我只是不明白这种行为。在“is_active”索引中，行应按 is_active 排序，后跟 id。我在查询中同时使用了“is_active”和“id”列，因此它似乎只需要在树上绕几跳就可以找到 ID，然后使用这些 ID 从表中检索标题。

发生了什么事？

编辑:关于我在做什么的更多信息:

• 查询缓存已禁用
• 运行 OPTIMIZE TABLE 和 ANALYZE TABLE 无效
• 6,620,372 行的“is_active”设置为 True。 874,714 行的“is_active”设置为 False。
• 再次使用 FORCE INDEX(is_active) 可以加快查询速度。
• MySQL 版本 5.1.54

最佳答案

看起来 MySQL 在如何使用索引方面做出了错误的决定。

从该查询计划来看，它表明它可以使用 PRIMARY 或 is_active 索引，并且它选择了 is_active 以便首先通过 track.is_active 缩小范围。但是，它仅使用索引的第一列 (track.is_active)。这得到 3747543 个结果，然后必须对其进行过滤和排序。

如果它选择了 PRIMARY 索引，它可以使用该索引缩小到 1597518 行，并且它们将按照 track.id 的顺序检索，不需要进一步排序。这样会更快。

新信息:

在第三种情况下，您使用的是 FORCE INDEX , MySQL 使用的是 is_active 索引，但现在不是只使用第一列，而是使用两列(参见 key_len)。因此现在可以使用相同的索引按 is_active 缩小范围并按 id 排序和过滤，并且由于 is_active 是单个常量，第二列满足 ORDER BY(即来自索引单个分支的行已经排序)。这似乎是比使用 PRIMARY 更好的结果 - 并且可能是您最初的意图，对吧？

我不知道为什么在没有 FORCE INDEX 的情况下不使用此索引的两个列，除非查询在两者之间以微妙的方式发生了变化。如果不是，我会把它归咎于 MySQL 做出了错误的决定。

关于mysql - 为什么在 MySQL 中删除此索引会使我的查询速度提高 100 倍？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7591611/