【データベース】インデックスの有効な場面・使用できない場面

0.はじめに

以下の内容はミックさんが書かれた「SQL実践入門」を参考にして一部をまとめたものです．より詳しい内容が気になる方は参考文献記載の本を読まれることをおススメします．

1.インデックスとは

データベースにおけるインデックスとはデータ検索を高速化するための仕組みのことを指します．RDBにおけるチューニングにおいてインデックスは最も採用される方法です．
アプリケーションの変更が不要で純粋にデータベース側のみで性能改善できるという利便性の高さと，その効果の高さから，ほぼすべてのシステムがチューニング手段として何らかの形でインデックスを利用します．

2.インデックスを有効活用する際の注意

2.1 「カーディナリティ」，「選択率」，「クラスタリングファクタ」とは

インデックスはテーブルの特定の列集合に対して作りますが，このときどのような列に対してインデックスを作成するべきなのかの基準となるのが，列のカーディナリティと選択率，クラスタリングファクタです．

「カーディナリティ」とは，値のばらつき具合を示す概念を指します．

カーディナリティが最も高い場合は，すべての行について値が異なる一意のキーの列で，最も低い場合は，値が1種類しか存在しない列です．インデックスを複数の列に割り当てる場合も考え方は同様です．

上記画像でカーディナリティが低い場合は，「りんご」「みかん」「ぶどう」の3種類ですが，カーディナリティが高い場合はすべて異なる果物です．

「選択率」とは，特定の列の値を指定したときに行をテーブル全体の母集団からどの程度絞り込めるかを示す概念を指します．

例えば10000件のレコードを持つテーブルに対して，一意キーpkeyに対してSELECT文のWHERE句で「pkey=1」と等号で指定すれば必ず1件に絞り込めるため，この条件の選択率は1/10000つまり0.01%です．

「クラスタリングファクタ」とは，ストレージ上で同じ値がどの程度物理的に固まって存在しているかを示す指標を指します．

クラスタリングファクタが高いほど分散して配置されており，低いほど固まってOSブロック上に格納されていることを示します．インデックスでアクセスする場合は，特定の値だけにアクセスすることが多いため，一般にクラスタリングファクタが低いほどアクセスするOSブロックの数が減り（データ量が小さくなり），好ましいとされています．
例えば図1の場合，3の値をすべて引き出す際，上のクラスタリングファクタが低い場合はOSブロック1回だけ読み込めばよいですが，下のクラスタリングファクタが高い場合はOSブロックを3回読み込む必要があります．

2.2 インデックスの利用が有効かの判断基準

インデックスを作成する列集合の条件は以下の2つの指標から判断します．
①カーディナリティが高いこと．
②選択率が低いこと．
まず①については値がよくばらついていることが良いインデックス候補列の条件です．そしてもう1つの基準が，選択率が低いこと，すなわち少ない行に絞り込めることです．具体的な閾値はDBMSやストレージ性能などの条件によって異なるのですが，最近のDBMSではだいたい5～10%前後というのが目安です．つまり，5%未満に絞り込める条件ならば，その列集合に対してはインデックスを作る価値があるかもしれない，ということになります．選択率が高いと，テーブルスキャンのほうが速い可能性が高くなります．

3.インデックスによる性能向上が難しいケース

扱うデータ量の規模が大きくなればなるほど，データベースのパフォーマンス確保は難しくなります．したがって，大規模なデータベースであるほど，インデックス設計も重要となってきます．しかし，1つ勘違いしてほしくないのは，インデックス設計というのは，テーブル定義とSQLだけを眺めれば完結させられるタスクではないということです．ただし，ここではインデックス設計に焦点を当てて考慮した際に，インデックスによる性能向上が難しい場合，つまりインデックスデータを絞り込める条件が当該のSQLに存在しなかった場合が具体的にどのような状況なのを整理します．

3.1 絞り込み条件が存在しない

SELECT order_id, receive_date
 FROM Orders;

注文テーブルからデータを全件取得するという，シンプルなSELECT文です．このクエリのスキャン動作は実行計画を確認するまでもなくフルスキャンとなります．レコードを絞り込めるようなWHERE句がないため，インデックスを作成すべき列も存在しません．このようなクエリは何らかの形のバッチ処理に含まれていると考えられます．

