DBのオプティマイザまとめ

#Q:オプティマイザとは？
SQLを元に具体的なデータの取得処理を生成する機能。各DBによって独自のオプティマイザが搭載されている。
(だた、DBオプティマイザの違いまで意識することは少ない。)
オプティマイザはその名の通り『最適化』を目的とし、現在のDBに対しSQLから最も最適なデータ取得処理を生成する。

#Q:SQLがデータ取得処理じゃないの？
SQLはどのテーブルからどのようにデータを取得するかを記述したものでDBからどのようにデータを取得するかは書いていない。

/* 社員コードは主キー */
SELECT * FROM 社員マスタ WHERE 社員コード = 'A1000'

このSQLの場合、下記のように様々な取得処理が考えられる。このときどのような取得処理を行うかはオプティマイザが決めて実行する。
１．上から順に1件1件見て行って社員コードが'A1000'のレコードを取得
２．下から順に1件1件見て行って社員コードが'A1000'のレコードを取得
３．社員マスタの社員コードインデックスに対し二分探索で'A1000'のレコードを探して取得

#Q:なんでオプティマイザがデータ取得処理を決めるの？
現在のDBの状況から最も動作速度が早くなる処理を毎回決定するため。
例えば上記の社員マスタにレコードが5件しかなかった場合、上記処理なら１．あるいは２．が早いと思われる。３．はインデックスを使うがレコード件数が少なすぎるため殆ど意味がなくアクセス処理の分遅くなる。

このようにテーブルの状態によって最適な取得処理が変わる。DBからのデータ取得処理は非常に重いため最適な処理を選ばないと致命的な性能劣化も起こりうる。
そのための最適化処理としてDBの状態から動的にデータ取得処理を生成するようになっている。

#Q:自分でデータ取得処理を決めることはできないの？
オプティマイザにどのようにデータ取得処理を生成するかを誘導することで可能。ただしデータ取得処理の生成自体はオプティマイザが行う。
オプティマイザは
・SQL
・統計情報
・ヒント句
上記をベースにデータ取得処理を決定する。そのため上記のいずれかの要素でデータ取得処理を指定すればデータ取得処理を指定できる。
ただし基本的に最も早いデータ取得処理を実行するため、自分でデータ取得処理を指定する必要はあまりない。
※SQLはそれ自体はデータ取得処理ではないが、書き方によってオプティマイザが生成するデータ取得処理に影響を与える。

#Q:オプティマイザがどのようなデータ取得処理を行うのか確認できる？

実行計画において確認ができる。ただし実行計画はSQLと違い統一規格はないため使用しているDBによって出力のされ方が全然違う。
ちゃんと読み解く場合は各DBごとの実行計画の読み方を勉強しないと読めない。
(SQLの実行が遅いのを改善したいのであればテーブルフルスキャンをしている所を探しその部分のSQLを書き換えるだけで大体改善する。)

#Q:とりあえずオプティマイザが最適なデータ取得処理を作るから任せとけばいいのね？
間違いではないが・・・実際はSQLの問題で最適なデータ取得処理になっていないことが多い。そのためSQLの作成時は実行計画をセットで確認するのが基本となる。

/* 
	取引テーブルのキー(普通こんなキーにはしないけど)
		取引先コード
		取引日
*/
/* 特定の取引先の最終取引の内容を取得する */
SELECT
	取引先名
	最終取引日
	取引内容
FROM 取引テーブル
INNER JOIN 
(
	SELECT 
		取引先コード
		MAX(取引日) AS 最終取引日
	FROM 取引テーブル
	GROUP BY 取引先コード
) 取引テーブル_最終取引日
ON 取引テーブル.取引先コード = 取引テーブル_最終取引日.取引先コード
AND 取引テーブル.取引日 = 取引テーブル_最終取引日.最終取引日
WHERE 取引テーブル.取引先コード = 'A1000'

上記SQLは指定した取引先の最終取引内容を取得するSQLであるが、INNER JOINのサブクエリで『全ての取引先の最終取引日』を取得している。
実行計画を確認すれば最終取引日の取得で取引テーブルにテーブルフルスキャンが掛かっている事が確認できるはず。
本来は指定した取引先の最終取引日のみでよい為、ここでSQLの間違いに気が付ける。

/* 例なので修正方法は適当 */
SELECT
	取引先名
	最終取引日
	取引内容
FROM 取引テーブル
INNER JOIN 
(
	SELECT 
		取引先コード
		MAX(取引日) AS 最終取引日
	FROM 取引テーブル
	WHERE 取引テーブル.取引先コード = 'A1000'
	GROUP BY 取引先コード
) 取引テーブル_最終取引日
ON 取引テーブル.取引先コード = 取引テーブル_最終取引日.取引先コード
AND 取引テーブル.取引日 = 取引テーブル_最終取引日.最終取引日
WHERE 取引テーブル.取引先コード = 'A1000'

これなら取引テーブルの取引先コードのインデックスを使用するためテーブルフルスキャンは掛からない。
オプティマイザは『SQLを元にデータ取得処理』を実行するので間違ったSQLの改善はできない。そのためSQLについては開発者が責任をもって作成する必要がある。

※最近のオプティマイザは賢いので、クソいSQLでも一番早いデータ取得処理を生成してくれる場合も多くなった。ただ複雑なSQLだとやっぱり遅いことも多いのでSQLを使うなら正しいSQLを作る知識は必要。

#Q:SQLは正しいのにテーブルフルスキャンされるのはなんでだ？
(本当にSQL正しい？)統計情報が古い可能性がある。
オプティマイザはデータ取得処理を作るとき、DBの状態がどうなっているかを統計情報から確認する。
例えば統計情報嬢はレコード数が数件だが実際のレコード数が1万件などの場合、インデックスにアクセスする時間を無駄と考えテーブルフルスキャンをオプティマイザが選ぶことがあり得る。
毎回実際のDB状態を確認しないのは状態確認が重い処理のため(データ取得処理自体より遅い)

回避策として、統計情報を更新するかヒント句を用いて使用するインデックスを強制的に指定する方法がある。
理想は統計情報更新が望ましいが統計情報更新は重い処理のため出来ない場合もあり一概には言えない。