Published on

DBパフォーマンス

Authors
  • avatar
    Name
    Kikusan
    Twitter

DBパフォーマンスに関わる事象をまとめる。

実行計画の見方

EXPLAIN [ANALYZE] SELECT AVG(PRICE) FROM STORE, PRODUCT
WHERE PRODUCT.PRICE > 1000
AND STORE.ID = PRODUCT.STORE_ID
GROUP BY STORE.ID
ORDER BY STORE.ID;

GroupAggregate  (cost=48.93..52.13 rows=160 width=36)  Group Key: store.id
 +-Sort  (cost=48.93..49.33 rows=160 width=20)  Sort Key: store.id
    +-Hash Join  (cost=26.65..43.07 rows=160 width=20)  Hash Cond: (product.store_id = store.id)
       +-Seq Scan on product  (cost=0.00..16.00 rows=160 width=20)  Filter: (price > '1000'::numeric)
       +-Hash  (cost=17.40..17.40 rows=740 width=4)
          +-Seq Scan on store  (cost=0.00..17.40 rows=740 width=4)

ANALYZEを指定すると予測値に加えて実測値も表示される。
一番子要素から実行され、最後の親が実行される。兄弟がある場合は上の方が先に実行される。

  • Seq Scan: 基本的なフルもしくは条件付きスキャン。インデックスを使用している場合はIndex Scanになる.
  • Hash Join: テーブル結合
  • Sort: ソート
  • GroupAggregate: グループ演算
  • cost= ... : 初期コスト(1行目を返すまでの準備コスト)...総コスト(処理完了までのコスト). コストはCPU,メモリ,I/Oなどを含むリソース量
  • rows: 返却される検索結果の行数
  • width: 返却される1行当たりの長さ

非正規化

正規化によってテーブル結合が増えるとそれだけパフォーマンスが落ちる。
基本的に正規化と検索SQLのパフォーマンスはトレードオフ。

  • デメリット1: 更新時のSQLの負荷が増える(冗長データが増える)
  • デメリット2: バッチ処理などによってデータを付与することが増えるため、リアルタイム性が失われる。
  • デメリット3: 改修コストが大きい。 原則として非正規化は許さず、最後の手段とする。

保持するデータを増やす

テーブル結合してGROUPBYをして取得するデータがあるなら、
更新時の処理が複雑になる代わりに結果を元々別カラムで持っておくのもあり。
これも基本的には行わない。

インデックス

DBはコストベースで探索方法を自動で決定する。
インデックスは探索時の索引を意味する。

B-tree

基本的にこれ一本でいけるくらいポピュラー。
データ量が増加してもパフォーマンス低下が少なく、CRUDどれもそこそこ早く、ソートも高速化できるオールラウンダー。
2分探索木でデータを保持し、最下層の葉にポインタを持つ。
木にカラムの値の大小を保持しながら持つから、探索が早くなる。
深さを一定(平衡木)に保つのでデータ量が増えても探索が安定する。
CRUDのいずれも計算量はO(logn)。インデックスは更新処理は多少遅くなる。
レコード数が1万程度であれば、あってもなくてもそんなに変わらない。
DBは主キーとユニークキーには自動で内部でB-Treeを作成している

インデックス選択基準: カーディナリティ・検索条件・結合条件・ソート条件 カーディナリティ: データの種類の多さ。性別は少なく、口座番号であれば多い。多いほどインデックスに適している。
ただし値の大小にばらつきがないとあまり意味ない。

SQLで条件に指定する際、カラムに演算した値を条件に指定するとインデックスの意味がなくなるので注意

LIKE検索で前方一致以外を使用するとインデックスが効かなくなる

<>, IS NULL などは大小が関係ないのでインデックスが意味ない場合がある

WHERE句でORを使用するとインデックスが利用されなくなる場合がある

大きなデータのソートを避ける

ソートは専用メモリが用意されているが、メモリに乗らない場合はディスクにデータを一時的に吐き出す。
I/Oがかかるため、なるべく避ける。もしくはソートするカラムにインデックスをはる。

UNIONは内部で重複排除のためのソートを行うので、重複を気にしなくていい場合はUNION ALLを使用する

DISTINCTも重複排除にソートを行うので、EXISTSで代用する

MAX/MINもソートを発生させるが、インデックス列を引数に指定することでインデックスが使用できる

サブクエリの引数にはINよりEXISTSを使う

サブクエリの存在チェックはINよりEXISTSのほうが速い

Discuss on Twitter