あなたの遅延はどこから? SQLから! 〜患部に止まってすぐ効くSQLレビューチェックリスト 年初め特大サービス号〜

あけましておめでとうございます!

今年は異世界放浪メシのアニメが放送されるらしいので楽しみなバックエンドの原田 (tomtwinkle)です。

内部で運用しているSQLレビューチェックリストの一部を抽出し思いつきで追記して行った結果、結構な分量になってしまいました。 暇な時でも流し読みして頂けるとありがたいです。

Motivation

皆さんはRDB使ってますか?

恐らく大体のプロダクトはAurora PostgreSQL MySQL SQLite 等何かしらのRDBを利用しているかと思います。

RDBのSQL発行にかかる時間は特にAPI全体のLatencyに直接関わる最大のボトルネックになることが多く、 DBのIndexやSQLを1つ見直しただけでAPIのレスポンスが100倍速になることもザラにあります。

RDBの厄介な所は100件や1000件等の少量のデータでは問題なく動作していたSQLが、 10万件、100万件とデータが増えていったタイミングで突然遅くなるということがよく発生します。

このようにプロダクト全体のユーザー体験に関わる根幹部分であるため、Repository周りのコードが変更された際に、そのロジックや発行するSQLが問題ないか、PullRequestが出された段階で事前にパフォーマンスを考慮したレビューが出来ているかどうかは今後の保守性を考えるにあたって重要な観点です。

然しながら昨今はORMを利用したプログラミングが当然になってきており、 Martin Fowler氏のPofEAAで言うところのActive Record パターンでDB設計を行うことが増えてきました。

Active Record パターン自体はメリット・デメリットあるのですが、結果的にアプリケーションとデータベース自体の密結合が進み、 それがプロダクトの成長に伴うユースケースの増大により複雑化し、n+1問題によるパフォーマンスの劣化、TableDataGatewayのmockを利用せずに毎度DBを参照する事によるテスト実行時間の増大、それら統合的な課題の蓄積によるプロダクト自体の成長の鈍化に繋がりかねないためCleanCodeの観点以外でもDBパフォーマンス観点でレビューを行うことで事前にパフォーマンス劣化を防ぐ意味を込めて今一度DatabaseやSQLチューニングについて理解するべきだという想いから本記事を執筆しています。

ちなみに話が長くなりそうなので Active Record や Table Data Gateway 等のPofEAA関連の話は今回はしないです。 あくまで一般的なDatabase設計、SQLチューニングについてだけ書いていきます。

PofEAAについては読んだことがない人でもバックエンド書いてる人ならあー分かるって感じの内容なので連休にパパっと読んで見ると良いかもです! 今調べたら本人公認の日本語翻訳サイトもあるんですね。凄い。

また、今回の記事はRDBについて書かれた内容となります。 MongoDBやDynamoDBのようなスキーマレスなデータベースはまた違ったレビュー観点が必要になるので今回は除外します。 特にDynamoDBのattribute updateはattribute自体のデータ量が増えてくると想定外に更新の時間がかかったりするので、きちんと計測しないと記事化は出来ないのでまた今度。

チューニングされた凄いSQLをDatabaseに流し込む絵 Painted by Stable Diffusion

SQLレビュー観点

大きくSQLが変更される修正の際にはEXPLAINをレビュー内容に加える

実際の所、どのようにRDBMSがSQLを解析してデータを取得しに行っているかを理解出来ていればデータ量が増えて突然遅くなるSQLというのは事前にある程度察知出来ます。 RDBMSにはRDBMS自身がどのようにSQLを解析してデータを取得しているかを調べるためのEXPLAIN機能が搭載されており、 SQLのレビューにおいてSQLだけ見せられてもよほどヤバいSQL以外は見ても判断つかないことが多いので、 レビュアーはレビュイーに対してきちんとEXPLAINを取らせるのが最優先事項です。

EXPLAIN SELECT id, name FROM users WHERE nickname = 'tomtwinkle'

例のような簡単なSQLでもnicknameにINDEXを貼っているのかどうかでEXPLAINが変わってきます。 またEXPLAINはデータ量によって変わるので計測する際には最低でも数百〜数千件程度のデータは必要になってきます。 Staging環境に対して定期的にランダムな値でe2eを走らせるなどして日々データを貯めていくのが良いでしょう。

