mysql死锁优化

查看连接
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 （锁等待的对应关系）