sqlserverのインデックス最適化で高速化を実現する秘訣

はじめに

本書の目的

本書は、SQL Serverにおけるインデックス最適化の基本と実践をわかりやすくまとめた入門書です。現場でよくある性能問題を想定し、対処法を具体例で示します。

対象読者

データベース初心者〜中級者を想定します。DBAや開発者で、クエリの遅さに悩んでいる方に役立ちます。専門用語は最小限に抑え、例を使って解説します。

本書で学べること

• インデックスの基本的な仕組みと効果
• 効率的なインデックス設計の考え方
• インデックスが効きづらい書き方とその直し方
• SELECT文・サブクエリ・DISTINCTの最適化テクニック
• インデックスの定期メンテナンスの重要性

読み方のポイント

実例を中心に進めます。まず概念を理解し、次に実際のクエリ例で確認してください。必要に応じて手元の環境で試すと理解が深まります。

前提条件

SQL Serverを対象とし、標準的なテーブル設計を前提にしています。各章で実践的なサンプルを示します。

インデックスの最適な設計方法

インデックス設計が重要な理由

インデックスは検索を速くしますが、追加・更新時にコストがかかります。負担と効果のバランスを考えて設計します。

列の選び方（選択性）

一意性が高い列に付けると効果的です。例：ユーザーIDやメールアドレス。一方、性別やフラグのような低選択性列は避けます。

複合インデックスの順序

複合インデックスは列の順序が肝心です。一般に、等価検索で使う列を先に置き、範囲検索や並び替えに使う列は後にします。例：WHERE country=’JP’ AND city=’Tokyo’ は INDEX(country, city) が有効です。先頭列で範囲検索を行うと、以降の列が使われにくくなります。

カバリングインデックスと実例

クエリで必要な列がすべてインデックスに含まれていればテーブル本体を参照せずに済みます。例：SELECT name,email FROM users WHERE email=’x’ に対し INDEX(email, name) を作ると高速化します。

実務向けの注意点

• 外部キーや結合に使う列はインデックス化すると効果的です。
• 更新頻度の高い列は慎重に。インデックス数が多いと書き込みが遅くなります。
• EXPLAINで実行計画を確認し、実際に使われているか検証してください。

インデックスが効きづらい書き方と対処法

はじめに

インデックスが効きないケースは意外と多く、書き方を少し変えるだけで速度が劇的に改善します。ここでは代表的なパターンと実務的な対処法を示します。

LIKE文の注意点

先頭一致（例: WHERE name LIKE ‘abc%’)はインデックスが使えますが、中間一致・後方一致（’%abc’や’%abc%’)では使えません。対処法は、全文検索やn-gram、逆順にしたカラムを用意する方法があります。簡単な代替は、検索用に正規化した別カラムを用意することです。

カラムに関数や演算子を適用する

WHERE LOWER(email) = ‘x’ のように関数を使うと通常インデックスを無効にします。対処法として、関数適用済みの生成カラムを作るか、DBが対応していれば関数インデックスを張ります。可能なら入力時に正規化して保存すると検索が速くなります。

OR条件はUNIONに分割する

複数の列にまたがるORは最適化を難しくします。代わりに各条件を個別のSELECTに分けてUNION（重複排除が不要ならUNION ALL）で繋ぐと、それぞれでインデックスが使えます。重複対策はケースに応じて行ってください。

否定条件の置き換え

NOTや<>はインデックス効率が悪くなります。候補が限られるならINや等価条件に変えると良いです。除外する必要がある場合は、LEFT JOINでの存在チェック（IS NULL）や反転した条件でのクエリも試してください。

実践のポイント

• 実行計画（EXPLAIN）でインデックス使用を確認する
• 小さな変更で速度を測定してから本番適用する
• 必要なら検索専用の仕組みを検討する

これらを順に試せば、インデックスの効果をより引き出せます。

SELECT文の最適化テクニック

1) SELECT * を避ける

必要な列だけを指定してください。ネットワーク転送量と読み込み時間を減らせます。
例: SELECT id, name FROM users WHERE active = 1;

2) 絞り込みは早めに行う

