SQL 文によるクエリパフォーマンスの向上 - MaxCompute - Alibaba Cloud ドキュメントセンター

このトピックでは、SQL 文を最適化してパフォーマンスを向上させる一般的なシナリオについて説明し、最適化の例を示します。

同時実行の最適化

並列処理の次数は、並列計算のメジャーです。たとえば、実行計画で、ID M1 のタスクが 1,000 個のインスタンスを使用する場合、その並列処理の次数は 1000 です。並列処理の次数を設定および調整することで、タスクの効率を向上させることができます。

このセクションでは、同時実行を最適化できるシナリオについて説明します。

インスタンスの強制実行

一部の操作では、システムはタスクを実行するために 1 つのインスタンスのみを強制的に呼び出します。次の操作を例として使用します。

group by 句を指定せずに集約を実行するか、group by 句で定数を使用します。
ウィンドウ関数の over 句の partition by に定数を指定します。
SQL 文で distribute by または cluster by に定数を指定します。

ソリューション: 定数に基づく操作が必要かどうかを確認します。システムがタスクを実行するために 1 つのインスタンスのみを強制的に呼び出すのを防ぐために、これらの操作をキャンセルすることをお勧めします。

呼び出されるインスタンス数が多すぎるか少なすぎる

重要

同時実行が増加するにつれて、実行パフォーマンスが常に向上するとは限りません。ジョブに対して呼び出すインスタンス数が多すぎると、次の理由により実行速度が低下する可能性があります。

インスタンス数が多すぎると、リソースを待機する時間が長くなり、キューでの待機回数が増加します。
各インスタンスの初期化には時間がかかります。同時実行が高いほど、初期化にかかる合計時間が長くなり、有効な実行時間の割合が低くなります。

次のシナリオでは、システムは非常に多数のインスタンスを強制的に呼び出します。

システムは、多くの小規模なパーティションからデータを読み取る必要があります。たとえば、10,000 個のパーティションからデータを読み取る SQL 文を実行すると、システムは 10,000 個のインスタンスを強制的に呼び出します。
ソリューション: SQL 文を最適化して、データを読み取るパーティションの数を減らします。たとえば、読み取る必要のないパーティションをプルーニングしたり、大規模なジョブを複数の小規模なジョブに分割したりできます。
一度に 256 MB のデータしか読み取らないのでは不十分です。これにより、インスタンスの実行時間が短くなります。合計入力データが大きい場合、これにより並列処理の次数が過度に高くなり、インスタンスはほとんどの時間をリソースのキューイングに費やすことになります。
ソリューション: 次のコマンドを実行して、reduce タスクに対して同時に呼び出すことができるインスタンスの最大数を減らします。この場合、各インスタンスで処理されるデータ量が増加します。
```
SET odps.stage.mapper.split.size=<256>;
SET odps.stage.reducer.num=<Maximum number of concurrent instances>;
```

インスタンス数の設定

テーブルの読み取りを伴うタスク
- メソッド 1: パラメーターを設定して同時実行を調整します。
```
-- マッパーの最大入力データ量を設定します。単位: MB。
-- デフォルト値: 256。有効な値: [1,Integer.MAX_VALUE]。
SET odps.sql.mapper.split.size=<value>;
```
- メソッド 2: MaxCompute が提供する分割サイズヒントを使用して、単一テーブルでの読み取り操作の同時実行を調整します。
```
-- 分割サイズを 1 MB に設定します。この設定は、src テーブルのデータが読み取られるときに、タスクが 1 MB のサイズに基づいてサブタスクに分割されることを示します。
SELECT a.key FROM src a /*+split_size(1)*/ JOIN src2 b ON a.key=b.key;
```
- メソッド 3: データ量、行数、または同時実行の指定に基づいてテーブルデータを分割します。
メソッド 1 では、odps.sql.mapper.split.size パラメーターは Mapper ステージのグローバル設定のみをサポートし、最小値は 1 MB です。必要に応じて、テーブルのディメンションに基づいて並列処理の次数を調整できます。これは、行あたりのデータ量は少ないが、その後の計算が重い場合に特に役立ちます。並列処理される行数を減らして、タスクの並列処理の次数を増やすことができます。
次のいずれかのコマンドを実行して、同時実行を調整できます。
- テーブルでの同時処理のために単一シャードのサイズを設定します。
```
SET odps.sql.split.size = {"table1": 1024, "table2": 512};
```
- テーブルでの同時処理の行数を設定します。
```
SET odps.sql.split.row.count = {"table1": 100, "table2": 500};
```
- テーブルの同時実行を設定します。
```
SET odps.sql.split.dop = {"table1": 1, "table2": 5};
```
説明
odps.sql.split.row.count および odps.sql.split.dop パラメーターは、内部テーブル、非トランザクションテーブル、および非クラスター化テーブルにのみ使用できます。
テーブルの読み取りを伴わないタスク
次のいずれかのメソッドを使用して、同時実行を調整できます。
- メソッド 1: odps.stage.reducer.num の値を調整します。次のコマンドを使用して、Reducer の並列処理の次数を設定します。この設定は、関連するすべてのタスクに影響します。
```
-- reducer タスクを実行するために呼び出されるインスタンスの数を設定します。
-- 有効な値: [1,99999]。
SET odps.stage.reducer.num=<value>;
```
- メソッド 2: odps.stage.joiner.num の値を調整します。次のコマンドを使用して、Joiner の並列処理の次数を設定します。この設定は、関連するすべてのタスクに影響します。
```
-- joiner タスクを実行するために呼び出されるインスタンスの数を設定します。
-- 有効な値: [1,99999]。
SET odps.stage.joiner.num=<value>;
```
- メソッド 3: odps.sql.mapper.split.size の値を調整します。
  テーブルの読み取りを伴わないタスクの場合、その同時実行はテーブルの読み取りを伴うタスクの同時実行の影響を受けます。テーブルの読み取りを伴うタスクの同時実行を調整することで、テーブルの読み取りを伴わないタスクの同時実行を調整できます。

