SQLチューニングの方法とプラクティス - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、SQLチューニングの方法とプラクティスについて説明します。調整が必要な低速SQLクエリを見つけたら、EXPLAINステートメントを実行して実行計画を表示できます。次に、スキーマの最適化に基づいてストレージ層でより多くの計算をMySQLにプッシュダウンし、インデックスを適切に追加し、実行計画を最適化し、並列性を高めることで、SQLステートメントを最適化できます。

より多くの計算をプッシュダウンする

PolarDB-Xは、できるだけ多くの計算をストレージ層のMySQLにプッシュダウンします。これにより、PolarDB-Xでのデータ送信、ネットワーク、および計算のコストが削減されます。これにより、より効率的なSQLクエリの実行も可能になります。 PolarDB-Xは、次の計算をプッシュダウンできます。

WHERE句やHAVING句の条件など、条件をフィルタリングします。
COUNT句やGROUP BY句などの集計。凝集は2つの相に分けられる。
ORDER BYなどのソート。
JOIN操作とサブクエリ。両側のJOINキーのシャーディングモードは同じであるか、片側がブロードキャストテーブルである必要があります。

次の例は、より多くの計算をMySQLにプッシュダウンして実行効率を向上させる方法を示しています。

EXPLAINステートメントを実行して、実行計画を表示します。
```
EXPLAIN SELECT * FROM customer, nation 
        WHERE c_nationkey = n_nationkey AND n_regionkey = 3;
```
サンプル結果:
```
Project(c_custkey="c_custkey", c_name="c_name", c_address="c_address", c_nationkey="c_nationkey", c_phone="c_phone", c_acctbal="c_acctbal", c_mktsegment="c_mktsegment", c_comment="c_comment", n_nationkey="n_nationkey", n_name="n_name", n_regionkey="n_regionkey", n_comment="n_comment")
  BKAJoin(condition="c_nationkey = n_nationkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="nation", shardCount=2, sql="SELECT * FROM `nation` AS `nation` WHERE (`n_regionkey` = ?)")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT * FROM `customer` AS `customer` WHERE (`c_nationkey` IN ('?'))")
```
実行計画では、バッチキーアクセス (BKA) 結合が使用されます。 BKA結合が左のテーブルから行のグループを選択するたびに、右のテーブルから相関する行を選択するためにINクエリを実行します。次に、BKA結合は、両方のテーブルからの行を結合する。左側のテーブルには大量のデータが含まれています。その結果、データを何度も検索しなければならない。これにより実行が遅くなります。
顧客はc_custkeyプライマリキーでグループ化され、国はn_nationkeyプライマリキーでグループ化されるため、JOIN操作をプッシュダウンすることはできません。このクエリのJOINキーは、c_nationkeyとn_nationkeyです。両側のJOINキーのシャーディングモードは異なり、プッシュダウンは失敗します。
国テーブルには少量のデータが含まれており、テーブルに対する変更はほとんどありません。したがって、次のブロードキャストテーブルを作成して、国テーブルを置き換えることができます。
```
CREATE TABLE `nation` (
  `n_nationkey` int(11) NOT NULL,
  `n_name` varchar(25) NOT NULL,
  `n_regionkey` int(11) NOT NULL,
  `n_comment` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`n_nationkey`)
) BROADCAST;  --- Claim the table as a broadcast table.
```
実行計画を再度表示します。
```
EXPLAIN SELECT * FROM customer, nation 
        WHERE c_nationkey = n_nationkey AND n_regionkey = 3;
```
サンプル結果:
```
Gather(concurrent=true)
  LogicalView(tables="customer_[0-7],nation", shardCount=8, sql="SELECT * FROM `customer` AS `customer` INNER JOIN `nation` AS `nation` ON ((`nation`.`n_regionkey` = ?) AND (`customer`.`c_nationkey` = `nation`.`n_nationkey`))")
```
国表を変更すると、joinは実行計画で使用されなくなります。ほとんどの計算は、MySQLのLogicalViewにプッシュダウンされます。 PolarDB-Xは、結果を収集してユーザーに返すだけで済みます。これにより、実行効率が大幅に向上します。プッシュダウンの詳細については、「プッシュダウンと書き換えクエリ」をご参照ください。

インデックスの追加

PolarDB-Xは、グローバルセカンダリインデックス (GSI) 機能をサポートしています。次の例は、グローバルセカンダリインデックスを使用してより多くの計算をプッシュダウンする方法を示しています。

EXPLAINステートメントを実行して、実行計画を表示します。
```
EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders, customer 
        WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-11' AND o_totalprice > 100;
```
サンプル結果:
```
Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  HashJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` > ?))")
```
実行計画では、注文テーブルはo_orderkeyに基づいてシャードされますが、顧客テーブルはc_custkeyに基づいてシャードされます。 2つのテーブルは異なるシャーディングモードを使用するため、結合をプッシュダウンすることはできません。
注文額が100米ドルを超える多数の注文が2019年11月11日に行われました。クロスシャード結合を実行するには時間がかかります。したがって、ジョイン計算をプッシュダウンするには、オーダテーブル上にグローバルセカンダリインデックスを作成する必要があります。注文テーブルのo_orderkey、o_custkey、o_orderdate、およびo_totalprice列がクエリに含まれます。 o_orderkey列は、プライマリテーブルのシャードキーです。 o_custkey列は、インデックステーブルのシャードキーです。 o_orderdate列とo_totalprice列は、table ACCESS BY index ROWIDの問題を防ぐために、インデックステーブルのカバー列として指定されます。

グローバルセカンダリインデックスを作成します。

CREATE GLOBAL INDEX i_o_custkey ON orders(`o_custkey`) 
COVERING (`o_orderdate`, `o_totalprice`) 
DBPARTITION BY HASH(`o_custkey`) TBPARTITION BY HASH(`o_custkey`) TBPARTITIONS 4;

EXPLAINステートメントを実行して、実行計画を再度表示します。
```
EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders FORCE INDEX(i_o_custkey), customer 
        WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-11' AND o_totalprice > 100;
```
サンプル結果:
```
Gather(concurrent=true)
  IndexScan(tables="i_o_custkey_[0-7],customer_[0-7]", shardCount=8, sql="SELECT `i_o_custkey`.`o_orderkey`, `customer`.`c_custkey`, `customer`.`c_name` FROM `i_o_custkey` AS `i_o_custkey` INNER JOIN `customer` AS `customer` ON (((`i_o_custkey`.`o_orderdate` = ?) AND (`i_o_custkey`.`o_custkey` = `customer`.`c_custkey`)) AND (`i_o_custkey`.`o_totalprice` > ?))")
```
グローバルセカンダリインデックスが作成され、FORCE index (i_o_custkey) を指定して強制的に使用された後、クロスシャード結合はIndexScanのローカル結合になり、MySQLによって実行されます。さらに、カバーする列は、TABLE ACCESS BY INDEX ROWIDの問題を防ぐのに役立ちます。これにより、照会効率が向上します。グローバルセカンダリインデックスの使用方法の詳細については、「GSI」をご参照ください。

実行プランの最適化

ほとんどの場合、PolarDB-Xのクエリオプティマイザは、最適な実行プランを自動的に生成できます。ただし、値が欠落しているか、偏差が存在する場合、自動的に生成される実行プランは最適ではありません。このような場合、ヒントを使用して実行計画を最適化できます。次の例は、実行プランを最適化する方法を示しています。

EXPLAINステートメントを実行して、実行計画を表示します。

EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders, customer 
        WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

サンプル結果:

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  HashJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")

注文額が10米ドル未満の少数の注文が2019年11月15日に行われました。この場合、BKA結合はハッシュ結合よりも効率的である。 BKA結合とハッシュ結合の詳細については、「JOIN操作の最適化と実行」をご参照ください。

/* + TDDL:BKA_JOIN (注文、顧客) * /ヒントを使用して、オプティマイザにBKA結合を強制的に使用します。 BKA結合は、ルックアップ結合としても知られている。

EXPLAIN /*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer
        WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

サンプル結果:

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  BKAJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer` WHERE (`c_custkey` IN ('?'))")

実行計画は、/* + TDDL:BKA_JOIN (注文、顧客) */ が追加された後に最適化されます。 SQLクエリのパフォーマンスが向上しました。ヒントを有効にするには、アプリケーションのSQL文にヒントを含めます。プラン管理機能を使用して、実行プランをSQL文にバインドすることもできます。 SQL文を変更する代わりに、プラン管理機能を使用することをお勧めします。次のサンプルコードを参照してください。

