x$schema_flattened_keys

• 帮助视图，用于帮助schema_redundant_indexes视图输出冗余索引信息，数据来源：INFORMATION_SCHEMA.STATISTICS

• x$schema_flattened_keys视图字段含义如下：

  • TABLE_SCHEMA：包含索引的表所在的schema名称
  • TABLE_NAME：包含索引的表名
  • INDEX_NAME：索引名称
  • NON_UNIQUE：索引中非唯一列的数量
  • subpart_exists：索引是否是前缀索引
  • index_columns：索引中列名称

• 视图定义语句

CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$schema_flattened_keys (
  table_schema,  table_name,  index_name,  non_unique,  subpart_exists,  index_columns
) AS
  SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    MAX(NON_UNIQUE) AS non_unique,
    MAX(IF(SUB_PART IS NULL, 0, 1)) AS subpart_exists,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE
    INDEX_TYPE='BTREE'
    AND TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
  GROUP BY
    TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

• 视图查询信息示例

admin@localhost : sys 01:01:20> select * from x$schema_flattened_keys limit 3;
+--------------+-------------+-------------------+------------+----------------+---------------+
| table_schema | table_name  | index_name        | non_unique | subpart_exists | index_columns |
+--------------+-------------+-------------------+------------+----------------+---------------+
| luoxiaobo    | public_num  | PRIMARY          |          0 |              0 | id            |
| luoxiaobo    | public_num  | public_name_index |          0 |              0 | public_name  |
| luoxiaobo    | t_luoxiaobo | PRIMARY          |          0 |              0 | id            |
+--------------+-------------+-------------------+------------+----------------+---------------+
3 rows in set (0.00 sec)

• 参考链接：辅助视图无参考链接

---

上一篇： x$ps_schema_table_statistics_io视图 |

下一篇： create_synonym_db()存储过程