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
不知道各位有没有遇到过呢?
1
jhdxr 2015-08-11 21:41:28 +08:00
可以force index / ignor index啊
|
2
jhdxr 2015-08-11 21:42:09 +08:00
哦你贴的连接里也有提到这儿了。
既然要让别人分析,为什么不把两种情况的explain贴出来看下呢。。。 |
3
hellogbk 2015-08-11 21:43:00 +08:00
不懂,
你用force index让查询强制使用联合索引试试 |
4
zts1993 2015-08-11 21:59:26 +08:00
联合索引 fid, reply 这样。?
|
5
ovear OP |
6
liprais 2015-08-11 22:57:49 +08:00
cost based optimizer 当然是选cost最低的,你可以 explain extended看下,
或者用performance schema |
7
ovear OP @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结果在我电脑模拟出的结果,但是模拟的时候似乎选择的是 联合索引 感觉很奇怪,不知道大神能看出原因么? |
11
ovear OP @bobbychen =。=感觉选了个花费资源最多的。。用lastReply扫表每次都要扫50多W
不知道有没有解决方法呢~ |
16
jhdxr 2015-08-12 16:05:47 +08:00
@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 应该是个百分比,不知道你上面那个为什么会那么大。。。 |
17
ovear OP @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行。。很不可思议 |