如何查询低效索引

查询示例

• 查看所有索引的使用情况：

  SELECT * FROM information_schema.logical_index_usage;

• 查找从未被使用的索引（TOTAL_USAGE = 0）：

  SELECT * FROM information_schema.logical_index_usage
  WHERE TOTAL_USAGE = 0;

• 按逻辑表名过滤：

  SELECT * FROM information_schema.logical_index_usage
  WHERE LOGICAL_TABLE = 'my_table';

• 按索引名过滤：

  SELECT * FROM information_schema.logical_index_usage
  WHERE INDEX_NAME = 'idx_col_b';

• 按逻辑库名过滤：

  SELECT * FROM information_schema.logical_index_usage
  WHERE LOGICAL_SCHEMA = 'my_database';

• 组合过滤，查询特定表的特定索引：

  SELECT * FROM information_schema.logical_index_usage
  WHERE LOGICAL_TABLE = 'my_table' AND INDEX_NAME = 'idx_col_b';

• 查找全局索引（GSI）上的局部索引使用情况：

  SELECT * FROM information_schema.logical_index_usage
  WHERE GSI_NAME != '' AND GSI_NAME IS NOT NULL;

• 按使用量排序，查找最热或最冷的索引：

  SELECT LOGICAL_TABLE, INDEX_NAME, TOTAL_USAGE, AVG_USAGE, PARTITION_COUNT
  FROM information_schema.logical_index_usage
  ORDER BY TOTAL_USAGE DESC;

• 查找低效索引（分区多但总使用量很低）：

  SELECT LOGICAL_TABLE, INDEX_NAME, TOTAL_USAGE, PARTITION_COUNT, AVG_USAGE
  FROM information_schema.logical_index_usage
  WHERE PARTITION_COUNT > 4 AND AVG_USAGE < 10
  ORDER BY AVG_USAGE ASC;

查询示例

• 查看所有索引在各个物理分区的使用情况：

  SELECT * FROM information_schema.physical_index_usage;

• 查找各个物理分区中从未被使用的索引（COUNT_STAR = 0）：

  SELECT * FROM information_schema.physical_index_usage
  WHERE COUNT_STAR = 0;

• 按索引名过滤：

  SELECT * FROM information_schema.physical_index_usage
  WHERE INDEX_NAME = 'idx_col_b';

• 按逻辑表名过滤：

  SELECT * FROM information_schema.physical_index_usage
  WHERE LOGICAL_TABLE = 'my_table';

• 按逻辑库名过滤：

  SELECT * FROM information_schema.physical_index_usage
  WHERE LOGICAL_SCHEMA = 'my_database';

• 查看 GSI 物理表上的索引统计：

  SELECT
      OBJECT_SCHEMA,
      OBJECT_NAME,
      INDEX_NAME,
      COUNT_STAR,
      LOGICAL_SCHEMA,
      LOGICAL_TABLE
  FROM information_schema.physical_index_usage
  WHERE LOGICAL_SCHEMA = 'my_database'
    AND LOGICAL_TABLE LIKE 'gsi_name%'
  ORDER BY COUNT_STAR DESC
  LIMIT 100

语法

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

参数说明

• MODE：指定诊断模式，支持 3 种诊断模式。

  • STATIC（默认）：用于诊断和发现静态问题，如索引的重复。刚建立索引时即可发现这类问题。

  • DYNAMIC：用于诊断和发现动态问题，如索引列的区分度太低、索引从未被使用。这类问题需要在索引被建立后，业务运行一段时间才能被发现。（需开启全局索引使用统计）

  • MIXED：同时诊断静态和动态问题。MIXED 模式也需要收集一段时间的索引使用数据，才能给出准确诊断结果。（需开启全局索引使用统计）

• FROM：指定数据表。

  • 不附加 FROM 选项时，默认对当前数据库的所有表的索引进行诊断。

  • 附加 FROM 选项时，仅对指定的数据表进行诊断。

• FULL：指定输出信息的详细程度。

  • 附加 FULL 选项时，会输出索引的详细诊断结果，包括存在的问题和建议。

  • 不附加 FULL 选项时，仅输出建议。

查询示例

• 例如，对下表 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 MODE=MIXED\G，可以得到 MIXED 模式的诊断结果：

  *************************** 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`);

  • PROBLEM给出索引存在的问题（如与主表分区规则相同、索引列与其他索引重复、USE_COUNT为0从未被使用等）。

  • ADVICE栏给出两步优化建议：

    • STEP1：建议先用invisible index隐藏该索引以评估业务影响。

    • STEP2：在确认无影响后再删除（或删除后重建更优的索引）。

    为避免影响业务，请务必先执行STEP1评估，再执行STEP2的删除。