SQLの最適化とは、基本的にはSQLの実行計画をチューニングし、適切に設計されたインデックスの組み合わせを作成することで、データスキャン行数を削減することが最も効果的なチューニング手法です。さらに、SQLの最適化はアプリケーションやデータベースの最適化と密接に関連しています。
SQLチューニングのマクロな概念とは、「アプリケーションがSQLリクエストを通じてデータベースからデータを取得する際の」ベストプラクティスを見つけることです。単一のSQL文に対しては、実行可能な最適化策はそれほど多くありません。例えば、フィルタ条件が一切ないSQL文を最適化する方法は存在せず、「このSQL文は本当に必要なのだろうか」と疑問に思うかもしれません。そのため、本章では広義の観点から、SQLチューニングの典型的なシナリオと事例を紹介します。
本章では、OceanBaseデータベースにおける一般的なSQLチューニングシナリオといくつかの事例をまとめ、これらの実際の事例を分析することで、よく使用されるSQL診断・チューニング手法を示します。
SQL容量管理
SQLパフォーマンスの過剰消費
SQLの徐々な遅延
シナリオの解析：
一定期間にわたり、SQLパフォーマンスが徐々に低下する典型的なビジネスシナリオは以下のとおりです：
- ビジネスで周期的にデータが書き込まれるため、書き込みバッチに応じてデータ量が徐々に増加し、データ量の増加に伴いSQLパフォーマンスが低下します。
- 大規模データセットのページネーションクエリでは、照会するページ数が増えるにつれてスキャンするデータ量も増加し、ページネーションSQLのパフォーマンスもますます低下します。
ケース分析：
シナリオ：eコマースの注文履歴クエリ
特定のeコマースプラットフォームでは、ビジネスの成長に伴い、注文履歴クエリのSQLが徐々に遅くなりました。初期段階では1000件の注文を照会するのに0.5秒かかりましたが、半年後には同じ数の注文を照会するのに10秒以上かかるようになりました。
原因： 注文テーブルのデータ量が100万から5000万に増加し、order_idインデックスが作成されていないため、テーブル全体のスキャンが発生しました。
最適化の提案：
データ量の蓄積によってSQLが徐々に遅くなる場合、最適化の提案は以下のとおりです：
- 全カバリングインデックスを追加し、
order+limitを使用して一度に処理する行レベルを制御し、データを均等に処理することで、SQLパフォーマンスを比較的一定に保つことができます。 - ビジネスの生成と下流の消費のバランスを確保し、可能な限り均等に書き込み、均等に消費することで、データの蓄積を防ぐ必要があります。蓄積が発生する場合は、データ移行を使用して、蓄積されたデータを非同期タスクを通じて継続的に均等に分散し、下流の消費パフォーマンスを比較的一定に保つことができます。
大規模データセットのページネーションによってSQLが徐々に遅くなる場合、最適化の提案は以下のとおりです：
ページネーションクエリでは、offsetの使用は推奨されません。これは、大きなページのパフォーマンスが非常に悪いためです。このような状況では、主キーidを渡す方法を使用できます。例えば、毎回nページ目を照会する際に、前回の照会で得られたページの末尾または主キーidを次のページのクエリSQLに渡すことで、「主キーidまたは前ページの結果+ ORDER BY LIMIT n」に基づいて迅速にデータを取得できます。
SQLリクエスト量の急増
シナリオの解析：
SQL実行回数の急増とは、短時間において、他の通常時のリクエスト量と比較してSQL実行頻度が大幅に増加し、その結果、SQLによるパフォーマンス消費が増大し、テナントのCPUを圧迫してデータベース全体のパフォーマンスに影響を与える可能性があることを指します。典型的なビジネスシナリオは以下のとおりです：
- ビジネスの定期タスク
- プロモーションイベント
- キャッシュ破綻
ケース分析：
シナリオ：eコマースの大規模セール期間中の在庫照会 特定のeコマースでは、「双十一」の大規模セール期間中に、在庫照会のSQLリクエスト量が通常の10倍に急増し、テナントのCPU利用率が95％に急騰し、他のビジネスにも影響が及びました。
原因： 在庫テーブルにはストリーム制御が設定されておらず、CPUのオーバーコミットリソースも設定されていませんでした。
最適化の提案：
- ビジネスの最適化提案：ビジネス層に前置きのストリーム制御を追加し、リクエストが瞬時にデータベースを圧迫するのを防ぐことが推奨されます。一般的なシナリオには、キャッシュ破綻、ビジネスプロモーション、定期タスクなどが含まれます。
- DBの最適化提案：データベースのCPU計算容量を確保し、一定のbuffer CPUを追加することが推奨されます。また、テナントレベルのCPUオーバーコミットを有効にして、緊急時に自動的にオーバーコミットされたCPUを使用することもできます。
- SQLの緊急対応提案：異常なSQLリクエストの変動に対して、データベース全体のサービスを回復するために、異常なSQLに対してリミットをかけ、単一のSQL上のビジネスを犠牲にして全体を回復することができます。
SQL業務のホットスポット
読み取りのホットスポット
シナリオの解析：
読み取りのホットスポットとは、短時間に同一行のレコードが複数回クエリされることで、特定のアカウントにおいて特定の期間にSQLリクエストが急増する現象を指します。このような場合、SQL読み取りのホットスポットは、SQL実行回数の急増の一種ですが、特定のアカウントを特定できれば、そのアカウントに対してのみトラフィック制御を行うことで、このアカウントのリクエストにのみ影響を与え、業務への影響を最小限に抑えることができます。
ケース分析：
シナリオ：銀行VIPアカウントの照会 ある銀行システムにおいて、VIPユーザーの残高照会リクエストが1時間で10倍に急増し、単一テーブルのロック待機が急激に増加しました。
原因：VIPアカウントのデータが集中して照会され、単一アカウントのリクエスト速度が制限されていませんでした。
最適化の提案：
SQL緊急時には、該当するホットスポットアカウントのリクエストを制限し、並行処理を低減してデータベースを復旧させることができます。
書き込みとロックのホットスポット
シナリオの解析：
書き込みのホットスポットはしばしばロック競合を引き起こし、その結果、SQLの大量失敗や再試行、またはロック待機が発生し、最終的にデータベース異常を引き起こします。一般的な書き込みのホットスポットとしては、同一行のレコードに対してUPDATEまたはSELECT FOR UPDATEを実行する場合が挙げられます。
ケース分析：
シナリオ：物流システムの注文状態更新 ある物流システムにおいて、人気のある注文が頻繁に状態変更（例えば、statusの更新）を行うため、SELECT FOR UPDATEによって多数のロック競合が発生しました。
原因：SQLにNOWAITが追加されておらず、トランザクションが長時間待機していました。
最適化の提案：
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性能が低下することもあります。
ケース分析：
シナリオ：ユーザー行動統計SQLの性能変動 あるソーシャルプラットフォームのユーザー行動統計SQLは、月初に実行されると性能が良好（0.1秒）ですが、月末に実行されると全テーブルスキャンに変わり、所要時間は10秒になります。
原因：初期のパラメータは小範囲の日付値（データ量が少ない）でしたが、月末にはパラメータが月間のデータをカバーするようになりましたが、インデックスはトリガーされませんでした。
最適化の提案：
通常、インデックスを通じて実行される計画の性能は、異常な全テーブルスキャン計画よりも明らかに優れています。緊急時には、正しい計画をバインドすることを推奨します。
Bufferテーブル
シナリオの解析：
Bufferテーブルとは、特殊なビジネスシナリオでのみトリガーされる一種のSQL例外を指します：
ビジネスシナリオとしては、データの
INSERT後、その大部分がすぐにDELETEされる場合、つまりテーブルの既存データが非常に少ない場合、一般的にこのテーブル上のSQL実行計画は主テーブルの全テーブルスキャンとなります。これは、OceanBaseデータベースがテーブルのデータブロックの「空洞」を回収するため、テーブルのデータブロックのハイウォータマークが非常に低く、主テーブルのスキャンコストが非常に低いからです。短時間で
INSERTとDELETEのデータ量が非常に大きく、かつテーブルのデータブロックのハイウォータマークが適時に回収されない、あるいはINSERT量がDELETE量を上回るためデータが滞留し、実際にスキャンされるテーブルデータブロックのデータが多くなり、SQL性能が低下します。
上記の2つの条件を同時に満たす低確率のSQLシナリオでのみ、BufferテーブルのSQL性能異常が発生します。また、インデックスフィールドの超頻度なUPDATE実行も、一定の確率でインデックステーブルのBuffer状態を引き起こす可能性があります。これは、インデックスフィールドのUPDATEがINSERTとDELETEによってインデックステーブルを維持するためです。
ケース分析：
- シナリオ：一時タスクテーブルの性能急落 あるタスクスケジューリングシステムでは、一時タスクテーブルに短期間で100万件のデータが挿入された後、適時にクリーンアップされなかったため、クエリ時にハイウォータマークが回収されず、全テーブルスキャンの性能が急激に低下しました。
最適化の提案：
通常の状況では操作を行わないことを推奨します。Bufferテーブルは低頻度で低確率のシナリオに属し、トリガー条件が厳しいためです。緊急時には、outlineを使用してsettle_idインデックスをバインドします。また、SQL性能の安定性を保ち、急激な悪化を防ぐために、日常的な性能を多少犠牲にしても構いません。Hintでsettle_idフィールドのインデックスを指定することができます。
複数の実行計画によるジッター現象
シナリオの解析：
このシナリオでは、SQL文が複数のインデックスから選択可能であり、異なるデータ分布によって実行計画が異なるインデックスを選択する可能性があります。OceanBaseデータベースのオプティマイザーは常に最適解を見つけ出すことを目指しているため、現在のデータ分布と入力パラメータに基づいてSQLの実行コストを推定し、コストが最も低く、つまりパフォーマンスが最も良い計画を選択します。業務データの書き込みや変更に伴いデータ分布が変化すると、異なる時間に異なる実行計画が生成される可能性があり、さらに異なる値のデータ分布の差異も大きくなるため、複数の計画間で切り替えが繰り返されることがあります。
ケース分析：
- シナリオ：注文状態のクエリ計画の変動 特定の注文システムにおいて、
SELECT * FROM orders WHERE status = 'PENDING' AND env = 'PROD'の実行計画は、異なるstatus値に対して頻繁にインデックス（例えばstatusインデックスとenvインデックス）を切り替えます。
最適化の提案：
複数のインデックスから選択可能なシナリオにおける実行計画のジッター現象に対して、以下の点から最適化を図ることができます：
- 互いに冗長なインデックスをあまり作成しないようにします。例えば、この例の
status、env、env+statusの3つのインデックスは重複しています。曖昧さを避けるために、statusの単一列インデックスを削除することで、他のシナリオで誤って選択されるのを防ぎ、同時にインデックステーブルのストレージコストを節約できます。 - 列挙型フィールドには、性別、年齢、タイプ、状態など、列挙値が非常に少ないフィールドには、明確にごく少数の値をフィルタリングできる場合を除き、インデックスを作成しないようにします。例えば、
status状態フィールドでは、99% のデータが初期化され、1% のデータが未処理です。業務シナリオとしては、insertで初期化状態のデータを挿入し、主キーに基づいてstatusフィールドをupdateして完了とし、わずか1% の例外データが未処理であり、補完注文メカニズムが必要で、そのテーブル内の未処理データを照会する場合、この時点でstatusフィールド上のインデックススキャンのパフォーマンスは良好になります。
- データの偏りが特に大きいシナリオについては、業務層からの最適化が必要です。サイズアカウントの章の最適化提案を参照してください。
インデックスの最適化
不適切なインデックスの選択
シナリオの解析：
このシナリオでは、SQLに複数のインデックスが利用可能であるにもかかわらず、実行計画で選択されたインデックスが最適なパフォーマンスを提供しない場合があります。その原因は以下の通りです：
- SQLヒントで不適切なインデックスが指定されています。
- SQLアウトラインが不適切なインデックスにバインドされたことがあります。
- SQLのハードパーソナライズ段階で入力パラメータのデータ分布が低確率なシナリオに該当し、誤った実行パスが選択されました。
- SQLの最適化段階で生成されたデータ分布に基づく計画は、業務運用後に変化したデータ分布に適していません。
ケース分析：
- シナリオ：ユーザー情報クエリで誤ったインデックスが選択される 特定のユーザー情報クエリSQLは本来user_idインデックスを使用すべきですが、OUTLINEがuser_nameインデックスにバインドされているため（データの偏りが深刻）、テーブル全体のスキャンが発生します。
最適化の提案：
SQLヒントで指定されたインデックスを解除し、オプティマイザーによる自動選択を許可するか、idx_fund_inst_type_timeインデックスを指定することを推奨します。
インデックスが作成されていない
シナリオの解析：
このシナリオでは、SQLのフィルタ条件に適切なインデックスが存在せず、テーブル全体のスキャンまたは他のインデックスを使用するしかなく、パフォーマンスが低下します。
ケース分析：
- シナリオ：ロジスティクス軌跡ログのクエリ 特定のロジスティクスシステムでは、
SELECT * FROM tracking WHERE dag_task_id = '123'はテーブル全体をスキャンする必要があります。これは、dag_task_idインデックスが作成されていないためです。
最適化の提案：
- SQLの最適化提案：適切なフィールドにインデックスを作成します。この例では、
dag_task_idに通常のインデックスを新規作成できます。通常のインデックスは最終的に主キーのtask_idフィールドを自動的に含むため、task_idのソートも排除できます。 - ビジネスプロセスの提案：コード統合段階でSQLレビューを実施し、このようなインデックスのないSQLが本番環境に導入されるのを防ぐことを推奨します。
不適切なインデックスの作成
シナリオの解析：
このシナリオでは、SQLはインデックスを使用できますが、パフォーマンスは依然として低く、より良いパフォーマンスを提供するインデックスを作成できる適切なフィールドが存在します。
ケース分析：
シナリオ：未払いの期限切れ注文を検出するSQL（作成時間が60分を超えて支払われていない） 特定の注文の期限切れ検出
SQL SELECT * FROM orders WHERE TIMESTAMPDIFF(MINUTE, gmt_create, NOW()) > 60のパフォーマンスは非常に悪いです。元のSQL
SELECT * FROM orders WHERE TIMESTAMPDIFF(MINUTE, gmt_create, NOW()) > 60 AND scene = 'online_payment' AND effective = 1;原因：
- 関数演算によりインデックスが無効になる：
TIMESTAMPDIFF(gmt_create, NOW())の関数演算により、gmt_createフィールドでインデックスを使用できなくなります。 - インデックス設計が不適切：テーブルにはsceneとgmt_createの結合インデックスidx_scene_timeが存在しますが、effective状態フィールドが含まれていないため、インデックスはすべての条件をカバーできません。
- 関数演算によりインデックスが無効になる：
最適化の提案：
上記の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が本番環境に導入されるのを防ぐことを推奨します。