优化重复查询同表的MariaDB订单查询语句

问题背景 #

需要查询客户的最后一次下单时间，网站支持**登录（用user_id标识）和未登录（用email标识）**两种结账方式，同时要合并曾更换过邮箱的客户记录。原有三层嵌套SELECT查询可运行，但面对近620万条订单数据时性能极差，需优化。

原查询问题分析 #

原查询存在三个核心性能瓶颈：

1. 关联子查询低效：WHERE time=(SELECT MAX(o2.time) FROM orders o2 WHERE o1.email = o2.email) 是逐行执行的关联子查询，每条记录都要扫描一次表，时间复杂度接近O(n²)。
2. 索引缺失：现有索引仅包含主键和user_id，没有针对email+time的组合索引，导致子查询无法快速定位每个邮箱的最大下单时间。
3. 多层GROUP BY冗余：两次GROUP BY操作进一步增加了计算开销。

优化步骤 #

1. 创建关键组合索引

首先给orders表添加email和time的组合索引，让分组、子查询操作能快速定位数据：

ALTER TABLE orders ADD INDEX idx_email_time (email, time DESC);

如果后续需要频繁按user_id统计，可补充创建：

ALTER TABLE orders ADD INDEX idx_userid_time (user_id, time DESC);

2. 优化查询语句

根据MySQL版本选择以下方案：

方案一：MySQL 8.0+ 用窗口函数（推荐）

窗口函数可以高效实现分组取Top1的逻辑，代码简洁且性能优异：

SELECT last_order_time, email, user_id
FROM (
    SELECT 
        o.time AS last_order_time,
        o.email,
        o.user_id,
        -- 按客户标识分组：登录用户用user_id，未登录用email
        ROW_NUMBER() OVER (
            PARTITION BY CASE WHEN o.user_id > 0 THEN o.user_id ELSE o.email END 
            ORDER BY o.time DESC
        ) AS rn
    FROM orders o
) AS temp
WHERE rn = 1  -- 取每组中时间最晚的记录
ORDER BY email;

方案二：MySQL 5.x 用分组+聚合函数

如果不支持窗口函数，用GROUP BY结合聚合函数实现，避免多层嵌套：

SELECT 
    MAX(o.time) AS last_order_time,
    -- 取该客户最后一次下单时使用的邮箱
    SUBSTRING_INDEX(GROUP_CONCAT(o.email ORDER BY o.time DESC), ',', 1) AS email,
    -- 统一客户标识：登录用户用user_id，未登录用email
    CASE WHEN o.user_id > 0 THEN o.user_id ELSE o.email END AS user_id
FROM orders o
GROUP BY CASE WHEN o.user_id > 0 THEN o.user_id ELSE o.email END
ORDER BY email;

效果说明 #

优化后的查询利用索引将时间复杂度降至O(n)左右，相比原三层嵌套+关联子查询，在百万级数据量下查询速度可提升数倍甚至数十倍。

内容的提问来源于stack exchange，提问作者Michael W.