検索のキーにINDEXを使用しているか

SQLを利用する際の基本中の基本ですが、検索キーにINDEXが貼られているのか 検索の際にどのINDEXが利用されるのか意識してSQLを書きましょう。

WHERE句だけではなくORDER BY句やGROUP BY句のキーにINDEXが貼られているかどうかも重要です。

SQL発行回数がN+1(1+N)の構造になっていないか

SQLの問題というよりアプリケーション側の設計の問題

N+1問題が何かと言うのは他に詳しく書かれている方がいるので詳細の説明は他者に譲ります。 個人的にはN+1じゃなくて1+N問題じゃないかとか思ったりしますが一般的に使われているのがN+1問題というワードなのでそっちの方で調べると記事が沢山出てきます。

親子の関係になっているドメインモデルで「子の項目の一覧を取得する際についでに親の項目もPreloadで取得する」ような実装になっている場合、子のリストを取得した際に子の数分親を取得するSQLが発行されるので大量のSQLが発行される可能性があります。

for文の中でループして1つずつSQLを発行している処理の場合はIN句でまとめて取得するようにしましょう。

処理中にPreload処理が現れた場合はそのRepositoryレイヤーよりも上位のレイヤーで複数回呼んでいないか要注意です。

同じ処理中で同一のデータを利用するために何度もSQL叩いているような場合はアプリケーション側でのcacheも考えたほうが良いでしょう。

サブクエリを利用したSQLはパフォーマンス要チェック

単純なサブクエリでも後述するEXISTSの例などは、結構罠が多いです。

結果だけOKだからヨシ!とはならずにパフォーマンス面でも問題ないことを確認するために、 サブクエリが登場するならまずEXPLAINを取ろうが基本です。

Viewの利用は基本的に禁止

そもそもSQL自由に書けるのにViewを使用するメリットがないし、 Viewを利用することでSQLが隠蔽され、知らぬうちにパフォーマンスが劣化している事があります。 何よりViewに対して絞り込みを自由にかけることが出来ずに、不要なレコードまで取得してしまうことが痛い。

だったらマテビューなら良いのかという話になりますが、マテビューを作成するとマテビューのリフレッシュが必要になりその分単体のSQLの実行が遅くなったり、 たまに同期ずれが発生したりして本当にどうしても絶対マテビュー以外では解決できない課題がない限りは使用するべきではありません。

参照専用のテーブルを作成してバッチ処理で同期を取る方法で十分なことが多いです。

CROSS JOINは禁止

SELECT a.id, b.id FROM a, b WHERE a.name = 'hoge' AND b.name = 'fuga'

CROSS JOINとは2つないしは複数のテーブルの情報を直積集合(デカルト積)で取得する方法ですが、 これが必要とされるケースは少なくともプロダクト開発においてあんまり想定出来ないです。 必要としているのであればテーブル設計を見直したほうが良いと思います。

WHERE句で十分に絞った検索をしているか

基本的に取得するデータ量が増えるとDBの負荷は高まりLatencyにも影響を与えるため不必要なデータを取得するのは避けるべきです。

そもそもWHERE句で絞り込みをしていなかったりカーディナリティが極めて低いColumnのみで絞り込みをしていたりする場合は見直しが必要です。

特にどうしてもサブクエリを使用しなければいけない場合、サブクエリ内のレコード数をちゃんと絞って取得しているかは重要な観点になってきます。

下記はありがちな例

SELECT humans.id
FROM humans
WHERE humans.nickname = 'tomtwinkle'
AND humans.id IN (
   SELECT human_id FROM japanese WHERE sex = 'man'
)

男女しかない性別というカーディナリティの非常に低いColumnのみで絞り込みを行っているためサブクエリの中で取得するレコード量が極端に大きくなっています。 直接的にPKで絞れないにしても日付で絞る、テナントIDで絞る等幾らでも絞りようはあるはずなので如何にレコード数を減らすかが鍵。

調べている間に出てきた以下の記事も似たような事象みたいです。

qiita.com

必要なcolumnだけSELECTしているか

SELECT * FROM hoge WHERE id IN (?, ?)

