問題の説明

ApsaraDB RDS for MySQLインスタンスでWHERE $CONDITION ORDER BY $A LIMIT $N句を実行すると、$Nの値が小さいと実行効率が低くなります。実行計画は、オプティマイザがインデックスとして $CONDITION条件に関連するインデックスの代わりに $Aインデックスを使用することを示しています。

原因

上記の句では、次の実行計画を使用できます。

$CONDITION条件に関連するインデックスの実行プラン1:
1. システムは、$CONDITION条件に関連するインデックスを使用してデータをスキャンします。
2. システムは、ORDER BYに続く $Aインデックスを使用してデータをソートします。
3. システムは $Nのデータレコードを返します。
ORDER BYに続く $Aインデックスの実行プラン2:
1. システムは、ORDER BYに続く $Aインデックスを使用してデータをスキャンします。
2. システムは $CONDITION条件を使用してデータをフィルタリングし、$Nのデータレコードを返します。

一部のSQL文では、$CONDITION条件に基づいて大量のデータが返されますが、LIMITの $Nの値は小さくなります。この場合、オプティマイザは、$Aインデックスが最適であるとみなす。しかし、SQL文の実行効率は低い。

ソリューション

以下のソリューションは、問題を解決するために提供されます。溶液1は、溶液2および溶液3よりも良好である。次の説明に基づいてソリューションを選択できます。

RDSインスタンスがMySQL 8.0またはMySQL 5.7を実行している場合は、ソリューション1を選択することを推奨します。
RDSインスタンスがMySQL 5.5またはMySQL 5.6を実行している場合は、ソリューション3を選択することを推奨します。

解決策1: ステートメントアウトライン機能を使用してインデックスを選択する

説明

このソリューションを使用することを推奨します。ステートメントアウトライン機能は、インデックスを選択するためにAliSQLによって提供されます。 ApsaraDB RDS for MySQLは、SQL文、特性、ヒントなどの情報を格納するための組み込みのシステムテーブルの概要を提供します。 ApsaraDB RDS for MySQLは、DBMS_OUTLNツールキットも提供します。ツールキットを使用して、ルールを定義、削除、変更、および表示できます。全てのルール情報はアウトラインテーブルに格納される。実行中のSQL文がアウトラインテーブルの特性と一致する場合、オプティマイザーヒントとインデックスヒントに基づいて実行計画が選択されます。

RDSインスタンスがMySQL 8.0またはMySQL 5.7を実行している場合、ステートメントアウトライン機能を使用してインデックスを選択できます。

# 1。 ステートメントアウトライン機能を使用して、$Cインデックスを指定するルールを作成します。
dbms_outln.add_index_outline('$SCHEMA', '',', '/* {USE | FORCE} INDEX ($C) */' 、
                                  "$COL_1、$COL_2から $TABLE_NAMEから $COL_2を選択します。$Aリミット $N;");
#2. ルールの一致を表示します。
dbms_outln.preview_out' ('$SCHEMA' 、'QUERY');

#3. ルールヒットを表示します。
dbms_outln.show_outline();

#4. ルールIDでルールを削除します。
dbms_outln.del_outline($Id);

説明

ステートメントアウトライン機能は、MySQL 8.0およびMySQL 5.7によって提供される次の種類のヒントをサポートします。詳細については、「ステートメントアウトライン」をご参照ください。

解決策2: オプティマイザの動作を無効にする

RDSインスタンスがマイナーエンジンバージョン20211231のMySQL 5.7以降のバージョン、またはマイナーエンジンバージョン20201231のMySQL 8.0以降のバージョンを実行している場合、オプティマイザのprefer_ordering_indexパラメーターをOFFに設定して実行プラン2を無効にできます。マイナーエンジンバージョンが20211231のMySQL 5.7は、MySQL 5.7.33に対応しています。マイナーエンジンバージョンが20201231のMySQL 8.0は、MySQL 8.0.21に対応しています。次のステートメントを実行して、パラメーターを変更できます。

# 1。 オプティマイザーオプションを設定します。
SET [グローバル | セッション] optimizer_switch='prefer_ordering_index=OFF';

#2. ORDER BY LIMITクエリを実行します。
$COL_1、$COL_2から $TABLE_NAMEを選択します。$COL_1、$COL_2 $A LIMIT $N;

説明

このソリューションは、後続のグローバルクエリとセッションの実行に影響します。このソリューションを使用してさまざまなクエリのオプティマイザの動作を管理する場合、各クエリの前にoptimizer_switchをリセットする必要があります。このソリューションは、特定のバージョンでのみ使用できます。
prefer_ordering_indexパラメーターはセッションレベルの変数です。 RDSインスタンスの実行中にパラメーターを変更できます。オプティマイザの設定方法の詳細については、「MySQL 5.7のオプティマイザの設定」および「MySQL 8.0のオプティマイザの設定」をご参照ください。

解決策3: インデックスヒントを使用したインデックスの選択

インデックスヒントを使用してインデックスを選択できます。インデックスを変更するには、次のステートメントを実行します。

# 1。 インデックスとして $Cを強制的に使用します。
SELECT $COL_1、$COL_2 FROM $TABLE_NAME {USE | FORCE} インデックス ($C) $CONDITION ORDER BY $A LIMIT $N;

説明

このソリューションでは、SQL文を調整して変更する必要があります。
このソリューションは、ソリューション2と比較して、オプティマイザのきめの細かい制御を強制します。オプティマイザヒントとインデックスヒントの詳細については、次のトピックを参照してください。