3.2 ほとんどのレコードを絞り込めない条件２つ

3.2.1 WHERE句の絞り込み条件が固定値の場合

SELECT order_id, receive_date
 FROM Orders
WHERE process_flg = '5';

現状process_flgの分布は次のようになっていると仮定します．
・1（仮受付）　　： 200万件
・2（受付済み）　： 500万件
・3（在庫確認中）： 500万件
・4（発送準備中）： 500万件
・5（発送済み）　： 8,300万件
WHERE句には「process_flg = '5'」という検索条件は存在していますが，この条件ではテーブルの半分以上のデータがヒットしてしまいます．選択率が83%と極めて高いケースです．この状態でprocess_flg列にインデックスを作成しそれが利用されてもフルスキャンよりも遅くなる可能性が高くなるでしょう．

3.2.2 WHERE句の絞り込み条件がバインド変数の場合

SELECT order_id
　FROM Orders
WHERE receive_date BETWEEN :start_date AND :end_date;

:start_dateと:end_dateは外部から日付をパラメータとして受け付けます．
例えば，ユーザが:start_dateと:end_dateにともに「2013-12-01」と入力したならば，ある特定の1日に受け付けた注文データを選択する意味となり，このテーブルが何年間のデータを保存しているかにもよりますが，かなり小さい選択率が期待できるでしょう．
しかし，ユーザが:start_dateに「2013-01-01」，:end_dateに「2013-12-31」と入力した場合は検索範囲が1年に広がります．これは業務集中などのばらつきがないと仮定した場合，単純計算で先ほどの1日を指定したときの365倍のレコードがヒットすることになります．

このように，検索条件がバインド変数によってパラメータ化されているSQL文においてはその時々の入力値によって選択率が良い方や悪い方にも転びます．

3.3 インデックスが使用できない検索条件５つ

インデックスが使用できない条件としては以下の５つ存在します．

項番	使用できない条件
1	中間一致，後方一致のLIKE述語
2	索引列で演算を行っている
3	IS NULL述語を使用している
4	索引列に対して関数を使用している
5	否定形を使用している

それぞれについて詳しく述べていきます．

3.3.1 中間一致，後方一致のLIKE述語

SELECT order_id
 FROM Orders
WHERE shop_name LIKE '%佐世保%';

例えば「佐世保北店」や「佐世保中央店」などを結果に含むような条件です．この条件でヒットするレコード数を5,000件と仮定します．するとこの条件の選択率は全データが100,000,000件なので0.005%です．5%の閾値を大きく下回っており，絞り込みは十分にきいています．
しかし，実際の実行計画を確認するとインデックスは使用されずフルスキャンが行われます．LIKE述語を使用する場合，インデックスが使用できるのは前方一致検索（'佐世保%'）のみです．

3.3.2 索引列で演算を行っている

SELECT *
 FROM SomeTable
WHERE col_l * 1.1 > 100;

索引列で演算を行っているとインデックスは利用できません．ただし，検索条件の右側で式を用いればインデックスが使用されます．

WHERE col_l > 100/1.1

上記のWHERE句を使用すれば問題ないです．

3.3.3 IS NULL述語を使っている

SELECT *
 FROM SomeTable
WHERE col_l IS NULL;

IS NULL述語を使用している場合も，インデックスは使用できません．NULLに対する検索条件でインデックスが使用されないのは通常，索引データの中にNULLは存在しないからです．ただし，DB2のようにインデックスにNULLを保持するDBMSもありますが，一般的ではありません．

3.3.4 索引列に対して関数を使用している

SELECT *
 FROM SomeTable
WHERE LENGTH(col_l) = 10;

索引列に関数を適用するとインデックスが使用されない理由は，索引列で演算を行っている場合と同じです．インデックスの中に存在する値はあくまで「col_l」の値であって，「LENGTH（col_l）」の値ではないからです．関数索引によって対応する方法もありますが，無駄な計算コストが発生するので基本は使用しない方がよいとのことです．

3.3.5 否定形を用いている

SELECT *
 FROM SomeTable
WHERE col_l <> 100;

否定形はインデックスを使用できません．

