MariaDB执行递归外连接查询获取好友关系远慢于MySQL问题咨询

问题1解答 #

这个理解是正确的。MySQL Workbench作为客户端工具，仅承担查询发送、结果接收展示的职责，只要两次测试的网络环境、返回结果规模一致，客户端带来的开销差异可以忽略，不会导致两个数据库出现数量级的性能差距。如果需要更严谨的测试结果，可以换成mysql命令行客户端执行查询，排除结果渲染环节的干扰。

问题2解答 #

核心原因是两个数据库对递归CTE的执行计划生成逻辑不同，直接看执行计划就能定位根因：

• MySQL的递归关联顺序是用CTE的结果驱动查询原表：每次递归遍历CTE已有行，拿cte.friend_id匹配ce_mutual_friends表主键的前缀user_id，走ref级别的索引查找，每次关联只扫描11行左右，总扫描量极小。
• MariaDB的关联顺序完全反了：递归步骤先全索引扫描ce_mutual_friends表（每次扫10506行），再拿t2.user_id匹配CTE临时表的索引，全量查询的递归次数叠加后，总扫描行数是MySQL的几十上百倍，自然会把单核CPU打满，耗时飙升。

优化建议 #

你可以通过修改查询语句强制关联顺序解决性能问题，把递归部分的INNER JOIN换成STRAIGHT_JOIN，强制数据库按照你写的表顺序关联，修改后的语句如下：

WITH RECURSIVE cte(level, user_id, friend_id) AS(
  SELECT 1, user_id, friend_id
    FROM ce_mutual_friends
  UNION
  SELECT cte.level+1, cte.user_id, t2.friend_id
    FROM cte
      STRAIGHT_JOIN ce_mutual_friends t2
        ON t2.user_id = cte.friend_id
    WHERE cte.level+1 <= 3 
)
SELECT cte.user_id, cte.friend_id, min(cte.level)
  FROM cte
  GROUP BY cte.user_id, cte.friend_id;

单用户查询也可以用同样的方法修改，实测性能可以追平MySQL。

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