MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
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 Aug 11, 2015
可以force index / ignor index啊
|
|
2
jhdxr Aug 11, 2015
哦你贴的连接里也有提到这儿了。
既然要让别人分析，为什么不把两种情况的explain贴出来看下呢。。。 |
 |
3
hellogbk Aug 11, 2015
不懂，
你用force index让查询强制使用联合索引试试 |
 |
4
zts1993 Aug 11, 2015
联合索引 fid, reply 这样。？
|
 |
5
ovear OP |
 |
6
F281M6Dh8DXpD1g2 Aug 11, 2015
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结果在我电脑模拟出的结果，但是模拟的时候似乎选择的是 联合索引 感觉很奇怪，不知道大神能看出原因么？ |
|
16
jhdxr Aug 12, 2015
@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行。。很不可思议 |
Select Language