关联watch_video表筛选用户未观看视频时查询性能优化咨询

首先，咱们先拆解下原查询慢的核心原因：一方面NOT EXISTS的子查询如果没有合适索引会导致全表扫描，另一方面ORDER BY rand()会对筛选后的结果集做全量排序，这俩加起来在数据量上去之后肯定拖慢速度。下面给你几个实操性强的优化方案，兼顾准确性和性能：

1. 先把索引拉满（最基础也最见效） #

原查询的子查询依赖watch_video表的user_id和video_id匹配，所以给watch_video建一个复合索引是关键：

CREATE INDEX idx_watch_user_video ON watch_video(user_id, video_id);

这个索引能让数据库快速定位到某个用户看过的所有视频ID，避免子查询做全表扫描。另外确保video表的id是主键（一般默认都是，但如果不是赶紧设上），主键的查询效率是最高的。

2. 替换NOT EXISTS为LEFT JOIN + IS NULL #

有些数据库的查询优化器对LEFT JOIN的处理比NOT EXISTS更高效，尤其是当watch_video数据量很大的时候。可以试试这个写法：

SELECT v.* 
FROM video v
LEFT JOIN watch_video wv ON v.id = wv.video_id AND wv.user_id = $user_id
WHERE wv.video_id IS NULL
ORDER BY rand() ASC 
LIMIT 10;

这个逻辑和原查询完全一致，但优化器可能会选择更优的执行计划，你可以对比下两种写法的执行计划（比如MySQL用EXPLAIN，PostgreSQL用EXPLAIN ANALYZE）。

3. 用缓存预存未观看视频列表（适合高并发场景） #

如果用户的观看行为不是实时高频更新，或者可以接受短时间的数据延迟，用缓存来存用户未观看的视频ID是性能提升最明显的方案：

• 比如用Redis的集合（Set）存储每个用户的未观看视频ID，当用户新注册或者有新视频发布时，把新视频ID加到对应用户的集合里；
• 用户观看某个视频时，从集合中移除该视频ID；
• 查询时直接从Redis取10个随机元素，再关联video表获取详情：

  -- 假设从Redis拿到的未观看视频ID是1,2,3...10
  SELECT * FROM video WHERE id IN (1,2,3,4,5,6,7,8,9,10);
  

这种方式把最耗时的筛选逻辑放到了缓存层，数据库只需要做简单的主键查询，速度会快很多。当然要注意数据一致性，比如新视频发布时要批量更新所有用户的未观看集合（如果用户量极大，可能需要异步处理）。

4. 优化ORDER BY rand()的性能 #

ORDER BY rand()会对整个筛选后的结果集做排序，当未观看视频很多时，排序成本极高。可以换成更高效的随机取样方式：

MySQL 方案： #

用随机数生成器直接定位行，避免全排序：

SELECT v.*
FROM video v
LEFT JOIN watch_video wv ON v.id = wv.video_id AND wv.user_id = $user_id
WHERE wv.video_id IS NULL
AND v.id >= FLOOR(RAND() * (SELECT MAX(id) FROM video))
LIMIT 10;

注意这个方法依赖video表的id是连续自增的，如果有大量删除导致ID不连续，可能会取样不均匀，这时候可以用子查询先获取随机偏移量。

PostgreSQL 方案： #

用TABLESAMPLE直接随机取样，再过滤未观看的视频：

SELECT *
FROM video TABLESAMPLE BERNOULLI(10) -- 取样10%的行，比例可以调整
WHERE NOT EXISTS (
    SELECT 1 FROM watch_video wv 
    WHERE wv.video_id = video.id AND wv.user_id = $user_id
)
LIMIT 10;

这个方法先减少需要过滤的行数，再做未观看判断，整体性能会提升不少。

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