この記事はアイスタイルAdvent Calendar 2023 7日目の記事です。
アイスタイルでDBREを担当している@suzukitoです。
今年のアドベントカレンダーは、SQLパフォーマンスチューニングの話にする事にしました。
私のSQLパフォーマンスチューニングのやり方
一言でまとめると、
「DBMSの気持ちになって考える」
です。
DBMSの気持ちになってSQL文を解釈し、「ここがこうなってたらなぁ」と、彼が思っているであろうことを察してあげるのです。
DBMSのお気持ち
彼はこんな気持ちでSQLを実行していると想像します。
- インデックスが無ければ、オレは愚直にフルスキャンをやるだけだぜ
- インデックスが無ければ、オレは愚直にソートをやるだけだぜ
- インデックスがあれば、検索やソートに利用するぜ
- SQL文を見て思うことはあるが、俺からは言えないんだよ。察してくれよな！
彼は愚直に指示通りに仕事をしてくれます。
でもSQLの実行を速くしたいなら、彼の気持ちを察して仕事が楽にできるようにしてあげる必要がありますね！
DBMSのSQL実行手順
おおむね、このような順番で実行されます。
- SQL文の構文解析をする
- 実行者の権限を確認する
- テーブル定義を確認する
- インデックス定義を確認する
- 統計情報でデータの概要を確認する
- 複数の実行計画を作成してコスト計算する
- 一番コストの低い実行計画を実行する
SQLのパフォーマンスチューニングをする時は、DBMSの気持ちになって、同じステップをなぞってみましょう。
SQL文の構文解析をする
まずは、SQL文自体を確認します。
SQL文の構文解析をしているとき、彼はこう感じていることでしょう。
- WHEREの条件指定で列の方に関数を使ってると、インデックス使えないんだよなぁ
- LIKEで先頭に%使ってると、インデックス使えないんだよなぁ
- INの引数をSELECTでテーブルから取得するなら、JOINしてくれた方がありがたいんだけどなぁ
- SELECTの列にSELECT文書いてんのか、JOINしてくれた方がありがたいんだけどなぁ
- 最後にまとめてWHEREするより、JOINする時に指定してしてくれた方がI/Oが減るんだけどなぁ
- 最初っから結果を意識してJOINしたり列を選びすぎたりしすぎなんだよなぁ。まずは軸になるテーブルの主キー集合を見つけてくた方がI/Oが減るんだけどなぁ
などなど。お気持ちを察してSQL文を修正してあげましょう。
テーブル定義を確認する
テーブル定義を確認するとき、彼はこう感じていることでしょう。
- この列はNOT NULLだから必ず値が入ってるな。やりやすいぜ
- この列はNULL許可か、、、面倒だけど、集計や比較で考慮せんとな
- この列intだったのか、WHEREで文字列と比較してるから型変換しないとだわ。インデックスが使えなくなるんだよね
- この列tinyintでチェック制約付きか。値の範囲が絞れるから助かるな
- この列には外部キー制約付いてるのか。てことは別のテーブルの主キーだな
などなど。察してSQL文を、可能であればテーブル定義を修正してあげましょう。
インデックス定義を確認する
インデックス定義を確認するとき、彼はこう感じていることでしょう。
- WHEREの指定列にインデックスがないんか、フルスキャンだな
- JOINの結合列にインデックスがないんか、フルスキャンだな
- ORDER BYの指定列にインデックスがないんか、結果返す前にソートせんとだわ
- インデックスがあるのに比較するデータ型を間違えてるんか、、、残念！フルスキャン
- インデックスがあるのに列を関数で囲っちゃってるんか、、、残念！フルスキャン
- 値がユニークなら、ユニークインデックスで定義して欲しいわ。優先的に使うようにするからさ
などなど。インデックスがない＝フルスキャンです。
お気持ちを察してインデックスを作ってあげましょう。
主キー検索とユニークインデックス検索は最速のアクセス方法です。
インデックスの値がユニークかどうか判断できるのは設計者だけです。
ユニークで定義できるなら、積極的にユニークインデックスにしましょう。
判断材料になります。
統計情報でデータの分布を確認する
統計情報はテーブルやインデックスの行数、列の値の種類や分布を集計した情報です。
統計情報を確認するとき、彼はこう感じていることでしょう。
- カーディナリティが高いから、このインデックスは使えそうだ
- カーディナリティが高いけど、分布が偏ってるな。このSQLには向かないかも
- 2値しかない列が第1列に指定されてんのか、、、少ない方を見つける時にしか使えんな
などなど。SQLのパフォーマンスはデータの分布状況によっても変わります。
SQL文ばかり見てないで、データの確認もしてお気持ちを察してあげましょう。
まとめ
人がSQLを書くときの観点と、DBMSがSQLを実行する時の観点は異なっています。
人が書くSQLは結果のデータを意識して書かれます。
そのSQLをDBMSが実行する時には、より少ない労力で処理することが意識されます。
DBMSの気持ちになって、DBMSが仕事をやりやすい環境を作ってさしあげて、SQLの速度改善に取り組んで行きましょう！
参考資料
他にも書いてます！
過去の投稿でDB関連の記事はこちら。