SQLパフォーマンス詳解

https://use-the-index-luke.com/ja

前書き

SQLでは「何が欲しいか」だけ書き，「どう機能するか」を見ないで済むので嬉しい．

パフォーマンスが問題になる場合にむしろ逆効果になっている．

インデックスを正しく貼ることがとにかく大事．

Bツリーインデックスのみを対象とする．

インデックスの内部構造

双方向連結リストと検索木で説明がつく．

双方向連結リストがデータの順序を確保してくれる．

実際にデータを順序よく並べた状態にするのは困難．

各ノードはデータベースブロックという最小の格納単位に保存される．

一般的には数KB

ノードの各スペースにインデックスを詰めるだけ詰め込む．

したがって，ノード内の順序と，ノードの順序という2つのレベルで順序が管理されている．

インデックスの順序はB木で管理されている．

インデックスの数に対して木の深さが対数で増えていく

インデックスによる検索

「木の走査」「双方向連結リストを追う」「テーブルからデータを取り出す」の3ステップで行われる．

インデックスによる恩恵があるのは「木の走査」の部分のみ．

INDEX UNIQUE SCANでは「木の走査」のみを行う．

UNIQUE制約がある場合にこの方法で走査できる．

MySQLではtype constと表示される．

INDEX RANGE SCANでは「木の走査」に加えて「双方向連結リストを辿る」検索を行う．

UNIQUE制約がなく値がUNIQUEではない可能性がある場合にこれを行う必要がある．

MySQLではtype refと表示される．

TABLE ACCESS BY INDEX ROWIDでは加えてテーブルの読み込みも行う．

TABLE SCAN自体はそれなりに早いので，大きめのINDEX RANGE SCANよりより大きなTABLE SCANの方が早いこともある．

フルテーブルスキャンは読み出すレコード数が十分大きい場合は一番早い．

必ずしもインデックスを効かせることが最適ではない．

複合インデックス

テーブルに対するインデックスが多いほど，INSERTやDELETEのコストが上がる．

INSERTのたびにインデックスを更新する必要があるため．

関数に対するインデックス

Oracleなどでは，LAST_NAMEに対するインデックスがあっても，UPPER(LAST_NAME)でWHEREを入れてもインデックスが効かない．

UPPER(LAST_NAME)に対するインデックスが必要．

これを関数インデックスと呼ぶ．

ユーザ定義の関数の場合，関数が確定した値を返す場合にのみインデックスを作成できる．

作成時点でのインデックスが不変ため，時間や乱数に依存する関数では正常に動作しなくなるため．

MySQLには関数インデックスがない．

パラメータ化クエリ

バインドパラメータ

値をSQL文に直接渡す代わりに?や@name，:nameなどのプレースホルダを置き，別のAPIから値を渡す仕組み．

SQLインジェクションを防ぐセキュリティでのメリットがある．

パフォーマンス面では実行計画を具体的に算出できないという点でも一長一短だが，一長一短ならバインドパラメータを使っていくべき．

インデックスの結合

多くの場合に複合インデックスが好まれる．

DATE型

TRUNCを使ったWHEREにインデックスを効かせるとき関数インデックスが必要だが，TRUNCを使わない場合のために関数でないインデックスも必要になってしまう．

QUARTER_BEGIN()とQUAETER_END()でWHEREで範囲指定すると対応できる．

列でなく検索キーワードの方に関数を施していく．

数値文字列

'42'などを42と比較する場合，列に対して暗黙的な型変換が発生し，関数インデックスが必要になってしまう．

検索キーワードの方に関数をあらかじめ施していく．

MySQLはDATETIMEを使おう．

日付と時刻を別々に管理するとかすると，インデックスが難しくなる．

使わなそうなインデックスは無効化する手もある．

code:disable_index.sql

SELECT last_name, first_name, employee_id

FROM employees

WHERE subsidiary_id = ?

AND last_name || '' LIKE ?

データ量がパフォーマンスに与える影響

実行計画だけで原因がわからないこともある．

アクセス数がパフォーマンスに与える影響

N+1問題

ORMを使う場合はロギングを有効にしよう．

結合はデータベース上で行う．

データベースとのやりとりの数が少ないほどえらい．

