mysql删索引很快_为什么在MySQL中删除这个索引可以加速我的查询100x？

我有以下

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’列标记了我在大多数但不是全部我的查询中忽略的行.我有一些查询可以定期读出这个表中的大块.其中一个看起来像这样：

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