「SQLが遅い！」は、もう卒業。実行計画とインデックスを使いこなす3ステップ高速化術

皆さんアプリやシステム開発において、避けては通れない言語がありますよね。

タイトルにも出ている通り、そうです。SQL（Structured Query Language）です。

2~3年目にもなってくとすらすらとクエリを書くことにも慣れ、複雑なSQLを組む機会が増えると思います。

そして、大規模な開発にもなってくると、何十ものマスタテーブル、何百ものトランザクションテーブル、何万件ものデータを扱うことになってくるのではないでしょうか。

そんな時にぶち当たる壁の一つとして、SQLの実行速度ではないでしょうか。

「SQLが遅い」「SELECT文で数十秒かかってしまう」――そう感じたとき、何をどう見直せば良いか分からず、途方に暮れたことはありませんか？

パフォーマンス改善は、闇雲にSQLを書き直したり、インデックスを作るだけではうまくいきません。

必要なのは「どこが遅くなっているのか」を見抜き、「それにどう対処するか」を知ることです。

ということでこの記事では、SQLが遅くなる原因を特定し、実行計画とインデックスを活用して具体的に改善していく方法を3ステップで解説していきます。

1. 実行計画で「無駄な読み取り」が発生している箇所を特定する

SQLが単独実行で遅い原因のほとんどは無駄な読み取りが発生しているからです。
無駄な読み取りが発生している箇所を特定するために見るのが「実行計画」です。

そもそも実行計画とは？？

→実行計画とは、RDBMSがどのようにSQLを処理するかを決めたものです。
つまりは、どのインデックスを使い、どれくらいデータを絞り込んだかなどの情報を見ることができます。

実行計画は慣れないと難解に感じますが、以下の２点のポイントで見ます。

• 「Scan」と書かれたオペレータが出ているテーブルはないか

　　⇒　Scanとは、テーブルの全ての行を読んでいるという意味です。
　　本当に全件読む必要があるのか見直す必要があります。
　　必要ないデータを読んでても仕方ないですもんね。

• WHERE句の条件があるにもかかわらず、そのテーブルの件数とほぼ同じ件数の読み取りが発生しているテーブルはないか

　　⇒　条件句があればその条件に合致するデータのみを抽出して処理しますが、何らかの原因で絞り込みができていないことが考えられます。

この２点に該当するテーブルを探し、件数が大きいテーブルから対処していきます。
一般的にScanはよくないと言われますが、数百件程度の少ない件数であればScanしても一瞬なので、無視しても問題ありません。

Scanについては適材適所で潰していくようなイメージです。

2. 読み取りが多いテーブルに最適なインデックスがあるかを見直す

先ほどのステップで実行計画を活用し、無駄な読み取りが発生しているテーブルをあぶり出す方法をお伝えしました。

次のステップとしてはその原因を探っていきます。

多くの場合、インデックスが適切に設定されていないか、使われていないことによるものです。

インデックスはどのように設定するべきか、例を見ながらインデックスの特性を理解しましょう。

USERというテーブルに、次の３列から成る複合インデックスIDX_1を作成します。

順番	列名	データ型
1	NAME	varchar型
2	AGE	int型
3	ADDRESS	varchar型

　IDX_1による絞り込みが可能なSQLはどのようなものか、４つの例を紹介します。

例１

SELECT 　　xxx FROM 　　USER WHERE 　　NAME=’AAA’ 　　AND AGE=123 　　AND ADDRESS=’CCC’

⭕️　IDX_1の全ての列がWHERE句の条件に書かれているため、”NAME”、”AGE”、”ADDRESS”の全ての列で絞り込めます。

例２

SELECT 　　xxx FROM 　　USER WHERE 　　NAME=’AAA’ 　　AND ADDRESS=’CCC’

🔺　IDX_1の一部（age）の条件がないSQLです。
この場合、”NAME”のみが絞り込みの対象となり、条件にない”AGE”以降の列である“ADDRESS“は絞り込みが効きません。
“NAME”だけであまり絞り込めないケースでは、“NAME”、”ADDRESS”という複合インデックスを新たに追加してもよいでしょう。 

例３

SELECT 　　xxx FROM 　　USER WHERE 　　age=123 　　AND address=’CCC’

