SQL Serverで数億規模のデータを扱って感じた最低限理解しておくべきインデックスの仕組み

はじめに

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

300万レコードを3~4時間以内に集計するバッチ処理を行うようなダッシュボードシステムの開発リーダーを1年半間ほど経験しました。

チームメンバーにDBAがいないこともあり特にインデックスの仕組みについての理解が足りずストレージを無駄に圧迫したり、性能が出せなかったり、調べてもOracleの記事ばかり、、、

と結構ハマったので、

同じようにハマっている方や性能問題を抱えておられる方向けに、最低限理解すべきインデックスの仕組みについてまとめました。

この記事を書いている僕はシステムエンジニア8年目です。

最近はnuxt(vue.js)でフロントエンド、spring(java)でAPIの開発をしたり、DB周りのチューニングをしたり、認可サーバ周りの設定をしたり、開発リーダーをしたりしてます。

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

僕がハマった経験を元に理解しておくべきと感じているのは以下の3つです。

  • インデックスの目的とデータ構造
  • データページとインデックスページの関係
  • select時のインデックス利用イメージ

これら3つについて見ていきます。

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

アプリケーションエンジニアの方でこの辺りを理解してインデックスを貼ったり開発している人にあまり出会ったことがないです。

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

まずインデックスとはSQLでデータを取得する時に無駄なデータにアクセスせずに目的のデータにアクセスする(IO回数を減らす)ために作成します。

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

絵にすると

上がB-Tree型のインデックスを利用して特定のデータを取得するイメージで、下が何も使わずに特定データを取得するイメージです。

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

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

テーブル内の具体的なデータはデータページというところに格納され、インデックスはインデックスページというところに格納されます。

MEMO

図のようにテーブルとインデックスは別々の領域に格納するのでインデックスを作成すると実データとは別にストレージの容量を使うことになります。

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

SQL Serverにはクラスター化インデックスと非クラスター化インデックスの2種類があります。

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

実際のテーブルのデータを特定のkeyの順番で並ばせるので1テーブルに1つしかクラスター化インデックスは貼れません。

一方非クラスター化インデックスはB-Treeのリーフ部も含めてインデックスページに入り、リーフ部には該当データへのポインタが格納されます。

MEMO

クラスター化インデックスはインデックスと実データの並びが揃うため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するレコード数が数十万規模だったり、USERテーブルが抱えるレコード数が数億規模などの場合、

ポインタで実データを見にいく(該当レコードをLookupする)コストはかなり大きくなるため、

B-Treeを辿った後に該当レコードをポインタで見にいく必要があるかないかで性能は大きく変わります。

さらに、カバリングインデックスと付加列インデックスの特徴を比較すると以下のようになります。

上のように付加列インデックスの方が更新範囲が狭く高速に更新でき(=インデックスを貼ったテーブルへのレコードの登録、更新が高速)、

ストレージ容量も節約できるのでSQL Server2005より前のバージョンを利用していない限り付加列インデックスを利用すべきです。

まとめ

自分が実際に性能改善をしていく中で押さえておくべきだと感じているSQL Serverのインデックス周りの内部の仕組みについて説明しました。

自分が普段、意識しているポイントは以下3つです。

  • インデックスを貼る際、無駄にストレージを圧迫させない
  • インデックス更新時のオーバーヘッドをできる限り減らす
  • データページへのインデックスを参照できる限りさせないために付加列インデックスを有効活用する

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

DBAだけでなくアプリケーション開発に関わるメンバー全員が意識できるとリリース間際にバタバタしなくて済むと思いますが、それが難しい。。。。

2022/03/21追記

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

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

現場で必要なデータベースの基礎知識まとめ|正規化、ACID特性、SQLチューニングなど

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください