关于 mysql 索引的一个奇怪的问题/mysql 选择错误索引

[ovear]

OAQ 踩坑踩的伤心死了，不知道有没有大神遇到过(:з」∠)
情景如下
表thread有以下几个字段
id int primary key
fid int '所属板块
reply int '回复的帖子id 0为帖子 其他为回复帖子的id
lastUpdate datetime

有索引如下
1、联合索引 fid, reply， lastUpdate
2、单独索引 lastUpdate

当执行select * from thread where fid = 1 and reply = 0 order by lastUpdate desc;的时候
mysql使用的是却是lastUpdate的单独索引，所以会进行超大量的全表搜寻。
但是我已经为此类查询建立了合适的联合索引，但是mysql依然是不依不挠使用 lastUpdate 单独索引这是为什么呢？thx~

在网上查到一个相同问题的，但是却没有解决这个问题
https://github.com/ericdum/mujiang.info/issues/5

不知道各位有没有遇到过呢？

[jhdxr]

可以force index / ignor index啊

[jhdxr]

哦你贴的连接里也有提到这儿了。
既然要让别人分析，为什么不把两种情况的explain贴出来看下呢。。。

[hellogbk]

不懂，
你用force index让查询强制使用联合索引试试

[zts1993]

联合索引 fid, reply 这样。？

[ovear]

@jhdxr 但是感觉这样有点奇怪=。=，我觉得应该是自己什么地方出问题了
explain晚点我补上=。=，目前线上环境不敢乱动

@hellogbk 恩 强制是可以的，联合索引已经试过了

@zts1993 这样也会跑到lastUpdate去 OAQ

[liprais]

cost based optimizer 当然是选cost最低的，你可以 explain extended看下，
或者用performance schema

[ovear]

@liprais 但是实际上似乎是我选择索引更优呢~

mysql> explain EXTENDED SELECT * FROM `thread` force index(lastUpdate) WHERE `replyto` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;
+----+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | thread | index | NULL | lastUpdate | 6 | NULL | 20 | 9668555 | Using where |
+----+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set

mysql> explain EXTENDED SELECT * FROM `thread` force index(unionIndex) WHERE `replyto` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;
+----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
| 1 | SIMPLE | thread | ref | unionIndex | unionIndex | 5 | const,const | 106756 | 100 | Using where |
+----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
1 row in set

mysql>

这里是explain结果在我电脑模拟出的结果，但是模拟的时候似乎选择的是 联合索引
感觉很奇怪，不知道大神能看出原因么？

[bobbychen]

@ovear 你把order部分去掉之后看看，用的是什么索引

[ovear]

@bobbychen 去掉了的话，目前是使用
reply, fid, status, lastReply

[bobbychen]

@ovear恩，因为查询还包括order和group by等，mysql优化器会综合考虑这些来选择index

[ovear]

@bobbychen =。=感觉选了个花费资源最多的。。用lastReply扫表每次都要扫50多W
不知道有没有解决方法呢~

[bobbychen]

@ovear 可以这样建reply, fid, lastReply，status

[ovear]

@bobbychen 恩已经建立了一个了~但是还会走lastReply的单独索引，我都无奈了OAQ

[bobbychen]

@ovear 呵呵，建议看一看《高性能mysql》，你也许可以找到答案

[ovear]

@bobbychen 好的谢谢这位菊苣啦_(:з」∠)_

[jhdxr]

@ovear `explain extened` 请配合 `show warnings`使用。看你的的explain感觉很奇怪，虽然 ref 是优于 index 的，但你用lastupdate的rows才20，下面的联合索引要106756，很明显是应该用lastupdate的。

另外我有一点不解的是，根据手册（https://dev.mysql.com/doc/refman/5.1/en/explain-extended.html） filtered 应该是个百分比，不知道你上面那个为什么会那么大。。。

[ovear]

@jhdxr 先感谢一下菊苣_(:з」∠)_

我也觉得奇怪，因为这个值如果使用强制索引会变得非常大。。而自动选择却不会。。在我自己的开发机上无法复现，我在线上操作了一下，得到的结果感觉很奇怪

1、强制使用lastReply
mysql> explain EXTENDED SELECT * FROM `thread` force index(lastReply) WHERE `reply` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;

+----+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | thread | index | NULL | LastReply | 6 | NULL | 20 | 31195690 | Using where |
+----+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set

2、强制使用联合索引
mysql> explain EXTENDED SELECT * FROM `thread` force index(adminForumGroup) WHERE `reply` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;

+----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
| 1 | SIMPLE | thread | ref | adminForumGroup | adminForumGroup | 5 | const,const | 250311 | 100 | Using where |
+----+-------------+------------+------+-----------------+-----------------+---------+-------------+--------+----------+-------------+
1 row in set

3、自动选择到LastReply
mysql> explain EXTENDED SELECT * FROM `thread` WHERE `reply` = 0 AND `fid` = 4 ORDER BY lastReply desc LIMIT 0,20;
+----+-------------+------------+-------+--------------------------------------------------------------------+-----------+---------+------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+--------------------------------------------------------------------+-----------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | thread | index | balalba很多 | LastReply | 6 | NULL | 83106 | 116.44 | Using where |
+----+-------------+------------+-------+--------------------------------------------------------------------+-----------+---------+------+-------+----------+-------------+
1 row in set

我刚刚也看了下 show warnings，语句里面把 * 换成了一堆 as，除此之外没有任何差别，不知道菊苣怎么看呢_(:з」∠)_

还有一个比较惊讶的是，使用lastReply竟然rows只有20行。。很不可思议