実行プランの管理 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、実行計画を管理し、繰り返しクエリまたは複雑なクエリの実行計画を長期間保持する方法について説明します。

背景情報

オプティマイザは、SQL (Structured Query Language) ステートメントごとに実行プランを生成します。しかし、多くの場合、アプリケーションからの要求のSQL文は、パラメータだけでなく繰り返される。パラメータ化されたSQL文はまったく同じです。この場合、パラメーター化されたSQL文に基づいてキャッシュを作成し、パラメーター以外のさまざまな情報 (実行計画など) をキャッシュできます。これは、プランキャッシュと呼ばれる。

さらに、プランキャッシュを使用すると、複数のテーブルの結合に関与するクエリなど、複雑なクエリの実行プランを比較的安定させることができます。プランキャッシュは、バージョンのアップグレードなどの理由で実行プランが変更されないようにもします。プラン管理では, SQL文ごとに実行プランのグループが記録され, バージョンアップしても永続的に保持されます。

ワークフローの概要

PolarDB-X 1.0がクエリSQLステートメントを受け取ると、このステートメントは次のプロセスを実行します。

クエリSQLステートメントをパラメーター化し、すべてのパラメーターをプレースホルダーに置き換えます。?.
パラメーター化されたSQL文をキーとして使用して、実行プランがプランキャッシュにキャッシュされているかどうかを確認します。実行プランがキャッシュされていない場合は、最適化のためにオプティマイザを取り消します。
SQL文が単純なクエリを実行する場合は、SQL文を直接実行し、計画管理に関連する手順をスキップします。
SQL文が複雑なクエリを実行する場合は、ベースラインに永続的に保持されている実行プランを使用します。複数の実行計画が利用可能な場合は、コストが最小のものを選択します。

Process of execution plans

キャッシュの計画

デフォルトでは、PolarDB-X 1.0でプランキャッシュ機能が有効になっています。 EXPLAIN結果のHitCacheは、現在のSQLステートメントがPlan Cacheにヒットするかどうかを指定します。プランキャッシュを有効にすると、PolarDB-X 1.0はSQL文の定数をプレースホルダー? に置き換え、対応するパラメーターリストを作成することで、SQL文をパラメーター化します。実行計画では、LogicalView演算子のSQLステートメントにプレースホルダーが含まれていることも確認できますか?

プラン管理

複雑なSQLステートメントは、プランキャッシュプロセスを実行した後、プラン管理プロセスも実行します。

プランキャッシュとプラン管理はどちらも、パラメータ化されたSQL文をキーとして使用してプランを実行します。すべてのSQL文の実行計画は、計画キャッシュにキャッシュされます。ただし、プラン管理では、複雑なクエリSQL文のみが処理されます。

SQLテンプレートは、特定のパラメーターの影響により、最適な実行計画と1対1で対応するとは限りません。

プラン管理では、各SQL文は1つのベースラインに対応し、各ベースラインには1つ以上の実行プランが含まれます。実際には、最小コストを有する実行計画が、使用されるパラメータに基づいて実行のために選択される。

プランの選択

プランキャッシュの実行プランがプラン管理に入ると、SQLプラン管理 (SPM) は、実行プランが既知かどうかをチェックするプロセスを実装できます。実行プランがわかっている場合、SPMは実行プランのコストが最小かどうかをチェックします。実行プランが未知である場合、SPMは、実行プランが実行される必要があるかどうかをチェックして、実行プランの最適化度を決定する。

操作とメンテナンス (O&M) の指示

PolarDB − X 1.0は、実行計画を管理するための様々な命令セットを提供する。次の構文が使用されます。

ベースライン (LOAD | PERSIST | CLEAR | VALIDATE | LIST | DELETE) [署名整数、署名整数...]
ベースライン (ADD | FIX) SQL (HINT Select Statemtnt)

BASELINE (ADD | FIX) SQL <HINT> <Select Statement>: HINTによって修復されるSQL実行プランのレコードを修正する。
BASELINE LOAD: システムテーブルの指定されたベースライン情報をメモリに更新し、ベースライン情報を有効にします。
BASELINE LOAD_PLAN: システムテーブルの指定された実行プラン情報をメモリに更新し、その情報を有効にします。
ベースラインリスト: 現在のすべてのベースライン情報をリストします。 BASELINE PERSIST: 指定されたベースラインをディスクにフラッシュします。
BASELINE PERSIST_PLAN: 指定された実行プランをディスクにフラッシュします。
BASELINE CLEAR: メモリからベースラインをクリアします。
BASELINE CLEAR_PLAN: メモリから実行プランをクリアします。
BASELINE DELETE: ディスクからベースラインを削除します。
BASELINE DELETE_PLAN: ディスクから実行プランを削除します。

実行計画のチューニングの実践

データが変更された後、またはPolarDB-X 1.0のオプティマイザエンジンがアップグレードされた後、同じSQL文に対してより良い実行計画が使用可能になる場合があります。自動進化中、SPMは、コストベースオプティマイザ (CBO) によって自動的に検出されるより優れた実行プランをSQLステートメントのベースラインに追加します。さらに、SPM命令を使用して実行計画を事前に最適化することもできます。

