云数据库 RDS：ORDER BY LIMIT执行效率低

问题现象

在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;