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

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

 

— 找出没有主键的表

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;

 

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

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存储引擎的表

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';

 

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

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

 

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

 

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

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;

 

— 找出有重复索引的表

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' );

 

— 找出没被使用的索引

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