WHERE句で対象行を減らすと後続の処理が速くなります。日付や状態で先に絞ってください。

3) 取得件数を制限する

LIMITを付けて不要な行を取らないようにします。ページングはOFFSETよりもキーセット（last_id）方式を推奨します。
例: WHERE id > ? ORDER BY id ASC LIMIT 20

4) 結合はインデックスがある列で行う

JOINはインデックスのある列を使うと大幅に速くなります。ON orders.user_id = users.id のようにしてください。

5) 列に関数を使わない

WHEREやJOINで列に関数を使うとインデックスが効かなくなります。可能なら正規化や別列で対応してください。

6) カバリングインデックスを意識する

検索・並び・出力に使う列をインデックスにまとめると、テーブル本体を読まずに済むことがあります。

7) サブクエリはEXISTSを検討

INよりもEXISTSが速い場合があります。用途に合わせて切り替えてください。

8) 実行計画を確認する

EXPLAINでどのように処理されるかを確認し、ボトルネックを見つけてください。

9) 準備済み文とパラメータ化

同じ構造のクエリはプレースホルダで実行計画を再利用させてください。

実践では1つずつ変更して効果を測り、最も改善する組み合わせを採用してください。

サブクエリとDISTINCTの最適化

概要

サブクエリとDISTINCTは便利ですが、使い方次第でパフォーマンスに影響します。ここでは実務で役立つ書き方と代替手段を分かりやすく説明します。

IN句とEXISTS句の使い分け

同じ結果を返す場合でも、EXISTSの方が効率的なことが多いです。EXISTSは外側の行ごとに内側の存在確認を行い、マッチが見つかれば早期終了します。例：

-- 良い例（EXISTS）
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.active = 1
);

INは小さなリストには問題ありませんが、サブクエリが大きいとメモリやソートで遅くなることがあります。

サブクエリの書き換え例

相関サブクエリはJOINに書き換えると高速化する場合があります。明示的なJOINでインデックスを利用しやすくなります。

DISTINCTの注意点

DISTINCTは結果を一意化するためにソートやハッシュ処理を行います。行数が多いと負荷が高くなります。不要に使わないことが第一です。

DISTINCTの代替

・GROUP BYで集約関数と組み合わせる
・ウィンドウ関数で重複を取り除く
・事前に必要な列だけで絞り込む

実践チェックリスト

・EXISTSに書き換え可能か確認する
・相関サブクエリはJOINで置き換えられないか試す
・DISTINCTを使う前に本当に必要か検討する
・実行計画でソートやハッシュの有無を確認する

これらを順に試すことで、サブクエリとDISTINCTの影響を減らし、応答性を改善できます。

インデックスメンテナンスの重要性

なぜメンテナンスが必要か

インデックスはデータの追加・更新・削除により断片化します。断片化が進むと読み取り時に余計なI/Oが発生し、応答時間が悪化します。定期的な点検と手入れで安定した性能を保てます。

断片化の確認方法

多くのDBMSは断片化率やページの統計を提供します。目安として、断片化率が5%未満は問題になりにくく、5〜30%は整理、30%以上は再作成を検討します。まず現状を確認してから方針を決めます。

REORGANIZEとREBUILDの違い

・REORGANIZE：インデックスを徐々に詰め直す操作で、実行中も比較的利用可能です。負荷が小さい一方、効果は限定的です。
・REBUILD：インデックスを完全に作り直します。断片化を確実に解消し統計も更新しますが、実行時間とリソースが大きくなります。

実施の目安と頻度

高頻度で更新されるテーブルは週次または日次で確認し、忙しい時間帯を避けて実行します。更新頻度が低いテーブルは月次で十分なことが多いです。

注意点と手順例

メンテナンス前にバックアップや実行計画の確認を行い、テスト環境で手順を試してください。運用中はログ増大やディスク空き容量を監視します。具体的なコマンドはDBMSごとに異なるため、使用する環境のマニュアルを参照して実行してください。

実行後の確認

実行後に断片化率とクエリ応答時間を再確認し、期待通りに改善したかを評価します。これを繰り返して最適な運用ルールを作ってください。