データベースのインデックスの種類と内部の仕組み｜SQL Serverで数億規模のデータ量を扱ってみて

はじめに

データベースのインデックスについて、

インデックスの種類、内部の仕組み、select時の動きを図解してまとめました。

とある自社プロダクト開発でテックリードをしていた際、

大きいテーブルで4億レコードほど登録した状態でのオンライン処理(検索処理)や、

300万レコードを3~4時間以内に集計するバッチ処理を作ることになりました。

チームメンバーにDBAがいないこともあり、

特にインデックスの仕組みについての理解が足りず

ストレージを無駄に圧迫したり、性能が出せなかったり、調べてもOracleの記事ばかり、、、

と困ったので、

同じような問題を抱えておられる方向けに、

この記事をまとめました。

動画でも解説したのでこちらも是非ご覧ください！！

＞＞利用したスライド

SQL Serverのインデックスの種類と内部の仕組みについて

理解しておくポイントは以下の３つです。

DBAの方からすると当たり前だと思いますが、

アプリケーションエンジニアの方でこの辺りを理解して

インデックスを貼ったり開発している人にあまり出会ったことがないです。

が、ここを理解してアプリケーションを作成できると性能問題に

苦しまなくて済むので是非理解しておいてください。

インデックスの目的とデータ構造

そもそもインデックスを貼る目的とは、

SQLでデータを取得する際、

無駄なデータにアクセスすることなく目的のデータにアクセスする(IO回数を減らす)ことです。

また、SQL ServerのインデックスはB-Tree型です。

絵にすると

上がB-Tree型のインデックスを利用して特定のデータを取得するイメージで、

下が何も使わずに特定データを取得するイメージです。

黄色の部分がIOしている箇所とすると、

インデックスを利用した方がIO回数が減らせている(黄色が少ない)ことが分かります。

データページとインデックスページの関係

テーブル内の具体的なデータは、データページというところに格納され、

インデックスはインデックスページというところに格納されます。

クラスター化インデックスと非クラスター化インデックス

SQL Serverにはクラスター化インデックスと

非クラスター化インデックスの２種類があります。

図のように、

クラスター化インデックスはB-Treeのリーフ部(一番下の層)のみがデータページに格納されます。

ただし・・・

実際のテーブルのデータを特定のkeyの順番で並ばせるので

１テーブルに１つしかクラスター化インデックスは貼れません。

一方、

非クラスター化インデックスはB-Treeのリーフ部も含めてインデックスページに入り、

リーフ部には該当データへのポインタが格納されます。

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のインデックスの種類と内部の仕組みについて説明しました。

自分が普段、アプリケーション開発でデータベース周りを設計したり製造する際、

意識しているポイントは以下３つです。

この辺りを意識せずにモノができて最後の性能測定をして大惨事にならないように、

DBAだけでなくアプリケーション開発に関わるメンバー全員が意識できると

リリース間際にバタバタしなくて済むと思いますが、それが難しい。。。。

おまけ

現場で必要なデータベース周りの知識に関するまとめ記事を作ったので

是非そちらも参照ください！！

初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