IIWAB MySQL实战：高效查询每个用户最近购买的3件商品 - IIWAB

MySQL实战：高效查询每个用户最近购买的3件商品

IIWAB 1月前 ⋅ 175 阅读

在电商、零售等业务场景中，“查询每个用户最近购买的N件商品”是高频需求，比如用户中心展示“最近订单”、基于近期消费行为做个性化推荐等。

一、需求与表结构定义

在动手写SQL前，先统一需求和基础表结构，确保所有方案基于相同的数据模型，避免理解偏差。

1. 核心需求

  • 关联用户表（users）、订单表（orders）、商品表（products）；
  • 为每个用户筛选出最近3条购买记录（按订单创建时间倒序）；
  • 最终返回结果包含：用户名、用户邮箱、商品名称、订单时间、订单金额。

2. 表结构定义

-- 用户表：存储用户基础信息
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '用户邮箱',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);

-- 商品表：存储商品信息
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL COMMENT '商品名称',
    price DECIMAL(10,2) NOT NULL COMMENT '商品单价',
    stock INT DEFAULT 0 COMMENT '库存数量'
);

-- 订单表：存储用户购买记录（核心关联表）
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL COMMENT '关联用户ID',
    product_id INT NOT NULL COMMENT '关联商品ID',
    amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

二、方案一：LATERAL连接（直观高效的“用户级筛选”）

LATERAL是MySQL 8.0及以上版本引入的关键字，核心优势是让子查询能引用外部表的字段，特别适合“为每个用户单独筛选数据”的场景，逻辑上更贴近“逐个用户查最近订单”的思维。

1. 实现代码

SELECT 
    u.username,        -- 用户名
    u.email,           -- 用户邮箱
    ro.product_name,   -- 商品名称
    ro.created_time,   -- 订单时间（最近的优先）
    ro.amount          -- 订单金额
FROM users u
-- LATERAL关键：子查询可直接引用外部表u的字段（如u.id）
JOIN LATERAL (
    -- 子查询：为当前用户（u.id）筛选最近3条订单
    SELECT 
        p.product_name,
        o.created_time,
        o.amount
    FROM orders o
    -- 关联商品表，获取商品名称
    JOIN products p ON o.product_id = p.id
    -- 锁定当前用户的订单，避免跨用户查询
    WHERE o.user_id = u.id
    -- 按订单创建时间倒序，确保最近的订单排在前面
    ORDER BY o.created_time DESC
    -- 限制仅返回3条记录，满足“最近3件商品”需求
    LIMIT 3
) AS ro  -- ro是“recent orders（最近订单）”的缩写，便于理解
ON TRUE;  -- LATERAL连接的固定写法，无需额外关联条件

2. 核心原理

  1. 逐用户遍历：MySQL会先遍历users表中的每一条记录（即每个用户）；
  2. 子查询精准筛选：针对每个用户，执行LATERAL后的子查询，通过o.user_id = u.id锁定该用户的订单，再用ORDER BY + LIMIT 3筛选出最近的3条；
  3. 结果拼接：将用户的基础信息（用户名、邮箱）与该用户的最近3条订单信息拼接，形成最终结果。

3. 优缺点分析

优点缺点
语法直观，逻辑清晰，容易理解（直接对应“每个用户查最近3单”的业务逻辑）版本兼容性有限，仅支持MySQL 8.0及以上，旧版本（如5.6、5.7）无法使用
性能高效：子查询仅处理单个用户的订单，避免全表排序带来的性能损耗若用户表数据量极大（如千万级），逐用户遍历的开销会增加
灵活性高：修改“最近N件”时，只需调整LIMIT后的数值（如改LIMIT 5获取最近5件）无法直接获取订单排名（如“第1近”“第2近”的标识）

4. 旧版本兼容方案（MySQL 5.x）

如果使用的是MySQL 5.x等不支持LATERAL的版本，可通过CROSS JOIN + 子查询计数实现等价效果：

SELECT 
    u.username,
    u.email,
    p.product_name,
    o.created_time,
    o.amount
FROM users u
-- 交叉连接订单表和商品表，先关联基础数据
CROSS JOIN orders o
JOIN products p ON o.product_id = p.id
-- 筛选条件：确保当前订单是用户最近3条以内的
WHERE o.user_id = u.id
  AND (
      -- 子查询：统计当前用户中，比当前订单创建时间晚的订单数量
      SELECT COUNT(*) 
      FROM orders 
      WHERE user_id = u.id 
        AND created_time >= o.created_time
  ) <= 3  -- 数量≤3，说明当前订单在“最近3条”范围内
-- 最终排序：按用户ID分组，同一用户内按订单时间倒序
ORDER BY u.id, o.created_time DESC;

三、方案二：窗口函数（灵活通用的“全表排序+筛选”）

窗口函数（如ROW_NUMBER()）是MySQL 8.0+引入的高级功能，核心能力是在不破坏表结构的前提下，为数据添加“分组排序标识”，适合需要“先全表关联，再按组筛选Top-N”的场景，尤其在需要排名信息时优势明显。

1. 实现代码

SELECT 
    username,
    email,
    product_name,
    created_time,
    amount
