优化AWS Aurora MySQL 8.0查询：获取最后访问记录的location_id

运行环境为AWS Aurora MySQL 8.0，现有visit_data表包含_record_id、location_id、visit_date字段。需求是传入一组location_id，判断其中是否存在任意record_id的最后访问地点。当前生产查询在200多万条数据的表上执行耗时超60秒，寻求查询结构及索引的优化方案。

原查询语句 #

SELECT 
    ud.record_id, 
    vd.location_id
  FROM
    user_data as ud
    visit_data as vd
  WHERE ud.record_id = vd._record_id
    AND vd._campaign_id in (123)
    AND vd.location_id = (SELECT
                          pvd.location_id
                        FROM
                          visit_data as pvd
                        WHERE
                          pvd._campaign_id in (123)
                          AND pvd._record_id = ud.record_id
                          AND pvd.location_id IN ('location-1', 'location-2',
                                         'location-3', 'location-4',
                                         'location-5', 'location-1800')
                       ORDER BY pvd.visit_date DESC
                        LIMIT 1)
    AND vd.location_id IN ('location-1', 'location-2',
                    'location-3', 'location-4',
                    'location-5', 'location-1800')
    AND vd.visit_date BETWEEN '2022-01-01' AND '2024-01-31'
  GROUP BY ud.record_id

当前执行计划 #

优化方案 #

1. 重构查询结构，替换相关子查询 #

原查询的相关子查询会对主查询每条记录重复执行，效率极低。改用窗口函数一次性获取每个record_id的最新访问记录，再筛选目标location_id：

WITH last_visits AS (
    SELECT 
        _record_id,
        location_id,
        ROW_NUMBER() OVER (PARTITION BY _record_id ORDER BY visit_date DESC) AS rn
    FROM visit_data
    WHERE 
        _campaign_id = 123
        AND location_id IN ('location-1', 'location-2', 'location-3', 'location-4', 'location-5', 'location-1800')
        AND visit_date BETWEEN '2022-01-01' AND '2024-01-31'
)
SELECT 
    ud.record_id,
    lv.location_id
FROM user_data ud
JOIN last_visits lv ON ud.record_id = lv._record_id
WHERE lv.rn = 1
GROUP BY ud.record_id, lv.location_id;

窗口函数ROW_NUMBER()可批量标记每个record_id的最新访问记录，避免循环执行子查询。

2. 创建针对性复合索引 #

根据查询的过滤、排序和关联逻辑，创建以下复合索引：

• visit_data表：(_campaign_id, _record_id, visit_date DESC, location_id)
  • 前两列快速过滤_campaign_id并匹配_record_id，visit_date DESC直接支持排序获取最新记录，最后包含location_id实现覆盖索引，无需回表查询。
• 若user_data.record_id非主键，为其创建索引：(record_id)

3. 简化冗余条件 #

原查询中vd.location_id的过滤条件重复出现，子查询已限定location_id范围，主查询可去掉重复的vd.location_id IN (...)条件，减少不必要计算。

4. 移除不必要的GROUP BY #

若user_data.record_id是唯一主键，GROUP BY ud.record_id完全冗余，可直接删除；若存在重复记录，保留分组但确保索引能支持分组操作。

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