MySQL 中常用有效检测数据库运行状态SQL脚本

1、查看数据库中不为 InnoDB 引擎的表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE
TABLE_SCHEMANOT IN ('sys', 'mysql', 'performance_schema', 'information_schema',
'test') AND ENGINE != 'InnoDB';
2、查看数据库中表的大小及数据量
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, CONCAT(round((DATA_LENGTH +
INDEX_LENGTH) /1024 / 1024, 2), 'MB') as data FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema',
'information_schema', 'test') ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;
3、查找数据库中无显式主键索引的表
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.TABLES t WHERE
(t.TABLE_SCHEMA, t.TABLE_NAME)NOT IN (SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI') AND t.TABLE_SCHEMA NOT
IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test');
4、查找数据库中主键为联合主键的表
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY
SEQ_IN_INDEX SEPARATOR',') cols, MAX(SEQ_IN_INDEX) len FROM
information_schema.STATISTICSWHERE INDEX_NAME = 'PRIMARY' AND TABLE_SCHEMA NOT
IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY
TABLE_SCHEMA, TABLE_NAMEHAVING len > 1;
5、查找数据库中不为自增主键的表
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE
TABLE_SCHEMANOT IN ('sys', 'mysql', 'performance_schema', 'information_schema',
'test') AND (TABLE_SCHEMA,TABLE_NAME) NOT IN (SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',
'performance_schema', 'information_schema', 'test') AND IS_NULLABLE = 'NO' AND
COLUMN_TYPELIKE '%int%' AND COLUMN_KEY = 'PRI' AND EXTRA = 'auto_increment');
6、查看数据库中存在外键约束的表
SELECT c.TABLE_SCHEMA, c.REFERENCED_TABLE_NAME, c.REFERENCED_COLUMN_NAME, c
.TABLE_NAME, c.COLUMN_NAME, c.CONSTRAINT_NAME, t.TABLE_COMMENT, r.UPDATE_RULE, r
.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE c JOIN information_schema
.TABLES t ON t.TABLE_NAME = c.TABLE_NAME JOIN information_schema.REFERENTIAL
_CONSTRAINTS r ON r.TABLE_NAME = c.TABLE_NAME AND r.CONSTRAINT_NAME = c
.CONSTRAINT_NAME AND r.REFERENCED_TABLE_NAME = c.REFERENCED_TABLE_NAME WHERE c
.REFERENCED_TABLE_NAME IS NOT NULL;
7、查找数据库中低区分度索引（区分度小于0.1）
SELECT p.TABLE_SCHEMA, p.TABLE_NAME, c.INDEX_NAME, c.car, p.car total FROM (
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(CARDINALITY) car FROM
information_schema.STATISTICSWHERE INDEX_NAME != 'PRIMARY' AND TABLE_SCHEMA NOT
IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) cINNER JOIN (SELECT TABLE_SCHEMA,
TABLE_NAME,MAX(CARDINALITY) car from information_schema.STATISTICS WHERE
INDEX_NAME ='PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql',
'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA,
TABLE_NAME) pON c.TABLE_NAME = p.TABLE_NAME AND c.TABLE_SCHEMA = p.TABLE_SCHEMA
WHERE p.car > 0 AND c.car / p.car < 0.1;
8、查找数据库中重复索引前缀的索引
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, a.cols, b.INDEX_NAME, b.cols
FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ',
GROUP_CONCAT(COLUMN_NAMEORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols
FROM information_schema.STATISTICS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',
'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a INNER JOIN (SELECT
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME
ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols FROM
information_schema.STATISTICSWHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',
'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) b ON a.TABLE_NAME = b.TABLE_NAME
AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.cols LIKE CONCAT(b.cols, '%') AND
a.INDEX_NAME != b.INDEX_NAME;
9、查找数据库中包索引重复包含主键列的索引
SELECT a.*, b.pk FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| '
, GROUP_CONCAT(COLUMN_NAMEORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') cols
FROM information_schema.STATISTICS WHERE INDEX_NAME != 'PRIMARY' AND
TABLE_SCHEMANOT IN ('sys', 'mysql', 'performance_schema', 'information_schema',
'test') GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a INNER JOIN (SELECT
TABLE_SCHEMA, TABLE_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY
SEQ_IN_INDEX SEPARATOR' | '), ' |') pk FROM information_schema.STATISTICS WHERE
INDEX_NAME ='PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql',
'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA,
TABLE_NAME) bON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.cols LIKE CONCAT('%', b.pk, '%');
10、查找数据库中没有被使用的索引
SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS FROM
performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE aINNER JOIN
information_schema.TABLES bON a.OBJECT_SCHEMA = b.TABLE_SCHEMA AND
a.OBJECT_NAME = b.TABLE_SCHEMAWHERE a.INDEX_NAME IS NOT NULL AND a.INDEX_NAME !=
'PRIMARY' AND a.COUNT_STAR = 0 AND OBJECT_SCHEMA NOT IN ('sys', 'mysql',
'performance_schema', 'information_schema') ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
11、查看数据库中的锁请求信息
SELECT r.TRX_ISOLATION_LEVEL, r.TRX_ID WAITING_TRX_ID, r.TRX_MYSQL_THREAD_ID
WAITING_TRX_THREAD, r.TRX_STATE WAITING_TRX_STATE, lr.LOCK_MODE
WAITING_TRX_LOCK_MODE, lr.LOCK_TYPE WAITING_TRX_LOCK_TYPE, lr.LOCK_TABLE
WAITING_TRX_LOCK_TABLE, lr.LOCK_INDEX WAITING_TRX_LOCK_INDEX, r.TRX_QUERY
WAITING_TRX_QUERY, b.TRX_ID BLOCKING_TRX_ID, b.TRX_MYSQL_THREAD_ID
BLOCKING_TRX_THREAD, b.TRX_STATE BLOCKING_TRX_STATE, lb.LOCK_MODE
BLOCKING_TRX_LOCK_MODE, lb.LOCK_TYPE BLOCKING_TRX_LOCK_TYPE, lb.LOCK_TABLE
BLOCKING_TRX_LOCK_TABLE, lb.LOCK_INDEX BLOCKING_TRX_LOCK_INDEX, b.TRX_QUERY
BLOCKING_QUERY FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN
INFORMATION_SCHEMA.INNODB_TRX b ON b.TRX_ID = W.BLOCKING_TRX_ID INNER JOIN
information_schema.INNODB_TRX R ON r.TRX_ID = W.REQUESTING_TRX_ID INNER JOIN
information_schema.INNODB_LOCKS lb ON lb.LOCK_TRX_ID = W.BLOCKING_TRX_ID INNER
JOIN information_schema.INNODB_LOCKS lr ON lr.LOCK_TRX_ID = W.REQUESTING_TRX_ID;
注：本文提供的 SQL 脚本只适应特定的数据库版本，不代表所有数据库版本。

• « 上一篇：我所认为的“资深售前”有哪些必杀技能！
• » 下一篇：线程池没你想的那么简单