x$schema_flattened_keys - xiaoboluo768/mysql-system-schema GitHub Wiki
-
帮助视图，用于帮助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)
- 参考链接：辅助视图无参考链接