PolarDB：索引診斷

背景資訊

“建立索引”是資料庫中加速查詢的常用手段，可以顯著提高查詢效率。但索引會在資料表更新的時候同步更新，因此索引會降低寫入效率。此外，索引還會佔用資料庫的儲存空間。因此，您需要合理使用索引，避免建立過多數量的或不必要的索引。

PolarDB-X的索引診斷功能，可以協助您探索資料庫中不必要的、低效的索引（包括局部索引和全域二級索引），並給出最佳化建議協助您對相關索引做出必要調整（刪除或重建），從而在不影響查詢效能的前提下，提升寫入效能，節約儲存空間。

前提條件

本功能支援PolarDB-X的5.4.17-16859297及以上版本。查看執行個體版本的方法請參見查看和升級執行個體版本。

注意事項

索引診斷功能同時支援AUTO模式資料庫與DRDS模式資料庫。

文法

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

參數說明

• MODE：診斷模式，支援3種診斷模式，不設定MODE參數時，預設為STATIC模式。

  • STATIC：用於診斷和發現靜態問題，如索引的重複。這類問題在索引剛建立就可以被發現。

  • DYNAMIC：用於診斷和發現動態問題，如索引列的區分度太低、索引從未被使用等。此模式需要收集一段時間內的索引使用資料後，才能給出準確的診斷結果，具體方法請參見DYNAMIC模式。

  • MIXED：同時診斷靜態和動態問題。該模式也需要收集一段時間（建議為一個完整的業務流量周期）的索引使用資料，才能給出準確的診斷結果，使用方法請參考DYNAMIC模式。

• FROM：需要診斷的資料表。

  • 不附加FROM選項時，預設對當前資料庫中所有表的索引進行診斷。

  • 附加FROM選項時，僅對指定的資料表進行診斷。

• FULL：輸出資訊的詳細程度。

  • 附加FULL選項時，會輸出索引的詳細診斷結果，包括存在的問題和建議。

  • 不附加FULL選項時，僅輸出建議。

STATIC模式

執行以下命令，建立表tb1。

create table tb1(
  id int,
  name varchar(20),
  code varchar(50),
  primary key(id),
  global index idx_name(`name`) partition by key(name),
  global index idx_name_code(`name`, `code`) partition by key(name, code),
  global index idx_id(`id`) partition by key(id)
) partition by key(id);

執行inspect full index from tb1\G，返回STATIC模式的診斷結果。

inspect full index from tb1\G
*************************** 1. row ***************************
          SCHEMA: d5
           TABLE: tb1
           INDEX: idx_id
      INDEX_TYPE: GLOBAL INDEX
    INDEX_COLUMN: id
 COVERING_COLUMN:
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  DISCRIMINATION: 0.0
         PROBLEM: ineffective gsi `idx_id` because it has the same rule as primary table
  ADVICE (STEP1): alter table `tb1` alter index `idx_id` invisible;
  ADVICE (STEP2): alter table `tb1` drop index `idx_id`;
alter table `tb1` add local index `idx_id` (`id`);
*************************** 2. row ***************************
          SCHEMA: d5
           TABLE: tb1
           INDEX: idx_name
      INDEX_TYPE: GLOBAL INDEX
    INDEX_COLUMN: name
 COVERING_COLUMN: id
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  DISCRIMINATION: 0.0
         PROBLEM: index columns duplicate: idx_name, idx_name_code;
  ADVICE (STEP1): alter table `tb1` alter index `idx_name` invisible;
  ADVICE (STEP2): alter table `tb1` drop index `idx_name`;
alter table `tb1` add local index `idx_name` (`name`);
*************************** 3. row ***************************
          SCHEMA: d5
           TABLE: tb1
           INDEX: idx_name_code
      INDEX_TYPE: GLOBAL INDEX
    INDEX_COLUMN: name,code
 COVERING_COLUMN: id
       USE_COUNT: 0
LAST_ACCESS_TIME: NULL
  DISCRIMINATION: 0.0
         PROBLEM: None
  ADVICE (STEP1): None
  ADVICE (STEP2): None
3 rows in set (0.42 sec)

診斷結果說明如下：

• PROBLEM欄輸出了當前索引存在的問題。

  • 全域索引idx_id的問題：分區方式和主表完全一致，這種全域索引無法提升查詢效能，是低效索引。

  • 全域索引idx_name的問題：索引列和索引idx_name_code有重複（idx_name_code的索引列包含了idx_name的索引列），所以idx_name是一個冗餘的索引。

• ADVICE欄輸出了2個步驟的最佳化建議（根據不同情況，最佳化建議會有變化）。

  1. 返回資訊中的step1，建議您使用invisible index語句隱藏此問題索引。這會使得該索引對最佳化器不可見，從而讓您的業務SQL在執行時繞過該索引。隱藏索引後，可以在不真正刪除問題索引的情況下，評估刪除該索引對業務帶來的影響。

     說明

     關於invisible index功能以及如何評估業務影響，請參見INVISIBLE INDEX。

  2. 返回資訊中的step2，PolarDB-X輸出的建議可能包括：直接刪除問題索引、刪除問題索引並建立一個更優的索引。

     警告

     為避免對業務造成影響，在刪除索引之前，請務必先通過隱藏索引評估能否真正刪除此問題索引。

DYNAMIC模式

DYNAMIC模式的診斷需要依據索引的使用資料，建議收集一個完整的業務流量周期的資料（如1天、1周，這取決於您的業務特點）後再進行診斷。

使用方法

1. 執行set global GSI_STATISTICS_COLLECTION=true，開啟資料庫的“索引使用資訊統計”開關，收集一段時間資料。

2. 執行inspect full index mode=dynamic\G，進行DYNAMIC模式的索引診斷。

3. 開啟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. row ***************************
             SCHEMA: testdb
              TABLE: 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
             STATUS: PUBLIC
         SIZE_IN_MB: 10.03
          USE_COUNT: 5
   LAST_ACCESS_TIME: 2023-06-08 10:06:33
        CARDINALITY: 389090
          ROW_COUNT: 404508
*************************** 2. row ***************************
             SCHEMA: testdb
              TABLE: tb1
         NON_UNIQUE: 1
           KEY_NAME: idx_name_code_$2986
        INDEX_NAMES: name, code
     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
             STATUS: PUBLIC
         SIZE_IN_MB: 0.03
          USE_COUNT: 15
   LAST_ACCESS_TIME: 2023-06-08 10:10:06
        CARDINALITY: -1
          ROW_COUNT: 404508
2 rows in set (0.10 sec)

返回資訊說明：

• SIZE_IN_MB：索引佔用的空間。

• USE_COUNT：自您開啟GSI_STATISTICS_COLLECTION後，索引被使用的次數。

• LAST_ACCESS_TIME：自您開啟GSI_STATISTICS_COLLECTION後，索引最後一次被使用的時間。

• CARDINALITY：索引的基數。

• ROW_COUNT：索引的行數。