查询耗时最高的慢sql
select query sql_text,
sum_query_time,
mnt as executions,
avg_query_time,
avg_proc_time,
avg_wait_time,
max_query_time,
avg_backoff_time,
Cop_proc_addr,
digest,
(case
when avg_proc_time = 0 then
'point_get or commit'
when (avg_proc_time > avg_wait_time and
avg_proc_time > avg_backoff_time) then
'coprocessor_process'
when (avg_backoff_time > avg_wait_time and
avg_proc_time < avg_backoff_time) then
'backoff'
else
'coprocessor_wait'
end) as type
from (select substr(query, 1, 100) query,
count(*) mnt,
avg(query_time) avg_query_time,
avg(process_time) avg_proc_time,
avg(wait_time) avg_wait_time,
max(query_time) max_query_time,
sum(query_time) sum_query_time,
digest,
Cop_proc_addr,
avg(backoff_time) avg_backoff_time
from information_schema.cluster_slow_query
where time >= '2022-07-14 17:00:00'
and time <= '2022-07-15 17:10:00'
and DB = 'web'
group by substr(query, 1, 100)) t
order by max_query_time desc limit 20;