MySQL Aurora查询异常缓慢：LIMIT值大于返回行数时的问题求助

问题概述 #

在AWS RDS上运行MySQL Aurora 8.0.mysql_aurora.3.02.0版本时，出现特定查询性能异常：当LIMIT参数值大于实际满足条件的返回行数时，查询耗时大幅飙升；而当返回行数恰好等于LIMIT值时，查询性能正常。业务依赖ORDER BY id DESC排序，无法移除。

示例对比 #

示例1：返回行数等于LIMIT值，耗时正常 #

查询语句：

SELECT `some_table`.* FROM `some_table` 
WHERE `some_table`.`deleted_at` IS NULL 
 AND `some_table`.`created` = TRUE 
 AND `some_table`.`owner_id` IN (286997, ... , 617727) 
 AND (some_table.activity_date >= '2024-09-01' 
 AND some_table.activity_date <= '2025-08-31') 
ORDER BY `some_table`.`id` DESC 
LIMIT 10 OFFSET 276;

查询结果：10 rows in set (2.08 sec)

EXPLAIN结果：

| id | select_type | table       | partitions | type  | possible_keys                                                                                                             | key     | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------+---------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | some_table  | NULL       | index | index_some_table_on_owner_id,index_some_table_on_activity_date,index_some_table_on_deleted_at,index_some_table_on_created | PRIMARY | 4       | NULL | 30590 |     0.00 | Using where; Backward index scan |

EXPLAIN ANALYZE结果：

-> Limit/Offset: 10/276 row(s)  (cost=195.49 rows=0) (actual time=788.859..1407.051 rows=10 loops=1)
    -> Filter: ((some_table.created = true) and (some_table.deleted_at is null) and (some_table.owner_id in (286997, ... , 617727)) and (some_table.activity_date >= DATE'2024-09-01') and (some_table.activity_date <= DATE'2025-08-31'))  (cost=195.49 rows=1) (actual time=1.604..1406.993 rows=286 loops=1)
        -> Index scan on some_table using PRIMARY (reverse)  (cost=195.49 rows=30590) (actual time=0.092..1358.637 rows=219665 loops=1)

示例2：返回行数小于LIMIT值，耗时暴增 #

查询语句：

SELECT `some_table`.* FROM `some_table` 
WHERE `some_table`.`deleted_at` IS NULL 
 AND `some_table`.`created` = TRUE 
 AND `some_table`.`owner_id` IN (286997, ... , 617727) 
 AND (some_table.activity_date >= '2024-09-01' 
 AND some_table.activity_date <= '2025-08-31') 
ORDER BY `some_table`.`id` DESC 
LIMIT 10 OFFSET 277;

查询结果：9 rows in set (1 min 0.74 sec)

EXPLAIN结果：

| id | select_type | table       | partitions | type  | possible_keys                                                                                                             | key     | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------+---------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | some_table  | NULL       | index | index_some_table_on_owner_id,index_some_table_on_activity_date,index_some_table_on_deleted_at,index_some_table_on_created | PRIMARY | 4       | NULL | 30697 |     0.00 | Using where; Backward index scan |

EXPLAIN ANALYZE结果：

-> Limit/Offset: 10/277 row(s)  (cost=210.44 rows=0) (actual time=514.159..50410.699 rows=9 loops=1)
    -> Filter: ((some_table.created = true) and (some_table.deleted_at is null) and (some_table.owner_id in (286997, ... , 617727)) and (some_table.activity_date >= DATE'2024-09-01') and (some_table.activity_date <= DATE'2025-08-31'))  (cost=210.44 rows=1) (actual time=0.896..50410.650 rows=286 loops=1)
        -> Index scan on some_table using PRIMARY (reverse)  (cost=210.44 rows=30697) (actual time=0.059..49691.395 rows=3153118 loops=1)

问题根源 #

从执行计划对比可见：当查询能凑够LIMIT指定的行数时，优化器仅扫描部分索引（示例1中扫描219665行）就停止；但当无法满足LIMIT行数时，优化器会遍历整个PRIMARY索引（共3153118行），直到确认没有更多符合条件的数据，导致耗时暴增。

当前单个字段索引无法让优化器高效筛选+排序，即使尝试过复合索引，也未匹配到最优执行计划。

解决方案建议 #

1. 创建覆盖复合索引 #

构建包含所有过滤条件+排序字段的覆盖索引，让优化器直接通过索引完成筛选和排序，无需回表或全索引扫描：

CREATE INDEX idx_some_table_filter_sort ON some_table (deleted_at, created, owner_id, activity_date, id DESC);

索引字段顺序遵循等值过滤优先，范围过滤次之，排序字段最后的原则，确保优化器能精准利用索引。

2. 子查询预获取ID再关联 #

先通过子查询获取满足条件的ID列表并排序，再关联原表获取完整数据，避免全索引扫描：

SELECT t.*
FROM some_table t
JOIN (
    SELECT id
    FROM some_table
    WHERE deleted_at IS NULL 
      AND created = TRUE 
      AND owner_id IN (286997, ... , 617727) 
      AND activity_date >= '2024-09-01' 
      AND activity_date <= '2025-08-31'
    ORDER BY id DESC
    LIMIT 10 OFFSET 277
) AS sub ON t.id = sub.id
ORDER BY t.id DESC;

子查询可利用索引快速筛选出目标ID，再关联原表仅获取所需数据，大幅减少扫描行数。

3. 更新表统计信息 #

执行以下语句更新表统计信息，确保优化器能基于准确数据生成最优执行计划：

ANALYZE TABLE some_table;

4. 调整优化器参数（可选） #

尝试调整优化器开关，强制优化器选择更优的索引策略：

SET optimizer_switch = 'index_merge=on';

该参数开启索引合并，让优化器能组合多个索引完成筛选，减少全索引扫描概率。需在测试环境验证后再应用到生产。

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