Database Autonomy Service:コストベースのSQL診断エンジン

データベースは、サービスシステムにおいて重要なコンポーネントとして機能する。 ほとんどのビジネスシナリオでは、データベースを使用する必要があります。 データベースで発生する小さなジッターは、サービスに悪影響を与える可能性があります。 ビジネスのパフォーマンスを最適化するには、データベースのパフォーマンスを安定させ、データベースを頻繁に最適化する必要があります。 これは常にサービスの重要な要件です。 ほとんどの場合、アプリケーション、インスタンス、およびSQLステートメントを最適化して、データベースサービスを改善できます。

• アプリケーション: アプリケーションのコードロジックを最適化できます。 このように、アプリケーションはより効率的な方法でデータを処理できます。

• インスタンス: 環境パラメーターの値を調整できます。 このように、インスタンスはより効率的な方法で動作できます。

• SQL文: SQL文は、さまざまな方法を使用して最適化できます。 例えば、物理データベースが提供され、SQL文が書き換えられる。 このようにして、データをより効率的に照会することができる。

SQLの診断および最適化機能に関して、次の2つの質問がよくあります。

• 機能は信頼できますか?

• 機能は包括的ですか?

両方の質問に対する答えはイエスです。 SQL診断および最適化機能が信頼性が高く包括的であることを確認するには、次の課題に取り組む必要があります。

• 信頼できる最適化提案を生成するために、信頼できる推奨アルゴリズムをどのように選択できますか?

  SQL診断および最適化の分野では、ルールベースのアルゴリズムおよびコストモデルベースのアルゴリズムは、最適化提案のための一般的な推奨アルゴリズムである。

  • ルールベースの方法

    ルールベースの推奨方法は、データベース、特にMySQLデータベースなどのWhat-If Tool (WIT) 機能を持たないデータベースで広く使用されています。 ルールベースの方法は、実装が簡単かつ容易である。 ただし、推奨事項は柔軟性がなく、関連する要件を満たすことができません。 たとえば、ルールベースのメソッドを使用して、次の単純なSQLクエリ

    SELECT *
    FROM t1
    WHERE time_created >= '2017-11-25'
    AND consuming_time > 1000
    ORDER BY consuming_time DESC;

    次の候補インデックスは、ルールベースの方法に基づいて生成されます。

    IX1(time_created)
    IX2(time_created, consuming_time)
    IX3(consuming_time)
    IX4(consuming_time, time_created)

    推奨されるインデックスを特定することはできません。 インデックスorまたはインデックスANDが使用される場合、複数のインデックスを推奨できます。 この問題の原因は、ルールベースの方法では正確な推奨を提供できないことです。 この例では、このSQL文を実行して、単一のテーブルからデータを照会します。 複数のテーブルを結合する必要があり、SQLクエリに複雑なサブクエリが含まれている場合、推奨される結果を判断するのはより困難です。

  • コストベースの方法

    DASのSQL診断および最適化機能は、コストモデルベースの方法を使用して実装されます。 これにより、DASはデータベースオプティマイザと同じ方法で最適化の問題を評価できます。 DASは、すべての可能な候補を評価し、コストモデルベースの方法を使用して推奨結果を提供できます。 極端な場合、DASはすべての候補を評価できませんが、できるだけ多くの候補を評価します。 これは、最適解が非決定性多項式 (NP) 問題を解くために使用されるからである。 MySQLデータベースなどのオープンソースデータベースの場合、この機能では次の問題が発生する可能性があります。

    • WIT機能の欠如: カーネルのデータベースオプティマイザによって提供されるWIT機能は、候補最適化ソリューションのコストを評価するために再利用できません。

    • 統計の欠如: 実行計画のコストは、候補最適化ソリューションのコストを評価するために計算されます。 統計が利用できない場合、候補最適化ソリューションのコストは評価できません。

• SQL診断および最適化機能をSQL文と完全に互換性を持たせるにはどうすればよいですか。

  開発されるSQL診断および最適化機能は、SQL文と完全に互換性がある必要があります。 この機能は、SQL解析とSQLセマンティクス検証をサポートする必要があります。 SQLの互換性は、機能の包括性とSQL診断の成功率に直接関係しています。

• 包括的な機能テストセットはどのように構築できますか?

  SQLの診断と最適化は長い間課題と考えられてきました。 データベース最適化の専門知識を機能に組み込む必要があります。 機能のコア機能を検証するには、大規模なテストケースライブラリを構築する必要があります。 ライブラリを使用して、フィーチャ機能を測定および改善できます。 機能を改善するには、包括的で正確なテストケースライブラリが不可欠です。

  修飾テストケースライブラリを構築するには、次の要件を満たす必要があります。

  • 包括性: SQLの最適化は、いくつかの要因の影響を受けます。 たとえば、インデックスの選択は数百の要因の影響を受け、これらの要因の組み合わせはケースシグネチャの大規模なコレクションを形成します。 これらのシグネチャを1つずつテストケースにマッピングすることは、要求の厳しい作業である。

  • 専門知識と大量の情報: テストケースを設計するには、専門知識と大量の情報が必要です。 たとえば、インデックス推奨ケースには次の情報が含まれます。

    • スキーマデザイン: スキーマデータには、テーブル、既存のインデックス、および制約が含まれます。

    • 複数のタイプの統計。

    • 環境パラメーター。

