griddb.github.io

— はじめに —

本書の目的と構成

本書では、GridDBのSQLのチューニングについて説明します。

本書は、GridDBでSQLを用いたシステム開発を行う開発者の方を対象としています。

本書は、以下のような構成となっています。

最適化のルールやチューニングなどについては、GridDBに特化した特徴的な点を主に説明します。

　　　

— チューニングのステップ —

SQLのチューニングは、一般的なデータベースシステムと同様に以下のような手順で行います。

　

STEP1からSTEP3について詳細を説明します。

　

遅いクエリの確認

システムで実行しているクエリの中で、時間がかかっている遅いクエリを特定します。

実行時間がかかった遅いクエリは、そのクエリと実行時間などの情報をイベントログに出力することができます。 これにより、アプリケーションから実行された複数のクエリの中から、ボトルネックとなっているクエリを特定することができます。

遅いクエリの確認の手順は以下の通りです。

  1. スロークエリの出力設定を行う

　

  1. クエリを実行する

　

  1. 遅いクエリを確認する

プランの取得

STEP1で特定したスロークエリを実行して、クエリのプラン(グローバルプラン)を取得します。

プランの取得には運用ツールgs_shを用います。EXPLAIN ANALYZE構文でクエリを実行し、サブコマンドgetplantxtでプランを取得します。

　

(1) クエリを「EXPLAIN ANALYZE」構文で実行する

gs[public]> sql EXPLAIN ANALYZE select * from table1, table2 where table1.value=1 and table1.id=table2.id ;
検索を実行しました。 (19 ms)

(2) プランを取得する

gs[public]> getplantxt
Id Type       Input Rows Lead time Actual time Node                 And more..
--------------------------------------------------------------------------------------------------------------------
 0 SCAN       -     -           30          30 192.168.15.161:10001 table: {table1}
 1   SCAN     0     3000        21          21 192.168.15.161:10001 table: {table1, table2} INDEX SCAN JOIN_EQ_HASH
 2     RESULT 1     14           0           0 192.168.15.161:20001

　

GridDBでは、SQLに対して以下のように処理を行います。 ノードはクエリを構文解析しプランを生成します。プランは実行単位であるタスクごとのプランから構成されており、各ノードは割り当てられたタスクを実行します。

SQLのプランと処理の流れ
SQLのプランと処理の流れ

プラン表示のサブコマンドgetplantxtでは、このタスク単位のプランが1行ずつ表示されます。タスクの出力が次のタスクの入力になります。

「(2) プランを取得する」の実行例で、具体的にプランの表示を説明します。

　

クエリのチューニング

WHERE句の絞り込み条件のスキャン処理や、テーブルのジョインのためのスキャン処理では、索引の利用有無によって大きく性能が変わる場合があります。 また、テーブルのジョインの場合は、結合順序などによっても大きく性能が変わります。 よって、これらをポイントにクエリのチューニングを行ってください。

(1) プランの分析

プランを分析して、索引の利用有無や意図したとおりに動作しているかを確認します。 プランの詳細はSQLのプランをご参照ください。

(2) チューニング

プランの分析による問題点に応じて、次のような方法でチューニングを行います。

　

例) テーブルのジョインを行うクエリで、チューニングの例を説明します。

table1とtable2をカラムvalueの値で結合するクエリについて、プランを取得します。

gs[public]> EXPLAIN ANALYZE select * from table1, table2 where table1.value=0 and table1.value=table2.value;
検索を実行しました。 (13 ms)
gs[public]> getplantxt
Id Type       Input Rows  Lead time Actual time Node                 And more..
-------------------------------------------------------------------------------------
 0 SCAN       -     -            20          20 192.168.15.161:10001 table: {table1}
 1 SCAN       -     -             9           9 192.168.15.161:10001 table: {table2}
 2   JOIN     0,1   10000,3000  891         891 192.168.15.161:20001 JOIN_EQ_HASH
 3     RESULT 2     32            2           2 192.168.15.161:20001

このプランでは、table1とtable2をそれぞれスキャンして、索引を使わずにジョインの処理を行っています。

テーブルの索引情報を確認すると、カラムvalueに索引が付いていなかったため、索引を作成します。

同じクエリを実行してプランを取得します。

