そのSQL、もっと速くなりますよ。

これは、FORCIA Advent Calendar 2021の７日目の記事です。

こんにちは。エンジニアの長尾と申します。旅行系アプリの開発・運用をしています。

今年の秋頃からSQLの高速化に取り組み、計600分以上の短縮に成功しました。

そのなかで特に効果の大きかった施策を４つほどご紹介させていただきます。

１. たった25行のコードの追加で、165分短縮。

「このTSVファイル20Gを超えているけど、一体これは何なんだ......。」

これを見たときに感じたことは「このデータは本当に全部使われているのだろうか」ということでした。

そこで、そのTSVファイルをもとに作られたテーブルがどこでどう使われているか調査しました。

いろいろな箇所でいろいろな使われ方をしていましたが、ひとつ共通点がありました。

必ず特定のテーブルとINNER JOINされている。

SELECT
	hoge
FROM
	巨大テーブル
INNER JOIN とあるテーブル

そしてJOIN後のレコード数は1/10以下になっていました。

と、いうことは90％以上のレコードは全く使われていなかったのです。

そこで、最上流である元データからTSVを生成する箇所で、そのテーブルをINNER JOINしました。

すると、20Gあった巨大TSVファイルは２G程度になり、関連する全ての処理が高速化しました。

それだけではありません。

DBのダンプ、転送、DBのリストアなども高速化し、計165分の大幅な短縮となりました。

まとめ：不要なレコードは最上流で削ろう

２. 100分かかっていたプレウォームを15分に。

アプリケーションによってはプレウォームを実施していることがあるかと思います。

これは、いろいろなSQLを前もって投げておくことで、
キャッシュをためてオンラインでのパフォーマンスを上げることが目的です。

このプレウォームに時間がかかっていたので原因を調査しました。

そして、SQLをいじって試行錯誤しているときに、あることを発見しました。

WHERE あるパラメータ = hoge

ある一つのパラメータをWHERE句から消すと超高速になったのです。

「このパラメータ消したい」

しかし、当然ながら必要だから書かれているわけで、消すわけにはいきません。

どうしよう......。消したいけど消せない......。消せないけど消したい......。

あ！

嗚呼！

テーブルのほうを分ければいいんだ。

そのパラメータの取り得る値は、１〜10といった具合に範囲が決まっており、
さらにユーザーは必ずその一つを選ぶ（必須かつ単数）という形式だったのです。

もとのテーブル → 新テーブル1, 新テーブル2, 新テーブル3 ...... 新テーブル10

と、分割し、レコードもそれぞれの新テーブルに分割して登録しました。

これにより晴れてWHERE句から消すことができたのです。

旧クエリ
SELECT hoge FROM もとのテーブル WHERE あるパラメータ = '2';

新クエリ
SELECT hoge FROM 新テーブル2 WHERE あるパラメータはもうない;

プレウォームが早くなっただけでなく関係するオンラインクエリもおよそ５倍速になりました。

まとめ：必須かつ単数のパラメータの処理が重くて困ったら、テーブルを分割したら解決するかも

３. ループにご注意。何度も繰り返されるSQL。

こんなSQLがありました。

５回ループ {
CREATE TABLE 新テーブル_{1~5} AS
SELECT
	hoge
FROM
	Aテーブル
INNER JOIN Bテーブル
INNER JOIN Cテーブル
INNER JOIN Dテーブル
INNER JOIN Eテーブル_{1~5}
;
}

Eテーブルが１〜５で分かれていて、それに対応して新テーブルも５つに分かれます。

どうにかして計算量を減らしたい。さてどうしたものか。

......。

CREATE TEMPORARY TABLE 一時テーブル AS
SELECT
	hoge
FROM
	Aテーブル
INNER JOIN Bテーブル
INNER JOIN Cテーブル
INNER JOIN Dテーブル
;

５回ループ {
CREATE TABLE 新テーブル_{1~5} AS
SELECT
	hoge
FROM
	一時テーブル
INNER JOIN Eテーブル_{1~5}
;
}

こうですね。

これで、BCDテーブルをAテーブルにJOINする回数が１回で済みました。

これだけで1/5強の処理時間になりました。

これは簡単なことですが、案外やってしまいがちだと思います。

そして、ちゃんと対応したときの効果は絶大です。

面倒くさがらずに一時テーブルを作ろう、というお話でした。

まとめ：ループを書くときは、本当にループさせるべきもの以外は外に出す

４. 超難解なSQLでも諦めないで。高速化できます！

この勢いでどんどん高速化してやろう、と意気揚々、次なるターゲット（遅いSQL）を眺めていました。

が、分からないのです。

何が書いてあるのか、全然。

SELECT文のなかで多重ループがされていて、そのループのなかでも関数がガンガン呼ばれていて......。

しかもSELECT自体も多階層になっていて、何が何やらという状態でした。

かろうじて階層の一番深い所の処理だけは理解できました。

SELECT
	超複雑な処理
FROM (
	SELECT
		超複雑な処理
	FROM (
		SELECT
		 	超複雑な処理
		FROM (
			-- かろうじて理解できた部分、ここから
			SELECT
				hoge
			FROM
				Aテーブル
			INNER JOIN Bテーブル
			INNER JOIN Cテーブル
			INNER JOIN Dテーブル
			INNER JOIN Eテーブル
			-- かろうじて理解できた部分、ここまで
		)
	 )
);

こんなSQLです。

しかも悲しいことには、時間がかかっている処理は、まさにその超複雑な処理の部分なのです。

さすがにこれは高速化は無理だよなぁ、と一度は諦めました。

が、しかし、口惜しい。

このSQLを見るたびに、どうにかしてやっつけられないだろうか、とずっと思っていました。

そんなあるとき、ほかのSQLで、一時テーブルをANALYZEしているものを見掛けました。

一時テーブルのためにわざわざANALYZEなんかして、一体何をやっているんだ......。

あ！

これだ！

最深部のSQLを一時テーブル化して、ANALYZEした状態で超複雑な処理を迎えれば......。

CREATE TEMPORARY TABLE 一時テーブル AS
SELECT
	hoge
FROM
	Aテーブル
INNER JOIN Bテーブル
INNER JOIN Cテーブル
INNER JOIN Dテーブル
INNER JOIN Eテーブル
;

ANALYZE 一時テーブル;

SELECT
	超複雑な処理
FROM (
	SELECT
		超複雑な処理
	FROM (
		SELECT
		 	超複雑な処理
		FROM 
			一時テーブル
	 )
);

なんとこれだけで25％の高速化に成功しました。

まとめ：困ったときのANALYZE

さいごに

やってみると意外と改善の余地はたくさんあるものだなぁと思いました。

SQLが速くなると気持ちが良いですね。

最後に各項のまとめを再掲しておきます。

• 不要なレコードは最上流で削ろう
• 必須かつ単数のパラメータの処理が重くて困ったら、テーブルを分割したら解決するかも
• ループを書くときは、本当にループさせるべきもの以外は外に出す
• 困ったときのANALYZE