■ 1. 記事の概要
- SmartHRのプロダクトエンジニアが実装した巨大なSQLにおいて活用したPostgreSQLの機能を紹介
- 一般的な構文の影に隠れがちだが非常に助けになった機能をピックアップ
- ORMだけでは解決できない課題やパフォーマンスチューニングをする際の一助となることを目的とする
■ 2. 複雑なクエリを保守しやすくするための基本
- WITH句とウィンドウ関数の知識:
- CTEとウィンドウ関数は現代的なSQLを書く上で必須
- SQLの自由度は理解度で劇的に変わる
- WITH句CTE:
- SQL内で一時的なテーブルに名前をつけて定義できる機能
- 古いスタイルのSQLではサブクエリをネストして書くことが一般的だったがネストが深くなると内側から外側へ読み解く必要があり脳のメモリを消費する
- WITH句を使うと処理を上から下へ流れるように記述できる
- アプリケーションコードでいう変数や関数に切り出して名前をつける感覚に近い
- ロジックをステップごとに分解して記述できる
- ウィンドウ関数:
- SUMやRANKなどの関数にOVER句を組み合わせることで実現する機能の総称
- 通常のGROUP BYとともに集約関数を使うと行がグループごとに1行に圧縮される
- 個別の明細行は残したいが全体の集計値も横に添えたい場合にウィンドウ関数を使用
- OVER句が出てきたらその手前にある関数がウィンドウ関数というグループに属していると理解すればよい
- 明細行を残したまま集計結果をすべての行に持たせることができる
- SUMは通常は集約関数だがOVERをつけるとウィンドウ関数として振る舞う
- DENSE_RANKなどウィンドウ関数としてのみ振る舞う関数も存在
- 行を集約しない特性がアプリケーションが必要とする複雑なデータ構造を1回のクエリで作る上で非常に重要
■ 3. 任意のIDリスト順にソートする方法
- unnest関数とARRAYとWITH ORDINALITYとCTEのWITH句を組み合わせて使用
- WITH ORDINALITYのWITHはCTEのWITH句とは全く別物
- すでにあるIDのリスト順の通りに検索結果を返したい要件が発生することがある
- WHERE id INのように検索すると返却されるレコードの順序はバラバラになる
- リストが並び替えでしか使われていない場合はarray_position関数を使う選択肢がある:
- array_positionは並び替え済みの値の配列と今の行の値という形式でその値が配列の何番目にあるかを返す関数
- ORDER BYを書くことが可能
- シンプルだがクエリの複数箇所でそのIDリストを使いたい場合は共通化した方が良い
- IDリストが数百から数千件と長大な場合:
- 配列リテラルをクエリのあちこちに埋め込むとSQL全体のサイズが肥大化
- 人間にとって読みにくいだけでなくDBのパーサーに無駄な負荷をかけ通信量も増大
- パフォーマンスの観点でも好ましくない
- unnest関数とARRAYとWITH ORDINALITYを使うとCTEのWITHと組み合わせて記述可能:
- 長大なリストの登場は一回だけで済む
- 長いリストはCTEで一度だけ定義しテーブルとして使い回す
- WITH ORDINALITYを使うと元のデータの横に連番が自動的に付与される
- AS t関数とカラム名でテーブルの別名とカラムの別名を同時に定義する構文を使用
■ 4. JOINで増えた行へマークするDENSE_RANK
- ランキング関数は売上ランキングのような用途が真っ先に思い浮かぶが実務特に履歴管理を含む複雑なデータを扱う場面では違った用途で活躍
- 1対多の1:NのJOINを含む履歴データから最新の1件N行すべてを取得したいケースで使用
- DENSE_RANKは同率順位があった場合番号を飛ばさずに詰めて採番
- 評価完了日が同じであればJOINによって行が増えていてもそれらは全て同率1位として扱われる
- 自分で名付けたrank_numで絞り込みをすれば各社員の最新のプロジェクトを取得可能
- 絞り込みはウィンドウ関数と同じ階層ではWHEREに書けず別のCTEなどで書かなくてはいけない
- DENSE_RANKに類似した関数としてROW_NUMBERやRANKがあるがいずれも採番のルールが違うため今回のケースでは適さない
■ 5. BOOL_ORウィンドウ関数
- グループ内のデータ全体を見渡してどれか一つでも条件を満たせばフラグを立てたい場合やどれか一つでも条件を満たしているグループのみ絞り込みたいケースでよく使用
- PostgreSQLならBOOL_ORをウィンドウ関数として使うことで自然言語に近い形で記述可能
- BOOL_OR論理和という名前の通りパーティション内のどれか一つでもtrueであれば結果はtrueになる
- グループ内のすべてがtrueのときだけtrueを返したい場合はBOOL_AND標準SQLだとEVERYが使用可能
■ 6. ウィンドウ関数に空のOVER関数を使って全体集計
- 通常ウィンドウ関数といえばPARTITION BYで区切って使うものだが何も指定しないことで実現できる便利な挙動がある
- 空のOVER関数とその使い道:
- Webアプリケーションで一覧画面を作る際に必ずと言っていいほど実装するのがページネーション
- 現在のページの20件と全ヒット件数1530件などの両方が必要
- 通常これを実装するには2回のクエリを発行するのが定石
- ウィンドウ関数を使えば1回のクエリで完結可能
- OVERの中にPARTITION BYを書かずに空っぽにすると検索結果全体を一つの大きなグループとして扱える
- パフォーマンス上の注意点:
- クエリの回数を減らせるというメリットがあるが常に最適解とは限らず最適解となることは少ない
- 単純なCOUNTであればインデックススキャンのみで高速に完了するケースでもウィンドウ関数COUNT OVERを使うとテーブル本体へのアクセスが必要になりパフォーマンスが落ちる可能性
- インデックスが効く単純な検索の場合はクエリを2回に分けたほうが速い場合が多い
- 複雑な検索でフルスキャンが避けられない場合は1回で済ませるこのテクニックが有効なことがある
- COUNT OVERはJOINで膨れ上がった重複レコードもすべてカウントしてしまう
- JOINで生まれた重複を除いた純粋な母数が知りたい場合には次に紹介する方法が有効
- ページネーション用にDENSE_RANKの値を使っていたときついでにJOINで膨れる前の件数を出す応用:
- ページネーションの判断基準を作るために既にDENSE_RANKの数値を利用していた場合その数値から全体の件数も出す
- 1人のユーザーが複数の行履歴を持っているデータにおいて全行数ではなく全ユーザー数をベースに1ページあたり10ユーザーのページネーションを実装したい場合
- 単にCOUNT OVERをすると行数分が返ってきてしまう
- LIMIT 10をかけると10行おそらくユーザーが重複し10ユーザー未満の数しか含まれないしか取れない
- DENSE_RANKで振った番号を活用しMAXを使うことでJOIN済みの結果セットが返ってくる状態でも実質のページネーションとともにその母数がいくつかを導出可能
- COUNT DISTINCT user_id OVERでも全ユーザー数は出せるがページネーションの絞り込み用として既にDENSE_RANKを計算していたためその計算結果を再利用するほうがクエリとして無駄がないと判断
■ 7. JOIN済みの結果をJSONにするjsonb_agg
- やむをえずアプリケーション層でほぼ生SQLを書く時にあるあるなのが1対多のテーブルをJOINしてデータを取得した結果行の増幅が発生してアプリ側での再整形が必要になること
- アプリケーション層で必要になる処理:
- DBから大量の行を取得
- forやeachでループを回す
- 1つ前のIDと今のIDをチェックし値が違ったら新しいユーザーオブジェクトを作る
- 値が同じならタグ配列に値をpushする
- IDが変わったかチェックして何かしらを行う処理はコードが複雑になりがち
- 素朴な一対多が一つだけあるならまだ大丈夫だが二重三重に一対多の関係がありJOINされたレコードを頭に入れて再構築するのは大変
- アプリケーションサーバーよりもDBサーバーの方がスケールしにくいためこの手法を嫌う人もいるがDB側でJSONに集約してから値を返してもらうのも一つの手
- json型とjsonb型:
- PostgreSQLにはJSONを扱うためのデータ型としてjson型とjsonb型の2種類が存在
- json型は入力されたテキストをそのまま文字列として扱う型で書き込みは速いが検索や加工のたびに構文解析が必要なため参照は遅い
- jsonb型は入力されたテキストをバイナリ形式に分解変換した型でjsonbのbはbinaryのb
- jsonb型は書き込みは少し遅いが参照や検索が非常に高速でカラムとして追加する場合はインデックスGINを貼ることも可能
- json型を使うのはレアなケースで基本的にはjsonb型を使うと覚えておけば大丈夫
- jsonb_agg集約関数で行を配列に畳み込む:
- 増幅された行を畳み込むのにはjsonb_aggを利用
- GROUP BYで集約された複数の行を一つのjsonb型の配列の値に変換する集約関数
- 集約関数内のORDER BYが重要なテクニック
- これを書かないと生成されるjsonb型の配列の値の中身の順序はランダムになる
- 配列内の順序を保証したい場合関数の中でソート指定を行う必要がある
- アプリケーション側でIDが変わったかどうかを判定するループ処理を書く必要はなくなる
- DBから返ってきたJSONをそのまま利用するだけになりロジックが劇的にシンプルになる
- jsonb_aggは集約結果をすべてメモリ上で構築してから返却するため数万から数十万行といった大量データを1つの値にまとめようとするとDBサーバーのメモリを急激に圧迫する恐れがある
- jsonb_build_arrayでjsonbの配列を作る:
- jsonb_aggが複数行をまとめてjsonb型の配列の値にするのに対しjsonb_build_arrayは指定したカラムや値をその場でjsonb型の配列の値にする関数
- 型合わせのために利用
- ある配列jsonb型と別のID単一の数値をUNION ALLで結合したかったが型が違うとエラーになる
- 単一のIDもjsonb_build_arrayで配列に変換することで強引に型を揃えてUNION ALLした
- データの形式を揃えたい時などに利用可能
- 生成したjsonb型の値に対する検索:
- 生成したjsonb型の値に対して後から検索をかけることも可能
- PostgreSQLのjsonb型は非常に優秀で包含演算子などを使用することで生成後の値に対しても検索可能
- パフォーマンスには注意が必要
- UNIONされた結果やCTEで動的に生成されたJSONデータに対してはインデックスを効かせることができない
- すでに対象件数が十分絞り込まれているコンテキストだったため許容したが大量データに対して無邪気にインデックスの効かない検索処理を行うと事故につながるリスクがある
■ 8. おわりに
- 紹介したテクニックはすべて実際のプロダクト開発におけるとある1つの巨大クエリに詰め込まれたエッセンス
- 無闇に複雑で巨大なSQLを書くことが正義ではない
- ミッションクリティカルな機能や多くの人が頻繁に触れる箇所であれば要件の再検討テーブルの非正規化アプリ側での処理分割などを選んでいた
- プロダクトの開発背景パフォーマンス要件開発速度それらを天秤にかけあえてDB側で完結させることが最適解となる瞬間は存在
- PostgreSQLには強力な機能が備わっていることを知っていただければ幸い
- 最終的に9つのCTEが連なることとなった