技術関連の覚書

案件でやったり自宅で試したことの覚書、自宅Redmineから移行

SQLのパフォーマンスを最適化するために

ひとまず棚卸が終わったので、今回からちゃんと書いていきます。

これまで書いてあった物を見てDBのメモはあったけどSQLについて書いて無かったので今回はSQLについて書いていきます(ちょうど今やってることだし)。

SQLと言うのはいろいろな現場に行くごとに用語が違っていたりしてどれが標準的な言葉かわからない事もあるので、 まず言葉の定義からしておきます。

用語 意味 他の言い方
副問合せ SQLのFROM句やWHERE句で別のSQLを定義すること
副問合せの検索結果をWHERE条件にしたり、テーブルと同様に扱うことができる
サブクエリー
別名 テーブルや項目に対してつける別の名前
関数を使った場合にもつけることができ、選択式、テーブル名につけることができる
エイリアス
外表 テーブル結合時に基準になる側のテーブル
下記のSQLのTABLE1に該当するが、結合結果も次に結合がある場合はそれが外表として扱われるためTABLE1とTABLE2の結合結果もTABLE3に対する外表となる
外部表、駆動表
内表 テーブル結合時に外表から結合される側のテーブル
下のSQLではTABLE1に対するTABLE2、TABLE1とTABLE2の結合表に対するTABLE3
アクセスパス SQL実行時にどのような手順で実行していくかを示したもの 実行計画
SELECT
  COUNT(ITEM1) CNT, ITEM1 I1, ITEM2 I2
FROM
  TABLE1 T1
INNER JOIN TABLE2 T2 ON
  T1.ITEM1 = T2.ITEM1
INNER JOIN TABLE3 T3 ON
  T2.ITEM3 = T3.ITEM3
WHERE
  T1.ITEM2 = ? AND
  T2.ITEM1 IN (
    SELECT
      ITEM1
    FROM
      TABLE1
   ) SUB_T1
ORDER BY
  ITEM1
GROUP BY
  ITEM1, ITEM2
;

インデックスの使用

SQLのパフォーマンスをあげるための一つとしてインデックスを使用する方法があります。 インデックスとは、本の索引と同じく、頭文字などを見出しにしてそれがどこにあるかを記述しているところです。

f:id:boctok-ctpoba:20170331235955p:plain

インデックスを使うと、検索する量を減らせるため速度をあげることができます。

テーブルの結合順

テーブルの結合順や結合項目によってパフォーマンスが変わります。 結合の方法としては以下の物があります

  • NESTED LOOPS JOIN

  • SORT MERGE JOIN

  • HASH JOIN

  • CROSS JOIN

NESTED LOOPS JOIN

外表からインデックスを利用して内表を検索する結合方法なので、結合条件にインデックスが使われていることが必要 外表の件数を少なくすることで検索回数を減らすことができるので、件数の少ないテーブル、検索条件によって件数が絞り込まれるテーブルを外表にする

計算量:O(N \log{M}) *1

SORT MERGE JOIN

外表、内表をソートして上から順に検索して結合していく方法 外表が大量データの場合はNESTED MERGE JOINよりも有利な場合がある

計算量:O(N+M)

HASH JOIN

データベースで、2つの表の小さい方を使用して、メモリー内にハッシュ表を作成する結合 大きい方の表をスキャンしてハッシュ表を調べ、小さい方の表の対応する行のアドレスを見つけます メモリー内にハッシュ表を作るため大量のメモリが必要になるが、メモリが多い場合は有利な検索方法

計算量:O(N h_c + M h_m + J) *2

CROSS JOIN

外表から1レコードずつ内表を全件検索する方法 外表件数×内表件数だけの検索回数があるためパフォーマンスはもっとも悪い

計算量:O(NM)

計算量についてはここを参照しました www.madeiradata.com

今日はここまで 意外に時間がかかりました(数式の書き方調べたり色々と)

さーて、明日のこのブログは

インデックスを使えなくする検索条件

インデックスを使う優先順位

副問合せの検索方法

の3本です 来週もまた見てくださいね

と言いながら、明日は出かけるのでかけるかどうかわかりません

(某国民的アニメとは関係ありません)

*1:Oは計算量を表す記号、括弧内に入る数に応じて変化する量。N,Mは外表、内表のそれぞれの件数

*2:件数はN<M、hc:ハッシュテーブルを作る計算量、hm:ハッシュマッチングの計算量、J:Jorker=ハッシュ関数の動的な計算量