mysql 查看SQL运行时间和大事务查询

--查看事务运行了多长时间

select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join
information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;


--查看对应的事物
SELECT
ps.id 'PROCESS ID',
ps.USER,
ps.HOST,
esh.EVENT_ID,
trx.trx_started,
esh.event_name 'EVENT NAME',
esh.sql_text 'SQL',
ps.time
FROM
PERFORMANCE_SCHEMA.events_statements_history esh
JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
WHERE
trx.trx_id IS NOT NULL
AND ps.USER != 'SYSTEM_USER'
ORDER BY
esh.EVENT_ID;


--查找大事务，超找出来的是大事务的大小 大事务/1024/1024 MB

mysqlbinlog mysql-bin.000002 | grep "GTID$(printf '\t')last_committed" -B 1 \
| grep -E '^# at' | awk '{print $3}' \
| awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
| sort -n -r | head -n 10