インデックスが効かずにSQLが終わらなくなった事象への対応記録

Views: 106

転職してから数ヶ月かけてとある機能を開発しました。
この間、本番へのリリースをしたのですが、恥ずかしながら
リリース後にSQLが終わらなくなるという経験をしました。

SQLのパフォーマンスチューニングの備忘録として
記事を書きます。

環境　Oracle19

バッチ処理で分析用のデータを作る機能の開発を担当していました。
バッチジョブを実行したところ、終わらない。

テスト環境では秒速で終わっていた処理が数分経っても終わらない。
これは何かがあると思ってSQLを調べてみたら、
数十億件が入ったテーブルをフルスキャンしていました。。。

なんでそうなったのかというと、
処理のメインとなるテーブル（テーブルA）とテーブルAに付与する情報をもつ
テーブルB（レコード数十億件！）を結合する際に、テーブルBのプライマリキーを
指定して検索していたのですが、インデックスが効かない書き方をしてしまいました。

登録年月日時分秒　がテーブルAのプライマリキーで、同じ情報をテーブルBも持っていました。
登録年月日時分秒　同志を結合キーとすればインデックス使用して検索されます。

ただ、テーブルAは登録年月日と時分と秒で列が分かれていました。
テーブルBの方は登録年月日時分秒は一つの列です。
ここで何をとち狂ったか、僕はテーブルAの登録年月日時分秒を　｜｜　で連結して、結合してしまいました。

[性能劣化を起こしたSQLはこんな感じ]

このミスのせいで、インデックスは使用されなくなり、見事フルスキャン。
永久に終わらないSQLを本番で流してしまいました。

このSQLのミスへの対応としては
テーブルBの登録年月日時分秒をSUBSTRで切り出すことで
インデックスを用いた結合が行えるように修正しました。

[修正したSQLはこんな感じ]

このてのミスはわかっているようで、実際にはまってみるまでは
全く気づきませんでした。
勉強になったな。

あと、テスト環境もそれなりに件数を入れておかないと
性能テストができませんね。