如何为Sphinx创建无重复ID的多关联表SQL查询？

问题根源 #

关联查询用户表与帖子、车辆表时，单个用户对应多条帖子/车辆记录，导致同一user.id被返回多次，而Sphinx要求每个文档ID必须唯一，因此触发重复ID警告。

解决方法 #

一、修改SQL查询避免重复 #

1. 聚合关联数据，返回单条用户记录
   用GROUP_CONCAT将用户的多条帖子、车辆信息合并为单个字段，确保每个用户ID只返回一行结果。示例SQL：

   SELECT 
       u.id,
       u.username,
       GROUP_CONCAT(DISTINCT cp.title SEPARATOR '||') AS post_titles,
       GROUP_CONCAT(DISTINCT CONCAT(m.make_name, ' ', mo.model_name) SEPARATOR '||') AS owned_cars
   FROM user u
   LEFT JOIN cars_posts cp ON u.id = cp.userId
   LEFT JOIN user_cars uc ON u.id = uc.userId
   LEFT JOIN models mo ON uc.model_id = mo.id
   LEFT JOIN makes m ON mo.make_id = m.id
   GROUP BY u.id, u.username
   

   注意：GROUP BY需包含所有非聚合字段，DISTINCT用来避免同一用户的重复帖子/车辆被多次合并。

2. 拆分索引，按子表建立独立索引
   如果需要单独检索帖子或车辆，不要以用户表为主表，分别针对帖子、车辆表建立索引：

   • 帖子索引：用cars_posts.id作为文档ID，关联用户、分类等信息
   • 车辆索引：用user_cars.id作为文档ID，关联用户、品牌等信息
     这样每个索引内的文档ID都是唯一的，不会出现重复。

二、利用Sphinx自身配置解决 #

1. 使用多值属性（MVA）存储关联数据
   将用户的帖子ID、车辆ID设为多值属性，主索引只保留唯一的用户记录，关联数据作为附加属性存储。在Sphinx配置中添加：

   # 定义帖子ID多值属性
   sql_attr_multi = uint post_ids from query; SELECT userId, id FROM cars_posts
   # 定义车辆ID多值属性
   sql_attr_multi = uint car_ids from query; SELECT userId, id FROM user_cars
   

   主查询只需获取用户表的唯一记录：

   SELECT id, username, email FROM user
   

   这样既保证文档ID唯一，又能保留用户的所有关联数据，还支持基于多值属性的筛选。

2. 使用sql_query_killlist清理重复记录
   如果必须保留原查询结构，但需要删除重复的旧记录，可以配置该参数让Sphinx自动保留最新的一条。例如：

   sql_query_killlist = SELECT id FROM (
       SELECT user.id, ROW_NUMBER() OVER (PARTITION BY user.id ORDER BY cp.created_at DESC) AS rn
       FROM user
       LEFT JOIN cars_posts cp ON user.id = cp.userId
   ) t WHERE rn > 1
   

   该查询会标记需要删除的重复ID，Sphinx在索引时会自动移除这些记录，只保留每组重复ID中的最新条目。

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