SELECT * の文字を見た瞬間に本当に * である必要があるのかは要チェック。

ORMを使っているとSELECTで項目を絞らずに全フィールドを取得しているケースがよく見かけられます。 ORM Libraryが勝手に発行しているので場合によっては実装者が意図せず実装してしまっていることもあるのが厄介です。 もちろん、実際に全フィールドが必要なのであればそのままでも問題ないですが 全項目必要ない場合(例えばIDだけ取ればいい場合等)はSELECTで取得する項目を絞りましょう。

レコード数だけ必要な場合にCOUNT用のSQLを発行しているか

レコード数が必要なだけなのにレコードを全件取得してアプリケーション側でカウントするよりも 素直にCOUNT句でレコードカウントだけ取得した方が良い。

× SELECT id FROM commits WHERE user_name = 'tomtwinkle'
○ SELECT COUNT(1) FROM commits WHERE user_name = 'tomtwinkle'

DB的には本来INDEXのカウントのみ返却すれば良いところを実際のレコードも取得しなければいけない上に、レコード数も1レコードのみ返せば良いところをカウント数分余計なレコードが取得されてしまうからです。

集計関数が必要か、また指定するColumnは正しいか

特に必要ない場合にGROUP BYなどの集計関数を使用していないか

例えばLEFT JOINした結果のレコードをユニークにしたいがために、何も考えずにSELECTする項目全部にGROUP BYを指定するようなケース等

集計が必要な場合SQLで取得した上でアプリ側で集計し直した方が、DBは自動でスケールアップしないがアプリは自動でスケールアウト出来るのでより多くのリクエストを捌くことが出来るようになります。

無茶なDISTINCTをしていないか

上記の例と似たような理由ですがレコードをユニークにしたいという理由だけでパフォーマンスを考慮せずに取り敢えず全Columnに対してDISTINCTを指定してしまうケース。

もちろん結果は想定通りになるのでテストケースはpassするしレコード数が少ないうちは正常に動作する……が、レコード数が増えてくると徐々に重くなりいずれ破綻します。

チューニングの方法としてはIDのみでDISTINCTしてID一覧を取得してから再度IDを指定してレコードを取得する等の工夫が可能です。

LIKEでの中間一致を極力しない

RDBではLIKE検索は前方一致検索でしかINDEXが使用できないため

SELECT id FROM users WHERE name LIKE '%tom%'

のような中間一致検索は非常に遅くなります。

他にカーディナリティの十分高くINDEXのあるColumnで絞っているなら使用しても良いが単体での使用はNG。

例えばMySQLではFULLTEXT INDEXを利用した全文検索も利用できますが n-gramを利用した自然言語検索は領域管理やインデックス作成コストが高いなどの問題があり基本的にRDBで対応するものでないと考えたほうが良いです。 使用する頻度が高いのであればElasticsearchのような全文検索エンジンの採用などを考えましょう。

oh....

WHERE句のキーとなる項目に関数や計算をしない

検索キーに計算や関数を実施するとインデックスが使われないためNG。

日時型の日付部分でのみ絞り込みたいみたいなケースでやっちゃってる人もたまに見かけます。

× WHERE DATE(created_at) = '2021-01-01'WHERE created_at >='2021-01-01' AND created_at <'2021-01-02'

日付・日時型へのLIKEは絶対禁止

FROM users WHERE birth_date like '%-06-%'

日付→文字列の暗黙変換が走っていますのでインデックスが利用されません。 結果的に上記のキー項目に文字列変換関数を使う事と同じことが起こります。

例のように誕生月で絞りたいなら誕生月専用のColumnを追加しましょう。

UNIONよりもUNION ALL

UNION ALLは重複をそのまま表示しますが UNIONは重複行を削除するためソート処理が走る事になります。

二つのクエリの結果が重複しない内容であればUNION ALLを選択しましょう。

というか、そもそもあまりUNIONが必要にならない設計を心がけましょう。

否定形を極力使わない

column != 1 といった否定形を使わないようにしましょう。

否定形はインデックスが利用されないため、他にカーディナリティの高いColumnで絞り込んでいない場合FULL SCANになりがちで非常に重いSQLになります。

ORを複数使うよりはIN

