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选择索引(推荐)

说明

Statement Outline是AliSQL提供的索引选择方法。RDS MySQL内置了一个系统表outline,用于保存SQL、特征、Hint等信息,并提供了工具包DBMS_OUTLN。用户可以使用工具包来定义、删除、修改、查看规则,所有的规则信息均保存在outline表中。当执行的SQL语句匹配到outline表中存在的特征时,则利用相应的Optimizer Hint和Index Hint灵活地选择执行计划。

对于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);
说明

Statement Outline支持官方MySQL 8.0、MySQL 5.7的所有Hint类型。详细信息,请参见Statement Outline

方案二:关闭优化器行为

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;
说明
  • 该方案会影响Global、Session后续查询的执行,若通过该方案来控制不同查询的优化器行为,则需要在每个查询前对optimizer_switch重新设置,较为繁琐。此外,该方案存在版本限制。

  • prefer_ordering_index参数为Session级别变量,可在实例运行中修改。优化器配置的详细信息,请参见优化器配置MySQL 5.7优化器配置MySQL 8.0

方案三:通过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;
说明
  • 该方案需要对SQL语句定制修改,可能会带来大量、频繁的变更。

  • 该方案比方案二更精细地控制优化器。Index Hints的详细信息,请参见: