PolarDB:SQLチューニングの方法とプラクティス

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

PolarDB-X 1.0は、より多くの計算をストレージ層MySQLにプッシュダウンします。 計算のプッシュダウンは、ネットワーク層とPolarDB-X 1.0層での転送データ量とオーバーヘッドを削減し、SQL文の実行効率を向上させます。 PolarDB-X 1.0は、次の項目を含むほぼすべての演算子をプッシュダウンできます。

• WHEREまたはHAVINGの条件などの条件をフィルターします。

• COUNTやGROUP BYなどの集計演算子。 凝集は2つの相に分けられる。

• ORDER BYなどのソート演算子。

• JOINとサブクエリ。 両側のJOINキーのシャーディングモードは同じであるか、片側がブロードキャストテーブルである必要があります。

次の例は、より多くの計算をMySQLにプッシュダウンして実行を高速化する方法を示しています。

> 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 ('?'))")

実行計画にBKAJOINが表示されます。 BKAJOINはINクエリをアセンブルして右側のテーブルに関連付けられた行を取得し、BKAJOINが左側のテーブルからデータのバッチを取得するたびにJOIN操作を実行します。 左側のテーブルには大量のデータが含まれています。 その結果、データを何度も検索しなければならない。 これにより実行が遅くなります。

顧客は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.

変更後、JOINは実行計画に表示されず、ほとんどすべての計算がストレージレイヤーMySQLにプッシュされ、LogicalViewで実行されます。 上位レイヤーは、Gather演算子を使用して結果を収集してユーザーに返すだけです。 これにより、実行パフォーマンスが大幅に向上します。

> 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`))")

プッシュダウンの原則と最適化の詳細については、「SQLの書き換えとプッシュダウン」をご参照ください。

インデックスの追加

プッシュダウンされたSQL文に (物理的な) 低速SQL文が表示されている場合は、テーブルのシャーディングにインデックスを追加できます。 詳細はここでは説明しない。

PolarDB-X 1.0は、バージョン5.4.1以降、グローバルセカンダリインデックスをサポートしています。 グローバルセカンダリインデックス (GSI) を追加して、論理テーブルを複数のディメンションでシャードできるようにすることができます。

次の例は、GSIを使用してより多くの演算子をプッシュダウンする方法を示しています。 この例では、低速SQL文が使用されています。

> 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によってシャードされ、customerはcustkeyによってシャードされます。 異なるシャーディング寸法のため、JOIN演算子を押し下げることはできません。

2019年11月11日には、合計価格が100米ドルを超える多数の注文が発生し、クロスシャードのJOIN操作には長い時間がかかります。 したがって、JOIN演算子をプッシュダウンできるように、注文テーブルにGSIを作成する必要があります。

ordersテーブルのo_orderkey、o_custkey、o_orderdate、およびo_totalprice列がクエリに使用されます。 4つの列では、o_orderkeyはプライマリテーブルのシャードキー、o_custkeyはインデックステーブルのシャードキーです。 o_orderdateとo_totalpriceは、テーブルへのアクセスを避けるためにインデックスのカバー列として使用されます。

> 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;

GSIが追加され、GSIを強制的に使用するために強制インデックス (i_o_custkey) が実行されると、クロスシャードJOIN操作はMySQLのIndexScanのローカルJOIN操作になります。 さらに、テーブルへのアクセスを避けるためにカバーコラムが使用されます。 これにより、クエリのパフォーマンスが向上します。

> 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` > ?))")

GSIの使用方法の詳細については、「グローバルセカンダリインデックスの使用」をご参照ください。

実行プランの最適化

ほとんどの場合、PolarDB-X 1.0のクエリオプティマイザは、最適な実行プランを自動的に生成できます。 ただし、いくつかのケースでは、生成された実行プランは、統計が欠落しているか無効であるため、期待どおりではありません。 この場合、ヒントを使用してオプティマイザの動作に介入し、オプティマイザがより優れた実行計画を作成できるようにすることができます。

次の例は、実行計画を最適化する方法を示しています。

次のクエリでは、PolarDB-X 1.0のクエリオプティマイザが両方のJOIN側のコストを比較します。

> 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` < ?))")

ただし、2019年11月15日、合計価格がUSD 10未満の注文数は非常に少ないです。 この場合、BKAJOINはHash JOINよりも良い選択です。 BKAJOINとハッシュJOINの詳細については、「JOIN操作とサブクエリの最適化と実行」をご参照ください。

/* + TDDL:BKA_JOIN (注文、顧客) */ ヒントを使用して、オプティマイザにBKAJOIN (LookupJOIN) を強制的に使用します。

> 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(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;

上記の操作により、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 1.0は、パラメーターが異なる可能性のあるこのSQLステートメントに対して、前述の固定実行プランを使用します。

プラン管理の詳細については、「実行プランの管理」をご参照ください。