SQLServerのインデックス、実行プランを理解することの大切さ
こんにちは！SEのフジヲです！
Noteを始めてから始めてエンジニアっぽい記事を書きます。
今回のお題は表題の通り、DBシステムのインデックスについてです。
私は現職のSQLServerの経験しかないので、他のデータベースについてはわかりませんので、あくまでSQLServerについてのみの経験を基に書いていきます。ご参考になりましたら幸いです。
これまで
かれこれ中小のメーカーにてSEとして5年半以上働いていますが、インデックスについては教えてもらった記憶がありませんし、恥ずかしながら、自分できちんと学んだ記憶もありませんでした。
中小企業のクライアントさんであれば、システムにもよりますがそこまでインデックスは意識しなくても普通に動いていたので、気にすることもありませんでした。テーブル結合の条件が悪い、等の原因で遅いことはありましたが…
現在は、ほとんどのクライアントさんがAzureのSQLServerを利用しているので、Azureの機能で自動チューニング機能を使用していれば、勝手にチューニングしてくれたりするので、なおさら機会がありませんでした。
現象
昨年から関わっているクライアントさんのシステムで月あたり10万件以上のデータが一つのテーブルに対してINSERTされるシステムに関わり、先週時点で110万件以上のレコードが登録されている状況で、データのDELETE&INSERTでタイムアウトになってしまう。
SSMSからプログラムで実行しているクエリと同じものを実行すると、データの取得に30秒以上かかってしまっていた。
要因
システム稼働後の仕様変更でインデックスを登録していない条件でデータをDELETEするクエリを発行していた。そのため、IndexScanとなり対象データの取得に時間がかかっていた。
対応
非クラスターインデックスを手動で作成し、データ取得の速度改善を確認。結果、取得時間が2分の1になった。
学んだこと
Azure SQLServerの自動チューニング機能をオンにしていると、自動で非クラスターインデックスを作ってくれるみたいですが、もちろん全てが開発者の意図通りに作成されるわけではないのでやはり知識を持っておくことが大切だと学びました。
また、テーブル設計の前後に関わらず、クエリを実行する際には「実行プラン」を確認し、必要に応じてインデックスの追加やクエリの条件式を見直すことが大切だと学びました。
実行プランとインデックスを理解するのにおすすめの本
問い合わせ対応後に、下記の本を読んでみましたがハンズオン形式でかなりわかりやすかったのでインデックスや実行プランの見方がわからない、理解していないという方にはおすすめです。Kindle Unlimitedに登録していれば無料で読めます。
簡単ですが本書の内容を抜粋してまとめましたので記載いたします。
実行プランの中身についてを解説しているものになりますので、画面を見ながらじゃないと分かりづらいと思います。
データベースのパフォーマンスとインデックスの基礎知識
キャッシュとは何か？
キャッシュとは、ディスクから読み込んだデータをメモリ内に一時的に保存しておく仕組みのことです。メモリ上のデータは高速に取得できますが、キャッシュがない状態ではディスクにアクセスするため時間がかかります。そのため、SQLの検索性能を正確に評価するには、キャッシュを無効化した状態で測定することが重要です。
テーブルスキャンとインデックスシーク
テーブルスキャンとは？
テーブルスキャンは「テーブル全体を読み取る操作」を指します。たとえば、社員テーブルの「ID」列に1から50,000までの値があり、重複が許可されている場合、「ID = 1」を検索する際に全行を確認する必要があります。結果的に該当データが1行だけでも、全行をスキャンすることで確認が行われるため、非効率な処理となります。
インデックスシークとは？
インデックスシークは、「インデックスを利用して目的のデータを直接特定する操作」です。インデックスが適切に機能している場合、特定の条件に基づいて効率的にデータを取得できます。
インデックスの種類と構造
インデックスには「非クラスター化インデックス」と「クラスター化インデックス」の2種類があります。
非クラスター化インデックス
非クラスター化インデックスを作成すると、ツリー構造で整理されたインデックスファイルが生成されます。このファイルには以下の情報が含まれます：
インデックス項目（例: ID）
RID（行情報）
RIDを利用して、実表データがどのファイルの何行目に存在するかを特定し、必要なデータを取得します。このプロセスをRIDLookupと呼びます。
クラスター化インデックス
クラスター化インデックスを作成すると、実データが直接インデックスのリーフページに移動されます。その結果、追加のデータ検索が不要となり、検索速度が向上します。ただし、テーブルごとに1つしか作成できないという制約があります。
スキャンとシークの違い
インデックスのパフォーマンスを評価する際には、「Scan」か「Seek」のどちらが使用されているかを確認することが重要です。
Scan：テーブルまたはインデックス全体を読み取る操作
Seek：インデックスを活用してピンポイントでデータを取得する操作
たとえインデックスが存在していても「IndexScan」であれば、すべてのインデックスを読み込むため効率が悪い状態を意味します。一方、「IndexSeek」であればインデックスが有効に機能している証拠です。
パフォーマンス指標とインデックス管理
推定コストの確認
SQLパフォーマンスを最適化する際は、「推定コスト」に注目してください。I/Oの推定コスト（ファイルアクセス量）とCPUの推定コストの合計値が低いほど、効率的な操作を示します。
インデックスの再構築
インデックスが断片化した場合は、「再構築」を行うことでパフォーマンスを向上させられます。
クラスター化インデックスの特性と注意点
クラスター化インデックスは、最速のデータ検索方法ですが以下の制約があります：
テーブルに1つのみ作成可能：実データがリーフページに移動されるため。
作成時に時間がかかる：データの移動が必要なため。
クラスター化インデックスの基本構造
クラスター化インデックスを作成することで、「実データ」という概念が消失し、クラスター化インデックスのリーフページが実データを格納する役割を担います。その結果、従来の「実データのファイル位置と行番号を示すRID」という考え方は不要になりました。
実行プランの変化
クラスター化インデックスを作成後、実行プランでは「キー参照」という表現が使われます。これは、非クラスター化インデックスがクラスター化インデックスのキーを基に検索を行う動作を示しています。
パフォーマンス改善のためのテーブル設計
非クラスター化インデックスの検索速度が低下する場合、テーブル設計の見直しが必要です。例えば、大きなサイズの列を別のテーブルに分離することで、インデックスの負荷を軽減し、検索速度の向上が期待できます。
クラスター化インデックスとTableScan
「ClusterdIndexScan」は、クラスター化インデックスのリーフページ全体を読み取る処理であり、以前のTableScanと同等の動作です。
付加列インデックスの活用
非クラスター化インデックスのリーフページに必要な列が含まれていない場合、キー参照が発生し検索速度が低下します。その場合、付加列インデックスを活用することで、検索速度を向上させることが可能です。
付加列インデックスの仕組み
例えば、リーフページに列がないために検索が遅い場合、その列を付加列として追加することで、キー参照を回避し、高速検索を実現します。
注意点
付加列インデックスは、データを二重に保持するため、余分なデータ容量が必要となります。可能な限り、純粋な非クラスター化インデックスのみで構成し、どうしても速度改善が必要な場合に限定して利用してください。
プライマリキーによるクラスター化インデックス
プライマリキーを作成すると、自動的にクラスター化インデックスが作成されます。そのため、プライマリキーの作成時には、クラスター化インデックスが生成されることを理解し、適切なキーを選択する必要があります。
キー選択のポイント
例えば、意味のない連番をプライマリキーに設定すると、最速の検索条件を活かせません。Id + 日付など、運用で使用する検索キーを想定した設計が重要です
ユニークキーと非クラスター化インデックス
ユニークキーを作成すると、自動的に非クラスター化インデックスが作成されます。メールアドレスのように重複しない列をユニークキーとして設定することで、重複チェックと検索性能向上の両方を実現できます。
インデックスと更新処理の負荷
更新時のパフォーマンス低下
インデックスを作成すると、検索は高速化されますが、更新時の処理が遅くなります。これは、データ変更時にインデックスの再構成が必要になるためです。
大量データ更新の影響
夜間のバッチ処理など大量のデータ更新が発生する場合、処理時間が大幅に増加する可能性があります。そのため、負荷テストを実施し、インデックスの影響を事前に確認することが重要です。
インデックスが効かないケース
以下のような場合、インデックスは期待通りに機能しません。
前方一致以外のLIKE検索
前方一致以外の条件では、インデックスの並び順を活用できず、すべてのデータを確認する必要があります。検索条件で関数を使用
インデックス値を加工した検索条件では、インデックスが利用されません。