查看正在执行的SQL
SELECT * FROM information_schema.processlist WHERE command !='Sleep';
查看数据库中的锁请求信息
SELECT r.`trx_id` waiting_trx_id,
r.`trx_mysql_thread_id` waiting_thread,
r.`trx_query` waiting_query,
b.`trx_id` bolcking_trx_id,
b.`trx_mysql_thread_id` blocking_thread,
b.`trx_query` block_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`;
表中查看当前未提交的事务
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trxG
查看数据库中不为 InnoDB 引擎的表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN
('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
AND ENGINE != 'InnoDB';
查看数据库中表的大小及数据量
查看总的数据库大小
select SUM(DATA) from (
SELECT TABLE_SCHEMA ,SUM(DATA) as data FROM (
SELECT TABLE_SCHEMA,
CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2), 'GB') AS DATA
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN
('sys', 'mysql', 'performance_schema', 'information_schema', 'test') )X GROUP BY TABLE_SCHEMA
) xx;
查看各个数据库大小
SELECT TABLE_SCHEMA ,SUM(DATA) FROM (
SELECT TABLE_SCHEMA,
CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2), 'GB') AS DATA
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN
('sys', 'mysql', 'performance_schema', 'information_schema', 'test') )X GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC ;
查看每个表大小
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;
查找数据库中无显式主键索引的表
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');
查找数据库中主键为联合主键的表
SELECT TABLE_SCHEMA, TABLE_NAME,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') cols,
MAX(SEQ_IN_INDEX) len
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
HAVING len > 1;
查找数据库中不为自增主键的表
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT 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_TYPE LIKE '%int%'
AND COLUMN_KEY = 'PRI'
AND EXTRA = 'auto_increment');
查看数据库中存在外键约束的表
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;
查找数据库中低区分度索引（区分度小于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.STATISTICS
WHERE INDEX_NAME != 'PRIMARY'
AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) c
INNER 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) p
ON c.TABLE_NAME = p.TABLE_NAME
AND c.TABLE_SCHEMA = p.TABLE_SCHEMA
WHERE p.car > 0
AND c.car / p.car < 0.1;
查找数据库中重复索引前缀的索引
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_NAME ORDER 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.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) 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;
查找数据库中包索引重复包含主键列的索引
SELECT a.*, b.pk
FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') cols
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, 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) b
ON a.TABLE_NAME = b.TABLE_NAME
AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.cols LIKE CONCAT('%', b.pk, '%');
查找数据库中没有被使用的索引
SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS
FROM performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE a
INNER JOIN information_schema.TABLES b
ON a.OBJECT_SCHEMA = b.TABLE_SCHEMA
AND a.OBJECT_NAME = b.TABLE_SCHEMA
WHERE 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;
查找数据库中没有创建索引的表
SELECT t.table_schema,t.table_schema FROM information_schema.tables AS t
LEFT JOIN (SELECT DISTINCT table_schema, table_name FROM information_schema.`KEY_COLUMN_USAGE` ) AS kt ON kt.table_schema=t.table_schema AND kt.table_name = t.table_name
WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'test','sys','zabbix') AND kt.table_name IS NULL
MySQL 查询某个数据库中所有包含数据记录的表名
select TABLE_NAME
from information_schema.TABLES
where TABLE_SCHEMA = '需要查询的数据库名' and TABLE_ROWS > 0;
查看MySQL里谁消耗了内存：
SELECT event_name,SUM_NUMBER_OF_BYTES_ALLOC FROM
performance_schema.memory_summary_global_by_event_name
ORDER BY SUM_NUMBER_OF_BYTES_ALLOC DESC LIMIT 10;
查看MySQL里哪些内部线程消耗了内存：
select event_name, SUM_NUMBER_OF_BYTES_ALLOC from
performance_schema.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;