どうも、こんにちは。
SQLのパフォーマンスについて、最近勉強してみたので、忘れないようにメモ書きをしておきます😊
検証内容
INDEXの有無によってどれほどパフォーマンスが変わるのかを検証します。
前提条件・環境
- MySQL
- GUIツール：「Sequel Pro」でSQLを実行しています。
- Aテーブルには、約12万レコード
- Bテーブルには、1,900レコード
※今回使用するテーブルをA・Bと呼称します。
テーブル構成
Aテーブル 12万
| カラム | 備考 |
|---|---|
| ID_A | primary key |
| ID_A2 | 結合条件に使用します。 最初は、インデックスを設定していません。 |
Bテーブル 1,900
| カラム | 備考 |
|---|---|
| ID_B | primary key 結合条件に使用します。 |
| ID_B2 | INDEX_B2という名前のINDEXが設定されている。 |
検証実施 INDEX追加前
まずは、どれだけ時間がかかっているかSQLを実行してみましょう。
SELECT count(*)
FROM B1900
LEFT OUTER JOIN A120000
ON B1900.ID_B = A120000.ID_A2;こちらのSQLでは、14.8秒かかりました。
うーん…こんなに時間がかかってしまっては、問題ですね。
SELECT count(*)
FROM A120000
LEFT OUTER JOIN B1800
ON A120000.ID_A2 = B1800.ID_B基準となるテーブルを反対にしてみたところ、こちらのSQLでは、0.170秒かかりました。
これはだいぶ差がありますね。
「EXPLAIN」を使おう
なぜこれだけ差が出てくるのでしょうか？
「EXPLAIN」を使って、SQL1とSQL2の実行計画を比較してみましょう。
EXPLAINでは、オプティマイザの実行計画を見ることができます。
オプティマイザとは、発行されたSQLを効率よく処理してくれる機能です。
基本的には、一番最適な方法で実行してくれています。
EXPLAINは、発行したSQL文のSELECT句の前に「EXPLAIN」をつけるだけです。
EXPLAIN SELECT count(*)
FROM B1900
LEFT OUTER JOIN A120000
ON B1900.ID_B = A120000.ID_A2;それぞれのEXPLAINの実行結果を比較してみましょう。
今回、注目する項目は、次のとおりです。
| type | ・index：遅い ・ALL：とても遅い ・eq_ref：速い(結合ではこれが最適) ・ref：primary keyやunique key以外のINDEXを使用することを表しています。今回は、eq_refの次に最適です。 |
| possible_keys | オプティマイザが使用可能だと判断したINDEXがここに表示されます。 |
| key | possible_keysに表示されているINDEXの中から、実際に使用するINDEXがここに表示されます。 |
EXPLAINの実行計画の見方について、詳細を知りたい方は、以下の記事をご覧ください。
比較してみると、SQL1では、A・BのどちらのテーブルでもINDEXが使用されていないことがわかります。
Bテーブルのkeyには、結合条件に使用していないカラムの「INDEX_B2」が使用されてしまっています。
一方で、SQL2では、Bテーブルのpossible_keysに使用できると判断されたINDEXにPRIMARYとあり、実際に使用するINDEXとして、keyにPRIMARYが表示されています。
結合条件に使用しているカラム:ID_B(primary key)のINDEXが使用されていることがわかります。
つまり、INDEXの使用の有無によって、これだけの実行速度の差が出てしまっているんですね。
しかし、なぜSQL1では、INDEXが使用されなかったのか・なぜオプティマイザがこの実行計画を最適だと判断したかはわからないですね…
INDEXを追加
上記の時点で、INDEXの使用有無により差が出ることはわかりましたが、次は、Aテーブルの結合条件に使用している「ID_A2」にINDEXを設定し、検証してみましょう！
構文 ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
追加 ALTER TABLE A120000 ADD INDEX INDEX_A2(ID_A2);早速、INDEX追加後のSQLを検証していきましょう。
まずは、上記のSQL1を再度実行してみましょう。
SELECT count(*)
FROM B1900
LEFT OUTER JOIN A120000
ON B1900.ID_B = A120000.ID_A2;結果は、14.8秒 → 0.0509秒でした。とても速くなりました！
では、INDEX追加前後を比較しながら、EXPLANも見てみましょう。
possible_keysに、追加したINDEXの「INDEX_A2」が使用可能となり、実際に使用するINDEXとして、keyに「INDEX_A2」が追加されていることがわかります。
次にSQL2を実行してみましょう。
SELECT count(*)
FROM A120000
LEFT OUTER JOIN B1800
ON A120000.ID_A2 = B1800.ID_B結果は、0.170秒 → 0.136秒でした。こちらも少し速くなりましたね。
SQL2では、元々結合条件のBテーブルのカラムのINDEXを使用していたので、実行速度は速かったですが、今回追加したAテーブルのINDEXも使用することで、さらに速くなりました。
Aテーブルの追加後のtype：indexの実行速度は遅いですが、追加前のALLはとても遅いでしたので、少し速くなったのかもしれません😅
ちなみに、内部結合の場合でも試してみました。
SELECT count(*)
FROM B1800
INNER JOIN A120000
ON B1800.ID_B = A120000.ID_A2;
結果は、
INDEX設定前：0.0698秒
INDEX設定後：0.0518秒
こちらも若干早くなりました。
まとめ
今回実行したSQLでは、全て速度が速くなりました。
実行速度に関しては、WHERE句に指定するカラムにINDEXを設定することで、速くなることは、学習上でもよく聞くことでしたが、今回は、結合条件でもその効果は絶大でした。
ポートフォリオ等の個人の開発では、あまり実行速度に気を配った開発は意識しづらいですが、業務では実行速度を気にすることは、とても重要です。
ぜひともINDEXを使いこなしていきたいですね！
