schema_redundant_indexes

• 查找重复或冗余索引，数据来源：x$schema_flattened_keys，该数据来源视图被称作schema_redundant_indexes视图的辅助视图

  • schema_redundant_indexes视图在MySQL 5.7.9中新增

• schema_redundant_indexes视图字段含义如下：

  • TABLE_SCHEMA：包含冗余或重复索引的表对应的schema名称
  • TABLE_NAME：包含冗余或重复索引的表名
  • redundant_index_name：冗余或重复的索引名称
  • redundant_index_columns：冗余或重复索引中的列名
  • redundant_index_non_unique：冗余或重复索引中非唯一列的数量
  • dominant_index_name：与重复或冗余索引相比占据优势的索引名称
  • dominant_index_columns：占据优势的索引中的列名
  • dominant_index_non_unique：占据优势的索引中非唯一列的数量
  • subpart_exists：重复或冗余索引是否是前缀索引
  • sql_drop_index：针对重复或冗余索引生成的drop index语句

• 视图定义语句

CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW schema_redundant_indexes (
  table_schema,  table_name,  redundant_index_name,  redundant_index_columns,  redundant_index_non_unique,  
dominant_index_name,  dominant_index_columns,  dominant_index_non_unique,  subpart_exists,  sql_drop_index
) AS
  SELECT
    redundant_keys.table_schema,
    redundant_keys.table_name,
    redundant_keys.index_name AS redundant_index_name,
    redundant_keys.index_columns AS redundant_index_columns,
    redundant_keys.non_unique AS redundant_index_non_unique,
    dominant_keys.index_name AS dominant_index_name,
    dominant_keys.index_columns AS dominant_index_columns,
    dominant_keys.non_unique AS dominant_index_non_unique,
    IF(redundant_keys.subpart_exists OR dominant_keys.subpart_exists, 1 ,0) AS subpart_exists,
    CONCAT(
      'ALTER TABLE `', redundant_keys.table_schema, '`.`', redundant_keys.table_name, '` DROP INDEX `', redundant_keys.index_name, '`'
      ) AS sql_drop_index
  FROM
    x$schema_flattened_keys AS redundant_keys
    INNER JOIN x$schema_flattened_keys AS dominant_keys
    USING (TABLE_SCHEMA, TABLE_NAME)
  WHERE
    redundant_keys.index_name != dominant_keys.index_name
    AND (
      (
        /* Identical columns */
        (redundant_keys.index_columns = dominant_keys.index_columns)
        AND (
          (redundant_keys.non_unique > dominant_keys.non_unique)
          OR (redundant_keys.non_unique = dominant_keys.non_unique
                AND IF(redundant_keys.index_name='PRIMARY', '', redundant_keys.index_name) > IF(dominant_keys.index_name='PRIMARY', '', dominant_keys.index_name)
          )
        )
      )
      OR
      (
        /* Non-unique prefix columns */
        LOCATE(CONCAT(redundant_keys.index_columns, ','), dominant_keys.index_columns) = 1
        AND redundant_keys.non_unique = 1
      )
      OR
      (
        /* Unique prefix columns */
        LOCATE(CONCAT(dominant_keys.index_columns, ','), redundant_keys.index_columns) = 1
        AND dominant_keys.non_unique = 0
      )
    );

• 视图查询信息示例

admin@localhost : sys 11:21:13> select * from schema_redundant_indexes limit 1\G;
*************************** 1. row ***************************
              table_schema: test
                table_name: test
      redundant_index_name: i_id
  redundant_index_columns: id
redundant_index_non_unique: 1
      dominant_index_name: i_id_id2
    dominant_index_columns: id,id2
dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `test`.`test` DROP INDEX `i_id`
1 row in set (0.01 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-redundant-indexes.html

---

上一篇： schema_object_overview视图 |

下一篇： schema_table_lock_waits,x$schema_table_lock_waits视图