FROM (
    -- 子查询1：为每个用户的订单添加“排名标识”
    SELECT 
        u.username,
        u.email,
        p.product_name,
        o.created_time,
        o.amount,
        -- 窗口函数：按用户分组，按订单时间倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY o.user_id  -- 按用户ID分组，每个用户单独排名
            ORDER BY o.created_time DESC  -- 按订单创建时间倒序，最近的排第1
        ) AS order_rank  -- order_rank：订单排名（1=最近，2=次近，以此类推）
    FROM orders o
    -- 关联用户表，获取用户名和邮箱
    JOIN users u ON o.user_id = u.id
    -- 关联商品表，获取商品名称
    JOIN products p ON o.product_id = p.id
) AS ranked_orders
-- 筛选条件：只保留排名≤3的订单，即每个用户的最近3条
WHERE order_rank <= 3
-- 最终排序：按用户ID分组，同一用户内按订单时间倒序
ORDER BY u.id, created_time DESC;

2. 核心原理

  1. 全表关联：先将orders（订单表）与users（用户表）、products（商品表）全量关联，获取所有用户的所有订单数据；
  2. 分组排名：通过ROW_NUMBER()窗口函数，按o.user_id（用户ID）分组（PARTITION BY），为每个用户的订单按created_time（创建时间）倒序分配排名（order_rank）；
  3. 筛选Top-N：外部查询通过WHERE order_rank <= 3，筛选出每个用户排名前3的订单，即最近3条购买记录。

3. 优缺点分析

优点缺点
功能灵活：可直接获取订单排名，支持复杂筛选（如“筛选排名为1或3的订单”）语法相对复杂，需要理解“窗口函数”“分组排序”等概念，新手入门门槛稍高
版本兼容性与LATERAL一致（支持MySQL 8.0+），无需额外适配性能依赖索引：若订单表数据量大且无合适索引，全表排序会产生较高开销
适用场景广：除了Top-N筛选，还可用于计算用户订单总数、平均金额等统计需求无法直接用LIMIT控制数量，需通过排名字段（order_rank）筛选

4. 性能优化建议

为避免窗口函数全表排序的性能损耗，建议为orders表添加联合索引，索引字段包含“分组字段”和“排序字段”：

-- 索引：user_id（分组字段）+ created_time（排序字段，倒序）
CREATE INDEX idx_orders_user_time ON orders(user_id, created_time DESC);

该索引能让MySQL直接按用户分组，并按时间倒序获取数据，无需额外排序，大幅提升查询效率。

四、两种方案的对比与选择建议

对比维度LATERAL连接方案窗口函数方案
核心逻辑逐用户筛选（“先分后查”）全表排序后筛选（“先查后分”）
版本要求MySQL 8.0+MySQL 8.0+
性能表现小用户表+大订单表时更优大用户表+需排名时更优
功能支持仅支持Top-N筛选，无排名支持Top-N筛选+排名，可复杂统计
语法复杂度低（直观易懂）中（需理解窗口函数）
适用场景需求简单，仅需“最近N件商品”，无需排名需排名信息，或有复杂筛选、统计需求

选择建议

  1. 优先选LATERAL连接：

    • 你的MySQL版本是8.0+；
    • 需求仅为“获取每个用户最近N条订单”，无需排名；
    • 用户表数据量不大（如百万级以内），逐用户遍历开销可控。

  2. 优先选窗口函数：

    • 需要显示订单排名（如“最近3单，第1单是XX，第2单是XX”）；
    • 有复杂筛选需求（如“筛选每个用户排名为奇数的订单”）；
    • 用户表数据量大，但已为订单表添加优化索引（如idx_orders_user_time）。

  3. 旧版本MySQL（5.x）：

    • 直接使用“方案一”中的兼容写法（CROSS JOIN + 子查询计数）；
    • 长期来看，建议升级MySQL版本至8.0+，不仅支持LATERAL和窗口函数，还能获得更好的安全性和性能。

五、常见问题与注意事项

  1. 订单时间相同的情况： 若两个订单的created_time完全相同（如同一秒内下单），ROW_NUMBER()会随机分配排名，可能导致每次查询结果不一致。解决方案是在ORDER BY后添加“次要排序字段”，如订单ID（o.id）：

    -- 窗口函数中调整排序逻辑
ORDER BY o.created_time DESC, o.id DESC

    这样即使时间相同，也会按订单ID倒序排名，保证结果稳定。

  2. 用户无订单的情况： 上述两种方案中，JOIN会只保留有订单记录的用户，无订单用户会被过滤。若需保留无订单用户（订单相关字段显示为NULL），可将JOIN改为LEFT JOIN：

    -- LATERAL方案示例（窗口函数方案同理）
FROM users u
LEFT JOIN LATERAL (...) AS ro ON TRUE;

  3. 性能测试建议： 若数据量较大（如订单表千万级），建议用EXPLAIN ANALYZE分析执行计划，重点关注是否存在“全表扫描”“Using filesort”等低效操作，及时通过添加索引优化。

总结

查询“每个用户最近3件商品”的核心是“按用户分组后的Top-N筛选”。在MySQL 8.0+环境下，LATERAL连接胜在直观高效，适合简单需求；窗口函数胜在灵活通用，适合需排名或复杂筛选的场景。实际应用中，需结合业务需求、数据量大小和MySQL版本，选择最适配的方案，同时通过合理索引优化性能，确保查询高效稳定。


注意：本文归作者所有，未经作者允许，不得转载

阅读全部

全部评论: 0 条

    我有话说: