如何高效查找SQL中包含子串的重复歌曲标题？

API 探路者

2026-6-28

高效查找歌曲重复标题方案

问题分析

你需要查找两类重复记录：标题完全相同，或一个标题是另一个的前缀子串。之前使用子查询+LIKE/SUBSTRING的方式性能差，本质是逐行执行子查询带来了O(n²)的时间复杂度，数据量增大后耗时剧增。

优化方案

核心思路是利用索引减少扫描范围，通过自连接替代子查询，避免逐行统计。

1. 先创建标题索引

首先给songs表的title字段添加索引，这是所有优化的基础：

CREATE INDEX idx_songs_title ON songs(title);

2. Rails Scope实现

以下是两种高效的Scope写法：

写法一：单自连接语句

通过一次自连接覆盖所有重复场景，最后去重：

scope :duplicate_titles, -> {
  joins(
    "JOIN songs s2 
     ON (songs.title = s2.title AND songs.id != s2.id)
     OR (s2.title LIKE CONCAT(songs.title, '%') AND songs.id != s2.id)
     OR (songs.title LIKE CONCAT(s2.title, '%') AND songs.id != s2.id)"
  )
  .distinct
}

写法二：拆分场景合并结果

将完全重复、前缀重复、被前缀重复三个场景拆分，再通过UNION合并，逻辑更清晰：

scope :duplicate_titles, -> {
  # 匹配完全重复的记录
  exact_dupes = where(title: select(:title).group(:title).having("COUNT(*) > 1"))
  # 匹配当前标题是其他标题前缀的记录
  prefix_dupes = joins("JOIN songs s2 ON s2.title LIKE CONCAT(songs.title, '%') AND songs.id != s2.id")
  # 匹配当前标题包含其他标题前缀的记录
  reverse_prefix_dupes = joins("JOIN songs s2 ON songs.title LIKE CONCAT(s2.title, '%') AND songs.id != s2.id")

  # 合并结果并去重
  from("(#{exact_dupes.union(prefix_dupes).union(reverse_prefix_dupes).to_sql}) AS songs")
}