索引诊断

背景信息

“建立索引”是数据库中加速查询的常用手段，可以显著提高查询效率。但索引会在数据表更新的时候同步更新，因此索引会降低写入效率。此外，索引还会占用数据库的存储空间。因此，您需要合理使用索引，避免建立过多数量的或不必要的索引。

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：索引的行数。