• 大規模な診断機能はどのように構築できますか?

  SQL診断および最適化機能は、数百万のクラウドデータベースインスタンスに対応できる必要があります。 この機能のオンライン機能は大きな課題に直面しています。 例えば、サービス分割を実装するために複雑なコンピューティングアーキテクチャが必要とされ、計算ノードを追加または除去することができる。 チャレンジには、同時クエリ要求の最大数、分散リソースアクセス環境での同時実行制御、さまざまな緊急優先順位に基づく有効なタスクスケジューリング、およびピーク値処理も含まれます。

上の図は、SQL診断および最適化エンジンのコアアーキテクチャを示しています。 エンジンは、データベースから独立したオプティマイザです。 エンジンは自動的に完全な統計を収集し、実行計画のコストを計算できます。 このように、エンジンは、WIT能力と同様の能力を提供する。 次のリストは、エンジンのワークフローを示しています。

1. SQLの解析と検証: エンジンはSQL文を解析し、入力されたクエリ文の構文が標準形式に準拠しているかどうかを検証し、クエリ文の要素を識別してから、構文ツリーを形成します。 構文ツリーは、クエリステートメント内の述語、述語型、ソートフィールド、集約フィールド、クエリフィールド、およびフィールドのデータ型を含む。 エンジンは、SQL文で指定されたテーブルとフィールドが指定されたデータベースのスキーマに準拠しているかどうかをチェックします。

2. 候補インデックス生成: エンジンは、生成された構文ツリーに基づいて、候補インデックスの複数の組み合わせを生成する。

3. コストベースの評価: データベースカーネルから独立した組み込みオプティマイザは、データベース統計を取得します。 統計は診断エンジンにキャッシュされます。 診断エンジンの組み込みオプティマイザは、統計に基づいて候補インデックスのコストを計算し、各インデックスのコストとSQLステートメントの書き換えに使用される各メソッドのコストを評価します。 このように、コストに基づいて、SQL文の書き換えに使用される最適なインデックスまたは最適な方法を選択することができる。

4. インデックスのマージと最適なインデックスの選択: エンジンは、データベースインスタンスで実行される1つ以上のSQL文またはすべてのSQL文を処理できます。 エンジンは、複数のSQL文のインデックス推奨を生成します。 既存の物理インデックスおよび異なるSQL文に対するインデックス推奨は、同じインデックス、同じプレフィックスを有するインデックス、および類似のインデックスを含むことができる。

Alibaba Groupでは、データベースインスタンスで実行されるすべてのSQL文がリアルタイムで収集および保存されます。 署名ベースの記述は、AlibabaエコシステムとさまざまなSQLシナリオに基づいて使用され、多数のオンラインの完全なSQLリソースを分析および検索し、指定された署名を満たす実際のケースを取得し、テストケースに必要な情報を抽出します。 このようにして、テストケースライブラリを構築することができる。

テストケースのシグネチャライブラリ内の特徴は、テストケースの包括性及びカバレッジを評価するために、テストケースライブラリ内に含まれるシグネチャと比較される。 次の4つの側面に基づいてテストケースを評価できます。

• 署名ライブラリのどのテストケースがテストケースでカバーされ、包括性は何ですか?

• 署名ライブラリのどのテストケースがSQL診断および最適化機能の対象外であるか、またはSQL検証に失敗したか。

• シグネチャライブラリのどのテストケースで、一定期間内に回帰の問題が頻繁に発生する可能性がありますか?

• 各機能レベルでのテストケースのカバー率はどのくらいですか?

DASのSQL診断および最適化機能がAlibaba Cloudでリリースされる前は、この機能はAlibaba Groupサービスで何年も実行されていました。 この期間に、この機能は1日あたり平均約50,000のSQL文を診断しました。 これにより、Alibaba CloudアプリケーションのSQL文が効率的に最適化されます。 SQL診断および最適化機能は、次のシナリオで使用できます。

• カスタム最適化: SQL診断および最適化機能を使用して、Alibaba Groupユーザーによって指定された異常なSQLステートメントを診断し、SQLステートメントのエキスパート最適化の提案を提供できます。

• 自動最適化: SQL診断および最適化機能は、データベースインスタンスのワークロードに基づいて低速クエリを自動的に識別し、SQL文を診断してから最適化の提案を生成します。 機能が提案を評価した後、機能は最適化タスクをスケジュールし、最適化をオンラインで完了し、SQL文のパフォーマンスを監視します。 最適化プロセスは完全に自動化され、データベースのパフォーマンスが向上します。 これにより、データベースインスタンスは常に最適な条件で実行できます。