次のSQL文を例として使用します。

SELECT *
ラインアイテムから参加部ON l_partkey=p_partkey
WHERE p_name LIKE '% green %';

通常、EXPLAINは、SQL文によって生成された実行計画がハッシュ結合を使用し、SQL文にはこの実行計画のみがBaseline Listに含まれていることを検出します。

mysql> select * from lineitem join part on l_partkey=p_partkeyで、p_nameは '% geen %' のようになります。+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| ローカルプラン |
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| 収集 (parallel=true) |
| ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") |
| LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantity' 、'l_dateat' 、'__shiplinitl' 、' 、'_' 、'_' 、'、' 、'__dateateditelligl' '_'_''L_shipmode', 'L_comment' FROM 'lineitem' AS 'lineitem'", parallel=true) |
| LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT 'p_partkey ', 'p_name', 'p_mfgr', 'p_brand', 'p_type ', 'p_size', 'p_container', 'p_container', 'part''parallel' '''''''''''''ne' '' '' '' '' '' '' '' ''
| HitCache:true |
| |
| |
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
セットの7行 (0.06秒)
mysql> ベースラインリスト;
------------ -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------
| BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | 受け入れ |
------------ -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------
| -399023558 | SELECT *
ラインアイテムから
    参加部分にl_partkey = p_partkey
WHERE p_name LIKE? | -935671684 |
収集 (parallel=true)
  ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantit.' 、'l_dateat' 、'_reshiplinitl' 、'_' 、'_date' 、' 、'、'_dateategl' 、'_tax'_' '_'L_shipmode' 、'L_comment' FROM 'lineitem' AS 'lineitem'" 、parallel=true)
    LogicalView(tables="[00-03].part" 、shardCount=4、sql="SELECT 'p_partkey '、'p_name' 、'p_mfgr' 、'p_brand' 、'p_type' 、'p_size' 、'p_container' 、'p_container' 、'part''paral' 、'''ne' 'Hep'? '? 'Hep' 、'AS 'Hher' 'Hep' 's''
 | 0 | 1 |
------------ -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------
1行セット (0.02秒)

ある条件下でバッチキーアクセス (BKA) 結合 (ルックアップ結合) が使用された後、SQLステートメントのパフォーマンスが向上した場合は、まずHINTを使用してPolarDB-X 1.0に命令し、期待される実行計画を生成する方法を見つける必要があります。 BKA結合の次のHINT形式が使用されます。

/* + TDDL:BKA_JOIN(lineitem、part)*/

EXPLAIN [HINT] [SQL] を実行して、出力実行プランが期待どおりかどうかを確認します。

mysql> explain /* + TDDL:bka_join(lineitem、part)*/ select * from lineitem join part on l_partkey=p_partkeyここでp_nameは '% geen %';
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| ローカルプラン |
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| 収集 (parallel=true) |
| ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") |
| LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantity' 、'l_dateat' 、'__shiplinitl' 、' 、'_' 、'_' 、'、' 、'__dateateditelligl' '_'_''L_shipmode', 'L_comment' FROM 'lineitem' AS 'lineitem'", parallel=true) |
| 収集 (同時=true) |
| LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT 'p_partkey ', 'p_name', 'p_mfgr', 'p_brand', 'p_type', 'p_size', 'p_container', 'p_container', 'part''part''''''''''''''?' HE'-'-'? '? 'HE'
| HitCache:false |
| |
| |
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
セットの8行 (0.14秒)

Hintの介入により、結合アルゴリズムがBKA結合に変更されたことに注意してください。ただし、これはベースラインを変更しません。このSQL文を実行するたびに上記のプランを使用する必要がある場合は、このプランをベースラインに追加する必要があります。

プラン管理の [ベースライン追加] 命令を使用して、SQL文の実行プランを追加できます。この場合、SQL文のベースラインには2つの実行プランが同時に存在します。 CBOは、コストに基づいて実行のための実行プランを選択する。

mysql> baseline add sql /* + TDDL:bka_join(lineitem、part)*/ select * from lineitem join part on l_partkey=p_partkeyここでp_nameは '% geen %';
+ ------------ + ---------
| BASELINE_ID | STATUS |
+ ------------ + ---------
| -399023558 | OK |
+ ------------ + ---------
セットの1列 (0.09秒)
mysql> ベースラインリスト;
------------ -------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
| BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | 受け入れ |
------------ -------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
| -399023558 | SELECT *
ラインアイテムから
    参加部分にl_partkey = p_partkey
WHERE p_name LIKE? | -1024543942 |
収集 (parallel=true)
  ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantit.' 、'l_dateat' 、'_reshiplinitl' 、'_' 、'_date' 、' 、'、'_dateategl' 、'_tax'_' '_'L_shipmode' 、'L_comment' FROM 'lineitem' AS 'lineitem'" 、parallel=true)
    収集 (concurrent=true)
      LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT 'p_partkey ', 'p_name', 'p_mfgr', 'p_brand', 'p_type', 'p_size', 'p_container', 'p_container', 'p_part' ''-'(') 'part') '?'-'(') ', ') ') 'Hep'
 | 0 | 1 |
