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

7

2011-12-01 06:18:34 +08:00

@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秒。