如何识别最耗资源的SQL

发布网友发布时间：2022-04-08 06:37

共3个回答

懂视网时间：2022-04-08 10:59

profiling=1;

执行要分析的SQL语句

mysql> select count(1) from wechat_employee,Employee;

显示profiles表

mysql> show profiles;

查询结果：

+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query      |
+----------+------------+-----------------------------------------------+
| 1 | 0.00072975 | select count(1) from wechat_employee,Employee |
| 2 | 0.00028450 | set profiling=1    |
| 3 | 0.00324875 | select count(1) from wechat_employee,Employee |
+----------+------------+-----------------------------------------------+

查询详细信息：

show profile cpu,block io for query 3;

结果：

+--------------------------------+----------+----------+------------+--------------+---------------+
| Status    | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting   | 0.000041 | 0.000000 | 0.000000 |  0 |  0 |
| Waiting for query cache lock | 0.000073 | 0.000000 | 0.000000 |  0 |  0 |
| checking query cache for query | 0.000025 | 0.000000 | 0.000000 |  0 |  0 |
| checking privileges on cached | 0.000023 | 0.000000 | 0.000000 |  0 |  0 |
| checking permissions  | 0.000018 | 0.000000 | 0.000000 |  0 |  0 |
| checking permissions  | 0.000012 | 0.000000 | 0.000000 |  0 |  0 |
| sending cached result to clien | 0.003009 | 0.000000 | 0.004000 |  0 |  0 |
| logging slow query  | 0.000032 | 0.000000 | 0.000000 |  0 |  0 |
| cleaning up   | 0.000018 | 0.000000 | 0.000000 |  0 |  0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
9 rows in set (0.00 sec)

使用Profiles分析SQL语句执行时间和消耗资源

标签：mysql profiling

热心网友时间：2022-04-08 08:07

执行最慢的SQL语句

SELECT

(total_elapsed_time / execution_count)/1000 N'平均时间ms'

,total_elapsed_time/1000 N'总花费时间ms'

,total_worker_time/1000 N'所用的CPU总时间ms'

,total_physical_reads N'物理读取总次数'

,total_logical_reads/execution_count N'每次逻辑读次数'

,total_logical_reads N'逻辑读取总次数'

,total_logical_writes N'逻辑写入总次数'

,execution_count N'执行次数'

,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) N'执行语句'

,creation_time N'语句编译时间'

,last_execution_time N'上次执行时间'

FROM

sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st

WHERE

SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) not like '�tch%'

ORDER BY

total_elapsed_time / execution_count DESC;

--总耗CPU最多的前个SQL:
SELECT TOP
20

total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],

last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],

SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN
qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),

qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs
WITH(nolock)
CROSS apply
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC

--平均耗CPU最多的前个SQL:
SELECT TOP 20

total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],

last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],

max_worker_time /1000 AS [最大执行时间(ms)],

SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN
qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),

qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs
WITH(nolock)
CROSS apply
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY
(qs.total_worker_time/qs.execution_count/1000) DESC

热心网友时间：2022-04-08 09:25

1，看执行计划里面的cost值，作为参考
2，看sql的执行时间。