SQL改善で処理時間を約98%カットできた話

# 概要

私は[株式会社ZUU](https://zuu.co.jp/)で、主にバックエンドの開発を担当しており、フロントエンド、インフラに関する業務も担当しています。
今回取り上げる内容は、SQLの書き方一つでパフォーマンスがどれだけ改善できるかを、実例をもとに記述していきます。

# シナリオ
使用しているDBMSはPostgreSQL12です。
話を単純化するために単純な例で説明します。今回は投稿記事を管理するケースを想定します。投稿される記事には複数の画像が載っており、全ての画像がアップロード完了していれば記事として有効なものと考えます。

テーブルはarticles, imagesを用意します。

```mermaid
erDiagram

articles ||--o{ images: ""

articles {
    id integer PK
    title text
    published_at timestamp
}

images {
    id integer PK
    article_id integer FK
    filename text
    status text
}
```

実際にデータを入れてみるとこんな感じ。

**articles**
id | title | published_at
-- | -- | --
1 | 物価上昇はいつまで続くのか | 2022-08-01 10:00:00
2 | クーラーの適切な使い方 | 2022-08-10 13:00:00
3 | 熱戦続く甲子園 | 2022-08-15 16:00:00

**images**
id | article_id | filename | status
-- | -- | -- | --
1 | 1 | breads.jpg | uploaded
2 | 1 | vegetables.jpg | uploaded
3 | 1 | udon.jpg | upload_failed
4 | 2 | airconditioner.jpg | uploaded
5 | 2 | kakigori.jpg | uploaded
6 | 3 | koshien.jpg | waiting

この例だと、全ての画像がアップロード完了している、つまり記事として有効なものは記事2の「クーラーの適切な使い方」という記事になります。では記事2だけをどうやって取得するか考えてみましょう。

# SQL

### 改善前 
改善前のSQLはこちら。全て"uploaded"の記事idを取得してきて、その記事idに含まれる記事を取得してくるようなSQLです。

```SQL
SELECT * FROM articles
WHERE id IN (
    SELECT article_id FROM images
    GROUP BY article_id
    HAVING (EVERY(status = 'uploaded'))
);
```

### 改善後
上記のSQLを以下のように修正しました。~~改善後のSQLでは、`GROUP BY`も`HAVING`も使用せずに済むし、`WHERE IN`も使用しないのでコストを大幅に削減することができる。~~

```SQL
SELECT * FROM articles AS a
WHERE (
    SELECT COUNT(i.id) FROM images AS i
    WHERE i.article_id = a.id
    AND i.status != 'uploaded'
) = 0;
```

### 追記（2022/09/08）
当記事を投稿した際には、「WHERE-INをやめたから処理が速くなった」という内容でした。しかし、何人かの方に「理由はWHERE-INではないのでは？」とコメントをいただきました。その後検証したところ、ご指摘の通り根本理由はWHERE-INではないことがわかりました。
問題は対象を絞り切れていないままWHERE-INを使用していたことでした。実際のデータではほとんどのデータで`status='uploaded'`だったので、`WHERE id IN (数十万データ)`を実施していることになっていました。
ここで発想の転換をして条件を`status='uploaded'`でなく`status!='uploaded'`を使用した次のSQLを使ってみました。
```SQL
SELECT * FROM articles
WHERE id NOT IN (
    SELECT article_id FROM images
    WHERE status != 'uploaded'
);
```
こちらのSQLでも「改善後」に記載したSQLと同等の結果を得ることができました。

今回はいただいたコメントをもとに色々勉強させていただきました。コメントをくださったみなさま、ほんとうにありがとうございました！！

# EXPLAIN ANALYZE
次に、改善前後での`EXPLAIN ANALYZE`の結果をみてみましょう。ここで示す内容は説明のために用意した上記のシンプルなシナリオではなく、実際に運用されているデータに適用した結果です。複数のテーブルが関連し合う割と複雑なケースで、articlesは数十万レコードです（実際のデータに適用したものですので、一部隠蔽させていただきます）。

該当の`WHERE`部分（赤く囲った部分）での大幅な改善が見られます。

### 改善前
![改善前](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2289807/a6043c42-31c2-b00b-71f8-94c84044e532.png)

### 改善後
![改善後](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2289807/95531e7e-edce-be98-b950-1e052f80811c.png)

# 処理時間の比較
適用前後の実際のSQL処理時間を実際に計測してみました。処理時間を<font color="RED">__約97.8%__</font>削減することができました。

測定 | Before[ms] | After[ms]
-- | -- | --
1回目 | 9,953 | 295
2回目 | 9,777 | 281
3回目 | 9,248 | 282
4回目 | 11,035| 260
5回目 | 10,814| 303
*平均* | *10,165.4* | *284.2*

# DBのCPU負荷への効果
次にDBのCPU負荷を取得してみました。適用前後のある1日の負荷を比較しています。値は5分平均値です。
![DBのCPU負荷.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2289807/732bdf6e-c20a-e028-c4b4-1a88c9223bf7.png)
だいたい午前9時過ぎ頃に訪問ユーザ数が1日のピークを迎えて夕方から夜にかけて徐々にユーザ数が減っていきます。
適用前（Before）のCPU負荷もその訪問ユーザ数の変化に追従していることがわかります。一方、適用後（After）の負荷を見ると安定して低い水準を保っていますし、適用前の深夜帯よりも負荷が小さいことがわかります。
99パーセンタイル値比較で負荷を<font color="RED" >__約71%__</font>削減できました。

# 当該SQLを使用したAPIレイテンシの変化
該当SQLを使用したAPIのレイテンシの変化を見てみます。2秒以上要したリクエストのカウントをヒストグラムで表しています。
![スクリーンショット 2022-08-26 21.55.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2289807/4d41528b-fc02-036c-0621-8d5b93e3bd94.png)
9時ごろから訪問ユーザ数が増えていきます。そしてこの日の午後2時に改善後のSQLをデプロイしました。その効果は歴然です。適用前のある1日と適用後のある1日でカウント数を比較してみたところ、その数を<font color="RED" >__約96%__</font>削減することができました。

# まとめ
今回実施したSQLの改善内容はそんなに大きいものではありませんでしたが、ここまで大きい効果を得ることができました。これによってクラウドのリソースも減らすことができ、数十万円単位のコストカットも実現できるようになります（今月適用したので実際のコストカット額は分かりませんが、次回の請求額が楽しみです）。
SQLによるパフォーマンスへの影響を把握できるのはエンジニアだけだと思います。今回はサービスを運用する運用チームに対してエンジニアチームから課題をボトムアップし、それを改善してユーザへのレスポンスタイムを向上させ経費削減にも寄与でき、しっかりと事業にコミットできたということは大きいと思っています。エンジニアとして大きな喜びを感じた瞬間でもありました。

# 補足
今回は`count == 0`を使ったWHERE句の事例を取り上げましたが、実は`NOT EXIST`を使った以下のSQLの方がパフォーマンスが向上する場合があります。
```SQL
SELECT * FROM articles AS a
WHERE NOT EXISTS (
    SELECT i.id FROM images AS i
    WHERE i.article_id = a.id
    AND i.status != 'uploaded'
);
```
`count == 0`では全てのレコードを取得する必要がありますが、一方`NOT EXIST`を使うと1つでもレコードが見つかるとそこで判定は終了するので全てのデータを参照する必要がありません。