ORMのeager fetchの仕組みをうまく使おう．

ハッシュ結合

結合が発生するクエリに対して，片方のテーブルのクエリの候補をハッシュテーブルに読み込むことで，もう1つのクエリ候補と高速に突き合わせられるようにする仕組み．

メモリ上に乗せるハッシュテーブルのサイズを小さくすることが重要．

必要な列だけを取り出すようにする．

ソートマージ

右外部結合と左外部結合とを同時に行う完全外部結合を実行できる外部結合

ソート済みのテーブル同士の結合に非常に優れる．

多くの場合にハッシュ結合の方が優れる．

データクラスタ

列志向データベースというのもある．

データクラスタの利用がインデックスの強力な点の2つ目．

検索木の走査がインデックスの強力な点の1つ目．

読み込む行が1つのテーブルブロックの保存されていれば読み込みは一挙にできるが，複数のテーブルブロックにまたがっていると，読み込みのコストが大きくなる．

インデックスの並びとテーブルの並びの相関の指標をクラスタ化係数と呼ぶ．

インデックスに追加されている列は自動的に理想的な順序で保存されるようになる．

インデックスのみのスキャン

カバリングインデックスと呼ぶ

WHEREを追加することで，カバリングインデックスのみで済んでいたクエリが，テーブルスキャンする必要が出てくるケースもある．

カバリングインデックスの優位性は，クラスタ化係数が小さい列には効果が小さい．

CREATED_DATEなどはクラスタ化係数がよくなる．

クラスタ化インデックス

索引構成表ともいう．

ヒープ(データ構造)構造を持たないB木インデックス．

ヒープ(データ構造)分の容量の節約

インデックスのみのスキャンになる

一時的なテーブルの保存先としてインデックスが利用できる．

セカンダリインデックスはROWIDへの参照を持たず，クラスタ化インデックスのキーの値だけを持つ．

クラスタリングキーと呼ぶ．

これを索引構成表の主キーとする．

索引構成表はインデックス上に作られるため，位置が固定されておらず，ROWIDへの参照を持つことができない．

セカンダリインデックスを用いた索引構成表へのアクセスは非効率．

ソート

インデックスによるアクセスは，ORDER BYが自然に行えるだけでなく，パイプライン化できるという点が大きい．

これがインデックスの3つ目の強さ

インデックスでソート済みの範囲に限っていれば，部分的にソートされているだけでも恩恵が受けられる．

ASCやDESCに関して

インデックスは逆順からでも読める．

code:asc_desc.sql

CREATE INDEX sales_dt_pr

ON sales (sale_date ASC, product_id DESC)

インデックスを使ったGROUP BY

SQLは2つの種類のGROUP BYアルゴリズムを持つ．

ハッシュを用いたアルゴリズム

レコードを一時的なハッシュテーブルでまとめ上げてから返す．

ソート・グループアルゴリズム

グループキーでソートすることでグループをそれぞれ処理できるようにする．

パイプライン化によって順次出力できることが大切．

パイプライン化されたORDER BY

ORDER BYをパイプライン化して部分結果だけを取り出すことができる．

全ての行を取り出そうとしないので，パフォーマンスが最高．

ページネーションとかで必要になってくる．

LIMITを書くとオプティマイザがパイプライン化されたORDER BYを使いたいという気持ちになる．

ページネーション

オフセット法

OFFSETが大きくなるほどパフォーマンスが低下する．

OFFSET分行を数える必要があるため．

途中で更新があると参照がずれる．

シーク法

行数の代わりに現在のページの最後の要素のソートキーで，「それ以降」を検索する．

この時，ソートは一意に定まる（キーの重複がない）状態である必要があるが，そうでない場合，他の列を利用して複合インデックスを作成し，複数の列でWHEREする．

行値式を利用する．

FETCH FIRSTで行数を絞る．

窓関数

Oracleなどはページネーションに窓関数が便利．

INSERT

DELETE

UPDATE

DELETEとINSERTを合わせたような挙動をする．

ただし，更新される列のインデックスのみがパフォーマンスに影響を与える．

必要な列だけ更新することが大切だが，ORMが全部の列を更新するようなクエリを発行してしまう場合があるので気をつける．

#SQLチューニング