问题现象

在RDS MySQL实例中，执行 WHERE $CONDITION ORDER BY $A LIMIT $N 类型的语句时，在 $N 值较小的情况下出现执行效率低的问题。查看执行计划，发现优化器选择了 $A 作为索引，而不是和 $CONDITION 相关的索引。

可能原因

问题现象中的SQL语句有以下两种执行计划：

• 选择 $CONDITION 相关的索引，执行计划一：

  1. 通过 $CONDITION 相关的索引扫描数据。

  2. 根据 ORDER BY 后的索引 $A 对数据排序。

  3. 选择 $N 条数据返回。

• 选择 ORDER BY 后的索引 $A ，执行计划二：

  1. 通过 ORDER BY 后的索引 $A 扫描数据。

  2. 应用条件 $CONDITION 筛选出 $N 条数据返回。

某些SQL语句中条件 $CONDITION 筛选出的数据量大，LIMIT的数量 $N 小，此时优化器认为 $A 索引更优，但实际SQL语句执行效率更低。

解决方案

共有三种解决方案，方案一优于方案二和方案三，方案选用建议如下：

• 对于MySQL 8.0、MySQL 5.7版本，推荐使用方案一。

• 对于MySQL 5.5、MySQL 5.6版本，请使用方案三。

方案一：通过Statement Outline选择索引（推荐）

对于MySQL 8.0、MySQL 5.7版本，可以通过Statement Outline来选择索引，相关语句如下：

# 1. Statement Outline创建规则，指定索引$C
call dbms_outln.add_index_outline('$SCHEMA', '', 1, '/* {USE|FORCE} INDEX ($C) */',
                                  "SELECT $COL_1, $COL_2 FROM $TABLE_NAME WHERE $CONDITION ORDER BY $A LIMIT $N;");
# 2. 查看规则匹配情况
dbms_outln.preview_outline('$SCHEMA','QUERY');
# 3. 查看规则命中情况
dbms_outln.show_outline();
# 4. 根据规则Id删除规则
dbms_outln.del_outline($Id);

方案二：关闭优化器行为

RDS MySQL 5.7 2021131（MySQL 5.7.33）、RDS MySQL 8.0 20201231（MySQL 8.0.21）之后的版本，支持将优化器配置参数prefer_ordering_index设置为OFF，来禁止执行计划二。修改参数语句如下：

# 1. 配置优化器选项
SET [GLOBAL|SESSION] optimizer_switch='prefer_ordering_index=OFF';
# 2. 执行ORDER BY LIMIT查询
SELECT $COL_1, $COL_2 FROM $TABLE_NAME WHERE $CONDITION ORDER BY $A LIMIT $N;

方案三 ：通过 Index Hints 选择索引

可以通过Index Hints选择索引，修改索引语句如下：

# 1. 强制选择$C作为索引
SELECT $COL_1, $COL_2 FROM $TABLE_NAME {USE|FORCE} INDEX ($C) WHERE $CONDITION ORDER BY $A LIMIT $N;