×
SELECT id FROM users
WHERE id = 1 OR id = 3 OR id = 4
SELECT id FROM users
WHERE id IN (1, 3, 4)

ORをたくさん並べると見た目も見にくいのでINを使いましょう。

1対多、多対多 のテーブル同士のLEFT JOINに注意

1対多のテーブルをLEFT JOINする際にはGROUP BYを指定しておかないと重複レコードが取得される可能性があります。

もちろんテストを書いていれば気付ける内容ではありますが、必要最低限のレコードでテストケースを書いているとpassしてしまうので 複数レコード×複数レコードのJOINのケースでテストをしているかテストケースの漏れがないかは十分チェックが必要になります。

パフォーマンスの問題というよりデータ重複の問題ですがよくあるのでレビュー観点としては注意。

EXISTS句のみで絞り込みしていないか

LEFT JOINするとレコード重複してしまうので代わりにEXISTS句で絞り込みをしているような例でたまに見かけます。

割とパフォーマンス劣化の原因になることが多いので要注意です。

×
SELECT tenants.id, tenants.name FROM tenants 
WHERE EXISTS (
   SELECT * FROM tenant_users
   WHERE tenant_users.user_id = 1
     AND tenants.id = tenant_users.tenant_id
)

上記SQLはtenantsをFULL SCANした結果でサブクエリの絞り込みを行っているため、テナントの数が増えると爆発します。 同じ結果を得たいのであればサブクエリで取得したIDをIN句で絞ってあげるのが適切です。

SELECT tenants.id, tenants.name FROM tenants 
WHERE tenants.id IN (
   SELECT DISTINCT id
   FROM tenant_users
   WHERE tenant_users.user_id = 1
)

カーディナリティの十分高い他のColumnで絞った上で利用する場合は問題ない事が多いです。

UPDATE JOINは使わない

UPDATE JOINはJOIN先のテーブルまでロックをかけて、デッドロックの可能性が高まるためバッチなどを除いてはできるだけ利用しない方が良いです。

サブクエリテーブルの使い方に気をつける

条件にもよりますが、下記のようにサブクエリ結果とのジョインは遅い可能性が高いので注意。

FROM users 
INNER JOIN
 (SELECT ・・・ FROM tenants WHERE ・・・) AS tenants
 ON users.tenant_id=tenants.id

4つ以上のテーブルJOINもしくはサブクエリ使用の場合、SQL分割も検討する

N+1問題との兼ね合いもありますが、複数テーブルをJOINした複雑なSQLを1回で投げるよりは、 1回目のSQLである程度属性値を絞り込んだ結果のID一覧を使用して2回目のSQLで実際の絞り込み結果を取得する場合の方がSQLパフォーマンスが良くなる可能性もあります。

設計時のレビュー観点

MySQLの場合PKにUUIDは使わない

RDBというよりMySQLのInnoDBの話なのでPostgreSQLになってくるとはまた話は違うのですが

MySQLはリーフページにテーブルの値を持つクラスタインデックス構造なので、 書き込み順序とIDが同列でソート出来ない場合、挿入や検索の際に複数のリーフページに偏りがないため、 複数のTreeに対して検索が走ってしまい結果的に遅くなること分かっています。

techblog.raccoon.ne.jp

auto incrementを利用せずランダムなIDをPKに利用したい場合は日付の順序でソート可能なULID等を利用してください。

jaywalkなデータを持つColumn設計は禁止

1つのColumnに対して複数のdataを持たせる設計はいにしえより「jaywalk(信号無視)」と呼ばれています。 例で言うと以下のChoice Columnのようにカンマ区切りで複数のデータを持たせるようなデータの持ち方です。

id name choice
1 hoge a,b
2 fuga a,c,d

上記の場合 choice=b でデータの絞り込みをしたくなった場合、 SQLでは WHERE choice LIKE '%b%' のような検索をする必要が出てきますが、 RDBでは前方一致検索以外ではINDEXが効かないため表全体を舐めるFULLSCANが走ってしまいます。

Naive Tree な設計は避けたい

SQLアンチパターンにも出て来るんですが割とよく使われる設計がNaive Treeです。

id parent_id name
1 NULL hoge
2 1 hoge child 1
3 1 hoge child 2
4 3 hoge child 2 child 1