| -399023558 | SELECT *
ラインアイテムから
    参加部分にl_partkey = p_partkey
WHERE p_name LIKE? | -935671684 |
収集 (parallel=true)
  ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantit.' 、'l_dateat' 、'_reshiplinitl' 、'_' 、'_date' 、' 、'、'_dateategl' 、'_tax'_' '_'L_shipmode' 、'L_comment' FROM 'lineitem' AS 'lineitem'" 、parallel=true)
    LogicalView(tables="[00-03].part" 、shardCount=4、sql="SELECT 'p_partkey '、'p_name' 、'p_mfgr' 、'p_brand' 、'p_type' 、'p_size' 、'p_container' 、'p_container' 、'part''paral' 、'''ne' 'Hep'? '? 'Hep'Hher' 、'Hher' 'Hep' 'AS'
               | 0 | 1 |
------------ -------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
セットの2列 (0.03秒)

Baseline List命令の上記の結果は、BKA_JOINに基づく実行プランがSQL文のBaselineに追加されたことを示しています。このSQL文に対してEXPLAIN文を実行すると、p_name LIKE? SQL文で条件が変更された、PolarDB-X 1.0は異なる実行プランを選択します。 2つの実行プランのいずれかを選択する代わりに、PolarDB-X 1.0で上記の実行プランを使用する場合は、[ベースライン修正] 命令を使用して、PolarDB-X 1.0を指定した実行プランに従わせます。

mysql> ベースライン修正sql /* + TDDL:bka_join(lineitem、part)*/ select * from lineitem join part on l_partkey=p_partkeyここでp_nameは '% geen %';
+ ------------ + ---------
| BASELINE_ID | STATUS |
+ ------------ + ---------
| -399023558 | OK |
+ ------------ + ---------
セットの1列 (0.07秒)
mysql> ベースラインリスト \G
*************************** 1。 行 ***************************
      BASELINE_ID: -399023558
PARAMETERIZED_SQL: SELECT *
ラインアイテムから
    参加部分にl_partkey = p_partkey
WHERE p_name LIKE?
          PLAN_ID: -1024543942
EXTERNALIZED_PLAN:
収集 (parallel=true)
  ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantit.' 、'l_dateat' 、'_reshiplinitl' 、'_' 、'_date' 、' 、'、'_dateategl' 、'_tax'_' '_'L_shipmode' 、'L_comment' FROM 'lineitem' AS 'lineitem'" 、parallel=true)
    収集 (concurrent=true)
      LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT 'p_partkey ', 'p_name', 'p_mfgr', 'p_brand', 'p_type', 'p_size', 'p_container', 'p_container', 'p_part' ''-'(') 'part') '?'-'(') ', ') ') 'Hep'
            FIXED: 1
         受け入れ: 1
*************************** 2. 行 ***************************
      BASELINE_ID: -399023558
PARAMETERIZED_SQL: SELECT *
ラインアイテムから
    参加部分にl_partkey = p_partkey
WHERE p_name LIKE?
          PLAN_ID: -935671684
EXTERNALIZED_PLAN:
収集 (parallel=true)
  ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
    LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantit.' 、'l_dateat' 、'_reshiplinitl' 、'_' 、'_date' 、' 、'、'_dateategl' 、'_tax'_' '_'L_shipmode' 、'L_comment' FROM 'lineitem' AS 'lineitem'" 、parallel=true)
    LogicalView(tables="[00-03].part" 、shardCount=4、sql="SELECT 'p_partkey '、'p_name' 、'p_mfgr' 、'p_brand' 、'p_type' 、'p_size' 、'p_container' 、'p_container' 、'part''paral' 、'''ne' 'Hep'? '? 'Hep'Hher' 、'Hher' 'Hep' 'AS'
            固定: 0
         受け入れ: 1
セットの2列 (0.01秒)

Baseline Fix命令が実行された後、BKA結合を含む実行プランのFixステータスビットが1に設定されていることがわかります。この場合、この実行計画は、HINTが追加されておらず、このSQL文のEXPLAIN文を条件として実行した場合でも必ず使用されます。

mysql> select * from lineitem join part on l_partkey=p_partkeyで、p_nameは '% green %';
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| ローカルプラン |
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| 収集 (parallel=true) |
| ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") |
| LogicalView(tables="[00-03].lineitem" 、shardCount=4、sql="SELECT 'l_orderkey' 、'l_partkey' 、'l_suppkey' 、'l_linumber' 、'l_quantity' 、'l_quantity' 、'l_dateat' 、'__shiplinitl' 、' 、'_' 、'_' 、'、' 、'__dateateditelligl' '_'_''L_shipmode', 'L_comment' FROM 'lineitem' AS 'lineitem'", parallel=true) |
| 収集 (同時=true) |
| LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT 'p_partkey ', 'p_name', 'p_mfgr', 'p_brand', 'p_type', 'p_size', 'p_container', 'p_container', 'part''part''''''''''''''?' HE'-'-'? '? 'HE'
| HitCache:true |
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
セットの8行 (0.01秒)