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 ;
select version(), user(), connection_id();


-- 所有会话及SQL执行情况
select t.PROCESSLIST_ID, t.THREAD_ID, t.PROCESSLIST_STATE, t.PROCESSLIST_COMMAND
    , block.BLOCKING_THREAD_ID, block.BLOCKING_EVENT_ID
    , 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 
    left join performance_schema.data_lock_waits block on t.THREAD_ID = block.REQUESTING_THREAD_ID 
where t.`TYPE` != 'BACKGROUND';


-- 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
;



// 使用悲观锁  模拟事务
set autocommit=0;　　
//设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;