gs[public]> EXPLAIN ANALYZE select * from table1, table2 where table1.value=0 and table1.value=table2.value;
検索を実行しました。 (7 ms)
gs[public]> getplantxt
Id Type       Input Rows Lead time Actual time Node                 And more..
--------------------------------------------------------------------------------------------------------------------
 0 SCAN       -     -           20          20 192.168.15.161:10001 table: {table1}
 1   SCAN     0     10000       80          80 192.168.15.161:10001 table: {table1, table2} INDEX SCAN JOIN_EQ_HASH
 2     RESULT 1     32           3           3 192.168.15.161:20001

プランID2で「INDEX SCAN」と表示されており、索引を使用したジョイン処理にプランが変わっています。

　

— SQLの最適化 —

索引を利用したスキャン

WHERE句の絞り込み条件に一致するデータを探すスキャン処理では、テーブルの全ロウにアクセスする「フルスキャン」よりも、 テーブルの索引を用いてアクセスする「索引スキャン」の方が、多くの場合に高速になります。

特に、WHERE句の絞り込み条件がテーブルのロウ数に対してヒット率が小さくなるような、データをより絞り込める条件ほど索引スキャンの効果が高くなります。

　

索引の選択ルール

GridDBのSQL最適化において、スキャン処理で使用する索引の選択ルールを説明します。

絞り込み条件に指定された演算子や式によって、ルールが異なります。

　

AND

基本的に、絞込み条件のカラムに索引が設定されている場合は先頭の索引を使用します。

例)

a>1 AND b=2    (aとbに索引あり)

ただし、例外的に先頭の索引を使用しない場合もあります。例を以下に示します。

なお、同一カラムに対する条件が重複する場合は、条件をマージして索引を使用します。

例)

a>1 AND a<=4 AND a<=3    (aに索引あり)

　

OR

ORの場合は、絞込み条件に指定したカラムに、すべて索引が設定されている場合にのみ索引を使用します。

例）

a>1 OR b=2    (aとbに索引あり)

例）

a>1 OR b=2    (bに索引あり)

　

比較演算子の式

比較演算子の値にカラム単独の式と定数式を用いる場合のみ、索引を使用します。

例）

a>10*1000-1    (aに索引あり)

例）

a+1>10*1000-1    (aに索引あり)

例）

a>b    (aとbに索引あり)

　

IN、BETWEEN

INとBETWEENは、ANDとORと比較演算子を組合せた式とみなしたうえで、上記のルールを適用します。

例）

a IN (1,2)        → a=1 OR a=2      (aに索引あり)
a BETWEEN 1 AND 2 → a>=1 AND a<=2   (aに索引あり)

　

[メモ]

複合索引の選択ルール

GridDBのSQL最適化において、スキャン処理で使用する複合索引の選択ルールを説明します。

絞り込み条件に指定されたカラムや演算子によって、索引を使用する範囲が異なります。 複合索引を構成するカラムの先頭よりAND条件で連続して存在するカラムの条件までを複合索引として使用します。

[メモ]

例）

where col1 = 1 and col2 = 1 and col3 = 2      (col1,col2,col3の複合索引あり)

ジョイン

GridDBのSQL最適化において、複数テーブルをジョインする処理に関する次のルールを説明します。

索引を使用しないジョイン処理においては、ジョイン順序やジョイン演算方法の違いが性能に大きく影響します。

ジョイン順序の選択ルール

結合処理において、最初にアクセスするテーブルを駆動表、次にアクセスして結合するテーブルを内部表といいます。

ジョインの順序(駆動表と内部表)は次のルールで決まります。

2つのテーブルの結合の場合、定数の等価絞込み条件があるテーブルが駆動表になります。

例)

t1.a=t2.x AND t2.y=1

　

3つ以上のテーブルの結合の場合、結合や絞込み度合いの強さによって順序が決まります。

[メモ]

索引の適用ルール

ジョイン処理で索引を使用するかどうかのルールを説明します。

次の5つのすべてを満たす場合に索引を使用します。

[メモ]

索引の選択ルール

ジョインの構文が索引の適用ルールに当てはまる場合、索引を使用してジョインを行います。 ジョインで使用する索引を選択するルールを説明します。