ウィンドウ関数の最適化

SQL 文でウィンドウ関数が使用されている場合、各ウィンドウ関数に reduce タスクが割り当てられます。多くのウィンドウ関数は大量のリソースを消費します。次の両方の条件を満たすウィンドウ関数を最適化できます。

テーブル内の行をパーティション分割およびソートする方法を定義する OVER 句は同じでなければなりません。
複数のウィンドウ関数は、SQL 文の同じネストレベルで実行する必要があります。

上記の条件を満たすウィンドウ関数は、1 つの reduce タスクで実行されるようにマージされます。次の SQL 文に例を示します。

SELECT
RANK() OVER (PARTITION BY A ORDER BY B desc) AS RANK,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B desc) AS row_num
FROM MyTable;

サブクエリの最適化

次の文にはサブクエリが含まれています。

SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);

table_b テーブルのサブクエリが col1 列に対して 9,999 を超える値を返す場合、システムは次のエラーを報告します: records returned from subquery exceeded limit of 9999。この場合、次の例に示すように、代わりに JOIN 文を使用します。

SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1);

説明

DISTINCT キーワードが使用されていない場合、サブクエリの結果テーブル c の col1 列に重複した値が含まれる可能性があります。この場合、テーブル a に対するクエリはより多くの結果を返します。
DISTINCT キーワードを使用すると、サブクエリの実行に割り当てられるワーカーは 1 つだけになります。サブクエリに大量のデータが含まれている場合、クエリ全体が遅くなります。
col1 列のサブクエリ条件を満たす値が一意であることが確かな場合は、DISTINCT キーワードを削除してクエリのパフォーマンスを向上させることができます。

join の最適化

2 つのテーブルを join する場合、次のルールに基づいて WHERE 句を使用することをお勧めします。

WHERE 句でプライマリテーブルのパーティション制限を指定します。まず、プライマリテーブルのサブクエリを定義して、必要なデータを取得することをお勧めします。
プライマリテーブルの WHERE 句を文の最後に記述します。
セカンダリテーブルのパーティション制限は、WHERE 句ではなく、ON 句またはサブクエリで指定します。

次のコードに例を示します。

SELECT * FROM A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id WHERE A.dt=20150301;
SELECT * FROM A JOIN B ON B.id=A.id WHERE B.dt=20150301; -- この文は使用しないことをお勧めします。システムは、パーティションプルーニングを実行する前に JOIN 操作を実行します。これにより、データ量が増加し、クエリのパフォーマンスが低下します。 
SELECT * FROM (SELECT * FROM A WHERE dt=20150301)A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id;

集計関数の最適化

集計関数を最適化するには、collect_list 関数を wm_concat 関数に置き換えることができます。次の例に、その方法を示します。

-- collect_list 関数を実装します。
SELECT concat_ws(',', sort_array(collect_list(key))) FROM src;
-- パフォーマンスを向上させるために wm_concat 関数を実装します。
SELECT wm_concat(',', key) WITHIN GROUP (ORDER BY key) FROM src;


-- collect_list 関数を実装します。
SELECT array_join(collect_list(key), ',') FROM src;
-- パフォーマンスを向上させるために wm_concat 関数を実装します。
SELECT wm_concat(',', key) FROM src;