SQLパフォーマンス詳解
je6bmq.icon SQLのパフォーマンスに大きな影響を与えるインデックスについて解説した本。
SQL自体は内部的にクエリが「どのように」処理するか隠蔽されている言語であるが(「何を」のみが表現されている)、一方でパフォーマンスを気にする上では開発者は多少「どのように」行われているか知る必要がある。
本書では「開発者が知るべき唯一のことはどのようにインデックスを張るか、ということ」になると述べている。
je6bmq.icon DBのパフォーマンスとして、ストレージの挙動なども影響するが、それはDB管理者の管轄であり、 開発者 の領域は外れている、という解釈。言い換えれば、開発者のタスクはインデックスをどのようにはるか、というところのみ。
基本的に主要なデータベース(Oracle、MySQL、PostgreSQL、SQLServer、etc...)のインデックスにおける普遍的な内容について触れているが、主にOracleを例に述べられている。
目次
インデックスの内部構造
where句
パフォーマンスとスケーラビリティ
結合処理
データのクラスタリング
ソートとグルーピング
部分結果
データの変更
実行計画
以下、je6bmq.iconにとって学びがあった記述
インデックスの内部構造
双方向連結リストとBTree(Balanced Tree)を併用した構造。
インデックスキーで順序付けされた双方向連結リスト
検索のためにBTreeを利用
where句
クエリが遅くなる一番の原因はインデックスとwhere句の使い方による
プライマリキーを使ったインデックスは自動作成される
キーの一意性が保証されて入れば、余計なインデックスノードの走査がなくなるため、クエリが高速になる
例えばテーブル間の循環参照がある場合にバリデーションを遅延させるために意図的に一意でないインデックスをプライマリキーに持つことがある
je6bmq.icon 遅延制約については要調査
複数の列に渡るインデックス(複合インデックス)を用いる場合が厄介
一つの列だけを使って(where句を使った)クエリを実行した場合にフルテーブルスキャンが行われる場合がある
フルテーブルスキャンが悪いというわけではなくて、テーブルの大部分を読みだす場合はその方が速い
インデックスの検索ではあるブロックを読み終えてから次のブロックを読むべきかどうか判断するため、オーバヘッドになりうる
je6bmq.icon 複合インデックスは木のソート基準を順番に与えているものだと考えるとその順序に乗っ取らない検索には役に立たないと考えると自然か
複合インデックスにおける列の順序が重要だが、運用コストを考えると組み合わせを網羅するわけにはいかない
インデックスの管理はもちろん、insert/updateごとにインデックスを更新する必要があるため
ので、あるインデックスを定義するときにそのインデックスを(列の順序も含めて)使えるSQLが可能な限り多くなるように定義する必要がある
関数(e.g. UPPER)をかませた場合にはインデックスの対象になるとは限らない
関数を噛ませることも含めてインデックスを張る必要がある(FBI: Function Based Index)
データ型の変換やフォーマットのためにクエリ中で変換しようとしてインデックスが使えない、みたいなことが起こりうる
SQLServer, MySQLは関数インデックスをサポートしていないが、生成列という機能がある
je6bmq.iconテーブル内のある列に関数をかませた結果を別の(仮想的な)列として付与する?
Oracleでは、関数インデックスに対する統計情報をテーブルレベルでバーチャルカラムとして保持する(10g以降)が、一方で自動更新がかかるわけではないので注意
ユーザ定義関数に対しても関数インデックスを張ることが可能だが、その関数は確定的(deterministic)でなければならない(e.g. その時々に依存したような、返り値が変化しうるものなどではだめ)
PostgreSQLやOracleではDETERMINISTICやIMMUTABLEといった修飾子があるが、検査してくれるわけではないので、確定的であるかどうかはあくまで開発者にゆだねられている
DBの統計情報が正確でなければ、クエリの実行計画も狂うので、注意
矛盾がある計画を提示してきたりしうる
クエリ内のバインドパラメータ利用について
ここでいうパラメータ化はプログラム上のクエリ文字列上で? をプレースホルダとして変数をクエリに使うすることを指す(e.g. Ruby、Java)
パラメータを使わなければ実行計画のキャッシュが効くデータベースも存在するが、速度を求めるうえで想定されるあらゆるパラメータのクエリに対してそれぞれ最適な実行計画を算出して、(DBによってはキャッシュする)という手順がいいかというとそういうわけではないので、バインドパラメータを使うかどうかは程度による
複合インデックスにおいて、「最も選択性の高い列をインデックスの一番左に置けばよい」、という都市伝説があるが、実際はそうとは限らない
インデックスの張り方によって木の葉(双方向連結リスト)をたどる量が異なるので、それを意識する必要がある
Oracleの実行計画におけるaccess述語が走査範囲を示している
access述語とfilter述語という概念があり、filter述語は走査範囲には影響しておらず、真にフィルタのための述語でしかない
LIKEフィルタでのインデックスが効くのは、最初のワイルドカード登場時まで
パフォーマンスとスケーラビリティ
クエリの応答時間が遅い、というのを例えば開発環境だから(本番環境より劣るから)と片づけてはいけない
むしろ本番環境の方が構成が複雑がゆえに遅くなったりする
Oracleでインデックスを張るとき、あるレコードのインデックスに使うキーの値が全てNULLだとインデックスに乗らない ことに注意