MySQL同表多JOIN查询优化：基于索引实现订阅帖高效查询

先明确两张表的核心字段假设（如果和你的实际表结构有出入，对应调整即可）：

• subscription表：user_id（当前订阅人ID）、target_id（订阅的目标用户/车辆ID）、type（0=订阅车辆，1=订阅用户）
• cars_posts表：post_id、user_id（发帖用户ID）、car_id（帖子关联车辆ID）

第一步：补全必要索引（如果现有索引不匹配） #

如果当前索引未生效，先确保创建了以下索引：

• subscription表：创建联合索引 (user_id, type, target_id) —— 先过滤当前用户的订阅记录，再按类型区分，最后快速定位目标ID
• cars_posts表：分别创建(user_id)和(car_id)的单列索引；如果常用固定字段返回，可扩展为覆盖索引（比如(user_id, post_id, title)），避免回表查询

第二步：编写索引友好的SQL #

合并两种订阅类型的查询（推荐）

用UNION ALL拆分两种订阅场景，避免单JOIN加OR条件导致的索引失效：

-- 订阅用户发布的帖子
SELECT cp.*
FROM subscription s
JOIN cars_posts cp ON s.target_id = cp.user_id
WHERE s.user_id = '当前用户ID'
  AND s.type = 1

UNION ALL

-- 订阅车辆关联的帖子
SELECT cp.*
FROM subscription s
JOIN cars_posts cp ON s.target_id = cp.car_id
WHERE s.user_id = '当前用户ID'
  AND s.type = 0;

为什么这个写法能命中索引？

• 对subscription表：WHERE条件先匹配user_id+type，正好命中(user_id, type, target_id)联合索引，快速筛选出所有订阅的目标ID
• 对cars_posts表：JOIN条件分别匹配user_id和car_id，直接命中对应的单列索引，彻底避免全表扫描

常见错误写法（导致索引失效的原因）

如果你之前用的是单JOIN加OR条件，这种写法会让数据库无法高效利用索引，比如：

-- 这种写法会触发全表扫描，索引完全失效
SELECT cp.*
FROM subscription s
JOIN cars_posts cp ON (s.type=1 AND s.target_id=cp.user_id) OR (s.type=0 AND s.target_id=cp.car_id)
WHERE s.user_id = '当前用户ID';

必须拆分为UNION ALL的形式，才能让索引正常工作。

第三步：验证索引使用 #

执行EXPLAIN查看执行计划，你应该能看到：

• 每个SELECT语句中，subscription表的type列显示ref或range，key列显示你创建的联合索引
• cars_posts表的type列显示ref，key列显示对应的user_id或car_id索引

额外优化：用覆盖索引提升性能 #

如果只需要返回帖子的固定字段（比如post_id、title、create_time），可以把这些字段加入cars_posts的索引，做成覆盖索引：
比如创建cars_posts(user_id, post_id, title, create_time)，查询时无需回表到主键索引，性能会进一步提升。

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