SQLチューニングの標準的なシナリオと事例

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メカニズムがあります。具体的には以下のとおりです：

1. SQLが初めて要求される際、ハードパースが行われ、その時のSQLのパラメータ値に基づいてコスト計算が行われ、対応する実行計画が生成され、Plan Cacheキャッシュに書き込まれます。
2. 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パフォーマンスが低下する場合。

上記の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 Hint で不適切なインデックスが指定されていたため。
• SQL Outline が不適切なインデックスにバインドされていたため。
• SQLのハードパース段階での入力パラメータのデータ分布が低確率なシナリオに該当し、誤った実行パスが選択されたため。
• SQL最適化段階で生成されたデータ分布に基づく計画が、業務運用後に変化したデータ分布に適していないため。

最適化の推奨事項：

SQLヒントで指定されたインデックスを解除し、オプティマイザーに自動選択を任せるか、idx_fund_inst_type_time インデックスを指定することを推奨します。

インデックスが作成されていない

シナリオの分析：

このシナリオでは、SQLのフィルター条件に適切なインデックスが存在せず、全表スキャンまたは他のインデックスを経由するしかないため、パフォーマンスが低下しています。

最適化の推奨事項：

• SQL最適化の推奨事項：適切なフィールドにインデックスを作成します。この例では、dag_task_id に通常インデックスを追加することができます。通常インデックスは最終的にプライマリキー task_id フィールドを含むため、task_id のソートも排除できます。
• ビジネスプロセスの推奨事項：コード統合段階で SQL Reviewを実施し、このようなインデックスのない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 Reviewを実施し、このような述語関数操作を含むSQLが本番環境に導入されるのを防ぐことを推奨します。