:FEDERATEDストレージエンジンのプッシュダウンのパフォーマンス最適化の計算

概要

FEDERATEDストレージエンジンを含むクエリの場合、MySQL Community Editionは、RANGEまたはREFメソッドを使用してインデックスをスキャンする場合にのみ、インデックスの条件をプッシュダウンできます。 その他の条件は、ローカルデータベースでのみ使用できます。 実際のシナリオでは、クエリのWHERE条件には多数のフィールドが含まれるか、またはインデックスフィールドで関数が使用される場合があります。 これらはインデックスを直接使用することを不可能にします。 この場合、FEDERATEDストレージエンジンは、フルテーブルスキャンクエリをリモートデータベースに送信し、クエリのためにすべてのデータをローカルデータベースにプルします。 PolarDB for MySQLの条件プッシュダウン機能は、リモートデータベースのデータをフィルタリングするために、できるだけ多くの互換条件をプッシュダウンします。 これにより、クエリのパフォーマンスが向上し、ネットワークの使用量が削減され、ローカルコピーとデータ形式の変換のコストが削減されます。

条件プッシュダウンを有効にする方法

条件プッシュダウン機能を有効にするには、loose_optimizer_switchパラメーターを設定します。 詳細については、「クラスターパラメーターとノードパラメーターの設定」をご参照ください。

例

以下の実施例では、異なる選択性での条件プッシュダウンによってもたらされる性能改善をシミュレートするために、1千万のエントリを含むSysbench表を使用する。 ローカルサーバーとリモートサーバーのクラスター仕様のは、polar.mysql.x8.large 4コアと32 GB (専用リソース) です。 ストレージタイプはPLS5です。

リモートテーブルとFEDERATEDテーブルの作成

# リモートテーブルの作成#
テーブル 'sbtest1' の作成 (
  'id' int NOT NULL、
  'k' int NOT NULL DEFAULT '0',
  'c' char(120) NOT NULL DEFAULT ''、
  'pad'char (60) NOT NULL DEFAULT ''、
  主要なキー ('id') 、
  キー 'idx_1 ' ('k')
) エンジン=InnoDBデフォルト料金=utf8;

# サーバーの作成#
サーバーの作成
外国データラッパーmysql
オプション (ユーザー 'ユーザー名' 、パスワード 'パスワード' 、ホスト 'ホスト名' 、ポート3306、データベース 'dbname');

# FEDERATEDテーブルの作成#
テーブル 'sbtest1' の作成 (
  'id' int NOT NULL、
  'k' int NOT NULL DEFAULT '0',
  'c' char(120) NOT NULL DEFAULT ''、
  'pad'char (60) NOT NULL DEFAULT ''、
  主要なキー ('id') 、
  キー 'idx_1 ' ('k')
) エンジン=FEDERATED DEFAULT CHARSET=utf8接続='s'; 

条件プッシュダウン機能が有効または無効になっているSQLクエリ

# 条件プッシュダウン機能が無効になっているSQLクエリ#
optimizer_switch='engine_condition_pushdown=off 'を設定します。クエリOK、影響を受ける0行 (0.03秒)

sbtest1からの選択カウント (*) WHERE id + 1 < 100;
---- ----------- ---------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ----------- ---------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | Using where |
---- ----------- ---------------------------------------------------------------------------------------------------------
セットの1列、1警告 (0.04秒)

# 条件プッシュダウン機能が有効になっているSQLクエリ#
optimizer_switch='engine_condition_pushdown=on' を設定します。クエリOK、影響を受ける0行 (0.10秒)

sbtest1からの選択カウント (*) WHERE id + 1 < 100;
---- ----------- ---------------------------------------------------- ----------------------------------...
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ----------- ---------------------------------------------------- ----------------------------------...
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | プッシュされた状態でwhereを使用する (('federated'.'sbtest1'.'id' + 1) < 100) |
---- ----------- ---------------------------------------------------- ----------------------------------...
セットの1列、1警告 (0.05秒) 

前述のSQLクエリでは、WHERE条件は主キーを含む関数を定数と比較します。 最適な実行計画は、フルテーブルスキャンを使用することです。 パフォーマンスは、WHERE条件が実行のためにリモートサーバーにプッシュダウンされるかどうかによって異なります。 定数値をselectivity * table_sizeに変更して、パフォーマンステストの条件に異なる選択性を与えることができます。

