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 （锁等待的对应关系）
https://blog.csdn.net/qq_15371293/article/details/124847589
https://zhuanlan.zhihu.com/p/493415374