1つのテーブルで子ノードの追加も簡単に行えるし整合性も担保できる設計なのですが、 子ノードから親ノードを辿っていくのが非常に面倒だし集約も削除もやりづらい。 コード自体の複雑化やパフォーマンス問題にも繋がりやすいです。

親子構造が2階層とかならまだしも任意のn階層とかになると単体のSQLで取るのが不可能なレベルでJOIN重ねないといけなかったりして設計者の頭が爆発します

ではどうすれば良いのかというとユースケース次第ではありつつも、RDBで表現するなら今の所 「閉包テーブル(Closure Tree)」 が良いんではないかなという所です。

以下のような組織を表す場合

- 株式会社なんかいい感じの名前の会社
   - 社長室
   - 営業本部
      - 営業1課
      - 営業2課
   - 開発本部
      - hoge開発

組織一覧表に紐づく形の組織階層テーブルを別途用意し、 階層構造の全パターンの紐付けデータを格納します。

  • 組織
id name
A 株式会社なんかいい感じの名前の会社
A-a 営業本部
A-b 開発本部
A-a-1 営業1課
A-a-2 営業2課
A-b-1 hoge開発
  • 組織階層
parent_id child_id depth
A A 1
A A-a 1
A-a A-a 2
A A-b 1
A-b A-b 2
A A-a-1 1
A-a A-a-1 2
A-a-1 A-a-1 3
A A-a-2 1
A-a A-a-2 2
A-a-2 A-a-2 3
A A-b-1 1
A-b A-b-1 2
A-b-1 A-b-1 3

これであれば例えば「営業1課」のツリーを全部取りたい場合、 child_id を指定して検索するだけで一覧が取得できるようになります。

SELECT 組織.id, 組織.name
FROM 組織階層 INNER JOIN 組織 ON 組織階層.parent_id = 組織.id
WHERE 組織階層.child_id = 'A-a-1'
ORDER BY 組織階層.depth ASC
id name
A 株式会社なんかいい感じの名前の会社
A-a 営業本部
A-a-1 営業1課

Goのように普通にSQL書く場合はそのまま利用できますね。

ActiveRecordの場合はgemがあるみたいです。

github.com

もちろんRDBにこだわる必要もないので、ユーザー毎の個別設定のようなそんなにデータ量が多くならないtree構造ならDynamoDBのようなスキーマレスなDBに頼るのもアリだと思います。

type毎に外部キーが参照するテーブルが変容するようなポリモーフィック関連なテーブル設計を避ける

1つの外部キーに相当するColumnで2つ以上のテーブルのIDとJOIN出来るようなテーブルをポリモーフィック関連と言います。

こんなやつです。

  • 親テーブル
id name to_id to_type
1 Aの親レコード1 1 A
2 Aの親レコード2 2 A
3 Bの親レコード1 1 B
  • 紐付テーブルA
id name
1 Aのレコード1
2 Aのレコード2
  • 紐付テーブルB
id name
1 Bのレコード1
2 Bのレコード2
SELECT 親テーブル.name, 紐付テーブルA.name
FROM 親テーブル
INNER JOIN 紐付テーブルA
  ON 親テーブル.to_type = 'A' 
    AND 親テーブル.to_id = 紐付テーブルA.id
WHERE 親テーブル.id = 1

この形でテーブルを作ってしまうとせっかくリレーショナルデータベース使っているのに外部キーが貼れません。 リレーショナルとは?

特にRailsを使用する場合はポリモーフィック関連(polymorphic association)は普通にパターンとして存在するのでシレッと混じる可能性がある。やめて欲しい。

hoge_id hoge_type みたいな並びがテーブルに出てきた時はレビュアーは要チェキだ。

論理削除は本当に必要か、ユースケースをよく考える

取り敢えず必要かどうか分からないけど論理削除を設定しておくような設計にすると、 頻繁にアクセスされるテーブルに対してバッチ処理で deleted_at を絞って消していく必要が出てきたりして、 場合によってはサービス全体のパフォーマンスをおとす結果に繋がります。

たまーに「間違えて消してしまったので戻して欲しい」という要望は来たりするので、 どうしても消したくないのであれば一定期間削除データを保持する削除履歴テーブルは検討しても良いかもしれないですね。

