如果您观看了寻找设计不佳的模式和如何修复它们，您会看到 Marcos Albe 使用了一些非常有趣的查询。这些查询让您可以找到没有主键的表、具有非整数主键的表、不使用 InnoDB 的表、延迟最大的表和索引、比该表大 50% 的索引、查找重复索引和未使用的索引索引。正如所承诺的，它们在下面。
— 查找没有 PK 的表
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;
— 查找具有非整数 PK 的表
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’;
— 查找具有最长延迟的表和索引
— Amdhal 定律：通过优化系统的单个部分获得的整体性能改进受到实际使用改进部分的时间分数的限制
SELECT *
FROM sys.schema_table_statistics
WHERE table_schema= ‘test’
AND table_schema NOT IN (‘mysql’, ‘information_schema’, ‘sys’, ‘performance_schema’);
SELECT *
FROM sys.schema_index_statistics
WHERE table_schema=‘test’
AND table_schema NOT IN (‘mysql’, ‘information_schema’, ‘sys’, ‘performance_schema’);
— 查找索引 > data by 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索引长度 > 数据长度 * 1.5；
— 查找具有重复索引的表
SELECT table_schema,table_name,redundant_index_name ASredundant_index,redundant_index_columns ASredundant_columns,dominant_index_name AScovered_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’);
原文标题：Queries for Finding Poorly-Designed MySQL Schemas and How to Fix Them
原文作者：David Stokes
原文地址：https://www.percona.com/blog/queries-for-finding-poorly-designed-mysql-schemas-and-how-to-fix-them/
