MySQL用户查询同时应用两个筛选条件时性能骤降问题咨询

问题背景 #

• 单条件查询性能正常：
  • 搜索description含"founder"的用户耗时0.3秒
  • 搜索关注用户X的用户耗时0.03秒
• 组合条件（同时满足上述两个条件）查询耗时长达45-118秒，即使仅取前20条结果。

核心原因：执行计划选择错误 #

从EXPLAIN ANALYZE的输出可以明确看到问题所在：

-> Limit: 20 row(s)  (cost=2.16 rows=1) (actual time=3779.933..91032.297 rows=20 loops=1)
    -> Nested loop inner join  (cost=2.16 rows=1) (actual time=3779.932..91032.285 rows=20 loops=1)
        -> Filter: (match twitter_user.`description` against ('+founder' in boolean mode))  (cost=1.06 rows=1) (actual time=94.166..90001.280 rows=198818 loops=1)
            -> Full-text index search on twitter_user using twitter_user_description_ft_index (description='+founder')  (cost=1.06 rows=1) (actual time=94.163..89909.371 rows=198818 loops=1)
        -> Covering index lookup on twitter_user_follower using tuf_twitter_user_follower_download_key (twitter_user_id=4899565692, follower_download_id=7440, follower_twitter_user_id=twitter_user.id)  (cost=1.10 rows=1) (actual time=0.005..0.005 rows=0 loops=198818)

MySQL优化器错误地选择了先执行全文搜索，得到198818条符合description条件的用户记录，然后对每一条记录去twitter_user_follower表中检查是否关注了用户X。而实际上，这19万条记录中绝大多数都不满足关注条件（实际匹配数为0的循环次数高达198818次），导致大量无效的索引查询，最终拖慢了整体速度。

正确的执行顺序应该是先获取关注用户X的所有用户列表（这个结果集规模远小于全文搜索的结果），再对这些用户检查description是否符合条件，这样可以大幅减少需要执行全文检查的记录数。

解决方案 #

方案1：使用STRAIGHT_JOIN强制表连接顺序 #

强制优化器先从twitter_user_follower获取数据，再关联twitter_user执行全文搜索：

SELECT STRAIGHT_JOIN `twitter_user`.`id`
FROM `twitter_user_follower`
JOIN `twitter_user` ON `twitter_user_follower`.`follower_twitter_user_id` = `twitter_user`.`id`
WHERE `twitter_user_follower`.`twitter_user_id` = 4899565692
  AND `twitter_user_follower`.`follower_download_id` = 8039
  AND MATCH(twitter_user.description) AGAINST('+create' IN BOOLEAN MODE)
LIMIT 20 OFFSET 0;

方案2：优先获取关注列表再关联全文搜索 #

通过子查询先获取关注用户X的用户ID列表，再与全文搜索的结果做交集：

SELECT t1.id
FROM (
    SELECT follower_twitter_user_id AS id
    FROM `twitter_user_follower`
    WHERE `twitter_user_id` = 4899565692
      AND `follower_download_id` = 8039
) t1
INNER JOIN (
    SELECT `twitter_user`.`id`
    FROM `twitter_user` 
    WHERE MATCH(twitter_user.description) AGAINST('+create' IN BOOLEAN MODE)
) t2 ON t1.id = t2.id
LIMIT 20 OFFSET 0;

若优化器仍选择错误顺序，可给子查询添加FORCE INDEX提示，或先将关注列表存入临时表后再关联。

方案3：确认索引有效性（可选） #

现有twitter_user_follower的复合索引tuf_twitter_user_follower_download_key（twitter_user_id,follower_download_id,follower_twitter_user_id）已是覆盖索引，无需调整。若关注用户X的结果集较大，可考虑对twitter_user的id与全文索引做联合优化，但优先级低于调整执行顺序。

内容的提问来源于stack exchange，提问作者Mahdi