基本的に、スキャンの索引の選択ルールと同様です。 カラムに設定されている索引はすべて記述順に使用します。

すべての索引を使用しない場合もあります。例を以下に示します。

絞込み条件のカラムに索引が設定されている場合は先頭の索引を使用します。

例)

  t1.a=t2.x AND t1.b>t2.y AND t2.z=1  (aとbに索引あり、駆動表はt2、内部表はt1)

　

OR条件(A OR B)において、Bがfalse定数の場合、Aの索引は使用しません。

例)

  t1.a=t2.x AND (t1.b=t2.y OR false) AND t2.z=1  (aとbに索引あり、駆動表はt2、内部表はt1)

　

ジョイン演算方法の選択ルール

ジョインの演算方法には次の3つの種類があります。

ジョインの演算方法 説明
ハッシュジョイン 駆動表の結合キーをハッシュ関数にかけてメモリ上に一時的なテーブルを作り、内部表のハッシュ値と一致するか比較して結合する方法です。
ソートマージジョイン 結合する2つのテーブルを結合キーでソートして、それらを順に比較して結合する方法です。
ネステッドループジョイン 駆動表の結合キーの値を元に、結合条件に合致する内部表のデータを探して結合する方法です。

速度が速いのは、ハッシュ、ソートマージ、ネステッドループの順です。

　

ジョインの第一結合条件の種類によって、これらの演算方法を選択します。

第一条件 選択する演算方法
等価条件 ハッシュまたはソートマージジョイン
(ハッシュが選択されていても、メモリリソースの制限により、部分的にソートマージに切り替わることがあります。)
大小条件 ソートマージジョイン
なし ネステッドループジョイン

　

ジョインの第一結合条件は、次の優先順で選択します。

　

ヒント句

　 実行計画を示すヒントをクエリに指定することで、SQL文を変えることなく実行計画を制御できます。

【注意事項】

用語

ヒント機能で用いる用語は次のとおりです。

用語 説明
ヒント句 実行計画を制御するための情報
ヒント ヒント句を列挙したもの。実行計画を制御するクエリに指定する。

ヒントの指定方法

実行計画を制御するクエリのブロックコメントの中にヒントを記述します。ヒント用のブロックコメントは、 SQL文中の先頭のSELECT(INSERT/UPDATE/DELETE)句の直前または直後のみ記述できます。通常のコメントと区別するため、 ヒント用のブロックコメントは「/*+」で始めます。

ヒントの対象は、ヒント句の括弧内にオブジェクト名または別名で指定します。複数のオブジェクト名を指定する場合、 スペース、タブ、改行のいずれかで区切って指定します。

以下の例では、Leadingヒント句により、テーブル結合順序を指定しています。

/*+
Leading(t3 t2 t1)
 */
SELECT *
  FROM t1, t2, t3
    ON t1.x = t2.y and t2.y = t3.z
  ORDER BY t1.x
  LIMIT 10;

【メモ】

ヒント句一覧

指定できるヒント句の一覧を次に示します。

分類 命令 説明
並列化 MaxDegreeOfTaskInput(上限数) 1タスクへの入力の上限
  MaxDegreeOfExpansion(上限数) プランノードの展開数の上限
スキャン方式 IndexScan(テーブル) 可能な場合はインデックススキャンを選択する
  NoIndexScan(テーブル) インデックススキャンを選択しない
結合方式 IndexJoin(テーブル テーブル) 可能な場合はインデックスジョインを選択する
  NoIndexJoin(テーブル テーブル) インデックスジョインを選択しない
結合順序 Leading(テーブル テーブル[ テーブル …]) 指定したテーブルを指定した順序で結合する
  Leading(( テーブル集合 テーブル集合 )) 1つ目に指定したテーブル集合を外部表、
2つ目に指定したテーブル集合を内部表として結合する

テーブル集合 = { テーブル or ( テーブル集合 テーブル集合 ) }

ヒント句詳細

ヒント句の分類ごとに詳細を説明します。

並列化

並列化処理の制御を行います。

スキャン方式

テーブルのスキャン方式を指定します。

結合方式

結合方式を指定します。

結合順序

テーブルのジョイン処理でどのような順番で結合するかを指定します。

(1) 結合順序のみ指定: Leading(テーブル テーブル[ テーブル …])

