檢視連線
show processlist
-- 已開啟10秒以上的活躍連線
SELECT id,user,db,command,state,time,info FROM information_schema.processlist
where command <> 'sleep' and time > 10 order by time;
-- 已執行超過10s的執行計劃
SELECT id,user,db,command,state,time FROM information_schema.processlist
where command <> 'sleep' and time > 10 and info like "select xxx from xxx%";
-- 統計指定庫+表的資料量（行數、資料大小、索引大小）
select table_name, TABLE_ROWS,
concat(round(DATA_LENGTH/1024/1024, 2),'MB') as DATA_LENGTH,
concat(round( INDEX_LENGTH/1024/1024, 2),'MB') as INDEX_LENGTH,
concat(round(DATA_FREE/1024/1024, 2),'MB') as DATA_FREE
from information_schema.tables
where table_name = 'xx';
-- 查詢是否鎖表
SHOW OPEN TABLES where In_use > 0;
-- 檢視正在進行中的事務
SELECT * FROM information_schema.INNODB_TRX
-- 檢視死鎖日誌
SHOW ENGINE INNODB STATUS
-- 檢視等待鎖的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 連線數相關統計
-- 統計總數
SELECT count(*) FROM information_schema.processlist;
--生成快速kill id語句
select concat('kill ', id, ';'),Time from information_schema.processlist where 1=1 and Time > 1000 order by Time desc;
select * from information_schema.innodb_trx （ 當前執行的所有事務）
select * from information_schema.innodb_locks （當前出現的鎖）
select * from information_schema.innodb_lock_waits （鎖等待的對應關係）