はじめに
データベースのパフォーマンスを向上させる手段としてお手軽でかつポピュラーな方法としてインデックスを作成する方法があります。しかし、お手軽といってもやみくもに作成すればよいのではなく、しっかりと考えて使わなければ恩恵を受けることはできません。そこで今回は、インデックスを作成するポイントと注意点を最も一般的に使用されるインデックスであるB-treeインデックスを例に解説していきます。
*読んでるときにあれ..?おかしいな?と思った点があればぜひ教えてください！それも勉強になります*
そもそもインデックスとは?
まずはインデックスについて軽く触れます。データベースにおけるインデックスとはレコードを高速に効率よく検索するための≪索引≫です。特定の列を識別できるキー値とそれに結び付けられる実データ、あるいはポインタが格納されています。イメージとしては本の巻末についている索引に近いですね(キー値とポインタ=単語とその単語が載っているページ)。
B-treeインデックス
インデックスと一言にいってもその種類は様々あります。今回は利用頻度の高いB-treeインデックスについて見ていきます(以降インデックスと記されている場合はB-treeインデックスを指している思ってください)。
B-treeインデックスの構造を簡単に図に表すと上記のようになります(正確にはB+treeという構造らしい)
特徴として
- ルートノードと子ノードはキー値と子ノードへのポインタを持つ
- リーフノードはキー値とデータへのポインタを持つ
といった点が挙げられます
なぜB-treeインデックスが多く使われるのか？
B-treeインデックスには以下のような長所が挙げられます
1.平衡木であるためどのデータに対しても計算量が一定になる
B-treeは平衡木と呼ばれる構造をしています。平衡木はどのリーフもルートからの高さが均一であるため、どんなキーを使ってもリーフまでの距離が一定になります。キー値に左右されず探索を同じ計算量で行うことができます。2.性能劣化が緩やかである
B-treeは最初に作られたときはきれいな平衡木であるが、テーブルの挿入や更新、削除が繰り返されることで徐々に崩れていき、非木構造のなっていくことがあります。そのため長時間の運用によって探索に費やすコストにばらつきが出るようになってしまいます(自動修復する機能も備わっていますがそれでも長時間運用による劣化は避けられません)。しかし、B-treeの性能劣化は非常に緩やかであり持続性に優れています。それはB-treeが平衡木であるのと同時に平べったい木であるからです。B-treeのルートからリーフまでの高さは3~5で構成されていることが多く、このように背の低い木はデータの量が増加しても探索速度は変わらない特性があります
3.範囲検索も高速化できる
B-treeは構築されるときに必ずキー値をソートします。そのため特定の値以上・以下を探索というような絞り込みが可能になり、高速化を可能にしますインデックスを作成するのに適した例
インデックスの特徴を押さえたところで次はインデックス作成の恩恵が大きくなる例の特徴について解説していきます例１：大規模なテーブルに対して作成する
当たり前ですが、レコード数の少ないテーブルにインデックスを作成しても大した恩恵を受けられません。それどころかインデックスは独自のインデックス領域を必要とするため、わざわざ領域を占有してまでインデックスを作成したのに検索性能が上がらない！といった事態になることも考えられます。では、どのくらいのレコード数がインデックス作成の目安になるのでしょうか。これには明確な基準はなく、ストレージやサーバーの性能といった環境要因について変化しますがレコード数が10万以下の場合はほぼ意味がないと思ってもいいそうです(あくまで目安です)。
例2：カーディナリティの高い列
インデックス作成の判断基準として重要な要素にカーディナリティがあります。カーディナリティとは、ある特定の列における値の種類の多さを表す概念になります。例えば、社員が普通乗用車の運転免許を持っているか否かを表す列「免許所持」の列があった場合、取りうる値はおそらく「TRUE」か「FALSE」の２つでしょう。「免許所持」の列のカーディナリティは「2」ということになります。このようにカーディナリティの小さい列にインデックスを作成してもあまり意味はありません。カーディナリティの目安は、「特定のキーを指定したときに、全体の5%に絞り込めるだけのカーディナリティがある」ことが目安になります。
しかし、注意点があります。それは「カーディナリティが高くても、特定の値にデータが集中している列はインデックスの作成に向いていない」という点です。極端な話ではありますが1~10000までの値をとる列があったとしても、1~9999の値は全体の0.1%しか取らず、残りの99.9%は10000の値をとるような列はインデックスの作成に向いていません。
例3：SQLで検索条件や結合条件として使用される列
これも当たり前の話ですが、検索条件や結合条件にならないような列にインデックスを作成しても意味がありません。WHERE句やORDER BY句、テーブル結合でON句に使われるような列にインデックスを作成するようにしましょう。SQLでの注意点
先ほど、インデックスを作成する列の基準として「検索条件として使用される列」を挙げました。しかし、検索条件として使用されていてもインデックスが使われない場合がいくつかあります。
1.インデックス列に演算を行っている場合
インデックスを作成した列はSQLにおいて「裸」で用いるのが原則となっています-- NG
SELECT * FROM hoge_table WHERE index_col + 2 > 50
--OK
SELECT * FROM hoge_table WHERE index_col > 48
上記ではindex_col列にインデックスが存在するとします。このような場合、インデックスが保持しているデータはあくまで「index_col」に対してであり、「index_col」ではありません。なのでOK例のようなSQLを記述しなければいけません
2.IS NULLが使われている
インデックスはNULLをデータ値としてはみなさず、保持をしません。なのでIS NULLやIS NOT NULLに対しては有効てきではないです。一部のDBMSはIS NULLに対しても有効的に作用しますが、汎用性はあまりありません
SELECT * FROM hoge_table WHERE index_col IS NULL
3.否定形が使われている
否定形は利用したとしても検索範囲が広すぎて役に立つことがありません。
SELECT * FROM hoge_table WHERE index_col <> 1234
4.前方一致以外のLIKE句の場合
LIKE句を使う場合は、前方一致の場合のみにインデックスが使用されます
-- NG
SELECT * FROM hoge_table WHERE index_col LIKE '%aaa'
-- OK
SELECT * FROM hoge_table WHERE index_col LIKE 'aaa%'
以上になります。一部割愛しましたが、ほかにもSQLの記述次第でインデックスが使用されないパターンがあるのでぜひ調べてみてください。
その他の注意事項
SQL以外にもいくつか注意事項があるのでいくつかご紹介したいと思います
1.主キーや一意制約キーの列についてはインデックスを作成する必要がない
DBMSは主キー制約や一意制約を作成する際に、内部的にインデックスを作成しています。そのため、二重にインデックスを作成することになってしまいます。主キーや一意制約キーといった列をSQL文内の条件として使用する場合は、自動的にインデックスが使用されています2.インデックスによって更新性能を劣化させてしまう
これについてはB-treeインデックスに限らず、インデックス全般に言われていることである。一般的にインデックスは独立したオブジェクトとしてDBMSに保持されている。そのため、インデックスの作成されている対象の列値が更新されると、インデックス内に保持している値も更新しなければならない。つまり、インデックスを作成すればするほどテーブルに対する更新性能が劣化していくというトレードオフの関係に注意してインデックスを作成する必要がある3.定期メンテナンスの必要性
本記事内でも述べましたが、DBMSを長期的に運用していると、構造がだんだんと崩れていき劣化します。その為、定期的なインデックスの再構築を行うのが望ましいです。頻度についてはシステムのデータ更新量によるため一概に言えないが、DBMSごとに指標がや調査方法が存在するため、マニュアルで調べてみると良いでしょう。
最後に
以上がインデックスの作成ポイントと注意点になります。こうしてみると実装がお手軽な反面、注意しないといけないことが多いことも分かったと思います。パフォーマンスを改善しようとしたのにかえってパフォーマンスが下がる...なんてことになりたくないですね。また、インデックス・ショットガンと呼ばれるようなアンチパターンも存在するそうなのでいずれ記事にしてみたいと思います。
以上、インデクス設計についてでした！
参考
「達人に学ぶDB設計徹底指南書 第2版」
「インデックスとは？仕組みをわかりやすく解説」
「B-treeインデックス入門」