MySQL单查询高效获取用户订阅的最新15条Issue方案咨询

完全可以用单条高效的MySQL查询实现需求，以下是两种常用方案及优化建议：

方案一：使用EXISTS子查询（推荐，性能更优） #

利用EXISTS的短路特性，找到匹配项就停止扫描，适合数据量较大的场景：

SELECT DISTINCT i.issue_ID, i.Date
FROM tIssues i
WHERE 
  -- 匹配用户关注的分类
  EXISTS (
    SELECT 1
    FROM Link_issue_category ic
    JOIN Link_user_category uc 
      ON ic.Category_ID = uc.Category_ID
    WHERE ic.Issue_ID = i.issue_ID
      AND uc.user_id = 123 -- 替换为指定用户ID
  )
  AND
  -- 匹配用户关注的县或市
  EXISTS (
    SELECT 1
    FROM (
      -- 合并县、市的匹配Issue
      SELECT ic.Issue_ID 
      FROM Link_issue_county ic
      JOIN Link_user_county uc 
        ON ic.County_ID = uc.County_ID
      WHERE uc.user_id = 123
      UNION
      SELECT iy.Issue_ID 
      FROM Link_issue_city iy
      JOIN Link_user_city uy 
        ON iy.City_ID = uy.City_ID
      WHERE uy.user_id = 123
    ) AS location_matches
    WHERE location_matches.Issue_ID = i.issue_ID
  )
ORDER BY i.Date DESC
LIMIT 15;

方案二：使用INNER JOIN关联 #

逻辑更直观的关联式写法，适合习惯表关联思路的场景：

SELECT DISTINCT i.issue_ID, i.Date
FROM tIssues i
-- 关联分类匹配
JOIN Link_issue_category ic 
  ON i.issue_ID = ic.Issue_ID
JOIN Link_user_category uc 
  ON ic.Category_ID = uc.Category_ID
-- 关联地域匹配
JOIN (
  SELECT ic.Issue_ID 
  FROM Link_issue_county ic
  JOIN Link_user_county uc 
    ON ic.County_ID = uc.County_ID
  WHERE uc.user_id = 123
  UNION
  SELECT iy.Issue_ID 
  FROM Link_issue_city iy
  JOIN Link_user_city uy 
    ON iy.City_ID = uy.City_ID
  WHERE uy.user_id = 123
) AS location_matches 
  ON i.issue_ID = location_matches.Issue_ID
WHERE uc.user_id = 123
ORDER BY i.Date DESC
LIMIT 15;

关键优化点 #

要保证查询高效，必须给以下字段添加索引：

• 所有关联表的外键组合索引：
  • Link_issue_category：(Issue_ID, Category_ID)、(Category_ID, Issue_ID)
  • Link_user_category：(user_id, Category_ID)
  • Link_issue_county：(Issue_ID, County_ID)、(County_ID, Issue_ID)
  • Link_user_county：(user_id, County_ID)
  • Link_issue_city：(Issue_ID, City_ID)、(City_ID, Issue_ID)
  • Link_user_city：(user_id, City_ID)
• tIssues表的Date字段添加降序索引：CREATE INDEX idx_issues_date_desc ON tIssues(Date DESC);，配合ORDER BY Date DESC LIMIT 15可快速定位最新数据，避免全表排序。

注意事项 #

• 用DISTINCT去重：一个Issue可能关联多个匹配的分类或地域，会产生重复行，必须用DISTINCT保证结果唯一。
• UNION替代UNION ALL：UNION会自动去重地域匹配的Issue ID，避免重复关联同一Issue。

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