￥## はじめに
遅いクエリを見つけた。
実行計画を確認し、インデックスも追加した。
それでも、しばらくするとまた遅くなる。
このようなとき、問題はクエリの書き方だけではないかもしれません。
データが増え続けているのか。古いデータまで毎回読んでいるのか。読み取りと書き込みのどちらが多いのか。
クエリの速さは、データのライフサイクルとアクセスパターンの上に成り立っています。
TL;DR
クエリを速くするには、SQLやインデックスだけでなく、データがどのように増え、読まれ、更新され、古くなるかを見る必要があります。
まず確認するのは、次の2つです。
- データが時間とともにどう変化するか
- そのデータへどのようにアクセスしているか
そのうえで、インデックス、アーカイブ、削除、水平分割などの対策を選びます。
1. インデックスは、増え続けるデータを止めてくれない
例えば、ユーザーの操作履歴を保存するテーブルを考えます。
SELECT *
FROM activity_logs
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 100;
user_idとcreated_atに適切なインデックスを追加すれば、検索は速くなります。
しかし、履歴データが毎日増え続けるなら、テーブルとインデックスも大きくなり続けます。
インデックスは、検索対象へたどり着くまでのコストを減らします。
一方で、データ量そのものを減らすわけではありません。
さらに、インデックスを増やすと、データを追加・更新するたびにインデックスも更新されます。
読み取りは速くなっても、書き込みは重くなる可能性があります。
つまり、
どのインデックスを追加するか
だけではなく、
そのデータは今後どこまで増えるのか
まで考える必要があります。
2. データは、時間とともに役割が変わる
すべてのデータが、作成直後から削除されるまで同じように使われるわけではありません。
操作履歴であれば、直近のデータは画面表示や問い合わせ対応で頻繁に使われます。
一方、数年前の履歴は、監査や障害調査のときにしか使われないかもしれません。
この変化が、データのライフサイクルです。
新しいデータほど頻繁に使われる
多くの業務システムでは、直近のデータほどアクセスされやすくなります。
注文一覧なら最近の注文、通知なら未読の通知、監査ログなら直近の操作です。
それにもかかわらず、数年前のデータまで同じテーブルへ置き続けると、頻繁に使うデータと、ほとんど使わないデータが混ざります。
アプリケーションが必要としているのが直近100件だけでも、データベース側では巨大なテーブルとインデックスを維持し続けなければなりません。
保存期間は技術だけでは決められない
古いデータを削除すれば、容量も検索対象も減らせます。
ただし、保存期間はパフォーマンスだけでは決められません。
監査、法令、問い合わせ対応、分析など、データを残す理由があるからです。
そのため、まずはデータごとに次の状態を分けます。
日常的に利用する
↓
まれに参照する
↓
保管だけ必要
↓
保存する理由がなくなる
この境界が決まると、すべてのデータを同じ場所へ置き続ける必要があるのかを判断できます。
3. 同じデータ量でも、アクセスパターンによって設計は変わる
データ量だけを見ても、適切な対策は決まりません。
1000万件のテーブルでも、主キーで1件だけ取得する処理が中心なら、大きな問題にならない場合があります。
一方、100万件でも、広い期間を集計するクエリが繰り返されれば負荷は高くなります。
見るべきなのは、データ量とアクセスパターンの組み合わせです。
読み取りと書き込みのどちらが多いか
読み取りが多い処理では、インデックスやキャッシュが効果を発揮しやすくなります。
しかし、書き込みが多いテーブルへインデックスを増やすと、追加や更新のたびに維持コストがかかります。
したがって、インデックスの設計では、検索条件だけではなく、そのテーブルのread/write比も確認します。
読み取り性能だけを改善した結果、書き込みが詰まり、システム全体のスループットが落ちることもあります。
1件の速さと、全体の処理量は違う
クエリを単体で実行したときに100ミリ秒で終わっても、同じクエリが1秒間に1000回実行されれば、大きな負荷になります。
反対に、数秒かかる集計でも、深夜に1回しか実行されないなら許容できるかもしれません。
クエリ単体のレイテンシ
×
同時実行数・実行頻度
=
システム全体への負荷
遅いクエリを見るときは、実行時間だけでなく、回数と並行度も合わせて確認する必要があります。
トランザクションは、データへの触れ方を変える
同じ行を複数の処理が読み書きする場合、トランザクションの分離レベルやロックも性能へ影響します。
長いトランザクションが行を保持すると、後続の更新が待たされます。
整合性を強く保つほど安全になる一方で、並行して処理できる量は減る可能性があります。
そのため、単にクエリを速くするだけでなく、
- どの範囲を一つのトランザクションにするか
- どこまで最新の値を保証するか
- 競合が起きやすい行へ集中していないか
まで見る必要があります。
重要なのは設定値を暗記することではありません。
データが、どの処理から、どの頻度で、どの整合性を求めて触られるのかを把握することです。
4. データ量とアクセスパターンから対策を選ぶ
データとアクセスの特徴が分かったら、初めて具体的な対策を選びます。
使わないデータは削除する
保存する理由がなくなったデータは、削除するのが最も単純です。
データ量が減れば、テーブル、インデックス、バックアップの容量も減ります。
ただし、削除処理そのものが大きな負荷にならないように、一度に消す件数や実行時間帯を考える必要があります。
必要だが普段使わないデータはアーカイブする
監査などの理由で残す必要はあるものの、通常の画面では使わないデータもあります。
その場合は、日常的に利用するデータと保管用データを分けます。
アーカイブによって、通常のクエリが触るデータ量を減らせます。
一方で、アーカイブしたデータをどのように検索するか、戻す必要があるかも設計しておかなければなりません。
時間やキーでアクセスが偏るなら分割する
データを一定のルールで分割すると、必要な範囲だけを扱いやすくなります。
例えば、時系列データを月単位で分ければ、直近期間の検索や、古い期間の削除を行いやすくなります。
ただし、分割しただけで、すべてのクエリが速くなるわけではありません。
分割キーと検索条件が合っていなければ、複数の領域をまたいで読むことになります。
よく使う検索条件
↓
分割キーと一致しているか
↓
必要な領域だけを読める
水平分割も同じです。
1台で扱えない規模になったときには有効ですが、複数の領域をまたぐ検索やトランザクションは複雑になります。
分割は、データ量だけではなく、アクセスが特定のキーへ偏っているかを見て判断します。
5. 遅いクエリを見るときの視野を広げる
クエリが遅いとき、SQLや実行計画を確認することは重要です。
しかし、そこで終わると、一時的にしか改善しないことがあります。
右へ進むほど、クエリ単体からシステム全体の設計へ視野が広がります。
例えば、インデックスを追加して一時的に速くなっても、データが増え続ければ再び遅くなります。
直近データしか使わないのに、古いデータを同じ場所へ残し続けているなら、アーカイブの方が本質的な対策かもしれません。
書き込みが集中しているなら、インデックスの追加よりも、競合やトランザクションの範囲を見直す必要があります。
クエリチューニングは、SQLだけを直す作業ではありません。
そのクエリが触るデータの流れを理解し、問題が生まれている場所を見つける作業です。
おわりに
クエリの速さは、クエリの書き方だけでは決まりません。
データは増え、古くなり、使われ方も変化します。
そのデータへ、読み取りと書き込みがどの程度発生し、どのような整合性が求められるかによって、必要な設計も変わります。
インデックスを直しても遅さが戻ってくるなら、クエリの外側を見ます。
そのデータは、なぜ増え続けているのか。
いつまで同じ場所へ置く必要があるのか。
どのように読まれ、書かれているのか。
クエリ単体ではなく、データの流れ全体を見ることで、削除、アーカイブ、分割といった次の選択肢が見えてきます。