第6回 インデックスに関するまとめ

今回は久々にデータベースネタを書いてみた。閑古鳥が沢山鳴いているような当ブログであるが、そんな中でデータベース関連の記事の参照が多いから書いてみました的な側面が強い。だが、検索条件によっては当ブログがトップで表示されるようなよく分からない状況に焦りもした。流石ははてなと言ったところだろうか。そしてこんな内容でごめんなさい。

さて今回の内容は、以前チューニングに関する勉強会を行ったのだが、その時の資料を元にインデックスに関する事項をまとめ直したものである。焼き直しとも言うが、まあいい復習になった。

インデックスを設定する目的

データベースのチューニングの話で必ず出るのはインデックスの設定である。だが、そもそも何故インデックスを設定しないといけないのだろう。主な理由は以下の通りだ。

• 全体集合から特定の部分のみを選別してアクセスするため
• オプティマイザが実行計画を作成する時、インデックスを見て最適なアクセス経路を探すため
• 検索処理の終了の目処を立てるため

最初のものは、自明なので割愛。二番目の実行計画とは、データベースが処理を行うとき、データをどう探して取り出すのかというものだ。オプティマイザ（データベースの主要機能の一つ）が作成した実行計画を元にデータベースは検索処理などを行う。適切なインデックスが設定されていれば、実行計画でそれの使用が採用されるため、検索処理は早くなる。インデックスがないと、テーブル全体を取って探すという方法を取ることになる。

最後の意味はどういうことかといういと、データベースは答えが見つからない限りは応答を返せないからだ。データとして無い場合は、無いという結論をきちんと導こうとする。インデックスが設定されていない場合、条件に一致するデータがないかどうかは、そのテーブルを全件調べ終えるまでは結論は出せない。同様に、途中で結果が見つかったとしても、最後まで見ないとすべてはわからない。人間のように途中でサボれないのだ。

インデックスが設定されている場合はどうなるか。そのインデックスに値がない場合は、そこで終了だ。インデックスはソートされているため、テーブルのように最後まで見る必要もない。値が見つかった場合も同様だ。その時点で辞めれる。インデックスが設定されていると、途中で処理を辞めていいとデータベースが判断できるのだ。まあ、これは自分でも割りと普段からやっていることでもある。技術本とか読むときは特に。

インデックスアンチパターン

ここではインデックスを設定しても効果がない事例について軽く述べておく。

NULL値には効果がない

NULL値は、値が0だということではない。値が不定ということである。つまりは未設定なわけで、そのような値はインデックスにはそもそも記録されないのである。検索条件で特定の値でないことを条件にした場合、例えばa != 5などした時にNULL値が含まれないのもこのためだ。値がないから除外されるのである。NULL値を含めたい場合は、a != 5 OR a IS NULLとやる必要がある。IS NULLを忘れるのはよくありがちなバグだ。

NULL値はインデックスに記録されないため、IS NULLを条件に指定した場合、インデックスは使われず、テーブルの全検索となる。

ところで、逆にIS NOT NULLとやった場合はどうだろうか？　この場合は、インデックスに項目がある＝NULLではないのでインデックスを使った検索になる。

余談ではあるが、結合インデックスの場合は、一部がNULLでもインデックス登録される。また、インデックスの種類としてビットマップインデックスを使った場合も同様にNULL値も記録される。

大量データ取得には効果がない

インデックスは10万件あるテーブルの中から100件程度を取り出すときに効果はあるが、10万件あるテーブルから5万件を取り出すような場合は効果がない。むしろテーブルを全検索した方が早いかもしれない。

これは、インデックスを使用した場合のデータの取り出し方を考えればわかると思う。データアクセスはインデックス検索→テーブルレコード取得となるため、多少のオーバヘッドが元々あるのだ。そのため、取り出し件数が多くなるほど、非効率なアクセスとなってしまう。

何故非効率となるのか。元々インデックスという余計な情報を取得しているのもそうだが、テーブルアクセスがランダムになるからというのもある。インデックスは値でソートされているが、テーブルはそうではない。そのため、インデックス上に記録されている位置が、テーブル上の位置（つまりはディスク上の位置）に沿っているとは限らないのだ。最悪、位置レコード毎に記録されているディスク上のページが異なるかもしれない。そのためアクセスが非効率になってしまう。

逆にテーブル全検索の場合は、テーブルの初めから終わりまでを連続して読みだしていくためアクセス上は効率がよい。

分布が偏っていると効果がない

ある値に対応するレコードが相当数ある（つまりは分布が偏っている）場合も同様に効果がない。理由は大量データ取得の場合とほぼ同じだ。その値だけではレコードが特定出来ないため、他の条件で探そうとするが、そのためにはそのレコード情報をテーブルから取得しないといけない。ランダムアクセスが発生するため効率が悪くなるのだ。