4.インデックスが使用できない場合の対象方法

インデックスが使用できない，あるいは使用すると逆に遅くなってしまうSQL文のパフォーマンスはどのようにチューニングすればよいのでしょうか．
方法は大きく以下の２つです．
①アプリケーション設計で対処するという王道
②インデックスにこだわる飛び道具
それぞれについて詳しく述べていきます．

4.1 アプリケーション設計での対処

アプリケーション設計での対処では，外部設計による対処や，データマートによる対処の２つについて言及します．

4.1.1 外部設計による対処

一番簡単な解決策は「3.インデックスによる性能向上が難しいケース」のようなクエリが実行されないよう，アプリケーション側で制御することです．以下の画面のようなWeb画面があったとします．

この画面では，ユーザ側が自由に入力できる項目がおおいため，選択率の高い検索条件を許容することに繋がります．そこで以下２つの具体的な対策をとるとぐっと選択率を下げることが可能です．
①「店舗ID」で検索をする際は必ず「受付日」も入力しなければ検索ボタンを押すことが出来ない．
この必須入力制御によってOrderテーブルに対する絞り込みがかなり利くようになります．
②「受付日(From)」と「受付日(To)」による期間検索を最大１カ月までとする．
上記のような条件をユーザと合意することが出来れば，期間検索においてもインデックスを有効に利用することのできる可能性が非常に高くなります．また，そうすれば月単位のパーティションをテーブルに設定するという選択肢も考えられるようになります．
アプリケーションがどのようなクエリを組み立てて，どのような検索条件の組み合わせがあり得るかは，アプリケーションの機能とUIの設計に大きく依存します．したがって，ユーザに対する業務要件を考慮しながら，どのようなユーザインタフェースを用意し，どのような入力制限を設けるかを，ユーザや業務側のエンジニアと一緒になって考える必要があります．

4.1.2 外部設計による対処の注意点

データベースエンジニアの目線として，外部設計による対処を行う際に心がける必要のあることが大きく２つ挙げられます．
１つ目は，システムの性能面とユーザの要望との落としどころを見つけることです．
基本的にユーザ視点に立って考慮すると「必須入力制限がなく自由に書き込み可」の要件の方が好まれることは一目瞭然です．しかし，「本当にシステムを使用する上で重要な条件」と，「性能のために譲歩しても良い条件」のトレードオフを整理し，妥協点を探ることがデータベースエンジニアに求められる仕事です．
２つ目は，業務側のアプリケーションエンジニアと基盤側のデータベースエンジニアで密にコミュニケーションをとることです．
アプリケーション側では現場によって，データベースやハードウェアを完全にブラックボックスとして扱い，ストレージの構成やテーブルの物理配置も知らない場合があります．そのような状況では，外部設計による調整が不調に終わり，選択率の低い必須条件をクエリに組み込むことのできないような事態が発生します．また，外部設計レベルでのパフォーマンスを意識した調整は，プロジェクトの比較的早い段階でユーザと合意を持つ必要がありますが，往々にしてその段階での設計ではパフォーマンスがあまり考慮されることなく，試験フェーズで初めて壊滅的なパフォーマンスであることが発覚します．こういった縦割りの分業体制が用いられるのにもそれなりの理由と合意性が存在するのですが，パフォーマンスについては，システムを俯瞰する人間がいなければ最適化することは難しいです．

4.1.3 データマートによる対処

外部設計に影響を与えない対処の方法としてデータマートによるものがあります．ここでのデータマートとは，特定のクエリで必要とされるデータだけを保持する，相対的に小さいサイズのテーブルのことです．もともとは大規模なデータを扱う必要のあるBI/DWHの分野で使用されていた言葉です．アクセス対象テーブルのサイズを小さくすることでI/O量を減らせることがこの方法の特徴です．

4.1.4　データマートを採用するときの注意点

データマートを採用する際に注意することは４つあります．

