概要
2026年11月17日、SakuraのD1読み取り行数が無料枠の上限に近づいていることを確認した。承認待ち通知の一覧取得SQLが、通常の投稿を含むmessagesテーブルを走査していた。
対象SQLは24時間で112回実行され、3,510,744行を読み取っていた。同期間の返却行数は0行だった。結合順序を変更し、承認依頼に対応する投稿を主キーで取得する形に修正した。修正はv0.4.1に含まれる。
本記録で確認できている影響は、D1読み取り枠の大量消費である。この事象による上限到達やサービス停止は確認していない。
保存データの構成については、Sakuraの暗号化設計を参照。今回の対象は承認依頼のメタデータを取得するSQLであり、本文の復号処理ではない。
検知と影響
CloudflareのGraphQL Analytics APIで、D1のクエリごとの統計を確認した。集計期間は日本時間の2026年11月16日21時42分52秒から、翌17日21時42分52秒までの24時間。
対象SQLの観測値は以下のとおり。
| 項目 | 観測値 |
|---|---|
| 実行回数 | 112回 |
| 読み取り行数 | 3,510,744行 |
| 返却行数 | 0行 |
| 書き込み行数 | 0行 |
| 1回あたりの読み取り | 約31,346行 |
| 平均SQL実行時間 | 約85ms |
このクエリだけで、取得したクエリ統計全体の読み取り行数の約98%を占めていた。
D1の無料枠は、執筆時点で読み取りが1日500万行。返した行数ではなく、クエリが走査した行数が対象になる。公式の料金説明にも、この区別が書かれている。結果0件の場合も読み取り枠を消費する。
なお、この24時間の分析期間と無料枠のリセット時刻は同じではない。無料枠はUTCの0時にリセットされるので、351万行をそのまま「今日の使用量」とは扱っていない。
原因
対象はGET /web/v1/notificationsで呼ばれる、承認待ちの一覧取得だった。ログイン中の利用者が承認できる部屋を調べ、その部屋にある未回答の依頼を新しい順に100件まで返す。
本文や選択肢を返す処理ではない。部屋と投稿のID、投稿者、日時などを通知に載せるための処理だ。
認可部分を省略すると、元のSQLはこうなる。以下は構造を示す抜粋で、そのまま実行するSQLではない。
SELECT allowed.project_id, allowed.owner, allowed.name,
msg.id AS message_id, msg.handle, msg.created_at
FROM (
-- 現在の利用者・鍵・部屋の参加権限を確認し、approverに絞る
...
) allowed
JOIN requests r ON r.project_id = allowed.project_id
JOIN messages msg
ON msg.id = r.message_id AND msg.project_id = r.project_id
WHERE NOT EXISTS (
SELECT 1 FROM decisions d
WHERE d.project_id = r.project_id AND d.request_id = r.message_id
)
ORDER BY msg.id DESC
LIMIT 100;
requestsは承認依頼、decisionsは回答済みの依頼を管理するテーブル。messagesには承認依頼だけでなく、普通の投稿も入っている。
本番D1でEXPLAIN QUERY PLANを確認すると、計画の途中に次があった。
SCAN msg
requestsの検索にもdecisionsの検索にもインデックスは使われていた。それでも、その前にmessagesを走査する計画になっていた。
ORDER BY msg.id DESC LIMIT 100に合わせて投稿をたどり、条件に合う依頼かどうかを後から確認する形だと考えられる。合うものが早く100件見つかれば終われるが、今回は返却が0件なので、途中で打ち切れない。
LIMIT 100が制限するのは返却件数で、読み取り行数ではない。普通の投稿が増えるほど、承認待ちがないと確認するための読み取りまで増える状態だった。
また、約31,346行という数字はテーブルの投稿数そのものではない。インデックスや関連テーブルへのアクセスを含む、D1が記録した読み取り行数になる。
対応
結合に使用するインデックスは既に存在していた。requestsには(project_id, message_id)、decisionsには(project_id, request_id)の主キーがあり、messages.idは整数主キーになっている。
認可された部屋、承認依頼、対応する投稿の順に検索するよう、次の2か所を変更した。
CROSS JOIN requests r ON r.project_id = allowed.project_id
CROSS JOIN messages msg
ON msg.id = r.message_id AND msg.project_id = r.project_id
SQLiteではCROSS JOINの左右を入れ替えない性質を使って、結合順序を制御できる。SQLiteの公式説明にある方法だ。結合条件のON句は維持した。
修正後の計画では、関連する部分が次のようになった。途中の認可検査などは省略している。
SEARCH r USING COVERING INDEX sqlite_autoindex_requests_1 (project_id=?)
SEARCH d USING COVERING INDEX sqlite_autoindex_decisions_1 (project_id=? AND request_id=?)
SEARCH msg USING INTEGER PRIMARY KEY (rowid=?)
USE TEMP B-TREE FOR ORDER BY
投稿全体の走査がなくなり、必要な投稿を主キーで取得するようになった。最後のソートには一時B-treeを使うが、今回のデータでは、その方が少ない読み取りで済む。
すべての走査が消えたわけではなく、参加者テーブルにはSCAN mが残っている。今回直したのは、通知取得が普通の投稿全体を走査してしまう部分だ。
検証結果
修正したSELECTを、本番D1に読み取り専用で1回実行した。実在する承認権限のある利用者と鍵で確認し、返却0件、読み取り78行、書き込み0行、SQL実行時間1.5227msだった。
修正前の24時間平均である約31,346行と比べると、読み取りは約99.75%少ない。ただし、これは単発測定と過去の平均の比較で、修正後24時間の運用結果ではない。利用者や依頼の数によって変わるし、SQL実行時間もブラウザから見た待ち時間とは別になる。
再発を防ぐテストでは、ローカルのテスト用D1に普通の投稿を6,000件入れた。その上で、次を確認している。
- 元のSQLは返却0件でも6,000行以上を読む。
- 修正後は承認待ちがない場合、ある場合、回答済みの場合のいずれも100行未満に収まる。
- 実行計画から
SCAN msgが消え、投稿を整数主キーで検索する。
この100行未満という条件は、あくまでテスト用データに対するものになる。将来の全データで読み取りが100行未満になるという保証ではない。
認可条件は変更していない。利用者や参加状態、鍵の失効、部屋ごとの失効、承認権限の確認は残している。既存のテストでも、別の利用者や承認権限を持たない参加者に依頼が出ないこと、回答後に一覧から消えることを確認している。
修正PR #63はマージ済みで、v0.4.1に入った。実装と回帰テストも公開している。
検証で不足していた点
従来のテストでは、承認待ちの有無や権限による返却内容を確認していた。一方、通常の投稿が増えた状態で、返却0件のクエリが何行を読むかは検証できていなかった。
LIMIT 100があっても走査量は制限されない。返却内容が正しいことに加え、結果がない場合の読み取り行数を確認する必要があった。
再発防止と残課題
通常の投稿6,000件を含む回帰テストを追加し、返却内容、rows_read、実行計画を検証対象にした。承認待ちがない場合も、投稿全体の走査に戻らないことを確認する。
今回の修正ではキャッシュや通知取得頻度は変更していない。通知取得はSSEイベントや通知欄を開く操作などで発生する。観測された112回の実行では、呼び出し頻度よりも1回あたりの読み取り行数が主な消費要因だった。
修正後24時間の読み取り総量は、本記録ではまだ比較していない。78行という値は単発の本番測定結果であり、今後の利用者数や依頼数でも一定になるとは限らない。部屋数や依頼数の増加時には、結合順序の固定が引き続き適切かを再評価する。