インデックス診断を行う方法 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、PolarDB-Xのインデックス診断機能について説明します。この機能を使用して、データベース内の非効率なインデックスを診断および特定し、データベースのパフォーマンスを向上させる対策を講じることができます。

の背景情報

インデックス付けは、クエリ操作を高速化し、データ検索の効率を大幅に向上させるためにデータベースで一般的に使用される方法です。ただし、テーブル内のデータが更新されるたびに、対応するインデックスも更新して変更を反映する必要があります。これにより、書き込み操作が遅くなり、データベースの全体的な効率が低下する可能性があります。さらに、インデックスはデータベースのストレージスペースを占有します。したがって、インデックスの使用を慎重に検討する必要があります。

PolarDB-Xのインデックス診断機能は、データベース内の不要で非効率的なインデックス (ローカルインデックスとグローバルセカンダリインデックスを含む) を特定するのに役立ちます。この機能は、インデックスの削除や再構築など、関連するインデックスに必要な調整を行うのに役立つ最適化の提案を提供することもできます。これにより、書き込みパフォーマンスが向上し、クエリパフォーマンスを損なうことなくストレージスペースが節約されます。

説明

データベースのパフォーマンスを維持するために、インデックス診断を定期的に実行することをお勧めします。

の前提条件

インデックス診断機能は、PolarDB-X 5.4.17-16859297以降で使用できます。インスタンスバージョンの表示方法については、「インスタンスのバージョンの表示と更新」をご参照ください。

使用状況ノート

インデックス診断機能は、データベースのAUTOモードとDRDSモードに使用できます。

構文

INSPECT [FULL] INDEX [FROM table_name] [MODE= {STATIC | DYNAMIC | MIXED}]

パラメータ

モード: 診断モード。 3つの診断モードがサポートされる。 MODEパラメーターを指定しない場合、STATICモードが使用されます。
- STATIC: このモードは、重複するインデックスなどの静的な問題を診断および識別するために使用されます。これらのタイプの問題は、インデックスが作成されるとすぐに検出できます。
- DYNAMIC: このモードは、選択性の低い列インデックスや未使用のインデックスなどの動的な問題を識別するために使用されます。このモードは、正確な診断結果を提供するために、ある期間にわたるインデックス使用に関するデータの収集を必要とする。詳細については、「DYNAMICモード」をご参照ください。
- MIXED: このモードは、静的問題と動的問題の両方を診断するために使用されます。また、このモードでは、正確な診断結果を提供するために、理想的には完全なビジネスサイクルにまたがる、ある期間にわたるインデックス使用に関するデータの収集が必要です。詳細については、「DYNAMICモード」をご参照ください。
FROM: 診断するデータテーブル。
- FROMパラメーターを指定しない場合、現在のデータベース内のすべてのテーブルが診断されます。
- FROMパラメーターを指定すると、指定されたデータテーブルのみが診断されます。
FULL: 診断操作中に生成される診断出力の詳細レベル。
- FULLパラメーターを指定すると、特定された問題や特定の提案に関する詳細情報を含む、インデックスに関する包括的な診断レポートが生成されます。
- FULLパラメーターを指定しない場合、診断出力には提案のみが含まれます。

静的モード

次の文を実行してtb1テーブルを作成します。

テーブルtb1を作成する (
  id int,
  名前varchar(20) 、
  コードvarchar(50) 、
  主キー (id) 、
  キー (名前) によるグローバルインデックスidx_name('name') パーティション、
  キー (名前、コード) によるグローバルインデックスidx_name_code('name' 、'code') パーティション、
  キー (id) によるグローバルインデックスidx_id('id') パーティション
) キーによるパーティション (id);

inspect full index from tb1\Gコマンドを実行し、STATICモードで診断結果を返します。

tb1\Gから完全なインデックスを検査する
*************************** 1。 行 ***************************
          スキーマ: d5
           テーブル: tb1
           インデックス: idx_id
      INDEX_TYPE: グローバルインデックス
    INDEX_COLUMN: id
 COVERING_COLUMN:
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  差別: 0.0
         問題: プライマリテーブルと同じルールを持つため、効果のないgsi 'idx_id'
  アドバイス (STEP1): テーブル 'tb1' を変更します。alter index 'idx_id' invisible;
  アドバイス (STEP2): テーブル 'tb1' を変更インデックス 'idx_id' を削除します。alter table 'tb1' ローカルインデックス 'idx_id' ('id') を追加します。*************************** 2. 行 ***************************
          スキーマ: d5
           テーブル: tb1
           インデックス: idx_name
      INDEX_TYPE: グローバルインデックス
    INDEX_COLUMN: 名前
 COVERING_COLUMN: id
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  差別: 0.0
         問題: インデックス列の重複: idx_name、idx_name_code;
  アドバイス (STEP1): alter table 'tb1' alter index 'idx_name' invisible;
  アドバイス (STEP2): テーブル 'tb1' を変更インデックス 'idx_name' を削除します。alter table 'tb1' ローカルインデックス 'idx_name' ('name') を追加します。3. 行 ***************************
          スキーマ: d5
           テーブル: tb1
           インデックス: idx_name_code
      INDEX_TYPE: グローバルインデックス
    INDEX_COLUMN: 名前、コード
 COVERING_COLUMN: id
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  差別: 0.0
         問題: なし
  アドバイス (STEP1): なし
  アドバイス (STEP2): なし
セットの3列 (0.42秒)

次のセクションでは、診断結果について説明します。