項番	注意点	内容
1	データ鮮度	データ同期のタイミングの問題です．データマートはオリジナルテーブルの部分的なコピーであり，同期のサイクルが短いほどデータ鮮度は新しく，オリジナルに近いものとなりますが，その代わり頻繁な更新処理が実行されパフォーマンスが劣化する可能性があります．
2	データマートのサイズ	オリジナルのテーブルとデータ量があまり変わらない場合はデータマートを作成してもあまり速くなりません．
3	データマートの数	データマートを作成しすぎたあまり，どのテーブルがどの処理に結びついているのか理解できず，中には参照されず同期処理だけ行われる*ゾンビマート**が残ることがあります．データマートは機能要件から要請されて作成されたエンティティではないのでER図にも存在せず，管理するのが難しい問題があります．
4	バッチウィンドウ	データマートを作成するには時間がかかるためバッチウィンドウを圧迫することがあります．作成したデータマートは，僅かな差分更新でない限り統計情報も収集する必要があります．このような処理を余裕をもって収めるためのバッチウィンドウとジョブネットの考慮が必要となります．

4.2 インデックスにこだわる飛び道具

4.2.1 インデックスオンリースキャンによる対処

インデックスオンリースキャンは，SQL文に必要な列をインデックスだけで充足できる場合にテーブルのアクセスをスキップすることができる技術のことです．この技術の利点は，データマートと同じく「I/Oを削減できること」です．インデックスは，テーブルの列のサブセットしか保持しないため，そのサイズはテーブルと比較して非常に小さいです．また，データマートでは最大のネックであったデータの同期の問題についてですが，基本的に更新処理が走る際にテーブルだけでなくインデックスも同時に更新されるため，この問題はクリアしています（基本的には更新処理があまり走らないテーブルにインデックスを貼りますが…）．しかも，データマートを作るにはアプリケーションにも改修が必要となりますが，インデックスの場合はそうした改修が不要であることも大きな利点です．

4.2.2 インデックスオンリースキャンの具体例

以下で具体例を見てみましょう．「3.1 絞り込み条件が存在しない」で使用したSQL文を見てましょう．

【再掲】
SELECT order_id, receive_date
 FROM Orders;

このクエリではフルスキャンはフルスキャンでもその対象を「テーブル」から「インデックス」に変更することが出来ます．そのためには，以下のように対象列をカバーするインデックスを作成します．

CREATE INDEX CoveringIndex
 ON Orders(order_id, receive_date);

order_idとreceive_dateの2列はSELECT句に含まれているだけなので，通常はインデックスの列候補にはなりえません．しかし，2列をカバーするインデックスが存在することで，テーブルではなくインデックスのみをスキャン対象とする検索～インデックスオンリースキャン～が可能となるのです．

4.2.3 インデックスオンリースキャンを採用するときの注意点

インデックスオンリースキャンは，データマートを作成しなくてもクエリを高速化できる優秀な技術ですが，注意することが５つあります．

項番	注意点	内容
1	DBMSによっては使用不可	Oracle，DB2，Microsoft SQL Server，PostgreSQL，MySQLいずれも10年以上前になりますが2014年12月時点の最新版であればサポートされています．旧バージョンを使用している際はご注意ください．
2	1つのインデックスに含められる列数の制限	インデックスのサイズは無制限ではなく，含められる列数やサイズに上限が決められています．
3	更新のオーバヘッド増加	インデックスが存在するテーブルに対する更新負荷は上がります．カバレッジインデックスは，列数が多く必然的にサイズの大きなインデックスとなる傾向があるため，テーブル更新時のオーバヘッドも通常のインデックスよりも大きくなることに注意です．
4	定期的なインデックスのリビルド	インデックスにしかアクセスしないため，検索性能がインデックスのサイズに直接影響を受けます．そのため，カバリングインデックスの定期的なサイズのモニタリングとリビルドを運用に組み込む必要があります．
5	SQL文に新たな列が追加されたら使用不可	アプリケーション改修によりクエリに新たな列が追加された場合，原則としてインデックスオンリースキャンは使用不可となります．

5.最後に

インデックスが有効な場面，そうでない場面についてまとめてから，使用できない場合の対策についてまとめました．時間があれば，B-Treeなどのインデックスの種類だけでなく，複合インデックスについてもまとめたいと思います．

参考文献

・「SQL実践入門 高速でわかりやすいクエリの書き方」著者 ミック
https://gihyo.jp/book/2015/978-4-7741-7301-6