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パフォーマンスとデータベース全体のスループットを効果的に向上させることができます。しかし、Plan Cacheメカニズム自体もいくつかの問題を引き起こす可能性があります。例えば、SQL実行計画の「良し悪し」は、SQLハードパース時の入力パラメータの「良し悪し」に依存します。言い換えれば、SQLが初めて要求された際のパラメータ値は必ずしも大多数の要求シナリオを満たしているとは限らず、「少数派」である可能性があり、それが逆に「多数派」のパフォーマンスを低下させる原因となることがあります。また、データ分布が変化した場合、古い計画が新しいシナリオに適用されなくなり、SQLパフォーマンスが低下することもあります。
最適化の推奨事項：
通常、インデックスを経由する実行計画の方が、異常な全表スキャン計画よりも明らかにパフォーマンスが優れています。緊急時には、正しい計画をバインドすることを推奨します。
Buffer表
シナリオの解析：
Buffer表とは、特定の業務シナリオでのみトリガーされるSQLの異常を指します：
業務シナリオとして、データに
INSERTが実行された後、その大部分がすぐにDELETEされる場合、つまりテーブルの既存データ量が非常に少ない場合、一般的にそのテーブル上のSQL実行計画は主表の全表スキャンとなります。これは、OceanBaseデータベースがテーブルのデータブロックの「空洞」を回収するため、テーブルのデータブロックのハイウォーターマークが非常に低く、主表スキャンのコストが低いからです。短時間に
INSERTとDELETEのデータ量が非常に大きく、かつテーブルのデータブロックのハイウォーターマークがタイムリーに回収されていない、あるいはINSERTの量がDELETEの量を上回りデータが滞留し、実際にスキャンされるテーブルデータブロックのデータ量が多くなり、SQLパフォーマンスが低下する場合。
上記の2つの条件を同時に満たす低確率のSQLシナリオでのみ、Buffer表の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ヒントで不適切なインデックスが指定されています。
- SQLアウトラインが不適切なインデックスにバインドされていたことがあります。
- SQLのハードパース段階での入力パラメータのデータ分布が低確率なシナリオに該当し、誤った実行パスが選択されました。
- SQL最適化段階で生成されたデータ分布に基づく計画が、業務運用後に変化したデータ分布に適していません。
最適化の推奨事項：
SQLヒントで指定されたインデックスを解除し、オプティマイザーに自動選択を任せるか、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が本番環境に導入されるのを防ぐことを推奨します。