MySQL+PHP实现搜索返回多类型结果的最优方案咨询

方案1：带自定义权重的UNION查询（单SQL实现） #

你之前遇到的UNION排序问题可以通过新增标记字段解决，不需要额外关联操作，SQL示例如下：

-- 优先返回用户的场景
SELECT 
  id,
  name AS show_content,
  'user' AS type,
  1 AS priority
FROM users 
WHERE name LIKE '%mat%'
UNION ALL
SELECT 
  id,
  title AS show_content,
  'movie' AS type,
  2 AS priority
FROM movies 
WHERE title LIKE '%mat%'
ORDER BY priority ASC, id ASC;

如果需要优先返回电影，仅需交换两类结果的priority数值即可，数值越小排序越靠前。

推荐使用UNION ALL替代UNION，不需要去重的场景下性能提升非常明显，即使两类结果的ID重复也可以通过type字段做区分，不会冲突。

方案2：分表查询+动态权重合并（灵活度最高） #

你提到的分查合并不是没有优点，反而在很多业务场景下是更优的选择：

• 两类结果可以各自做独立排序：比如用户按匹配度/粉丝数排序，电影按匹配度/评分排序，不需要在UNION里兼容多字段排序规则
• 配额可以动态调整，不需要固定50/50：比如根据搜索词特征自动分配配额，人名类搜索给用户更高展示比例，内容类搜索给电影更高比例
• 分页逻辑更简单，不会出现UNION分页时偏移量跨类型统计错误的问题，后续扩展其他搜索类型（比如文章、话题）也不需要改原有SQL结构

选型参考 #

• 数据量小于100w、排序规则固定、无复杂分页需求选方案1，单请求耗时更低
• 数据量大、后续有扩展搜索类型的需求、需要做个性化排序推荐选方案2，长期维护成本更低

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