AQL查询添加日期过滤后耗时超30秒，寻求优化方案

核心问题分析 #

你的查询性能瓶颈主要来自嵌套循环导致的N+1查询，以及单字段date索引无法覆盖多条件过滤：

1. 外层遍历accounts→rotationpicks时，每个k都会触发一次rotationstats的子查询，当rotationpicks结果集较大时，会重复执行上万次查询；
2. 单字段date索引无法配合apikey、keywordid的等值过滤，数据库仍需大量扫描数据才能定位目标记录。

具体优化步骤 #

1. 重建复合索引 #

给rotationstats创建复合哈希索引（因所有过滤条件均为等值匹配，哈希索引比范围索引更高效），字段顺序建议为：

CREATE INDEX idx_rotationstats_apikey_keywordid_date ON rotationstats (apikey, keywordid, date)

该索引可直接覆盖子查询中的三个过滤条件，让数据库快速定位到目标记录，避免全表扫描。

2. 重构查询，消除N+1查询 #

将rotationstats的聚合逻辑提前执行一次，再通过哈希表关联结果，避免重复查询：

// 提前计算当天所有apikey+keywordid的点击总和（仅执行一次）
LET target_date = DATE_FORMAT(DATE_UTCTOLOCAL(DATE_ADD(DATE_NOW(), 0, 'hour'), "UTC"), '%yyyy-%mm-%dd')
LET daily_clicks = (
    FOR s IN rotationstats
    FILTER s.date == target_date
    COLLECT apikey = s.apikey, keywordid = s.keywordid AGGREGATE total_clicks = SUM(s.clicks)
    RETURN { apikey, keywordid, total_clicks }
)

// 将聚合结果转为哈希表，实现O(1)快速查询
LET clicks_map = MERGE(
    FOR item IN daily_clicks
    RETURN { [CONCAT(item.apikey, "|", item.keywordid)]: item.total_clicks }
)

// 主查询关联数据
FOR a IN accounts
FILTER a.keywordsystem == true
    FOR k IN rotationpicks
    FILTER k.apikey == a._key
    // 从哈希表获取点击数，默认返回0
    LET clicks = clicks_map[CONCAT(k.apikey, "|", k.keywordid)] ?? 0
    SORT k.rpc DESC
    RETURN MERGE(k, { clicks })

3. 辅助优化：给rotationpicks加索引 #

若rotationpicks的apikey字段无索引，需补充创建：

CREATE INDEX idx_rotationpicks_apikey ON rotationpicks (apikey)

确保FOR k IN rotationpicks FILTER k.apikey==a._key这一步能快速定位数据，避免全表扫描。

4. 验证索引有效性 #

用EXPLAIN命令检查查询计划，确认复合索引是否被使用：

EXPLAIN
FOR s IN rotationstats
FILTER s.apikey == 'test-api-key' AND s.keywordid == 'test-kwid' AND s.date == '2024-05-20'
COLLECT kwid=s.keywordid AGGREGATE clicks=sum(s.clicks)
RETURN clicks

若执行计划中显示Using index idx_rotationstats_apikey_keywordid_date，则索引生效。

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