はじめに
SQL美味しくなかった人が、改めて少々かじってみたチラシの裏です。
2023/11/11
読みかけアウトプットです。読み進めたら更新します。
[読んでる本]
SQLパフォーマンス詳解
インデックスを理解しよう
パフォーマンスを意識するためには、まずインデックスを理解するのが一番なようです。
2章は全力かけて読めって書いてありましたが、インデックス適当に作ってました派の人は、1章もけっこう真面目に読んだ方が良いです。
インデックスの目的は、インデックスを張ったデータに対して 「順序をつけてアクセスできるようにすること」 にあります。
インデックスを作ると起きること
-
指定インデックス順のインデックスリーフノードができる。
対象テーブルをインデックスの順にソートしたリストが出来上がります。
バラバラ順のデータから検索するわけではなく、インデックス順になったものから検索するため、インデックスによるselectの方が早くなるのです。 -
指定インデックス順のリストは、ストレージ領域を使う。
ソートしたリストがいっぱいあれば便利じゃないか、と思いますが、インデックスをたくさん張ると、それだけ領域を使ってしまいます。なので、作りすぎ注意です。 -
指定インデックス順のリストは、insert、update、deleteの書き込みのたびにメンテナンスされる。
データが編集されると、そのたびにソート順に変更がないかメンテナンスが必要になります。
そのため、データ編集(ins/upd/del)にはオーバーヘッドが発生してしまいます。
なので、やっぱり作りすぎ注意なのです。
インデックス順のリスト(インデックスリーフノード)
インデックス順のリストは、↓こんな感じで用意されます。
ルートノード、ブランチノードでは、ブロックの最大値を保持しています。
リーフノードが末端、ルートがトップ、ブランチは間の分です。図では深さ3にしていますが、これはデータに応じて変わります。
インデックステーブルの検索
まず、データの走査は愚直に上から見ていくということを念頭に置きます。
インデックスによる検索では、ツリーを利用して、二分探索的なことを繰り返して、目的のデータを取得します。
インデックスでデータが一意に定まっている場合、最後のリーフノードの走査はする必要がなく、ツリーの走査だけで良くなります。
逆に、インデックスでデータが一意に定まらないと、せっかくインデックスを張ってもselectが遅くなってしまう場合があります。
例えば、図では末端に15のデータが2個あります。これはインデックスでデータが一意に定まっていない状態です。一意に定まらないので、最初の15がヒットしたあと、次のリーフノードも走査対象にしなくてはなり、遅くなる原因になるとか。
プライマリキー
プライマリキーは勝手にインデックスが作られます。
なので、一意性が保証されていれば、検索は早くなるそうです。
複合インデックス
Index unique scanが使えれば、特に問題ないようです。
つまり、検索には、指定したキー列を全て利用することが条件です。
そうしないと一意に定まらないので全行検索しにいくことになり、ツリーのメリットが失われます。
検索に使われない列については、後ろの方に定義しておいてあげるのも有効とのことです。
※複合インデックスを作る時に指定した列順＝ソート順です。
インデックスは
- 等価性を確認する
- 範囲を調べる
の順で使いましょう。
この順は「大ざっぱに言えば」とのことですが、見ている限り、実際の条件設定の時に念頭に置く必要がありそうです。
Where句を改善しよう
本気出して読めと書かれていました。がんばるわ。
Where句が悪いと、パフォーマンスが悪くなる。
返ってくる量が多ければ遅くなるじゃないか。
その通り。絞り込むのが大事です。そして、絞り込み方も大事なようです。
アクセス述語とフィルタ述語
検索の話の前に、出てくる用語を整理せんとす。
- アクセス述語
インデックス操作のはじめと終わりを決める条件のこと。
インデックスをスキャンする範囲を定義する。つまり狭いと良いのかも。 - フィルタ述語
リーフノードの走査時に適用される条件。
スキャン範囲を狭めるためには使われない。
実行計画を見るとaccessだとかfilterだとかと出てきますね。
つまりそういう意味か(｀・ω・´)
等号(=)による検索
プライマリインデックスでの検索の場合、一意であることが保証されるため、素早く検索が可能になります。
(実行計画的には、Index unique scanが使われます。)
これは1行しか結果がない＝1アクセスしかしない＝遅くなる要因がない状態になっているため、素早く検索ができるわけです。
範囲検索その1 <>,between
基本の考え方は統合と同じで、インデックスがどういう並びになっているかが肝です。
インデックスの選択性は必ずしも重要ではなく、スキャン範囲が狭いことが大事です。
始点と終点がはっきりしている分には、インデックスも難しく考える必要はありません。
birthday >= to_date(? , 'YYYY-MM-DD')
and birthday <= to_date(? , 'YYYY-MM-DD')
普通にbirthdayがインデックスで良さそうです。
では次はどうでしょう。
birthday >= to_date(? , 'YYYY-MM-DD')
and birthday <= to_date(? , 'YYYY-MM-DD')
and last_name = ?
インデックスはbirthdayとlast_nameで良いでしょう。
順番はどうでしょうか。
birthdayの範囲の方がlast_nameの絞り込みより広くなる場合、last_nameを優先したインデックスが良いようです。
まずは等価性の確認を優先し、それから範囲を検索する順ということですね。
範囲検索その2 LIKE
範囲検索という点では、考え方はその1と変わりません。
ただし、LIKEは注意点があります。
インデックスのツリー走査上、LIKEが有効なのは最初のワイルドカードの前までです。残りの文字はスキャンされるインデックスの範囲を狭めてくれない、ただのフィルタ述語になります。
そんなわけで、ワイルドカードを頭に持ってくるのは良くないようです。
後方一致どうするんだろうな。
関数を含んだ場合
例えば
where UPPER(name) = UPPER('deep impact')
という検索条件を指定したい時のインデックスは、どう指定するでしょうか。
インデックスはnameではなく、UPPERT(name)に対して張る必要があります。
これは関数インデックスというそうです。
ただし、関数インデックスが許されるDBとそうでないものがあります。
許されない場合は、テーブルに列にUPPERにした列を作成しておき、それをインデックスに追加するのが良いとか。内部的には関数インデックスにした時と、やっていることはあまり変わらないようです。
※UPPERは任意の関数で。
ユーザー定義関数の時の注意
関数インデックスは、ユーザー定義の関数でも同様に設定できるそうです。
ただし、引数に対して必ず同じ結果が常に返ってくる関数(確定的な関数)であることが条件です。
例えば、誕生日から年齢を割り出す関数を用意します。
この関数は、誕生日が来るごとに結果が変わってしまいます。今日は正しい値がインデックスとして入っていても、誕生日を過ぎたらインデックスには間違った値が入っていることになります。
データ編集以外で定期的にソートをメンテナンスすることはないため、同じ値が返ってくるわけではない関数はインデックスに指定できないということですね。
というわけで、インデックスに指定できるユーザー定義関数はDETERMINEISTIC(Oracle)とか、IMMUTABLE(PostgreSQL)とかがついたものに限ります。
感想
勉強してなさすぎて、適当にインデックス張ってすみませんでしたというお気持ちです。
順番関係あるとは思ってなかったぜ。