MYSQL常用的几个SQL

按照客户端IP分组，看哪个客户端的连接数最多

select client_ip, count(client_ip) as client_num
from (select substring_index(host, ':', 1) as client_ip
      from information_schema.PROCESSLIST) as connect_info
group by client_ip
order by client_num desc;

查看正在执行的线程，并按Time倒排序，看看有没有执行时间特别长的线程

select *
from information_schema.PROCESSLIST
where COMMAND != 'Sleep'
order by Time desc;

找出所有执行时间超过5分钟的线程，并拼出kill语句，方便后面查杀

select concat('kill ', id, ';')
from information_schema.PROCESSLIST
where COMMAND != 'Sleep' and Time > 300
order by Time desc;

performance_schema 开启相关监控项

update performance_schema.setup_instruments
set ENABLED = 'YES', TIMED = 'YES'
where NAME like '%statement%';

update performance_schema.setup_instruments
set ENABLED = 'YES', TIMED = 'YES'
where NAME like '%stage%';

update performance_schema.setup_consumers
set ENABLED = 'YES'
where NAME like '%events_statements_%';

update performance_schema.setup_consumers
set ENABLED = 'YES'
where NAME like '%events_stages_%';

查找并分析慢SQL

select EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
from performance_schema.events_statements_history_long
where SQL_TEXT like '%salaries%';

select EVENT_NAME as Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
from performance_schema.events_stages_history_long where NESTING_EVENT_ID = 945;