SQLはユーザーがデータベースを利用する一般的な手段であり、SQLを通じてデータの定義、格納、更新、クエリなど、あらゆるデータ管理操作を実現できます。SQLは高度に非手続き型のプログラミング言語であり、ユーザーは「何をするか」を指示するだけで、「どのようにするか」を具体的に指定する必要はありません。また、データの格納方法を指定したり、具体的なデータ格納方式を理解したりする必要もありません。SQLはむしろ「口語的」なプログラミング言語であり、私たちが普段話す言葉と同じように、主語・述語・目的語の階層構造を持っています。例えば、図書館で本を借りるとき、司書に「中国近代史の本を一冊ください」と言います。これはSQL文で select * from book where book_name='中国近代史' となります。司書は事前にすべての本を分類・整理しておく必要があります。例えば、「中国近代史」という本を入庫するとき、「文史類区域・近現代史書架・第二層第三本」という位置に置いておけば、後で取り出すときに非常に便利です。逆に、本を入庫するときに適当に積み上げただけでは、本が少ないうちはなんとかなるかもしれませんが、本が多くなると針の山から針を探すような作業となり、効率は極めて低下します。 この「分類・整理」の管理手法こそが、データベースSCHEMAのインデックス構造です。効率的かつ合理的なインデックス設計は、SQLパフォーマンスにとって極めて重要です。私たちがよく言うSQL最適化の中核は、SQL実行時のスキャンデータ量をどのように削減するか、すなわちSQLインデックスの最適化です。日常的にSQLを使用する過程では、単一のSQLを最適化するよりもはるかに複雑なシナリオを考慮する必要があります。例えば：
- 既存のインデックスに対して、どのようにSQLを書けばパフォーマンスが向上するか？
- 単一のSQLに対して、どのようなインデックスを作成すればパフォーマンスが向上するか？
- 業務システム全体に対して、どのように合理的なインデックスを設計すれば最高のパフォーマンスを得られるか？
- データベース全体で異常が発生した場合、どのように問題のあるSQLを特定し、迅速に復旧するか？
本章では、主に開発段階、統合段階、運用保守段階の各プロセスにおけるSQL最適化の枠組みの構築について説明します。
開発段階：良いSQLを書く
日常の開発プロセスにおいて、良いSQLを書くためには、主に以下の3つのことをしっかり行うことが重要です：
- SQL開発規範を設定し、それに厳密に従って実行する。
- 業務シナリオに基づいてスキーマを適切に計画し、SQLが効率的なインデックスを利用できるようにする。
- 単一のSQLに対して、最適化可能なシナリオを考える。
SQL開発規範は、SQL品質保証の基盤であり、SQLをより安定させるための重要な手段です。SQLの運用段階で発見されるSQL問題は、既に業務に影響を与えている可能性があり、一般的なSQL緊急対応ではすべてのSQL問題をカバーすることはできず、対処が遅れがちです。そのため、明確なSQL開発規範を策定することで、SQL問題の発生タイミングを開発段階まで前倒しし、コード中の低レベルなSQLエラーを解消し、潜在的なSQLリスクを回避することができます。
スキーマ設計は、業務シナリオが絶えず変化する中で、継続的に設計と最適化を行うプロセスです。複雑な業務システムには多様なSQLが含まれており、各SQLの負荷構造は業務モデルの変化に伴って変動します。また、基盤となる業務データの継続的な書き込みも、データ分布の絶え間ない変化を引き起こします。私たちは、SQLの負荷状況に応じてスキーマ設計を継続的に最適化し、SQLが業務スループットに最適な状態を維持できるようにする必要があります。結局のところ、すべてのSQLのパフォーマンスを最適化することを目的としているわけではありません。それは不必要であり、達成も困難です。なぜなら、それにはより高いコストがかかる可能性があるからです。
単一のSQLに対して最も重要なのは、SQLが適切なインデックスを利用できるようにすることであり、データスキャン行数を削減することが最も効果的なチューニング手法です。さらに、業務の観点から考える必要があります。そのSQLが実現している業務要件に対して、より合理的な方法が存在するかどうかです。同時に、SQLが本番環境に導入された後、データの変化に伴い、SQLに性能のボトルネックや潜在的なリスクが生じる可能性があるかどうかも予測する必要があります。
開発段階で良いSQLを書けば、SQLの安定性を最大限に保証し、ソースレベルで不良SQLの発生を大幅に減らすことができます。
統合段階：リスクのブロック
統合段階でSQLを本番運用環境に投入する際、最も重要なのは一つだけです：SQLレビューです。コードを書いた後にテストを行い、コミット前にCode Reviewを行うのと同じように、SQLレビューは開発プロセスにおいて欠かせません。では、SQLレビューでは主に何に注目するのでしょうか？
- SQL開発規範のチェック
- SQLパフォーマンスの評価
SQL開発規範のチェックとは、SQLが開発規範に準拠しているかスキャンし、開発規範の徹底を支援することです。SQL開発規範の実施は、低レベルなSQLエラーを減らし、低パフォーマンスなSQLシナリオの発生を防ぐだけでなく、SQLの可読性を向上させ、業務シナリオの理解を容易にし、人為的なSQL問題の調査を支援することができます。
SQLパフォーマンス評価では、多くのSQLリスクを事前に特定する必要があります。主に、効率的なインデックスを利用できているか、Rewriteによる最適化が必要か、パフォーマンスの低下が発生する可能性があるかどうかに注目します。審査時にデータサポートや業務シナリオの理解が必要な場合は、実装方法についてさらに詳細な評価を行うことができます。
統合段階でSQLのリスクをレビューすることで、リスクSQLの本番環境への導入を効果的にブロックし、プロセス全体で不良SQLの発生を削減することができます。
運用保守段階：対症療法から根本原因の解決へ
運用保守段階で行われるSQL最適化は、一般的に最も多く取り扱うケースです。SQLが新たに導入された当初は、データ量が少なく、同時実行数も低いため、多くのSQLではパフォーマンス問題が発生しません。しかし、業務量の増加とデータの蓄積に伴い、SQLが遅くなったり、実行計画の突然の変化によりパフォーマンスが急激に低下したりすることがあります。運用保守段階で行うSQL最適化には、以下のような作業が含まれます：
- 異常診断：問題のあるSQLを特定する
- 根本原因分析：DBが突然クラッシュさせたSQLを特定する
- SQL緊急対応：根本原因となるSQLに対して緊急対応を行い、DBを復旧する
- 日常的な管理：DB内の不良SQLを継続的に管理する
- 継続的な最適化：異常事例をアーカイブし、類推による改善を図る
異常検出と根本原因分析では、一般的なSQL問題を理解し、SQL異常調査の経験を蓄積・体系化することが重要です。これにより、根本原因を迅速に特定し、緊急対応を行って業務を復旧することが可能になります。ここでは、一般的なSQLの典型的なシナリオと事例を紹介します。
SQL緊急対応は大まかに二つの方法に分けられます：実行計画の介入とSQLの制限です。SQLに正しい実行計画や適切なインデックスがある場合、OceanBaseのoutlineを使用して計画やインデックスをバインドし、実行計画を介入することで、迅速かつ効果的に復旧できます。インデックスを追加する必要がある場合は、時間がかかり、インデックスの作成が完了して初めて介入効果が得られます。SQLが計画選択の問題ではない場合、多くはSQL容量に関連する問題であり、効果的な方法はSQLの制限です。しかし、SQLの制限は業務に損害を与えるため、SQLの並行処理を犠牲にして全体のDBを回復する必要があります。
日常的な管理と継続的な最適化では、データベースの運用保守プロセスにおいて、データベース上の低パフォーマンスなSQLの最適化を継続的に推進し、不良SQLによるデータベースクラッシュのリスクを低減する必要があります。