BASELINE FIX SQL /*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15';

このように、PolarDB-Xは、SQL文のパラメーターが変更された場合でも、指定されたSQL文の前述の実行プランのみを実行します。プラン管理機能の詳細については、「実行プラン管理」をご参照ください。

クエリを並列に処理する

HINT /* + TDDL:PARALLELISM=4 * /を使用して、並列処理の程度を指定できます。これにより、システムは複数のCPUコアを使用して計算を高速化できます。サンプルコード：

EXPLAIN PHYSICAL SELECT a.k, count(*) cnt FROM sbtest1 a, sbtest1 b 
        WHERE a.id = b.k AND a.id > 1000 GROUP BY k HAVING cnt > 1300 ORDER BY cnt limit 5, 10;

サンプル結果:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorType: AP_LOCAL                                                                                                                                                 |
| The Query's MaxConcurrentParallelism: 2                                                                                                                           |
| Fragment 1                                                                                                                                                        |
|     Shuffle Output layout: [BIGINT, BIGINT] Output layout: [BIGINT, BIGINT]                                                                                       |
|     Output partitioning: SINGLE [] Parallelism: 1                                                                                                                 |
|     TopN(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                                     |
|   Filter(condition="cnt > ?1")                                                                                                                                    |
|     HashAgg(group="k", cnt="COUNT()")                                                                                                                             |
|       BKAJoin(condition="k = id", type="inner")                                                                                                                   |
|         RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k))                                                             |
|         Gather(concurrent=true)                                                                                                                                   |
|           LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))") |
| Fragment 0                                                                                                                                                        |
|     Shuffle Output layout: [BIGINT, BIGINT] Output layout: [BIGINT, BIGINT]                                                                                       |
|     Output partitioning: SINGLE [] Parallelism: 1 Splits: 16                                                                                                      |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)")                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

デフォルトの並列度は高くありません。並列処理の程度を指定して、スタンドアロンまたは並列コンピューティングモードでのクエリ効率を向上させることができます。

EXPLAIN PHYSICAL /*+TDDL:PARALLELISM=8*/SELECT a.k, count(*) cnt FROM sbtest1 a, sbtest1 b 
        WHERE a.id = b.k AND a.id > 1000 GROUP BY k HAVING cnt > 1300 ORDER BY cnt limit 5, 10;

サンプル結果:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorMode: AP_LOCAL                                                                                                                                      |
| Fragment 0 dependency: [] parallelism: 8                                                                                                                    |
| BKAJoin(condition="k = id", type="inner")                                                                                                                   |
|   Gather(concurrent=true)                                                                                                                                   |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `id`, `k` FROM `sbtest1` AS `sbtest1` WHERE (`id` > ?)")               |
|   Gather(concurrent=true)                                                                                                                                   |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, sql="SELECT `k` FROM `sbtest1` AS `sbtest1` WHERE ((`k` > ?) AND (`k` IN (...)))") |
| Fragment 1 dependency: [] parallelism: 8                                                                                                                    |
| LocalBuffer                                                                                                                                                 |
|   RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k, INTEGER_UNSIGNED k0))                                        |
| Fragment 2 dependency: [0, 1] parallelism: 8                                                                                                                |
| Filter(condition="cnt > ?1")                                                                                                                                |
|   HashAgg(group="k", cnt="COUNT()")                                                                                                                         |
|     RemoteSource(sourceFragmentIds=[1], type=RecordType(INTEGER_UNSIGNED id, INTEGER_UNSIGNED k, INTEGER_UNSIGNED k0))                                      |
| Fragment 3 dependency: [0, 1] parallelism: 1                                                                                                                |
| LocalBuffer                                                                                                                                                 |
|   RemoteSource(sourceFragmentIds=[2], type=RecordType(INTEGER_UNSIGNED k, BIGINT cnt))                                                                      |
| Fragment 4 dependency: [2, 3] parallelism: 1                                                                                                                |
| TopN(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                                   |
|   RemoteSource(sourceFragmentIds=[3], type=RecordType(INTEGER_UNSIGNED k, BIGINT cnt))                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+