ApsaraDB RDS:What do I do if the execution efficiency of the ORDER BY LIMIT clause is low?

Problem description

When I execute the WHERE $CONDITION ORDER BY $A LIMIT $N clause on an ApsaraDB RDS for MySQL instance, the execution efficiency is low when the value of $N is small. The execution plan indicates that the optimizer uses the $A index instead of the index that is related to the $CONDITION condition as the index.

Causes

The following execution plans are available for the preceding clause:

• Execution plan 1 for the index that is related to the $CONDITION condition:

  1. The system uses the index that is related to the $CONDITION condition to scan data.

  2. The system uses the $A index following ORDER BY to sort data.

  3. The system returns $N data records.

• Execution plan 2 for the $A index following ORDER BY:

  1. The system uses the $A index following ORDER BY to scan data.

  2. The system uses the $CONDITION condition to filter data and returns $N data records.

For some SQL statements, the system returns a large amount of data based on the $CONDITION condition but the value of $N in LIMIT is small. In this case, the optimizer considers that the $A index is optimal. However, the execution efficiency of the SQL statements is low.

Solutions

The following solutions are provided to resolve the issue. Solution 1 is better than Solution 2 and Solution 3. You can select a solution based on the following description:

• If your RDS instance runs MySQL 8.0 or MySQL 5.7, we recommend that you select Solution 1.

• If your RDS instance runs MySQL 5.5 or MySQL 5.6, we recommend that you select Solution 3.

Solution 1: Use the statement outline feature to select an index

If your RDS instance runs MySQL 8.0 or MySQL 5.7, you can use the statement outline feature to select indexes.

# 1. Use the statement outline feature to create a rule to specify the $C index.
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. View rule matches.
dbms_outln.preview_outline('$SCHEMA','QUERY');

#3. View rule hits.
dbms_outln.show_outline();

#4. Delete a rule by rule ID.
dbms_outln.del_outline($Id);

Solution 2: Disable the optimizer behavior

If your RDS instance runs a version that is later than MySQL 5.7 with a minor engine version of 20211231 or later than MySQL 8.0 with a minor engine version of 20201231, you can set the prefer_ordering_index parameter for the optimizer to OFF to disable Execution Plan 2. MySQL 5.7 with a minor engine version of 20211231 corresponds to MySQL 5.7.33. MySQL 8.0 with a minor engine version of 20201231 corresponds to MySQL 8.0.21. You can execute the following statements to modify the parameter:

# 1. Configure optimizer options.
SET [GLOBAL|SESSION] optimizer_switch='prefer_ordering_index=OFF';

#2. Run the ORDER BY LIMIT query.
SELECT $COL_1, $COL_2 FROM $TABLE_NAME WHERE $CONDITION ORDER BY $A LIMIT $N;

Solution 3: Use index hints to select indexes

You can use an index hint to select an index. To modify the index, execute the following statement:

# 1. Forcefully use $C as the index.
SELECT $COL_1, $COL_2 FROM $TABLE_NAME {USE|FORCE} INDEX ($C) WHERE $CONDITION ORDER BY $A LIMIT $N;