求助 - 关于MYSQL关联查询索引的走向问题及优化方案

[eric_zyh]

explain select * from A ,B where a.userid = b.reuserid and b.userid = 13 and a.type=107 order by a.add_time desc limit 1,30;
| id | select_type | table | type | key | key_len | ref | rows | Extra
| 1 | SIMPLE | b | ref | userid | 4 | const | 6630 | Using temporary;
| 1 | SIMPLE | a | ref | userid_2 | 10 | b.reuserid,const | 5 | Using where

数据量：
A表 18w
B表 50w

索引：
A表 联合索引:(userid,add_time)
B表 联合索引:(userid,reuserid) 单独索引 userid 、reuserid

表及字段含义：
A表 ：用户动态
B表 ：用户关注情况表
userid ：用户ID
reuserid：被关注用户ID

SQL含义：
查询13号会员关注的人的最近30条动态。


explain出来的结果，索引的走向是：
1.B表的 userid
2.A表的 userid,add_time

哪位牛人解释一下为什么这个SQL会慢到卡死？应该如何改进？

[Livid]

a.type = 107 的作用是？

[eric_zyh]

@Livid 是用户动态的类型。 这个字段是必须的。一共有8种类型。

[Livid]

a.type 没有做索引？

[keakon]

@eric_zyh

可以试试给a表加userid,type,add_time DESC联合索引。
检查一下2个表的userid长度是否一致。
另外，b表怎么查出来这么多行，难道13这个用户关注了6000多人？

[eric_zyh]

@keakon
1.13这个用户关注了3000多人，至于那6000mysql是什么个算法。
2.两个userid长度一样，都是int型。
3.我试过几种索引方案
情况一、A表给(type,add_time)加联合索引 , 索引执行顺序：
1）A（type,add_time）
2) B (userid,reuserid)
情况二、只要A表的userid字段有索引（不管是联合还是单独的）, 索引执行顺序：
1）B(userid,reuserid)
2) A(xx)

[eric_zyh]

@Livid
我有做一个组合索引：userid,type,add_time 不过还是很慢

当时如果只是type,add_time两个组合字段索引，速度就变快了。

索引的执行顺序见LS。 好迷茫啊。。

type只有8个取值，单给type加索引好像没什么用处。

[keakon]

@eric_zyh 做了个测试，比较了一下这2种执行策略。

考虑b.userid = 13 and a.type=107。一般的用户不会关注那么多人，所以b表的结果集一般会比a表少，先查询它更快（不考虑排序的情况下）。
这种情况下b表需要(userid, reuserid)或(userid)上的索引，而a表需要(userid, type)或(userid)上的索引。
由于a表后查询，所以无法使用add_time上的索引来排序，需要filesort。很显然，该用户关注的用户越多，这个排序就越费时。

当使用use/force index来指定使用(type, add_time, userid)或(type, add_time)上的索引时，就会变成先查询a表了。
此时会选出a.type=107的所有记录，并且它已经按add_time排序了。如果索引中还有userid的话，就形成索引覆盖了。
接下来还有2种策略：b可以先连接，再过滤userid = 13，这需要(reuserid, userid)或(reuserid)；也可以先过滤userid = 13，再连接，这需要(userid, reuserid)或(userid)。
很显然，结果集越小的就应该越先执行，如果a.type=107的数目不超过3000的话，就可以用前者。这里假设每种type都占1/8，那么应该是18万/8，约2000多。
这种策略的缺点就是如果该用户关注的人很少，也需要取出这么多条记录，显得很不划算。极端地说，假如这个用户没有关注任何人，这个操作也很费时。

考虑到正常的用户关注人数应该在1000以内，超过的基本上都是机器人。而排序1000条记录的表应该是可以接受的。
所以使用第一种方式处理，一般来说是比较合适的。

我用innodb测试了一下，表结构如下：
CREATE TABLE a (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, userid MEDIUMINT UNSIGNED, type TINYINT(3) UNSIGNED, add_time INT UNSIGNED) ENGINE=InnoDB;
CREATE TABLE b (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, userid MEDIUMINT UNSIGNED, reuserid MEDIUMINT UNSIGNED) ENGINE=InnoDB;

然后往a表插入了361463条数据，b表776892条（插入时忘记给(userid, reuserid)加唯一索引了）。其中用户id为1～50000。
某个用户在b中有3000条记录，这3000条记录对应地在a表中有2432条记录，查询前30个用时0.02秒。而如果用户只关注1个人，用时0.00秒。
使用第二种策略时用时0.01秒，但如果用户只关注1个人，用时0.19秒。

