はじめに
データベースのインデックスについて、
インデックスの種類、内部の仕組み、select時の動きを図解してまとめました。
とある自社プロダクト開発でテックリードをしていた際、
大きいテーブルで4億レコードほど登録した状態でのオンライン処理(検索処理)や、
300万レコードを3~4時間以内に集計するバッチ処理を作ることになりました。
チームメンバーにDBAがいないこともあり、
特にインデックスの仕組みについての理解が足りず
ストレージを無駄に圧迫したり、性能が出せなかったり、調べてもOracleの記事ばかり、、、
と困ったので、
同じような問題を抱えておられる方向けに、
この記事をまとめました。
動画でも解説したのでこちらも是非ご覧ください!!
SQL Serverのインデックスの種類と内部の仕組みについて
理解しておくポイントは以下の3つです。
- インデックスの目的とデータ構造
- データページとインデックスページの関係
- select時のインデックス利用イメージ
DBAの方からすると当たり前だと思いますが、
アプリケーションエンジニアの方でこの辺りを理解して
インデックスを貼ったり開発している人にあまり出会ったことがないです。
が、ここを理解してアプリケーションを作成できると性能問題に
苦しまなくて済むので是非理解しておいてください。
インデックスの目的とデータ構造
そもそもインデックスを貼る目的とは、
SQLでデータを取得する際、
無駄なデータにアクセスすることなく目的のデータにアクセスする(IO回数を減らす)ことです。
また、SQL ServerのインデックスはB-Tree型です。
絵にすると
上がB-Tree型のインデックスを利用して特定のデータを取得するイメージで、
下が何も使わずに特定データを取得するイメージです。
黄色の部分がIOしている箇所とすると、
インデックスを利用した方がIO回数が減らせている(黄色が少ない)ことが分かります。
データページとインデックスページの関係
テーブル内の具体的なデータは、データページというところに格納され、
インデックスはインデックスページというところに格納されます。
図のようにテーブルの具体的なデータとインデックスは別々の領域に格納するので、
インデックスを作成すると実データとは別にストレージの容量を使うことになります!!
クラスター化インデックスと非クラスター化インデックス
SQL Serverにはクラスター化インデックスと
非クラスター化インデックスの2種類があります。
図のように、
クラスター化インデックスはB-Treeのリーフ部(一番下の層)のみがデータページに格納されます。
ただし・・・
実際のテーブルのデータを特定のkeyの順番で並ばせるので
1テーブルに1つしかクラスター化インデックスは貼れません。
一方、
非クラスター化インデックスはB-Treeのリーフ部も含めてインデックスページに入り、
リーフ部には該当データへのポインタが格納されます。
クラスター化インデックスは、
インデックスと実データの並びが揃うためbetweenなどで
ある程度の範囲を一括で取得する必要がある場合有効で、
非クラスター化インデックスは数レコードのみを取得する必要がある場合に有効です。
select時のインデックス利用イメージ
クラスター化インデックス、非クラスター化インデックスをそれぞれ利用して
selectした場合のインデックス利用イメージを図にすると
クラスター化インデックスはB-Treeを辿れば実データまで辿れますが、
非クラスター化インデックスはB-Treeを辿って
さらにポインタで実データを見に行く必要があります。
(ただしインデックスを貼ったカラムのみselectする場合は
B-Treeに情報があるためデータページを見る必要はない)
自分はできる限り、
B-Treeを辿るだけで完結するようにインデックスを貼ることを意識しています。
SQLServerのチューニングアドバイザもそのように指摘してきます。
具体的には、
非クラスタ化インデックスをクラスタ化インデックスのように
B-Treeをたどるだけで完結させるには
カバリングインデックスか付加列インデックスを作成すれば良いです。
(SQL Server2005以降を利用している場合は付加列インデックスを使う方べきです。理由は後述の通りです。)
カバリングインデックスと付加列インデックス
USERテーブル(Id、Name、Phoneカラムを持つ)に対して、
IdをkeyにIdとNameをselectする場合を例に挙げると、
非クラスタ化インデックスを利用した場合、先ほどの絵のように、
IDカラムのB-Treeを辿る+ポインタで実レコードを特定してName情報を取得する必要があります。
一方、非クラスタ化インデックスのカバリングインデックスや付加列インデックスを利用した場合を絵にすると、
カバリングインデックスではIdとNameの情報がB-Treeの全ノードあるため、
ポインタで実データを見に行く必要がありません。
一方、
付加列インデックスではB-Treeのリーフ部にのみNameの情報があるため、
B-Treeを辿った後にポインタで実データを見に行く必要がありません。
実際にselectするレコード数が数十万規模だった場合、
ポインタで実データを見にいく(該当レコードをLookupする)コストはかなり大きくなるため、
B-Treeを辿った後に、
該当レコードをポインタで見にいく必要があるかないかで性能は大きく変わります。
さらに、
カバリングインデックスと付加列インデックスの特徴を比較すると以下のようになります。
上のように付加列インデックスの方が
更新範囲が狭く高速に更新でき(=インデックスを貼ったテーブルへのレコードの登録、更新が高速)、
ストレージ容量も節約できるので、
SQL Server2005より前のバージョンを利用していない限り
付加列インデックスを利用すべきです。
まとめ
自分が実際に性能改善をしていく中で押さえておくべきだと感じている
SQL Serverのインデックスの種類と内部の仕組みについて説明しました。
自分が普段、アプリケーション開発でデータベース周りを設計したり製造する際、
意識しているポイントは以下3つです。
- インデックスを貼る際、無駄にストレージを圧迫させない
- インデックス更新時のオーバーヘッドをできる限り減らす
- データページへのインデックスを参照できる限りさせないために付加列インデックスを有効活用する
この辺りを意識せずにモノができて最後の性能測定をして大惨事にならないように、
DBAだけでなくアプリケーション開発に関わるメンバー全員が意識できると
リリース間際にバタバタしなくて済むと思いますが、それが難しい。。。。
おまけ
現場で必要なデータベース周りの知識に関するまとめ記事を作ったので
是非そちらも参照ください!!