先に結合するテーブルから順にテーブル名または別名を指定します。この指定方式の場合、常にLeft-deep joinで結合されます。

(例1)

/*+ Leading(S R Q P) */
SELECT * FROM P,Q,R,S WHERE P.x = Q.x AND ...
結合順序(例1)
結合順序(例1)

(2) 結合方向を含めた指定: Leading((テーブル集合 テーブル集合))

テーブル集合 = { テーブル or (テーブル集合 テーブル集合) }

(1)のように結合順序のみを指定した場合、結合方向(外部表/内部表の別)が期待と異なる場合があります。 結合方向を固定したい場合は以下の書式を使います。

/*+ Leading((t1 (t2 t3))) */
SELECT ...

この書式では、括弧をネストして記述できます。括弧内の1つ目に指定したテーブル集合を外部表、 2つ目に指定したテーブル集合を内部表として結合されます。

(例2-1)

/*+ Leading(((P Q) R)) */
SELECT * FROM P,Q,R WHERE P.x = Q.x AND ...
結合順序(例2-1)
結合順序(例2-1)

(例2-2)

/*+ Leading((R (Q P))) */
SELECT * FROM P,Q,R WHERE P.x = Q.x AND ...
結合順序(例2-2)
結合順序(例2-2)

(例2-3)

/*+ Leading(((P Q) (R S))) */
SELECT * FROM P,Q,R,S WHERE P.x = Q.x AND ...
結合順序(例2-3)
結合順序(例2-3)

【メモ】

エラーの扱い

以下の場合は構文エラーとなります。

以下の場合はテーブル指定エラーとなります。

【メモ】

　

— SQLのプラン(実行計画) —

SQL最適化によってどのような演算や索引が選択されたかは、SQLのEXPLAIN ANALYZE文で確認することができます。

SQLの処理では、SQL構文を解析して最適化し、ジョインやソート・スキャンなどの複数の「タスク」という処理単位に分解してプラン(実行計画)を生成します。

タスクは、クラスタを構成するノードのいずれかひとつで実行され、タスク間でデータのやり取りをしながら並列実行します。

[メモ]

　

EXPLAIN ANALYZE文を実行すると、タスクのプランや実行時間などの情報を、タスク単位で1行ごとにJSON形式で出力します。

出力する主な項目は以下の通りです。

項目 説明
id プランID
type 処理の種類
inputList 入力値となるプランのプランIDの列
profile/leadtime 処理時間
profile/rows 入力件数
profile/address 処理を実行したノードのアドレスとポート番号

[メモ]

処理の種類は以下の通りです。

typeの値 説明
GROUP グルーピング演算
JOIN ジョイン演算
LIMIT 行単位件数フィルタ演算
SCAN テーブルスキャン演算
SELECT 選択演算(条件フィルタ・プロジェクション)
SORT ソート演算
UNION 連結・集合演算
INSERT、UPDATE、DELETE 各種コンテナ変更操作
DDL DDL・DCL文相当
RESULT 結果の保持

　

プランは、運用ツールgs_shで取得することができます。 　

例) クエリ「select * from table1, table2 where table1.value=0 and table1.id=table2.id」のプラン取得

gs[public]> EXPLAIN ANALYZE select * from table1, table2 where table1.value=0 and table1.id=table2.id;
検索を実行しました。 (11 ms)
gs[public]> getplantxt
Id Type       Input Rows Lead time Actual time Node                 And more..
--------------------------------------------------------------------------------------------------------------------
 0 SCAN       -     -            0           0 192.168.15.161:10001 table: {table1} INDEX SCAN
 1   SCAN     0     0            2           2 192.168.15.161:10001 table: {table1, table2} INDEX SCAN JOIN_EQ_HASH
 2     RESULT 1     0            0           0 192.168.15.161:20001

gs_shでサブコマンドgetplanjsonを実行すると、プランをJSON形式で出力することもできます。

[注意]

例) プランのJSON形式の例

{
  "nodeList" : [ {
    "cmdOptionFlag" : 65,
    "id" : 0,
    "indexInfoList" : [ 3, 3 ],
    "inputList" : [ ],
    "outputList" : [ {
      "columnId" : 0,
      "columnType" : "INTEGER",
          :
          :
}