MySQL中高效获取商品最近10位点赞用户的技术问询

针对你这个需求，我推荐用窗口函数来实现高效的分组TopN查询，这是处理“每个分组取前N条记录”场景的最优方案之一，完美适配你点赞量上千的性能需求。下面是具体的实现步骤和优化建议：

1. 核心SQL查询（支持MySQL 8.0+/PostgreSQL等主流数据库） #

这个查询会为每个商品返回最多10个最新点赞的用户ID，同时关联商品的基础信息：

SELECT 
    p.id AS product_id,
    p.title,
    l.user_id,
    l.created_at
FROM products p
LEFT JOIN (
    SELECT 
        product_id,
        user_id,
        created_at,
        -- 按商品分组，点赞时间倒序排号
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at DESC) AS rn
    FROM likes
) l ON p.id = l.product_id AND l.rn <= 10
-- 最终按商品ID、点赞时间倒序排序
ORDER BY p.id, l.created_at DESC;

逻辑说明： #

• 子查询里用ROW_NUMBER()窗口函数，给每个商品的点赞记录按created_at降序分配序号；
• 只保留序号≤10的记录，确保每个商品最多取10条最新点赞；
• 用LEFT JOIN保证没有点赞的商品也能被返回（如果只需要有点赞的商品，换成INNER JOIN即可）。

2. 必须的索引优化（性能关键） #

如果没有合适的索引，哪怕是几千条点赞记录，查询速度也会变慢。给likes表创建联合覆盖索引：

CREATE INDEX idx_likes_product_created_user ON likes (product_id, created_at DESC, user_id);

为什么这个索引有用？ #

这个索引包含了查询需要的所有字段：

• product_id：用于分组筛选指定商品的点赞记录；
• created_at DESC：直接在索引里完成排序，不需要额外排序操作；
• user_id：直接从索引返回结果，不需要回表查询原表数据。
  数据库可以直接通过这个索引获取所有需要的信息，性能会提升一个量级。

3. 后续获取用户头像的高效方式 #

拿到这些user_id后，绝对不要循环单个查询用户头像，这会导致大量数据库请求，严重影响性能。建议：

1. 在应用层收集所有查询到的user_id（去重）；
2. 一次性批量查询用户头像：

SELECT id, avatar FROM users WHERE id IN (/* 这里放入收集到的所有user_id */);

3. 将查询结果与商品-用户ID的对应关系关联起来，最终组装成每个商品的点赞用户头像列表。

4. 旧版MySQL（<8.0）的替代方案 #

如果你的MySQL版本不支持窗口函数，可以用变量模拟分组排号：

SELECT 
    p.id AS product_id,
    p.title,
    l.user_id,
    l.created_at
FROM products p
LEFT JOIN (
    SELECT 
        product_id,
        user_id,
        created_at,
        -- 用变量记录当前商品，实现分组排号
        @rn := IF(@current_product = product_id, @rn + 1, 1) AS rn,
        @current_product := product_id
    FROM likes, (SELECT @current_product := NULL, @rn := 0) vars
    ORDER BY product_id, created_at DESC
) l ON p.id = l.product_id AND l.rn <= 10
ORDER BY p.id, l.created_at DESC;

不过这个写法可读性较差，性能也不如窗口函数，优先建议升级到支持窗口函数的数据库版本。

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