MySQL常用的性能诊断语句

/*====MySQL===========================================================*/
select * from information_schema.PROCESSLIST p ;
select * from information_schema.INNODB_TRX it ;  --未提交的事务
select * from performance_schema.threads t where t.`TYPE` != 'BACKGROUND';
select * from performance_schema.data_locks;      --数据库锁的持有情况
select * from performance_schema.data_lock_waits; --锁等待情况（5.7对应的有可能是information_schema.innodb_lock_waits）
select * from performance_schema.events_statements_current;
select * from performance_schema.events_transactions_current etc ;

-- 所有会话及SQL执行情况
select t.PROCESSLIST_ID, t.THREAD_ID, t.PROCESSLIST_STATE, t.PROCESSLIST_COMMAND
    , t.PROCESSLIST_TIME, sc.NESTING_EVENT_TYPE, sc.SQL_TEXT
    , t.*, sc.*
from performance_schema.threads t
    join performance_schema.events_statements_current sc on t.THREAD_ID  = sc.THREAD_ID
where t.`TYPE` != 'BACKGROUND'
    and t.PROCESSLIST_USER  = ''
    and t.PROCESSLIST_HOST = '' ;

-- SQL阻塞情况
select dlw.REQUESTING_THREAD_ID, ts.PROCESSLIST_INFO as requesting_processlist_info
    , dlw.BLOCKING_THREAD_ID, tt.PROCESSLIST_INFO as blocking_processlist_info
    , dlw.*
from performance_schema.data_lock_waits dlw
    join performance_schema.threads ts on dlw.REQUESTING_THREAD_ID  = ts.THREAD_ID
    join performance_schema.threads tt on dlw.BLOCKING_THREAD_ID = tt.THREAD_ID ;

-- 根据阻塞线程id，查找历史执行的所有SQL
select *
from  performance_schema.events_statements_history sh 
where sh.THREAD_ID = @blk_thread_id;

-- 直接查找root block thread对应的SQL
with src as (
    select *
    from performance_schema.data_lock_waits dlw
    where not exists (
        select * from performance_schema.data_lock_waits dlw2
        where dlw.BLOCKING_THREAD_ID = dlw2.REQUESTING_THREAD_ID
    )
)
select *
from performance_schema.events_statements_history esh
where esh.THREAD_ID in (select src.BLOCKING_THREAD_ID from src)
order by esh.THREAD_ID, esh.TIMER_START
;


-- 所有未提交事务及对应会话的当前SQL
select t.PROCESSLIST_ID, t.THREAD_ID, t.PROCESSLIST_STATE, it.trx_id, sc.SQL_TEXT, it.*, t.*, sc.*
from information_schema.INNODB_TRX it
    join performance_schema.threads t on it.trx_mysql_thread_id  = t.PROCESSLIST_ID
    join performance_schema.events_statements_current sc on t.THREAD_ID  = sc.THREAD_ID
;

-- 开启事务
start transaction;
commit; 
-- kill会话
kill 191;  --thread_id

-- 当前连接（进程）
select connection_id();

select * 
from performance_schema.threads t 
where t.ProcessList_Id  = connection_id();