前の図に示すように、選択性が低い (0.1未満) 場合、イネーブル条件のプッシュダウンは、パフォーマンスを約100% 増加させます。

SQLクエリを実行すると、条件プッシュダウン機能は頻繁に有効または無効になります。 PolarDBコンソールのパフォーマンスモニタリング機能を使用して、SQLクエリの帯域幅使用量を表示できます。

• 条件がプッシュダウンされない場合、SQLクエリは大量のネットワーク帯域幅を消費します。 ネットワーク帯域幅の使用は、異なる選択性を有するクエリに対して同じである。

• 条件が押し下げられ、選択性が低い (0.1未満) 場合、ネットワークトラフィックは非常に小さくなります。 ネットワークトラフィックは、選択性が増加するにつれて徐々に増加する。 これは、条件がプッシュダウンされていないSQLクエリのネットワークIOとは明らかに異なります。

概要

FEDERATEDストレージエンジンを含むSQLクエリがリモートテーブルからデータを取得すると、すべての列の値が返されます。 実際のシナリオでは、クエリは、代わりにいくつかの列の値だけを必要とし得る。 クエリによって必要とされない他の列の値は、リモートサーバのためのデータ選択およびフォーマット変換のコストを増加させ、より多くのネットワーク帯域幅を占有する。 したがって、PolarDB for MySQLは、MySQL Community Editionの列戻りロジックを最適化し、FEDERATEDテーブルを含むSQLクエリがリモートサーバーから必要な列のみを取得できるようにします。 これにより、データ選択のコストとリモートサーバのネットワーク使用量が大幅に削減され、クエリのパフォーマンスが向上します。 テーブルに含まれる列が多いほど、クエリのパフォーマンスが向上します。

例

次の例では、シミュレーション用に100万のデータレコードと異なる数の列を含むテーブルを使用しています。 100の列を含むテーブルは、次のサンプルコードを使用して定義されます。 Sysbenchテーブルのk、c、pad列を繰り返し定義してテーブルを広げます。 文字タイプの長さを短くすることで、より多くのスペースを占有することなく、より多くの列を定義できます。

テーブルの定義

テーブル 'sbtest1' の作成 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  'k' int (11) NOT NULL DEFAULT '0',
  'c' char(15) NOT NULL DEFAULT ''、
  'pad'char (8) NOT NULL DEFAULT ''、
  'k1' int (11) NOT NULL DEFAULT '0',
  'c1' char(15) NOT NULL DEFAULT ''、
  'pad1' char(8) NOT NULL DEFAULT ''、
  ...
  'k32' int (11) NOT NULL DEFAULT '0',
  'c32' char(15) NOT NULL DEFAULT ''、
  'pad32'char (8) NOT NULL DEFAULT ''、
  主要なキー ('id') 、
  キー 'k_1 ' ('k')
) エンジン=InnoDB AUTO_INCREMENT=1000001デフォルト料金=utf8; 

オンデマンド列戻り機能が有効または無効のSQLクエリ

[SELECT pad FROM sbtest1] を使用して、オンデマンド列のリターン機能を有効または無効にして、パフォーマンスをテストします。 、padフィールドはリモートテーブルでインデックスされません。 したがって、実行計画は主キーのフルテーブルスキャンを使用します。 次の表に、実行時間を秒単位で示します。

上の図に示すように、列の数が増えると、オンデマンド列の戻り機能により、クエリ速度が線形に加速されます。

さらに、オンデマンド列の戻り機能により、リモートサーバーの実行計画が増えます。 クエリされた列にインデックスが作成されている場合、実行計画では、テーブル全体のスキャンではなく、リモートテーブルのインデックススキャンが使用されます。 これにより、性能がさらに向上する。

# オンデマンド列の戻り機能が無効になっているクエリ#
SET federated_fetch_select_field_enabled=OFF;
クエリOK、影響を受ける0行 (0.19秒)

federated_col_64.sbtest1からのSELECT SUM(k);
+ -------------
| SUM(k) |
+ -------------
| 499868973740 |
+ -------------
セットの1列 (5.20秒)

# オンデマンド列の戻り機能が有効になっているクエリ#
SET federated_fetch_select_field_enabled=ON;
クエリOK、影響を受ける0行 (0.11秒)

federated_col_64.sbtest1からのSELECT SUM(k);
+ -------------
| SUM(k) |
+ -------------
| 499868973740 |
+ -------------
1行セット (0.45秒) 