問題の行は、インデックスに関する問題を示しています。
- idx_idグローバルインデックスの問題: idx_idインデックスは、プライマリテーブルと同じパーティショニングルールを持つため、無効であると報告されます。
- idx_nameグローバルインデックスの問題: idx_nameのインデックス付き列は、idx_name_codeのインデックス付き列にすでに含まれている列の複製であるため、idx_nameインデックスは冗長として報告されます。
ADVICEラインは、問題のあるインデックスのパフォーマンスを最適化するための提案を提供します。提案は、2つのステップで提示され、特定のシナリオに基づいて異なり得る。
1. 最適化アドバイスのSTEP1では、INVISIBLE INDEXステートメントを使用して問題のあるインデックスを非表示にすることを提案しています。このステートメントは、オプティマイザからインデックスを隠します。この方法では、ビジネスSQLクエリは、クエリの実行にインデックスを使用または依存しません。インデックスを非表示にすることで、インデックスを完全に削除することなく、インデックス削除の影響を評価できます。
  説明
  INVISIBLE INDEX機能の詳細と、インデックス削除によるビジネスへの影響を評価する方法については、「INVISIBLE index」をご参照ください。
2. 提供される最適化アドバイスのSTEP2PolarDB-Xは、問題のあるインデックスを削除したり、問題のあるインデックスを最適化されたインデックスに置き換えるなどの提案を含みます。
  警告
  意図しない結果やデータベースシステムへの悪影響を防ぐために、インデックスを削除する前にインデックス削除の影響を評価することをお勧めします。

DYNAMICモード

DYNAMICモードでのインデックス診断は、インデックス使用データに依存します。診断を実行する前に、ビジネスの特性に基づいて、1日または1週間などのビジネスサイクル全体をカバーする使用状況データの完全なセットを収集することをお勧めします。

使用法

set global GSI_STATISTICS_COLLECTION=trueコマンドを実行して、データベースのインデックス使用統計の収集を有効にします。このオプションにより、一定期間にわたる使用状況データの収集が可能になります。
inspect full index mode=dynamic\Gコマンドを実行し、DYNAMICモードでインデックス診断を実行します。
GSI_STATISTICS_COLLECTIONオプションを有効にすると、データベースのクエリパフォーマンスにわずかな影響を与えることがあります (潜在的に約1% 減少します) 。インデックス診断が完了したら、set global GSI_STATISTICS_COLLECTION=falseコマンドを実行してこのオプションを無効にします。

シナリオ

ビジネスストレステスト段階でテストパフォーマンスを向上させます。ビジネスストレステストを開始する前に、DYNAMICモードでインデックス診断を実行します。これにより、ストレステストを開始する前に、非効率的なインデックスを特定し、診断提案に基づいて調整できます。
実行時にアプリケーションのパフォーマンスを最適化します。インデックスの使用状況に関する情報を収集し、ビジネストラフィックサイクルが完了するのを待ってから、DYNAMICモードでインデックスを調整してアプリケーションのパフォーマンスを最適化します。

その他

PolarDB-Xは、グローバルインデックスの最適化を容易にするためのINFORMATION_SCHEMA.GLOBAL_INDEXESビューを提供します。このビューでは、PolarDB-Xのグローバルインデックスの使用状況情報に簡単にアクセスして調べることができます。

次のステートメントを実行して、グローバルインデックスの使用状況を表示します。

select * from information_schema.global_indexes where table="tb1"\G
*************************** 1。 行 ***************************
             スキーマ: testdb
              テーブル: tb1
         NON_UNIQUE: 1
           KEY_NAME: idx_id_$3449
        INDEX_NAMES: id
     COVERING_NAMES:
         INDEX_TYPE: NULL
   DB_PARTITION_KEY:
DB_PARTITION_POLICY:
 DB_PARTITION_COUNT: NULL
   TB_PARTITION_KEY:
TB_PARTITION_POLICY:
 TB_PARTITION_COUNT: NULL
             ステータス: パブリック
         SIZE_IN_MB: 10.03
          USE_COUNT: 5
   LAST_ACCESS_TIME: 2023-06-08 10:06:33
        カーディナリティ: 389090
          ROW_COUNT: 404508
*************************** 2. 行 ***************************
             スキーマ: testdb
              テーブル: tb1
         NON_UNIQUE: 1
           KEY_NAME: idx_name_code_$2986
        INDEX_NAMES: 名前、コード
     COVERING_NAMES: id
         INDEX_TYPE: NULL
   DB_PARTITION_KEY:
DB_PARTITION_POLICY:
 DB_PARTITION_COUNT: NULL
   TB_PARTITION_KEY:
TB_PARTITION_POLICY:
 TB_PARTITION_COUNT: NULL
             ステータス: パブリック
         SIZE_IN_MB: 0.03
          USE_COUNT: 15
   LAST_ACCESS_TIME: 2023-06-08 10:10:06
        カーディナリティ: -1
          ROW_COUNT: 404508
セットの2列 (0.10秒)

レスポンスパラメーター

SIZE_IN_MB: インデックスによって占有されるストレージ容量。
USE_COUNT: GSI_STATISTICS_COLLECTIONオプションが有効になってからインデックスが使用された回数。
LAST_ACCESS_TIME: GSI_STATISTICS_COLLECTIONオプションが有効になってからインデックスが最後に使用された時刻。
CARDINALITY: インデックスのカーディナリティ。
ROW_COUNT: インデックス付きテーブルの行数。