mysql limit 导致索引选择 问题

[lubude163]

有limit的时候，选择索引 heats 导致结果集非常大。

explain  SELECT DISTINCT t.* FROM `pre_forum_thread` as t   WHERE t.tid > 498405 AND t.readperm='0'  AND t.tid NOT IN ('492187','513918') AND t.isgroup='0' AND t.dateline >= '1413771125' AND t.heats>'0' AND t.displayorder>='0' ORDER BY t.heats DESC limit 0,9  ;
+----+-------------+-------+-------+-----------------------+-------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys         | key   | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+-----------------------+-------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | PRIMARY,heats,isgroup | heats | 4       | NULL | 374891 | Using where |
+----+-------------+-------+-------+-----------------------+-------+---------+------+--------+-------------+

去掉limit使用主键索引，

explain  SELECT DISTINCT t.* FROM `pre_forum_thread` as t   WHERE t.tid > 498405 AND t.readperm='0'  AND t.tid NOT IN ('492187','513918') AND t.isgroup='0' AND t.dateline >= '1413771125' AND t.heats>'0' AND t.displayorder>='0' ORDER BY t.heats DESC ;
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+-----------------------------+
| id | select_type | table | type  | possible_keys         | key     | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY,heats,isgroup | PRIMARY | 3       | NULL | 21462 | Using where; Using filesort |
+----+-------------+-------+-------+-----------------------+---------+---------+------+-------+-----------------------------+

强制使用索引回复正常

explain SELECT DISTINCT t.* FROM `pre_forum_thread` as t FORCE INDEX (PRIMARY)  WHERE t.tid > 498405 AND t.readperm='0'  AND t.tid NOT IN ('492187','513918') AND t.isgroup='0' AND t.dateline >= '1413771125' AND t.heats>'0' AND t.displayorder>='0' ORDER BY t.heats DESC limit 0,9  ;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 3       | NULL | 21462 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

表的主要几个索引
PRIMARY KEY (`tid`),
KEY `digest` (`digest`),
KEY `sortid` (`sortid`),
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `recommends` (`recommends`),
KEY `heats` (`heats`),
KEY `authorid` (`authorid`),
KEY `isgroup` (`isgroup`,`lastpost`),
KEY `special` (`special`),
KEY `fid_dateline_displayorder_heats` (`fid`,`displayorder`,`dateline`,`heats`)

比较好奇的是为什么没有limit的时候使用的主键索引，加上limit之后会变成heats

[lubude163]

顶一下

[ACMAIN_CHM]

贴出 show index from pre_forum_thread 以供进一步分析。 原理上是MYSQL认为使用 limit 后可以直接使用heats以得到非常小的数据集，然后进行处理。 而不使用limit则MYSQL认为总归是要输出所有ORDER BY t.heats的记录，这样不如先利用其它索引进行筛选。

[Rotel-刘志东]

mysql在使用limit的时候得到较小的结果集处理，在不使用limit 输出order by 排序后的数据记录。

[lubude163]

引用 2 楼 ACMAIN_CHM 的回复:

贴出 show index from pre_forum_thread 以供进一步分析。 原理上是MYSQL认为使用 limit 后可以直接使用heats以得到非常小的数据集，然后进行处理。 而不使用limit则MYSQL认为总归是要输出所有ORDER BY t.heats的记录，这样不如先利用其它索引进行筛选。

恩，这个说法靠谱