MySQL常用的性能诊断语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

/*====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;