オンプレミスで運用しているSQL Serverでのデータ取得の高速化が不可欠となることがあります。
今回、SQL文のチューニングにあたり、様々な方法での速度改善に関して調査いたしましたので、記事にまとめたいと思います。
使用するインデックスの選定
クエリの性能向上にはインデックスが不可欠です。
インデックスには、データを探す条件となる検索項目と、テーブル内に存在する実データのアドレスが格納されており、基本的な動作としてはデータを探す条件と合致する検索項目を探し、その項目に紐づくデータのアドレスを基に、テーブルで実データを探しに行くという流れとなっています。
インデックス
| 検索項目 | アドレス |
|---|---|
| A00001 | 0x000001 |
| A00002 | 0x000002 |
| A00003 | 0x000003 |
| ・・・ | ・・・ |
| A10001 | 0x002710 |
テーブル
| ID (primary) |
企業番号 | 企業名 | 本社 | 売上 | 従業員数 | アドレス |
|---|---|---|---|---|---|---|
| 1 | A00001 | 企業A | 東京都 | 100万 | 1000人 | 0x000001 |
| 2 | A00002 | 企業B | 大阪府 | 500万 | 200人 | 0x000002 |
| 3 | A00003 | 企業C | 福岡県 | 30万 | 400人 | 0x000003 |
| 10001 | A10001 | 企業AAA | 北海道 | 3億 | 4000人 | 0x002710 |
テーブルのアドレスは自動で振り分けられ、項目としては表示されません。あくまでもイメージです。
上記のテーブルは企業番号をインデックスとして設定した場合のテーブル表です。
企業番号を条件として企業名を取得するというデータ検索を行うと、最初にインデックスを検索し、企業番号に該当するアドレスを取得します。
そして、テーブルの該当するアドレスを基にテーブルを検索して企業名を取得するという形になります。
この流れだけ見ると、
「インデックスを見てからテーブルを見るより、インデックスの検索のみでデータを取得するとより効率的なのではないか？」
と疑問を抱きます。
この疑問に対しては、インデックスに複数の検索項目を設定できる「複合インデックス」という機能があり、「複合インデックス」を用いるとインデックスに検索情報と実データを持つことになるので、インデックスの検索のみで完結しますが、そのインデックスを使用した検索時には検索項目が増えてしまい、取得時間が長くなる懸念が発生します。
「インデックス内で完結したい、ただ検索時間も長くしたくない…」 といった課題に対して、SQL Serverにはその問題を解決する機能が「付加列インデックス」です。
簡単に説明すると、インデックス内に検索条件としては使用しないが、データとしては持っておきたい項目を設定するインデックスとなります。
インデックスのイメージ
| 検索項目(検索用情報含む) | アドレス | 会社名(データのみ) |
|---|---|---|
| A00001 | 0x000001 | 企業A |
| A00002 | 0x000002 | 企業B |
| A00003 | 0x000003 | 企業C |
| ・・・ | ・・・ | ・・・ |
| A10001 | 0x002710 | 企業AAA |
複合インデックスを上記の設定とすることで、テーブルを介することなくインデックスの検索のみで会社名を取得でき、かつ検索項目のみを取得するため、取得を高速に行えるようになります。
ただし、注意点もあります。
すべてのインデックスに言えることですが、次のようなデメリットもあるため、インデックスを追加する際は、「本当に必要か」「この条件で問題ないかどうか」を想定し設定する必要があります。
- 大きすぎるデータ(画像データなど)が入る可能性がある項目をインデックスおよび付加列に設定しない
(検索の遅延の原因、同じデータをテーブルとインデックスに持つことになるため、データ肥大化の原因となる) - むやみにインデックスを増やさない
(テーブルにデータを増やす際にインデックスにもデータを追加するため、遅延の原因となるため)
必要なデータのみの抽出
インデックスの活用以外にも、パフォーマンス改善の方法があります。
さまざまなテーブルを結合するクエリの場合、基本はオプティマイザが自動的に実行環境で最適な実行計画を作成し、データ操作を処理します。
しかし、結合するテーブルが多くなった場合やデータ数や統計情報などの条件により、最適でない実行計画が作成されることがあります。
このような問題が発生した場合は、意図した実行計画を作成してもらうようなクエリを記述すると解決することがあります。
以下の方法は必要なデータのみを抽出するよう意図したクエリです。
テーブル内の全てのデータを抽出すると時間がかかるため、必要なカラムのみを抽出し時間を短縮する手法はよく知られていますが、その手法は結合するデータとして抽出する前の副問い合わせ文にも適用できます。
SELECT
A.ID
B.Name
FROM
A
INNER JOIN
B
ON
tempA.ID = tempB.A_ID
上記の場合だと、INNER JOINの際に抽出する必要のないカラムまで結合してしまい、時間がよりかかってしまいます。
必要なデータは決まっているため、結合する前からデータを抽出したテーブルで結合を行うと、より効率的な結合を行えることになります。
SELECT
tempA.ID
tempB.Name
FROM
(
SELECT
ID
FROM
A
)tempA
INNER JOIN
(
SELECT
A_ID,
Name,
FROM
B
)tempB
ON
tempA.ID = tempB.A_ID
上記の場合だと、INNER JOINを実施する前に不要なカラムは切り捨てられ、必要なデータのみが結合されるので、データ数を事前に間引くことができ時間が短縮されるような処理のクエリです。
この方法は不要なカラムに大きなサイズのデータがあるほど有効です。
レコード件数が少ない状態であれば微々たる差ですが、件数が多くなってくると結合回数も増えるため、効果が実感できるでしょう。
結合順序の固定
こちらも上記と同様、データ数や統計情報などの条件によりまれに結合順が大きなデータ同士のテーブルから走査する実行計画となってしまい、速度遅延の原因となる場合があります。
例として、テーブルA、テーブルAに紐づくデータが存在するテーブルB、テーブルBに紐づくデータが存在するテーブルCに各1000万件ずつレコードが存在する状態とします。
以下はテーブルAの条件指定を行うことで5万件絞り込めるというクエリになります。
SELECT
tempA.ID
B.Name
C.Date
FROM
(
SELECT
ID
FROM
A -- 1000万件あり
WHERE
STATUS = 1 -- 5万件まで絞れる
)tempA
INNER JOIN B -- 1000万件あり
ON
tempA.ID = B.A_ID
INNER JOIN C -- 1000万件あり
ON
B.ID = C.B_ID
上の状況であれば、AからBの結合を実施し、最後にCの結合を実施すると、 Aで既に5万件で絞られているため、BとCの結合時も5万件となり処理時間が短縮されますが、BとCの結合を先に実施すると、何も絞っていない状態となるため、1000万件の結合処理が発生し、大幅に処理時間が延びてしまうということになります。
そのようなことを防ぐため、オプティマイザが作成する実行計画を制御するヒント句として、「FORCE ORDER」というものがあります。
使い方としてはクエリ文の最後にOPTION「（FORCE ORDER）」を記載する形になります。
～
ON
B.ID = C.B_ID
OPTION (FORCE ORDER)
これを付与することでオプティマイザはクエリ文で記載した順に結合される実行計画を生成し、意図通りの結合を実行するようになります。
注意点としては、良くも悪くも、上から順に結合していくため、効率が悪い結合順序の場合でもその通りに実行計画を生成してしまうということです。
そのため、FORCE ORDERを使用する場合は結合順序が最適であることを精査する必要があります。
まとめ
オプティマイザは基本的に優秀なので、効率がいい実行計画を生成してくれるため、実行計画などを確認して適切な動きをしていない場合のみ、上記の対応をすべきです。
今回、調査した大容量データでの改善方法は以上ですが、DBのパフォーマンスチューニングには他にもさまざまな方法や観点があります。
この記事がお役に立てると幸いです。