INDEXの数は出来るだけ減らす

どこのFieldで検索されるか分からないから取り敢えず全部のColumnに付けとくみたいなのは絶対NGです。

カーディナリティの高いテーブルにのみINDEXを貼る、複合INDEXを利用する等で使用するINDEXの数は出来るだけ減らしましょう。

論理削除のための deleted_at のようなFieldは大半がNULLだったりするのでカーディナリティが低く、あんまりINDEXを貼る意味がなかったりします。

また、INDEXの数が多いとINSERTやUPDATEにも時間も掛かってしまうので不必要なINDEXは削除しておくべきです。

エムスリーさんのテックブログでも最近見かけましたね。

www.m3tech.blog

INDEXは定期的に見直す

適切にINDEXが利用されているかは定期的に見直す必要があります。

未使用のINDEXがないか確認し、不要なINDEXがあれば削除しましょう。

ちなみに以下はMySQLで未使用INDEXを抽出する例

SELECT `t`.`OBJECT_SCHEMA` AS `object_schema`,
       `t`.`OBJECT_NAME`   AS `object_name`,
       `t`.`INDEX_NAME`    AS `index_name`
FROM (`performance_schema`.`table_io_waits_summary_by_index_usage` `t`
         JOIN `information_schema`.`STATISTICS` `s`
              ON (((`t`.`OBJECT_SCHEMA` = CONVERT(`s`.`TABLE_SCHEMA` USING UTF8MB4))
                  AND (`t`.`OBJECT_NAME` = CONVERT(`s`.`TABLE_NAME` USING UTF8MB4))
                  AND (CONVERT(`t`.`INDEX_NAME` USING UTF8) = `s`.`INDEX_NAME`))))
WHERE ((`t`.`INDEX_NAME` IS NOT NULL)
    AND (`t`.`COUNT_STAR` = 0)
    AND (`t`.`OBJECT_SCHEMA` <> 'mysql')
    AND (`t`.`INDEX_NAME` <> 'PRIMARY')
    AND (`s`.`NON_UNIQUE` = 1)
    AND (`s`.`SEQ_IN_INDEX` = 1))
ORDER BY `t`.`OBJECT_SCHEMA`, `t`.`OBJECT_NAME`;

UI上で必要な項目だけPreloadする設計になっているか

前述のN+1問題にも関連しますが、画面で使用していない項目に対してPreloadを行うと1回のリクエストでのSQLの発行回数が爆増するため全体のパフォーマンスに影響があります。

「必要かも?」という理由だけでPreloadしない!

設計段階でフロントエンドとしっかり調整して使用している項目を確認し、不要ならPreloadしないとするのが吉。 バックエンド側の人がフロントエンド側のデータ構造もちゃんと理解していると話が早いです。

データが増えた際にも1度に処理するレコードの上限が変わらない設計になっているか

「WHERE句で十分に絞った検索をしているか」の件と似ていますがどちらかというとUIを含めた全体設計の話。

テナントIDでテナントに紐づく全レコードを取得みたいな設計をしていると年数経過で処理に使用するデータ量が増大していき、いつか破綻します。

必ず日付での絞り込みを入れて、直近1ヶ月のデータのみを対象にする

とか

ページング処理をUI側に組み込み1度のリクエストで全件取得するような処理を避ける

とか

全体のデータ量が増えても一度に処理するデータ件数が必ず制限される設計になっているのかが重要です。

Special Thanks

最後に、このレビュー項目自体は全部が全部自分で全部書きあげたわけではないです。 DBREチームの皆さんの記事への追記やレビュー協力を得て書き上げる事が出来ました。多大なる感謝を申し上げます!!

それではみなさん、今年も頑張っていきまっしょい!

おわりに

アンドパッドでは、SQLチューニングがしたいバックエンドエンジニアや、データベースのパフォーマンスをもっともっと向上したいDBREを募集しています。 ご興味のある方は以下からカジュアル面談のお申し込みをお願いします。

▼ カジュアルに話が聞きたいエンジニアはこちらから engineer.andpad.co.jp

▼アンドパッドが募集しているエンジニア職種が気になった方はこちら hrmos.co