SQLはユーザーがデータベースを利用する際の一般的な方法であり、SQLを通じてデータの定義、保存、更新、クエリなど、すべてのデータ管理操作を実現できます。これは高度に非手続き型のプログラミング言語であり、ユーザーは「何をするか」を指示するだけで、「どのように行うか」までは具体的に指定する必要がありません。また、データの保存方法を指定したり、具体的なデータ保存方式を理解したりする必要もありません。SQLはむしろ「口語的」なプログラミング言語であり、私たちが普段話すように、主語・述語・目的語の階層構造を持っています。例えば、図書館で本を借りるときに司書に「中国近代史の本を1冊ください」と言うように、SQL文は select * from book where book_name='中国近代史' となります。司書は事前にすべての本を分類・整理しておく必要があります。例えば、「中国近代史」という本を入庫する際に、「文史コーナー・近現代史の本棚・2段目の3番目の位置」に配置しておけば、後で本を取り出すときに非常に便利です。逆に、本を入庫するときにただ無造作に積み上げてしまうと、本が少ないうちはまだ対応できるかもしれませんが、そうでなければ針の山から糸を探すような状況で、効率は極めて低くなってしまいます。 この「分類・整理」の管理手法こそが、データベースのSCHEMAにおけるインデックス構造であり、効率的かつ合理的なインデックス設計はSQLパフォーマンスにとって非常に重要です。よく言われるSQLの最適化とは、実際にはSQL実行時にスキャンするデータ量をどのように減らすか、つまりSQLインデックスの最適化ということです。日常的にSQLを使用する中で考慮すべきSQL最適化のシナリオは、単一のSQLを最適化するよりもはるかに複雑です。例えば：
- 既存のインデックスを活用して、どのように書くとSQLパフォーマンスが向上するか？
- ある1つの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レビューは研究開発プロセスにおいて不可欠です。では、SQLレビューでは主にどのような点に注意すべきでしょうか？
- SQL研究開発規範のチェック
- SQLパフォーマンスの評価
SQL研究開発規範の検査では、SQLが研究開発規範に適合しているかどうかをスキャンし、研究開発規範の実施を支援します。SQL研究開発規範の実施は、低レベルなSQLエラーを減らし、低パフォーマンスなSQLシナリオの発生を防ぐだけでなく、SQLの可読性を向上させ、SQL業務シナリオの理解を容易にし、人為的なSQL問題のトラブルシューティングを支援することができます。
SQLパフォーマンスの評価では、多くのSQLリスクを事前に特定する必要があり、主に効率的なインデックスへのアクセスが可能か、リライト最適化が必要か、パフォーマンスの低下が発生する可能性があるかどうかに注目します。審査時にデータサポートやビジネスシナリオの理解が必要な場合は、実装方法についてさらにレビューを行うことができます。
統合段階でSQLの潜在的なリスクをレビューすることで、リスクのあるSQLの本番導入を効果的にブロックし、プロセス中に不良SQLの発生を削減することができます。
運用保守段階：臨機応変な対応
運用保守段階で行われるSQLの最適化は、一般的に最も多くのケースで対処するものです。なぜなら、SQLが新たに導入された当初は、データ量が少なく、同時実行数も低いため、多くのSQLではパフォーマンス問題が発生しません。しかし、業務量の増加とデータの蓄積に伴い、SQLは徐々に遅くなったり、実行計画の突然の変更によってパフォーマンスが急激に低下したりする可能性があります。運用保守段階で行うSQLの最適化には、以下のような内容が含まれます：
- 異常診断により、問題のあるSQLを特定する
- 根因分析により、DBの突然のクラッシュを引き起こしたSQLを特定する
- SQL緊急対応により、根本原因となるSQLに対して緊急対応を行い、DBを復旧する
- 日常的な治理により、DB内の不良SQLを継続的に改善する
- 継続的な最適化により、異常ケースをアーカイブし、類推によって他のケースにも対処する
異常検出と根本原因分析では、一般的なSQL問題について理解し、SQL異常のトラブルシューティング経験を蓄積・沈殿させることで、根本原因の問題を迅速に特定し、緊急対応を行い、業務を回復できるようにする必要があります。私たちは、一般的なSQLの典型的なシナリオとケースを共有します。
SQL緊急対応は大まかに2つの方法に分けられます：実行計画の介入とSQLの制限です。SQLに正しい実行計画または適切なインデックスがある場合、OceanBaseのアウトラインを使用して計画またはインデックスをバインドし、実行計画の介入を行うことで、迅速かつ効果的に復旧できます。インデックスの追加が必要な場合は、復旧までに時間がかかり、インデックスの作成が完了して初めて介入効果が得られます。一方、SQLが計画選択の問題ではなく、多くの場合はSQL容量に関連する問題である場合、効果的な方法はSQLの制限です。しかし、SQLの制限は業務に損害を与えるため、全体のDBを回復するためには、SQLの同時実行数を犠牲にする必要があります。
日常的な治理と継続的な最適化では、データベース運用保守プロセスにおいて、データベース上の低パフォーマンスなSQLの最適化を継続的に推進し、不良SQLによるデータベースクラッシュのリスクを低減する必要があります。