慢sql表分析

慢sql记录到mysql.slow_log表

1、查询oss库3月慢sql

select
user_host as '客户端地址',
start_time as '执行时间点',
db as '查询数据库',
query_time as '查询耗时',
rows_examined as '扫描行数',
rows_sent as '返回行数',
lock_time as '锁耗时',
cast(sql_text as char) as '查询语句'
from mysql.slow_log
where db = 'oos'

and start_time >= '2022-03-01 00:00:00'
and start_time < '2022-04-01 00:00:00'
order by query_time desc;

2、查询oss库3月高频sql

select
cast(sql_text as char) as '查询语句',
count(*) as '执行频次'
from mysql.slow_log
where db = 'oos'

and start_time >= '2022-03-01 00:00:00'
and start_time < '2022-04-01 00:00:00'
group by '执行频次';

3、查询oss库3月慢sql分布

select CONCAT(m.tm,m.con) from
(SELECT
tm tm,
COUNT(*) con
FROM
(SELECT
CASE
WHEN TIME_TO_SEC(a.query_time) >= 1
AND TIME_TO_SEC(a.