数据库MySQL的运行情况主要是检查什么_MySQL 中常用有效检测数据库运行状态SQL脚本...

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

发布时间：2018-07-09 13:12,

浏览次数：289

, 标签：

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 脚本只适应特定的数据库版本，不代表所有数据库版本。