大量のデータを含むクエリが実行されている場合、SQL層がストレージエンジン層からデータを読み取り、計算を実行するため、大量の計算リソースが消費されます。 クエリのパフォーマンスを向上させるために、PolarDB for MySQLはLIMIT OFFSETをストレージエンジン層にプッシュします。 このトピックでは、LIMIT OFFSETプッシュダウン機能について説明します。
前提条件
PolarDB for MySQLクラスターのバージョンが8.0で、リビジョンバージョンは次の要件を満たしています。
- 8.0.1.1.16以降
- 8.0.2.2.0以降
クラスターバージョンを表示する方法の詳細については、「エンジンバージョンの照会」をご参照ください。
背景
MySQLコミュニティでは、LIMIT句はSQL層で実行されます。 SQL層は、ストレージエンジン層からデータを読み取り、クエリを処理し、OFFSETに基づいてフィルタリングされた結果を返します。 セカンダリインデックスがクエリされ、プライマリテーブルの列にアクセスする必要がある場合、SQLレイヤーはテーブルから列データを取得する必要があります。 述語がストレージエンジン層に完全にプッシュされる場合を含め、WHERE条件がSQL層で処理されない場合、データは計算なしでLIMIT OFFSET句に基づいてフィルタリングされます。 この場合、大量のデータがストレージエンジン層からSQL層に送信される。 ページ化されたクエリでは、ページ数が増えるにつれて応答時間が長くなります。 PolarDB for MySQLはLIMIT OFFSET句をストレージエンジンにプッシュします。これにより、データはストレージエンジン層でフィルタリングされ、セカンダリインデックスが使用されている場合は取得されません。
制限事項
OFFSET値は512より大きくなければなりません。
説明 この制限を無効にするには、ignore_polar_optimizer_ruleパラメーターを
ONに設定します。 詳細については、「クラスターとノードパラメーターの設定」をご参照ください。 | パラメーター | レベル | 説明 |
| ignore_polar_optimizer_rule | グローバルとセッション | OFFSET値の制限を無効にするかどうかを指定します。 デフォルト値: OFF。 有効な値：
|
LIMIT OFFSETプッシュダウン機能の使用
loose_optimizer_switchパラメーターのlimit_offset_pushdown変数を使用して、LIMIT OFFSETプッシュダウン機能を有効にすることができます。詳細については、「クラスターおよびノードパラメーターの設定」をご参照ください。 | パラメーター | レベル | 説明 |
| loose_optimizer_switch | グローバルとセッション | PolarDBのクエリ最適化機能を有効にするかどうかを指定します。 LIMIT OFFSETプッシュダウン機能に関連する変数:
|
性能テスト
このパフォーマンステストでは、TPC-Hスキーマが使用されます。 LIMIT OFFSETプッシュダウン機能が有効になっていて、EXPLAINステートメントを実行して実行計画を表示すると、
Extraパラメーターに対してUsing limit-offset pushdownが返されます。 - 一般的な制限オフセットシナリオ次の例では、TPC-HテストのQ1が使用されます。 プライマリテーブルがアクセスされ、ステートメントに述語条件が含まれていません。
を使用して説明 SELECT * ラインアイテムから LIMIT 10000000、 10\G *************************** 1。 行 *************************** id: 1 select_type: シンプル テーブル: lineitem パーティション: NULL タイプ: すべて possible_keys: NULL キー: NULL key_len: NULL ref: NULL 行: 59440464 filtered: 100.00 余分: 限界オフセットpushdown - 述語条件を持つLIMIT OFFSETシナリオ述語条件を含むクエリの場合、述語条件が、ストレージエンジンがデータをスキャンする基準となる範囲をカバーする場合、述語条件は、完全な述語プッシュダウン機能によって削除される。 この場合、LIMIT OFFSET句をプッシュダウンすることができます。
- Q2の実行プランは、プライマリキーにアクセスし、プライマリキーに基づく範囲条件を含みます。
を使用してEXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G *************************** 1。 行 *************************** id: 1 select_type: シンプル テーブル: lineitem パーティション: NULL タイプ: 範囲 possible_keys: PRIMARY、i_l_orderkey、i_l_orderkey_quantity キー: PRIMARY key_len: 4 ref: NULL 行: 29720232 filtered: 100.00 余分: 限界オフセットpushdown - Q3の実行プランは、セカンダリインデックスにアクセスし、プライマリキーに基づく範囲条件を含み、テーブルから他の列のデータを取得する必要があります。
を使用してEXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G *************************** 1。 行 *************************** id: 1 select_type: シンプル テーブル: lineitem パーティション: NULL タイプ: 範囲 possible_keys: i_l_partkey,i_l_suppkey_partkey キー: i_l_suppkey_partkey key_len: 5 ref: NULL 行: 11123302 filtered: 100.00 余分: 限界オフセットpushdown
- Q2の実行プランは、プライマリキーにアクセスし、プライマリキーに基づく範囲条件を含みます。
- ORDER BY句と順序付けインデックスを使用したLIMIT OFFSETシナリオ標準のページクエリでは、ORDER BYを使用してクエリ結果をソートします。 インデックスを使用してORDER BY句を高速化する場合、SQLレイヤーで述語が削除された後にLIMIT OFFSET句をプッシュダウンできます。
を使用してEXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G *************************** 1。 行 *************************** id: 1 select_type: シンプル テーブル: lineitem パーティション: NULL タイプ: 範囲 possible_keys: i_l_partkey,i_l_suppkey_partkey キー: i_l_suppkey_partkey key_len: 5 ref: NULL 行: 11123302 filtered: 100.00 余分: 限界オフセットpushdown
パフォーマンスの改善
TPC-Hテストは10のスケールファクタで実行されます。 Q1、Q2、およびQ3が使用される。 次の図は、LIMIT OFFSETプッシュダウン機能の有効化と無効化のパフォーマンスの違いを示しています。