[eric_zyh]

@keakon 分析的很透彻 谢咯~~

我把A表的索引换成 (type,add_time) (userid) 速度很快

不过还是不太理解mysql在多表关联时索引的优先级 的算法。

[keakon]

@eric_zyh 从这个例子看来，结果集越小的越先执行。然后选择覆盖字段最多的索引。而临时表和排序的开销好像忽略了，不过这也和结果集并不大有关。

另外，刚想起为什么关注的人少时，时间会猛增了。如果没有达到30条记录，就相当于遍历了a.type=107的2000多条记录来连接；但如果关注得多，很快就找到了30条，后面的连接就无需执行了。
而在第一种策略中，连接次数和关注的人数相同，所以性能刚好相反。

[eric_zyh]

@keakon 我的数据量type=107的数据占了1/3有6W条。mysql 忽略临时表和排序的开销好像与结果集也没关系，还是不知道mysql怎么处理这个索引顺序的。

现在在恶补Mysql，希望能搞清楚吧~~ 出了问题临时抱佛脚。压力大啊！ 呵呵~

[keakon]

@eric_zyh 发现不需要指定索引，直接用STRAIGHT_JOIN就可以设置连接的顺序了。
http://dev.mysql.com/doc/refman/5.6/en/select.html

[napoleonu]

1. 始终要让b表结果集驱动a表，否则用不上a表的索引进行排序而是使用排序算法进行排序，使用排序算法效率是很低的。
2. b表驱动a表的时候，增加条件让被驱动表的需要join的记录越少越好，过滤性特别大的一个条件就是add_time字段，过滤的条件越精确性能提示的越多，给add_time字段增加索引后，过滤时间越精确性能倍数的提升（实测）（显示你关注的人最近1小时，最近一天，最近一周，最近一个月等等）。
3. a表加索引idx1(uid,reuid)，可以做到覆盖索引。
4. b表排序可以变相实现，不一定要根据add_time进行排序，比如根据自曾字段进行排序（如果增加了可用的time相关的索引则按time进行排序可能更好）。
5.当系统的数据上升一到两个数量级之后，mysql的join功能几乎不可用，架构上可能就需要改变了。

[napoleonu]

我的测试环境
关系表b，500用户，每个用户关注500人，一共250000记录
信息表a，500000记录，b表每个用户500条，填充250000记录，type为0，1，2根据id mod 3更新的。

CREATE TABLE `a` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uid` int(10) NOT NULL,
`time` datetime NOT NULL,
`type` tinyint(4) DEFAULT NULL,
`status` varchar(320) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx1` (`time`,`type`,`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8

CREATE TABLE `b` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uid` int(10) NOT NULL,
`reuid` int(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx1` (`uid`,`reuid`)
) ENGINE=InnoDB AUTO_INCREMENT=250001 DEFAULT CHARSET=utf8

索引和数据全部能进入内存，索引如表结构。查下语句如下：
select sql_no_cache a.* from a,b where a.uid=b.reuid and b.uid=13 and a.time<='2012-01-11 12:36:00' and a.type=0 order by a.time desc limit 0,30;

以下过滤出全表数据越大说明过滤性越差过滤也越不精确。
当时间的过滤性达能过滤出全表数据0%-50%时查询时间基本0.01秒以下。
当时间的过滤性达能过滤出全表数据50%-62.5%时查询时间基本0.1秒左右。
当时间的过滤性达能过滤出全表数据62.5%-75%时查询时间基本0.3秒左右。
当时间的过滤性达能过滤出全表数据75%-87.5%时查询时间基本0.45秒左右。
当时间的过滤性达能过滤出全表数据87.5%-100%时查询时间基本0.6秒左右。

[eric_zyh]

@napoleonu 谢谢！

不过 我测试的时候a表的索引 还有key index2(uid)。当时a表索引一直走 index2 不走 index1。

我现在的做法是强制走KEY `idx1` (`time`,`type`,`uid`) 。具体为什么还是搞不清除。

你试试给a表的uid 加一个索引，看看速度？

[napoleonu]

@eric_zyh 这个是MySQL查询分析器的问题，查询分析器认为不能过滤掉85%（具体多少我也不知道，大概这个样子）的无用结果的索引不可用，所以就不使用time开头的那个索引，你如果把时间进一步精确之后去看执行计划可以看到执行计划的变化。

https://gist.github.com/1605283

[udonmai]

@napoleonu
@keakon 求推荐关系型数据库的设计和SQL语句的查询方面的经验和分享~ 顺便问下，隐性连接相比显性的（xxx jion ... on）性能差很多？