MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
这是一个创建于 3788 天前的主题，其中的信息可能已经有所发展或是发生改变。
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
不知道各位有没有遇到过呢？
17 条回复 2015-08-12 18:40:00 +08:00
 | 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 F281M6Dh8DXpD1g2 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行。。很不可思议 |
Select Language