検索条件を複数指定して、インデックスも複数指定しても、採用されるインデックスはその中の一つだけであり（採用されたのがテーブルからレコードを引っ張ってくる条件となる）、他の条件はレコードを取り出し後のチェック条件になるのである（インデックスの設定状況が合えばインデックスジョインという方法が採用される可能性もあるがここでは気にしなくて問題ない）。

とは言え、実際に検索する場合、データベースは空気を読んで（統計情報を元に）インデックスを採用するか、しないかを判断して効率がよい手段を採用してくれる。そのため、実行計画を見て、テーブルアクセスがFULLとなっていた場合でもそれが必ずしも悪手とは限らないことに注意しよう。ただ、データベースが空気を読むためには統計情報を適時更新しておく必要があることも注意しておこう。

インデックス戦略

ここではインデックスを付与する指針について軽く述べておく。

インデックス付与の原則

• インデックス追加コストが低い場合は、原則設定しておく

基本キーには必ずインデックスをもたせるようにする。そうしないとJOIN時に不利になるかもしれない。

• テーブルがマスタ型の場合は積極的に設定する

マスタ型とは、主に参照先の役割をするテーブルのことである。データの増減がないため、追加コストが発生しないので躊躇う理由は特にない。設定するときは、データの識別を目的としたインデックスを設定すること。ただ、マスタ量が大量の場合は、インデックス無でデータを追加した後にインデックスを作成するのが良い。

• テーブルがトランザクション型の場合は、まずはアクセスパターンを収集する

トランザクション型とは、業務の具体的な実行内容を管理するテーブルの事。アクセスパターンをまずは洗い出し、それぞれのアクセスに最適なインデックスの組み合わせを探し出す。業務の内容によっては、初めはインデックスを少なめに設定し、大量のデータが入力された後にインデックスを再生成する方法を取るのもよい。バッチ処理で大量のデータが追加され、多様なアクセス形態が発生するようになる場合はビットマップインデックスの活用も考えること。

• アクセスパターンの調査の手順

1. 繰り返し実行されるアクセス形態を探す
2. 分布度が非常に良好な列を抽出してアクセス種類を調査する
3. 頻繁に広範囲の条件が付与される形態を探す
4. 条件に頻繁に使用される主要な列を抽出し、アクセス種類を調査する
5. 頻繁に結合して使用される列の組み合わせの形態およびソート順序を調査する
6. 逆順でソートして抽出される場合を探す

結合インデックスの原則

原則の前にアンチパターンを軽く述べておく。単項目インデックスとは微妙に異なるので注意が必要だ。

• 最初のカラムが条件句にない
• 列の中の一部のみが条件を受ける
• ＝以外の演算子が数多く存在

例えば三つのカラムに結合インデックスを設定したとして、条件では一つ目と三つ目だという場合は効率が悪くなる。２つ目のカラムは全部検査対象となるためだ。また、範囲指定の場合も具合が悪い。最初のカラムがそれだとなおさらだ。=以外だとチェック範囲が大きくなり効果が薄くなるのである。最初のカラムが条件にない場合は、そもそもその結合インデックスが採用されることはない。

さて、それでは原則である。最初のカラムの採用ポイントは、条件として＝を使っているか？ということである。単項目インデックスのように分布度が良いというのはポイントにならないので注意しよう。何故分布度がポイントにならないのか？　分布度が良いカラムがあるのであれば、それに対して単項目インデックスを張れば十分だからだ。分布度が良いのであれば、最初のカラムで取得対象のレコードは絞り込めているはずなので、第二、第三カラムはインデックスの付与がそもそも不要なのである。

結合インデックスの列順の決定基準は以下の通りだ。

• 結合インデックスの列順の決定基準

1. 常に使用するか？
• 多く使用される属性か？
• 数多く使用される属性をピックアップする　→　一位と二位
2. 常に「＝」で使用するか？
• BETWEENとかの場合、INに変えれないか？も考慮に入れる
3. どれがより良い分布度をもつのか？
• これから発生するであろうアクセスも考慮に入れる
• ＝以外の演算子を使うことになりそうかも考慮に入れる
4. 頻繁に発生するソート順は何か？
• インデックスが使えればソート不要のためレスポンス時間の短縮につながる
5. 付加的に追加する列はどのようなものとなるか？
• ランダムアクセスを減らしたり、インデックスのみにアクセスする等補助的に追加出来る列を探しておくと便利

参考文献

データベースパフォーマンスアップの教科書 基本原理編