フォルシアの技術ブログ

全248件

2021/12/07

これは、 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

フォルシア の技術ブログ

コンテンツ

フォルシアの技術ブログ