[MVS]TIDB数据库慢SQL查询

慢SQL查询

搜索某个用户的 Top N 慢查询

### 排除 TiDB 内部的慢查询sql，并指定查找的用户名

select query_time, query, user

from information_schema.slow_query

where is_internal=false

and user="user1"

order by query_time desc

limit 2;

根据 SQL 指纹搜索同类慢查询:

1.先根据最近慢语句 List 查找特定慢语句:

select query_time, query, digest

from information_schema.slow_query

where is_internal = false

and time between '2025-03-01' and '2025-03-05'

order by query_time desc

limit 1 \G;

输出示例：

*************************** 1. row ***************************

query_time: 0.340376587

     query: drop table dep;

    digest: e63277c19304d71f13b9f5b47e636923b76faa096f6c79f5abd5ae4ae9917176

1 row in set (0.01 sec)

2.再根据 digest 确定同类慢查询:

select query, query_time

from information_schema.slow_query

where digest ="e63277c19304d71f13b9f5b47e636923b76faa096f6c79f5abd5ae4ae9917176" \G;

输出示例：

*************************** 1. row ***************************

     query: drop table dep;

query_time: 0.340376587

1 row in set (0.03 sec)

3.搜索统计信息为 pseudo 的慢查询 SQL 语句:

select query, query_time, stats

from information_schema.slow_query

where is_internal = false

and stats like '%pseudo%'\G;

输出示例：

*************************** 1. row ***************************

     query: SELECT count(*) FROM (SELECT  c.c_id, c.c_d_id, c.c_balance c1, c_ytd_payment,    (SELECT sum(ol_amount) FROM orders, order_line    WHERE OL_W_ID=O_W_ID AND OL_D_ID = O_D_ID AND OL_O_ID = O_ID AND OL_DELIVERY_D IS NOT NULL AND    O_W_ID=? AND O_D_ID=c.C_D_ID AND O_C_ID=c.C_ID) sm FROM customer c WHERE  c.c_w_id = ?) t1    WHERE c1+c_ytd_payment <> sm [arguments: (1, 1)];

query_time: 0.728453657

     stats: orders:pseudo,order_line:pseudo,customer:pseudo