❌  　”NAME”が条件にないため、IDX_1は使われません。
インデックスの重要な特性として、複合インデックスの先頭の列が条件句に含まれない場合、そのインデックスは使われません。
IDX_1の場合、先頭は”NAME”なので、”NAME”が条件にないSQLでは使われないことになります。
このようなSQLも実行される場合は、“AGE”、”ADDREESS”複合インデックスを作ることを考えます。

例４

SELECT 　　xxx FROM 　　USER WHERE 　　NAME=’AAA’ 　　AND AGE > 999 　　AND ADDRESS=’CCC’

🔺　このSQLは、IDX_1の２列目であるageが不等号による範囲検索になっています。
IDX_1の列が条件句に全て書かれているので、一見”NAME”、”AGE”、”ADDRESS”の全てで絞り込みがされそうですよね。
ただ、実際には範囲検索となっている列以降の列（この場合は“ADDRESS“）では絞り込みがされません。
そのため条件が範囲検索の列は、インデックス列の並び順が後ろになるように設計するのがコツです。
特に日付列が範囲検索されることが多いです。
日付列をインデックスに含める場合は並び順に注意しましょう。

　このように、複合インデックスは列の並び順が重要ですので、よく使われる条件を理解した上で設定してください。

3. インデックスが効くようにSQLの書き方を変える

　インデックスを適切に設定していても、SQLの書き方次第でインデックスでの絞り込みがされないケースがあります。インデックスが効かない例を紹介します。

①列に対して演算をしている／関数を使っている

COL2に対してインデックスが作成されている前提の例

❌悪い例：SELECT xxx FROM USER WHERE AGE * 2 = 100 ⭕️改善例：SELECT xxx FROM USER WHERE AGE = 100 / 2

悪い例は、100とイコールになるデータを探すためにテーブルの全ての行で”age * 2″という演算を行う必要があります。
この結果、テーブルスキャンとなり、インデックスの効果はありません。
テーブルの列はそのままの形で条件に書き、パラメータやスカラー値側で演算を行うようにしましょう。

次のように関数を使っている場合も同様です。

“NAME”に対してインデックスが作成されている前提の例

❌悪い例：SELECT xxx FROM USER WHERE SUBSTRING (NAME, 1, 1) = ‘A’ ⭕️改善例：SELECT xxx FROM USER WHERE NAME LIKE ‘A%’

②列とパラメータのデータ型が一致していない

varchar型のCOL1に対してインデックスが作成されている前提の例

DECLARE @num INT = 123 ❌悪い例：SELECT xxx FROM USER WHERE NAME = @num ⭕️改善例：SELECT xxx FROM USER WHERE NAME = CONVERT(varchar, @num)

悪い例は、データ型が異なるものを比較しています。
この場合も”NAME“を全て型変換してから比較するので、テーブルスキャンの原因になります。
列とパラメータのデータ型は必ず一致させ、型変換が必要であれば変数側を変換します。

③中間一致、後方一致を使っている

“NAME”に対してインデックスが作成されている前提の例

❌悪い例：SELECT xxx FROM USER WHERE NAME LIKE ‘%A%’ ❌悪い例：SELECT xxx FROM USER WHERE NAME LIKE ‘%A’

中間一致、後方一致にもインデックスは効きません。
これに関しての改善策は難しく、他の絞れる条件を追加することで対処可能か考えます。

④OR条件を使っている

COL1に対してインデックスが作成されている前提の例

❌悪い例：SELECT xxx FROM USER WHERE NAME = ‘AAA’ OR NAME LIKE ‘Z%’ ⭕️改善例：SELECT xxx FROM USER WHERE NAME = ‘AAA’ UNION ALL SELECT xxx FROM USER WHERE NAME LIKE ‘Z%’

OR条件がある場合もインデックスは効きません。
改善例のようにUNION句を使ってSQLを分割してOR条件をなくせば、インデックスが効くようになります。

まとめ：SQLのパフォーマンスの要はインデックス

SQLのパフォーマンス劣化の原因の大半はインデックスです。

Nested Loopsなどの結合アルゴリズムの違いでも性能差が出ると聞いたことがあるかもしれませんが、結合アルゴリズムもインデックスが効いているかどうかで変わります。

そのため、適切なインデックスは存在するか、それがきちんと使われる形でSQLが書かれているかを押さえておけば、大半の遅延は防ぐことができます。

この3ステップを踏めば、あなたのSQLは必ず改善されます。
小手先のチューニングではなく、インデックスを作って最大限活かすという基本に忠実なチューニングを心がけてみてください。