找出MySQL库中设计不好的Schemas并修复

使用以下脚本，找出数据库中设计不好的Schemas并修复

 

— 找出没有主键的表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT   t.table_schema,   t.table_name,   t.ENGINE FROM   information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE   t.table_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' ) AND t.table_type = 'BASE TABLE' GROUP BY   t.table_schema,   t.table_name,   t.ENGINE HAVING   SUM(IF( column_key IN ('PRI','UNI' ), 1, 0 )) = 0;

 

— 找出主键不是整型类型的表

?

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT   table_schema,   table_name,   column_name,   data_type,   character_maximum_length FROM   information_schema.COLUMNS WHERE   column_key IN ('PRI',' UNI' ) AND ordinal_position = 1 AND data_type NOT IN ('TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'BIGINT', 'TIMESTAMP', 'DATETIME' ) AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' );

 

— 找出不是innodb存储引擎的表

?

1 2 3 4 5 6 7 8 9 10

SELECT   t.table_schema,   t.table_name,   t.ENGINE FROM   information_schema.TABLES t WHERE   t.table_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' ) AND t.ENGINE <> 'INNODB' AND t.table_type = 'BASE TABLE';

 

— 查找时延最大的表和索引

?

1 2 3 4 5 6 7

SELECT   * FROM   sys.schema_table_statistics WHERE   table_schema ='abce' AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' );

 

?

1 2 3 4 5 6 7

SELECT   * FROM   sys.schema_index_statistics WHERE   table_schema ='abce' AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' );

 

— 找出索引大小大于表数据的50%的表

?

1 2 3 4 5 6 7 8 9 10 11

SELECT   table_schema,   table_name,   index_length,   data_length,   index_length / data_length AS index_to_data_ratio FROM   information_schema.TABLES WHERE   table_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' ) AND INDEX_LENGTH > DATA_LENGTH * 1.5;

 

— 找出有重复索引的表

?

1 2 3 4 5 6 7 8 9 10 11

SELECT   table_schema,   table_name,   redundant_index_name AS redundant_index,   redundant_index_columns AS redundant_columns,   dominant_index_name AS covered_by_index,   sql_drop_index FROM   sys.schema_redundant_indexes WHERE   table_schema NOT IN ( 'mysql', 'information_schema', 'sys', 'performance_schema' );

 

— 找出没被使用的索引

?

1 2 3 4 5 6

SELECT   * FROM   sys.schema_unused_indexes WHERE   object_schema NOT IN ('mysql', 'information_schema', 'sys', 'PERFORMANCE_SCHEMA' );