› 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
这是一个创建于 3390 天前的主题，其中的信息可能已经有所发展或是发生改变。
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
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行。。很不可思议 |
Select Language