上記の例では、インデックスの後にk列で使用されます。 オンデマンド列の戻り機能を有効にすると、クエリのパフォーマンスが1000% 以上向上します。

概要

MySQL Community Editionでは、FEDERATEDテーブルを含むクエリのページ化されたクエリのすべての条件をプッシュダウンすることはできません。 その結果、すべてのデータがリモートサーバーからローカルサーバーに返されます。 ページングする前に、WHERE条件を使用してデータをフィルタリングする必要があります。 条件pushdownが有効になっている場合PolarDB for MySQL SQL文には1つだけFEDERATEDテーブルには、集計、ウィンドウ、UNION、DISTINCT、HAVINGなどの結果の精度に影響を与える句は含まれていません。 [<cols> による注文] オフセットの制限 句は、処理のためにリモートサーバーにプッシュダウンできます。 ローカルサーバは、結果をクライアントに直接返すことができる。

How to use [ORDER BY <cols>] LIMIT OFFSET pushdown

loose_optimizer_switchパラメーターを設定して、 [ORDER BY <cols>] LIMIT OFFSETプッシュダウンを有効にします。 詳細については、「クラスターパラメーターとノードパラメーターの設定」をご参照ください。

例

テーブルの作成

テーブル 'sbtest1' の作成 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  'k' int (11) NOT NULL DEFAULT '0',
  'c' char(15) NOT NULL DEFAULT ''、
  'pad'char (8) NOT NULL DEFAULT ''、
  'k1' int (11) NOT NULL DEFAULT '0',
  'c1' char(15) NOT NULL DEFAULT ''、
  'pad1' char(8) NOT NULL DEFAULT ''、
  ...
  'k32' int (11) NOT NULL DEFAULT '0',
  'c32' char(15) NOT NULL DEFAULT ''、
  'pad32'char (8) NOT NULL DEFAULT ''、
  主要なキー ('id') 、
  キー 'k_1 ' ('k')
) エンジン=InnoDB AUTO_INCREMENT=1000001デフォルト料金=utf8; 

[ORDER BY <cols>] LIMIT OFFSETプッシュダウン機能が有効または無効になっているSQLクエリ

# [ORDER BY <cols>] LIMIT OFFSETプッシュダウン機能が有効になっているクエリ#
set optimizer_switch='limit_offset_pushdown=on';
クエリOK、影響を受ける0行 (0.05秒)

EXPLAIN SELECT * federated.sbtest1リミット100オフセット1000;
---- ----------- ---------------------------------------------------- ----------------------------------...
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ----------- ---------------------------------------------------- ----------------------------------...
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | 限界オフセットのプッシュダウンの使用 |
---- ----------- ---------------------------------------------------- ----------------------------------...
セットの1列、1警告 (0.06秒)

# [ORDER BY <cols>] LIMIT OFFSETプッシュダウン機能が無効になっているクエリ。set optimizer_switch='limit_offset_pushdown=off';
クエリOK、影響を受ける0行 (0.05秒)

EXPLAIN SELECT * federated.sbtest1リミット100オフセット1000;
---- ----------- ---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ----------- ---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | NULL |
---- ----------- ---------------------------------------------------------------------------------------------------------------
セットの1行、1警告 (0.05秒)

# オンデマンドORDERプッシュダウンを無効にする#
SET federated_pushdown_order_enabled=OFF;
クエリOK、影響を受ける0行 (0.19秒)

# オンデマンドORDERプッシュダウンの有効化#
SET federated_pushdown_order_enabled=ON;
クエリOK、影響を受ける0行 (0.11秒)

mysql> explain select * from federated.sbtest1のid制限100オフセット1000による順序;
---- ------------ -------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------ -------------------------------------------------------------------------------------
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | 限界オフセットpushdownの使用; 順序pushdown 'id' |
---- ------------ -----------------------------------------------------------------------------------... 

[ORDER BY <cols>] LIMIT OFFSET機能を有効または無効にして、SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET番号を使用して、1,000万件のデータレコードを含むSysbenchテーブルのパフォーマンスをテストできます。 次の表に、さまざまなOFFSET値のクエリのパフォーマンスを示します。

OFFSET値が小さいほど、クエリのパフォーマンスが向上します。 クエリのパフォーマンスを最大600% 向上させることができます。