SQL最適化とは、基本的にはSQLの実行計画を調整することを指します。適切に設計されたインデックスの組み合わせにより、データスキャン行数を削減することが最も効果的なチューニング手法です。さらに、SQL最適化はアプリケーションの最適化やデータベースの最適化と密接に関連しています。
SQLチューニングのマクロな概念とは、「アプリケーションがSQLリクエストを通じてデータベースからデータを取得する」ための最適な実践を見つけることです。単一のSQL文に対して、私たちが取れる最適化戦略はそれほど多くありません。例えば、フィルタ条件が一切ないSQLを最適化することは手が付けようがなく、「このSQLは本当に必要なのか」と疑問に思うかもしれません。そのため、本章では主に広義の観点から、SQLチューニングの典型的なシナリオと事例を紹介します。
本章では、OceanBaseデータベースで一般的なSQLチューニングのシナリオといくつかの事例をまとめ、これらの実際の事例を分析することで、一般的なSQL診断・チューニング手法を示します。同時に、これらの事例データを通じて徐々に完全なSQLチューニング手法の集合を構築し、プラットフォーム上でGUIベースのSQL診断・最適化機能を実現することで、SQLリスクの管理とSQLの最適化をより効果的に支援することを目指します。
SQL容量管理
SQLパフォーマンスの過剰消費
SQLが徐々に遅くなる
シナリオの分析：
SQLパフォーマンスが一定期間にわたって徐々に低下する典型的なビジネスシナリオは以下のとおりです：
- ビジネスでデータを定期的に書き込む場合、書き込みバッチに応じてデータ量が徐々に増加し、データ量の増加に伴いSQLパフォーマンスが低下します。
- 大規模データセットのページネーションクエリでは、検索するページ数が増えるにつれてスキャンするデータ量が増加し、ページネーションSQLのパフォーマンスもどんどん遅くなります。
最適化の推奨事項：
データ量の累積によりSQLが徐々に遅くなる場合の最適化の推奨事項は以下のとおりです：
- フルカバレッジインデックスを追加し、
order+limitを使用して一度に処理する行レベルを制御し、データを均等に処理することで、SQLパフォーマンスを比較的一定に保つことができます。 - ビジネスの生成とダウンストリームの消費のバランスを保ち、可能な限り均等に書き込み、均等に消費し、データの滞留を防ぐことが重要です。滞留が発生するシナリオでは、データ移行を使用して、滞留したデータを非同期タスクを通じて継続的に均等に開放し、ダウンストリームの消費パフォーマンスを比較的一定に保つことができます。
大規模データセットのページネーションによりSQLが徐々に遅くなる場合の最適化の推奨事項は以下のとおりです：
ページネーションクエリでは、offset の使用は推奨されません。大きなページのパフォーマンスは非常に悪くなるためです。このような状況に対処するためには、主キー id を渡す方法を使用できます。例えば、毎回nページ目をクエリする際に、前回のクエリの最終ページまたは主キー id を次のページのクエリSQLに渡すことで、「主キー id または前ページの結果 + ORDER BY LIMIT n」に基づいて迅速にデータを取得できます。
SQLリクエスト量の急増
シナリオの分析：
SQL実行回数の急増とは、短期間において、他の通常時のリクエスト量と比べてSQL実行頻度が大幅に増加し、その結果SQLが占めるパフォーマンス消費が増え、テナントのCPUを使い果たしてデータベース全体のパフォーマンスに影響を与えることを指します。典型的なビジネスシナリオは以下のとおりです：
- ビジネスの定期タスク
- セールイベント
- キャッシュミス
最適化の推奨事項：
- ビジネス最適化の推奨事項：ビジネス層にプリフロー制御を追加し、リクエストが瞬時にデータベースをダウンさせるのを防ぎます。一般的なシナリオには、キャッシュミス、ビジネスセール、定期タスクなどが含まれます。
- DB最適化の推奨事項：データベースのCPU計算容量を確保し、一定のbuffer CPUを追加するか、テナントレベルのCPUオーバーコミットを有効にして、緊急時に自動的にオーバーコミットされたCPUを使用することができます。
- SQL緊急対応の推奨事項：異常なSQLリクエストの変動に対処し、データベース全体のサービスを復旧するために、異常なSQLに対してレート制限をかけ、単一のSQL上のビジネスを犠牲にして全体を回復することができます。
SQLビジネスホットスポット
読み取りホットスポット
シナリオの分析：
読み取りホットスポットとは、短期間に同一行のレコードが繰り返しクエリされることで、特定のアカウントの一定期間のSQLリクエストが急増することを指します。シナリオ上、SQL読み取りホットスポットはSQL実行回数の急増の一種ですが、具体的なアカウントを特定できれば、そのアカウントに対してターゲットを絞ったトラフィック制御を行い、影響を受けるのはそのアカウントのリクエストのみとなり、止血措置によるビジネスへの影響を最小限に抑えることができます。
最適化の推奨事項：
SQL緊急時には、該当するホットアカウントのリクエストを制限し、並行処理によるデータベースの復旧を抑えることができます。
書き込みとロックのホットスポット
シナリオの解析：
書き込みのホットスポットはしばしばロック競合を引き起こし、結果としてSQLの大量失敗や再試行、またはロック待ちが発生し、最終的にデータベース異常を引き起こします。一般的な書き込みのホットスポットには、同一行レコードに対してUPDATEまたはSELECT FOR UPDATEを実行する場合があります。
最適化の提案：
SELECT FOR UPDATEを実行する際は、キーワードNOWAITの使用を推奨し、業務コード層で再試行メカニズムを実装することで、データベース層でのロック競合を回避し、データベースのダウンを防ぎます。SQL緊急時には、該当するホットアカウントのリクエストを制限し、ロックの並行処理を抑えることができます。
大小アカウント
シナリオの解析：
大小アカウントは、業務データの偏りによって引き起こされるSQL最適化のシナリオであり、通常はテーブル内の特定のアカウントタイプが他のアカウントに比べてデータ量がはるかに多いために発生します。大アカウントのSQLクエリを実行する際、スキャンするデータ量が非常に多くなり、パフォーマンスの変動が生じます。
事例分析：
以下の業務シナリオでは、特に大小アカウントのシナリオが発生しやすいです。
システム内に異なる業務規模が存在し、データ量全体が逆ピラミッド形状を示す場合、つまりごく少数の業務が全体の大部分のデータを占める場合、大規模業務のSQLリクエストのパフォーマンスは、他の小規模業務に比べて大幅に低下します。
最適化の提案：
日常的な最適化に関しては、単純にSQL層面から有効な最適化手段はありませんが、業務アーキテクチャ層面から以下の最適化方法を提供できます：
- 大小アカウントのデータを分割します。データテーブルの観点からは、水平シャーディングに加えて、大小アカウントに基づく垂直シャーディングも可能であり、データの偏りをできるだけ減らすことができます。
- 大小アカウントのリクエストを分流します。異なるアカウントはそれぞれ異なるSQLインデックス要件を持つ可能性があるため、アカウント層面での分流が可能であれば、SQLが走るインデックスを効果的に制御し、優先順位を保証することができます。例えば、SQL緊急時に具体的なアカウントを一時的に特定できない場合、あるチャネルのSQLのみを制限し、他の業務の安定性を確保することができます。
- 可能な限りページネーションクエリ方式を採用し、IDシャーディングを通じて伝達します。毎回「id > 前ページの最後のid」の方式で、各シャーディングのデータを1つずつのみクエリし、その後業務層でデータを集計します。これにより、大規模顧客であっても小規模顧客であっても、SQL層面でのパフォーマンスを一定に保つことができます。
緊急シナリオに対しては、テナント仕様の拡張やSQLの制限以外に良い手段はありませんが、該当する大アカウントのSQLリクエストを制限することができます。
SQLパフォーマンス最適化
実行計画の最適化
実行計画の悪化
シナリオの解析：
OceanBaseデータベースはSQL最適化段階でPlan Cacheメカニズムを備えています。すなわち：
- SQLが初めてリクエストされる際、ハードパースが行われ、その時のSQLのパラメータ値に基づいてコスト計算が行われ、対応する実行計画が生成され、Plan Cacheキャッシュに書き込まれます。
- SQLが次にリクエストされる際、Plan Cache内にそのSQLのキャッシュされた計画があるかどうかを確認します。ある場合、計画キャッシュにヒットし、対応する実行計画を用いてSQLを実行します。
SQLのハードパースはCPUを消費するプロセスであり、Plan CacheメカニズムはSQLパフォーマンスと全体的なDatabaseスループットを効果的に向上させることができます。しかし、Plan Cacheメカニズム自体もいくつかの問題を引き起こす可能性があります。例えば、SQL実行計画の「良し悪し」は、SQLハードパース時の入力パラメータの「良し悪し」に依存します。言い換えれば、SQLが初めてリクエストされた際のパラメータ値は必ずしも大多数のリクエストのシナリオを満たしているとは限らず、「少数派」である可能性があり、それが逆に「多数派」のパフォーマンスを低下させる原因となることがあります。また、データ分布が変化した場合、古い計画が新しいシナリオに適用されなくなり、SQLパフォーマンスの低下を招くこともあります。
最適化の提案：
通常のインデックスを使用した実行計画は、異常な全表スキャン計画よりも明らかにパフォーマンスが優れています。緊急時には、正しい計画をバインドすることを推奨します。
Buffer テーブル
シナリオの解析：
Buffer テーブルとは、特定の業務シナリオでのみトリガーされる SQL の異常状態を指します：
業務シナリオとして、データに対して
INSERTを実行した後、その大部分がすぐにDELETEされる場合です。つまり、テーブルの既存データ量が非常に少ない状態です。通常、このようなテーブルにおける SQL 実行計画は、主表の全表スキャンとなります。これは、OceanBase データベースがテーブルのデータブロックの「空き」領域を回収するため、テーブルのデータブロックのハイウォーターマークが非常に低くなり、主表スキャンのコストが低く抑えられるからです。短時間に
INSERTとDELETEのデータ量が非常に大きくなり、テーブルのデータブロックのハイウォーターマークが適時に回収されない場合、またはINSERTの量がDELETEの量を上回りデータが滞留することで、実際にスキャンされるテーブルデータブロックのデータ量が多くなり、SQL のパフォーマンスが低下する場合です。
Buffer テーブルの SQL パフォーマンス異常は、上記の2つの条件を同時に満たす低確率の SQL シナリオでのみ発生します。また、インデックスフィールドに対する超高頻度の UPDATE も、一定の確率でインデックステーブルの Buffer 状態を引き起こす可能性があります。これは、インデックスフィールドの UPDATE が INSERT と DELETE によってインデックステーブルをメンテナンスするためです。
最適化の推奨事項：
日常的な状況では操作を行わないことを推奨します。Buffer テーブルは低頻度・低確率のシナリオに属し、トリガー条件が厳しいためです。緊急時には、outline を使用して settle_id インデックスをバインドします。SQL パフォーマンスの安定性を保証し、急激な悪化を防ぐために、日常的なパフォーマンスを犠牲にしても構わない場合は、Hint で settle_id フィールドのインデックスを指定することもできます。
複数計画のジッター
シナリオの解析：
このシナリオでは、SQL が複数のインデックスから選択可能であり、データの分布状況によって実行計画が異なるインデックスを選択する場合があります。OceanBase データベースのオプティマイザーは常に最適解を見つけようとするため、現在のデータ分布と入力パラメータから SQL 実行コストを推定し、コストが最も低い、つまりパフォーマンスが最も良い計画を選択します。業務データの書き込みや変更によりデータ分布が変化すると、異なる時間に異なる実行計画が生成されることがあります。また、異なる値のデータ分布の差異が大きい場合もあり、複数の計画間で切り替えが発生することがあります。
最適化の推奨事項：
複数のインデックスから選択可能なシナリオにおける実行計画のジッターに対しては、以下の点から最適化を図ることができます：
- 互いに冗長なインデックスを過剰に作成しないようにします。例えば、この例の
status、env、env+statusの3つのインデックスは互いに重複しています。曖昧さを避けるために、statusの単一列インデックスを削除することで、他のシナリオで誤って選択されるのを防ぎ、同時にインデックステーブルのストレージコストを節約できます。 - 列挙型のフィールドには、性別、年齢、タイプ、状態など、列挙値が非常に少ないフィールドには、極めて少数の値をフィルタリングできる明確な列挙シナリオがない限り、インデックスを作成しないようにします。例えば、
status状態フィールドでは、99% のデータが初期状態で、1% のデータが未処理状態です。業務シナリオとしては、初期状態のデータをinsertしてから、主キーに基づいてstatusフィールドを完了済みにupdateし、1% の異常データのみが未処理状態であり、補正伝票メカニズムがこのテーブル内の未処理データを照会する必要がある場合です。このような場合、statusフィールドのインデックススキャンのパフォーマンスは良好になります。
- データの偏りが特に大きいシナリオについては、業務層からの最適化が必要です。サイズアカウントの章の最適化推奨事項を参照してください。
インデックスの最適化
インデックスの選択ミス
シナリオの解析：
このシナリオでは、SQL が複数のインデックスから選択可能ですが、実行計画が選択したインデックスが最適なパフォーマンスではない場合があります。考えられる原因は以下の通りです：
- SQL Hint で不適切なインデックスが指定されています。
- SQL Outline が不適切なインデックスにバインドされたことがあります。
- SQL のハードパース段階での入力パラメータのデータ分布が低確率シナリオに該当し、誤った実行パスが選択されました。
- SQL 最適化段階で生成されたデータ分布に基づく計画が、業務運用後に変化したデータ分布状況に適していません。
最適化の推奨事項：
SQL Hint で指定されたインデックスを解除し、オプティマイザーに自動選択させるか、idx_fund_inst_type_time インデックスを指定することを推奨します。
インデックスが作成されていない
シナリオの分析：
このシナリオでは、SQLフィルター条件に適切なインデックスが存在せず、全表スキャンまたは他のインデックスを経由するしかなく、パフォーマンスが低下しています。
最適化の推奨事項：
- SQLの最適化推奨事項：適切なフィールドにインデックスを作成します。この例では、
dag_task_idに通常インデックスを追加できます。通常インデックスは最終的に主キーtask_idフィールドを含むため、task_idのソートも排除できます。 - ビジネスプロセスの推奨事項：コード統合段階でSQLレビューを実施し、インデックスのないSQLが本番環境に導入されるのを防ぐことを推奨します。
インデックスの作成が不適切
シナリオの分析：
このシナリオでは、SQLはインデックスを利用できるものの、パフォーマンスは依然として低いですが、より良いパフォーマンスを発揮するインデックスを作成できる適切なフィールドがあります。
最適化の推奨事項：
上記のSQLシナリオの分析に基づき、最適化の推奨事項は以下の通りです：
SQLの最適化には以下の2つの推奨事項があります：
gmt_createフィールド上の関数操作を削除します。"'TIMESTAMPDIFF(MINUTE, gmt_create, now()) > ?" を'gmt_create > DATE_SUB(now(), INTERVAL ? MINUTE)" に書き換えることができます。scene+gmt_createインデックスをscene+effective+gmt_createインデックスに変更することで、パフォーマンスは約3倍向上します。
ビジネスプロセスの推奨事項：コード統合段階でSQLレビューを実施し、述語関数操作を含むSQLが本番環境に導入されるのを防ぐことを推奨します。