如何优化获取指定settings_id最新记录的MySQL查询？

问题背景 #

需要从data_rows表中提取settings_id为7、9、11的最新记录，当前使用的查询语句能得到预期结果，但执行耗时超过1分钟，急需优化。

当前查询语句：

SELECT id, settings_id, MAX(added_date) 
FROM data_rows 
WHERE settings_id IN (7,9,11) 
GROUP BY settings_id;

原表数据 #

预期结果 #

优化方案 #

1. 创建复合索引（核心优化） #

原查询慢的主要原因是全表扫描，针对查询过滤、排序字段创建复合覆盖索引，让数据库直接通过索引定位数据，无需回表：

CREATE INDEX idx_settings_added ON data_rows (settings_id, added_date DESC, id);

• 索引顺序：先按settings_id过滤目标数据，再按added_date倒序排列（直接取最新记录），最后包含id字段，实现覆盖索引，彻底避免访问原表数据。

2. 改写查询语句（替代低效GROUP BY） #

原查询的GROUP BY在数据量大时会产生大量分组计算，可改用以下更高效的写法：

方法一：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等）

用窗口函数为每个settings_id标记最新记录，过滤后直接得到结果：

SELECT id, settings_id, added_date
FROM (
    SELECT 
        id, 
        settings_id, 
        added_date,
        ROW_NUMBER() OVER (PARTITION BY settings_id ORDER BY added_date DESC) AS rn
    FROM data_rows
    WHERE settings_id IN (7,9,11)
) t
WHERE rn = 1;

方法二：子查询关联（兼容低版本数据库）

先获取每个settings_id的最大added_date，再关联原表匹配对应id：

SELECT dr.id, dr.settings_id, dr.added_date
FROM data_rows dr
INNER JOIN (
    SELECT settings_id, MAX(added_date) AS max_date
    FROM data_rows
    WHERE settings_id IN (7,9,11)
    GROUP BY settings_id
) t ON dr.settings_id = t.settings_id AND dr.added_date = t.max_date;

注：如果同一settings_id和added_date存在多条记录，该语句会返回所有符合项；如需唯一记录，可叠加MAX(dr.id)进一步过滤。

方法三：UNION ALL（针对固定少量settings_id）

因为目标settings_id是固定的3个，拆分为3个独立查询后合并，每个查询利用索引快速定位单条最新记录：

(SELECT id, settings_id, added_date FROM data_rows WHERE settings_id =7 ORDER BY added_date DESC LIMIT 1)
UNION ALL
(SELECT id, settings_id, added_date FROM data_rows WHERE settings_id =9 ORDER BY added_date DESC LIMIT 1)
UNION ALL
(SELECT id, settings_id, added_date FROM data_rows WHERE settings_id =11 ORDER BY added_date DESC LIMIT 1);

数据量极大时，这种写法性能可能优于前两种，因为每个子查询都能精准命中索引取到单条数据。

3. 辅助优化措施 #

• 定期清理表中过期冗余数据，减少扫描的数据量；
• 检查表碎片情况，执行表优化（如MySQL的OPTIMIZE TABLE data_rows;）；
• 确认数据库内存分配、缓存配置是否合理，提升查询缓存和执行效率。

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