MySQL5.6如何查询user_changelog表每个用户指定字段的最新变更记录

问题原因 #

你之前的写法存在两处核心问题：

1. 子查询过滤条件缺失：子查询仅匹配了用户维度的全局最大变更日期，没有限制field = 'Max Orders'的条件，导致子查询返回的是对应用户所有字段变更的最大日期，而非该用户修改Max Orders字段的最大日期，二者不一致时自然匹配不到正确的记录。
2. 执行效率问题：无对应索引的前提下，逐行匹配的关联子查询在数据量较大时会出现严重的性能问题，表现为查询长时间无返回。

解决方案（适配MySQL 5.6版本） #

写法1：修正后的关联子查询

在子查询中同步增加field字段的匹配条件即可：

SELECT
    cl.user_id,
    cl.field,
    cl.old_value,
    cl.new_value,
    cl.date,
    cl.username
FROM user_changelog AS cl
WHERE 
    cl.field = 'Max Orders'
    AND cl.date = (
        SELECT MAX(d1.date) 
        FROM user_changelog AS d1 
        WHERE d1.user_id = cl.user_id AND d1.field = cl.field
    )
ORDER BY cl.date DESC;

写法2：分组联表查询（性能更优）

先通过分组查询拿到每个用户修改Max Orders的最大日期，再关联原表取完整行数据，数据量较大时效率远高于关联子查询：

SELECT
    cl.user_id,
    cl.field,
    cl.old_value,
    cl.new_value,
    cl.date,
    cl.username
FROM user_changelog AS cl
INNER JOIN (
    SELECT user_id, MAX(date) AS max_date
    FROM user_changelog
    WHERE field = 'Max Orders'
    GROUP BY user_id
) AS t ON cl.user_id = t.user_id AND cl.date = t.max_date
WHERE cl.field = 'Max Orders'
ORDER BY cl.date DESC;

优化建议 #

建议给user_changelog表添加联合索引(field, user_id, date)，可以大幅提升上述查询的执行效率。

注：如果存在同一个用户同一天多次修改Max Orders的场景，可替换匹配逻辑为匹配最大主键ID（如果表中有自增主键ID字段），确保每个用户仅返回一条最新记录。

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