#1.查询不是sleep或者有状态的sql
SELECT * FROM `information_schema`.processlist WHERE command !='Sleep' OR state !='';
#2.查询运行中的事务
SELECT trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx;
#3.查看死锁
SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time
, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id
, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY a.requesting_trx_id;
#4.查看非innodb引擎表
SELECT table_name,table_schema,ENGINE FROM information_schema.tables WHERE ENGINE!='innodb'
AND table_schema NOT IN('mysql','information_schema','performance_schema');
#5.查看行锁
#查看当前有没有锁
select * from information_schema.innodb_locks;
SELECT trx_id,trx_state,trx_query FROM information_schema.innodb_trx WHERE trx_state LIKE '%LOCK%';
SELECT trx_tables_locked,trx_lock_structs,trx_wait_started,trx_state,trx_requested_lock_id FROM information_schema.INNODB_TRX
WHERE trx_tables_locked != 0 OR trx_wait_started IS NOT NULL;
#6.查看进程信息
#按客户端 IP 分组，看哪个客户端的链接数最多
SELECT client_ip,COUNT(client_ip) AS client_num
FROM (SELECT SUBSTRING_INDEX(HOST,':' ,1) AS client_ip FROM information_schema.processlist ) AS connect_info
GROUP BY client_ip ORDER BY client_num DESC;
#查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
SELECT * FROM information_schema.processlist WHERE Command != 'Sleep' AND INFO LIKE "%select%" ORDER BY TIME DESC;
#找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
SELECT CONCAT('kill ', id, ';') FROM information_schema.processlist WHERE Command != 'Sleep' AND TIME > 300 ORDER BY TIME DESC;
#7.查看无主键的表
SELECT table_schema,table_name,TABLE_TYPE,ENGINE,ROW_FORMAT,TABLE_ROWS
FROM information_schema.tables WHERE (table_schema,table_name) NOT IN
(SELECT DISTINCT table_schema,table_name FROM information_schema.columns WHERE COLUMN_KEY='PRI')
AND table_schema NOT IN ('sys','mysql','information_schema','performance_schema' );
#8.查看innodb行锁信息----------------------
SELECT p2.`HOST` 被阻塞方host,
p2.`USER` 被阻塞方用户,
r.trx_id 被阻塞方事务id,
r.trx_mysql_thread_id 被阻塞方线程号,
TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) 等待时间,
r.trx_query 被阻塞的查询,
l.lock_table 阻塞方锁住的表,
m.`lock_mode` 被阻塞方的锁模式,
m.`lock_type` "被阻塞方的锁类型(表锁还是行锁)",
m.`lock_index` 被阻塞方锁住的索引,
m.`lock_space` 被阻塞方锁对象的space_id,
m.lock_page 被阻塞方事务锁定页的数量,
m.lock_rec 被阻塞方事务锁定行的数量,
m.lock_data 被阻塞方事务锁定记录的主键值,
p.`HOST` 阻塞方主机,
p.`USER` 阻塞方用户,
b.trx_id 阻塞方事务id,
b.trx_mysql_thread_id 阻塞方线程号,
b.trx_query 阻塞方查询,
l.`lock_mode` 阻塞方的锁模式,
l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",
l.`lock_index` 阻塞方锁住的索引,
l.`lock_space` 阻塞方锁对象的space_id,
l.lock_page 阻塞方事务锁定页的数量,
l.lock_rec 阻塞方事务锁定行的数量,
l.lock_data 阻塞方事务锁定记录的主键值,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事务空闲的时间
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 l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY 等待时间 DESC ;
#9.查看哪些sql执行最多
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME !='information_schema'
ORDER BY COUNT_STAR DESC LIMIT 1;
4. 哪个SQL扫描的行数最多(IO消耗)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1\G
5. 哪个SQL使用的临时表最多
SELECT SCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1\G
6. 哪个SQL排序数最多(CPU消耗)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_SORT_ROWS desc LIMIT 1\G
7. 哪个索引使用最多
SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC limit 1;
8. 哪个索引没有使用过
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
9. 哪个表、文件逻辑IO最多（热数据）
SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM performance_schema.file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2