はじめに
SQLをチューニングする際、チューニング観点を全てまとめた記事が存在しておらず、調べながら作業するうえで不便を感じたため、自分が調べた内容を1つの記事で閲覧できるよう投稿します。
目次
1. インデックス
1-1. インデックスとは
1-2. クラスター化インデックス / 非クラスター化インデックス
1-3. 複合インデックス
1-4. 付加列インデックス
1-5. インデックスの活用
1-6. インデックスの断片化
2. パーティション
3. 駆動表・内部表
4. 処理が遅くなるクエリの書き方
4-1. SELECT
4-2. WHERE
4-3. INSERT
1. インデックス
■ 1-1. インデックスとは
テーブルへの処理を高速化するための設定で、「索引」と呼ばれることもあります。
本の目次に例えて説明します。
目次がない場合、見たい内容を探す際の指標がなく、本を最初から最後まで見てどこに記載されているかをしらみつぶしに探していく必要があり時間がかかります。
これが、テーブルにインデックスが設定されていない状態を指します。
目次がある場合、章ごとにページ数が記載されており、見たい内容が本のどこに記載されているかがすぐにわかります。
これが、テーブルにインデックスが設定されている状態を指します。
上記の例を、SQLのテーブルに当てはめてみます。
[章]列にインデックスを設定した場合は、章のみの目次がある本、
[節]列にインデックスを設定した場合は、節のみの目次がある本、
[章]列・[節]列・[項]列に複合インデックス(後述)を設定した場合は、すべての目次がある本になります。
| 章 | 節 | 項 |
|---|---|---|
| 第1章 インデックス | 第1節 インデックスとは | 1項 インデックスとは |
| 第1章 インデックス | 第2節 クラスター化インデックス | 1項 クラスター化インデックス |
| 第1章 インデックス | 第3節 非クラスター化インデックス | 1項 非クラスター化インデックス |
| 第1章 インデックス | 第4節 その他 | 1項 複合インデックス |
| 第1章 インデックス | 第4節 その他 | 2項 付加列インデックス |
| 第2章 パーティション | 第1節 パーティションとは | 1項 パーティションとは |
| 第2章 パーティション | 第1節 パーティションとは | 2項 パーティションの効果 |
■ 1-2. クラスター化インデックス / 非クラスター化インデックス
クラスター化インデックス
・テーブルのデータが設定されたインデックスに従い物理的に並び替えられる。
（キー参照を必要としないため、最速で検索が可能）
・テーブルに対して1つまでしか設定できない。
・一般的に、PKの項目で設定される。
非クラスター化インデックス
・テーブルのデータの並び替えは行わず、実データへの参照を保持する。
この参照をレコードID（RID）といい、実データへのアクセスをページジャンプという。
・テーブルに対して複数設定可能。
■ 1-3. 複合インデックス
・複数項目に対してインデックスを設定した場合、複合インデックスとなる。
・大量のレコードIDを保持することになるため、インデックスサイズが大きくなる。
クエリの処理に必要なデータを全て含んでいるインデックスを カバリングインデックス と呼び、
ディスクI/Oが減り高速なアクセスが可能になる。
■ 1-4. 付加列インデックス
・インデックスに加えて付加列として項目を追加することができる
・付加列のレコードIDは保持しなくてもよいため、複合インデックスと比べてインデックスサイズを抑えることができる
（実際はノードの原理に基づいた説明が必要だが、本記事では省略）
■ 1-5. インデックスの活用
インデックスが設定された項目をWHERE句や結合の条件に設定することで、
テーブル全体を捜査するTable Scanではなく、インデックスを使用して効率よく捜査するIndex Seek、もしくはIndex Scanになります。
複合インデックスの場合、インデックスに指定する項目の順番も考慮する必要があります。
例えば、項目A・項目B・項目Cの順番の複合インデックスを作成した場合、項目AをWHERE句の条件に設定した場合はインデックスを活用することができますが、項目BをWHERE句の条件に設定した場合はインデックスを活用できない場合があります。
そのため、よく条件に使用する条件から先に指定するようにしましょう。
インデックスが適用できない条件
※環境によってはインデックスが効いたという報告もあるため、あくまで参考程度
・インデックス列に対して演算をする
・インデックス列に対してSQL関数を適用している
・インデックス列に対してファンクションを使用している
・LIKE句を後方一致、もしくは中間一致に使用している
・暗黙的な型変換になっている（数値型の項目に対して'10'を比較するようなケース）
・否定条件を利用している
・NULLに対しての条件を利用している
・OR条件を使用している
■ 1-6. インデックスの断片化
インデックスは、データの更新・削除をすることで断片化が発生し、CPU付加の増加やクエリ実行時間の遅延などのパフォーマンス低下を引き起こす傾向にあります。
そのため、インデックスの断片化が進んでいる場合は、定期的に解消してあげる必要があります。
断片化を解消する手段として、インデックスの再構成と再構築という2つの方法があります。
ALTER INDEX ［インデックス名］ON［テーブル名］REORGANIZE
ALTER INDEX ［インデックス名］ON ［テーブル名］REBUILD
どちらの手段を選択するかですが、Microsoftのドキュメントでは以下の記載があります。
| avg_fragmentation_in_percent | 断片化解消ステートメント |
|---|---|
| 5 ～ 30% | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD |
avg_fragmentation_in_percentの値は、「sys.dm_db_index_physical_stas」を実行することで取得可能です。
SELECT
SCM.name AS schema_name,
OBJCT.name AS table_name,
IDX.name AS index_name,
IPS.index_level,
IPS.avg_fragmentation_in_percent,
IPS.fragment_count,
IPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) AS IPS
LEFT OUTER JOIN sys.objects AS OBJCT
ON IPS.object_id = OBJCT.object_id
LEFT OUTER JOIN sys.indexes AS IDX
ON IPS.object_id = IDX.object_id AND IPS.index_id = IDX.index_id
LEFT OUTER JOIN sys.schemas AS SCM
ON OBJCT.schema_id = SCM.schema_id
WHERE OBJCT.type = 'U'
AND IDX.index_id > 0
-- 断片化率が30%以上の情報を抽出する
AND IPS.avg_fragmentation_in_percent > 30
ORDER BY IPS.avg_fragmentation_in_percent DESC
2. パーティション
データのパーティション化とは、データの格納場所を分割する機能です。
これに伴い、データアクセス速度の向上につながります。(*1)
カレンダ年月という項目に対してパーティションを設定した例を説明します。
すべての期間のデータを保持した全体のテーブルを1つの箱としたとき、パーティションを設定することでカレンダ年月ごとの引き出しが作られるイメージです。
例) カレンダ年月を条件に絞り込みをした場合
・パーティション無：大きい箱の中を手探りで探して対象データを取り出すイメージ
・パーティション有：対象のカレンダ年月の引き出しを開けて、対象データを取り出すイメージ
上記の例を見ると、パーティション有の方がアクセス速度が向上することがわかると思います。
しかし、私はインデックスとの違いをいまいち理解することができませんでした。
上記の例のようにカレンダ年月に対してインデックスを設定した場合でも、効率よくカレンダ年月を抽出して検索することが可能です。
ただ、SQLの処理をする際に、一度テーブルのデータファイルをメモリに乗せる必要があります。
パーティションが設定されている場合は、データファイルがパーティション毎に分かれているため、メモリの使用量が小さくて済みます。しかし、インデックスの場合はテーブルのすべてのデータが1つのデータファイルに存在しているため、すべてをメモリに乗せる必要があります。
そのため、パーティションとインデックスを比較した場合、SELECTにおいてはパーティションのほうがパフォーマンスは向上する可能性が高いといえます。
ただ、パーティションにはいくつかの制約もあるため、基本的にはインデックスを優先的に使用して、それでもパフォーマンスが改善されない場合に検討していくことになります。(*2)
参考(*1)：https://qiita.com/fuk101/items/603ddb445070aebbfcfa
参考(*2)：https://qiita.com/maaaaaaaa/items/6ca9e5546ae2e7d1e431
3. 駆動表・内部表
テーブルを結合する際にどちらのテーブルを基準とし、どちらのテーブルを結合するかによって、パフォーマンスに影響出ることがあります。
基準となるテーブルを「駆動表」、結合するテーブルを「内部表」と言います。
例えば、レコード数が(m)件のテーブル（駆動表）に対して、(n)件のテーブル（内部表）を結合しようとしたときの処理量を(m×n)と表したとします。
これを逆にしたとしても、処理量は(n×m)となるだけで変わらないように感じるかもしれません。
ただ、(n)件のテーブル（内部表）の結合条件の項目に対してインデックスが張られていた場合、実際の処理量は大きく変わります。
大げさに言うと、(n)件のテーブルに対しての捜査はインデックスをもとに実施されるため、テーブル全体を読み込む必要はなくピンポイントでデータを検索できるため、実際の処理量が(m×1)になるイメージです。
この場合、駆動表として件数の少ないテーブルを指定したほうがパフォーマンスが向上することがわかります。
他の方法でパフォーマンスを向上させるためには、単純に(n)件・(m)件の件数を減らすことで全体の処理量を削減することができるため、サブクエリ等を使用して処理件数を減らすことが効果的です。
しかし、そのサブクエリの条件の中でファンクション等を使用して、逆にテーブルがフルスキャンされるようになってしまいパフォーマンスが低下するということもあるため、テーブルのデータがどのように読み込まれるかを意識してください。
4. 処理が遅くなるクエリの書き方
■ 4-1. SELECT
必要な項目だけをSELECTする
「SELECT *」で実装するだけで、以下の場合などに対応することができて便利だと思います。
・テーブルに新規項目が追加となった場合
・機能追加により新しい項目のSELECTが必要になった場合
しかし、取得項目が増えるにつれパフォーマンスが悪化していきます。
そのため、必要最低限な項目をSELECTするようにしましょう。
パフォーマンスとして一番良いのは、インデックスとして指定されている列のみを指定する場合です。
ちなみに、「SELECT *」はSELECTで全項目を指定してSELECTするよりも遅いという噂を聞いたことがありますが、実際はそんなことはないようです。
■ 4-2. WHERE
WHERE句で関数や計算を利用しない
WHERE句の条件で関数や計算、ファンクションを利用する場面、インデックスが貼られている項目に対して実施してしまうと、インデックスが効かずにパフォーマンスが悪化してしまうことがあります。
-- 例1
SELECT * FROM [テーブル名]
WHERE CONVERT(NUMERIC, [項目A]) = 1230
-- 例2
SELECT * FROM [テーブル名]
WHERE [項目B] * 10 = 1230
同じ条件でも書き方さえ変えればインデックスを適用させることが可能な場合もあります。
-- 例1（[項目A]が8桁の0埋め数字の文字列型の場合）
SELECT * FROM [テーブル名]
WHERE [項目A] = RIGHT('00000000' + CONVERT(NVARCHAR,1230), 8)
-- 例2
SELECT * FROM [テーブル名]
WHERE [項目B] = 1230 / 10
また、SELECT句ではインデックス項目に対して計算や関数を使用しても問題ありません。
そのため、SELECT句を使って条件を再現することが可能なケースもあります。
-- 例
SELECT * FROM
(
SELECT [項目A], [項目B]*10 AS [項目Bの10倍] FROM [テーブル名]
) AS [別名]
WHERE [項目Bの10倍] = 1230
■ 4-3. INSERT
BULK INSERT
複数行をINSERTする際に、INSERT文を1行ずつ書くとパフォーマンスが遅くなってしまいます。
INSERTしたいデータが既に別のテーブルに登録されている場合、そのテーブルのSELECT結果をINSERTできるBULK INSERTを使用するようにしましょう。
INSERT INTO [挿入先テーブル名] SELECT [項目] FROM [取得元テーブル名]
SELECT INTO
SELECT INTOもBULK INSERTと同程度のパフォーマンスで複数行のINSERTを実施することができます。
SELECT INTOでは選択された項目を持つテーブルを新規で作成することができます。
ただ、プライマリキー制約やインデックス等はコピーされないため注意してください。
SELECT [項目] INTO [挿入先テーブル名(未作成の状態)] FROM [取得元テーブル名]
最後に
SQLのチューニングに必要な内容をまとめてきましたが、実際には調べた内容通りの挙動をしないこともあります。
これは、SQLServerのクエリオプティマイザが最適にクエリを動かそうとする中で、ユーザーの想定していない動作を選択することがあるからです。
逆に、パフォーマンスが悪化すると思われる記述でも、SQLServerがクエリを読み替えて問題なく動作してくれることもあります。
そのため、今回記載した内容を鵜呑みにし過ぎず、実行計画を見ながら状況に応じたチューニングをしていこうと思います。
以下、パフォーマンス検証をする際に気を付けることを記事としてまとめておりますので、良ければご覧ください。
https://qiita.com/khata0131/items/f34c586a40ed6e0b8e89