文档

索引诊断

更新时间:
重要

本文中含有需要您注意的重要提示信息,忽略该信息可能对您的业务造成影响,请务必仔细阅读。

本文档向您介绍PolarDB-X的索引诊断功能,您可以使用此功能诊断和发现数据库中低效的索引,进而采取措施提升数据库的性能。

背景信息

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

PolarDB-X的索引诊断功能,可以帮助您发现数据库中不必要的、低效的索引(包括局部索引和全局二级索引),并给出优化建议帮助您对相关索引做出必要调整(删除或重建),从而在不影响查询性能的前提下,提升写入性能,节约存储空间。

说明

为使得您的数据库保持良好性能,建议您定期进行索引诊断。

前提条件

本功能支持PolarDB-X5.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:索引的行数。