MySQL show processlist 具体语句 mysql show profiles

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQL profile，不涉及具体的样例分析。

如何查看执行SQL的耗时的步骤：开启profile、发送sql、查看profile的资源开销结果、关闭profile。

1 mysql> select version();
2 profile默认是不打开的
3 mysql> show profiles;
4 Empty set (0.02 sec)
5 验证修改后的结果
6
7 mysql> show variables like "%pro%";
8 可以看到profiling 默认是OFF的。
9 开启profile，然后测试
10
11 开启profile
12 mysql> set profiling=1;
13 获取profile的帮助
14 help profile;
15
16 root@localhost[sakila]> help profile;
17 Name: 'SHOW PROFILE'
18 Description:
19 Syntax:
20 SHOW PROFILE [type [, type] ... ]
21 [FOR QUERY n]
22 [LIMIT row_count [OFFSET offset]]
23
24 type:
25 ALL --显示所有的开销信息
26 | BLOCK IO --显示块IO相关开销
27 | CONTEXT SWITCHES --上下文切换相关开销
28 | CPU --显示CPU相关开销信息
29 | IPC --显示发送和接收相关开销信息
30 | MEMORY --显示内存相关开销信息
31 | PAGE FAULTS --显示页面错误相关开销信息
32 | SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
33 | SWAPS --显示交换次数相关开销的信息
执行业务SQL，并用profile分析示例：
--发布SQL查询
1 root@localhost[sakila]> select count(*) from customer;
2 +----------+
3 | count(*) |
4 +----------+
5 | 599 |
6 +----------+
7
8 --查看当前session所有已产生的profile
9 root@localhost[sakila]> show profiles;
10 +----------+------------+--------------------------------+
11 | Query_ID | Duration | Query |
12 +----------+------------+--------------------------------+
13 | 1 | 0.00253600 | show variables like '%profil%' |
14 | 2 | 0.00138150 | select count(*) from customer |
15 +----------+------------+--------------------------------+
16 2 rows in set, 1 warning (0.01 sec)
17
18 --我们看到有2个warning，之前一个，现在一个
19 root@localhost[sakila]> show warnings; --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉
20 +---------+------+--------------------------------------------------------------------------------------------------------------+
21 | Level | Code | Message |
22 +---------+------+--------------------------------------------------------------------------------------------------------------+
23 | Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |
24 +---------+------+--------------------------------------------------------------------------------------------------------------+
获取SQL语句的开销信息
--可以直接使用show profile来查看上一条SQL语句的开销信息
--注，show profile之类的语句不会被profiling，即自身不会产生Profiling
--我们下面的这个show profile查看的是show warnings产生的相应开销
1 root@localhost[sakila]> show profile;
2 +----------------+----------+
3 | Status | Duration |
4 +----------------+----------+
5 | starting | 0.000141 |
6 | query end | 0.000058 |
7 | closing tables| 0.000014 |
8 | freeing items | 0.001802 |
9 | cleaning up | 0.000272 |
10 +----------------+----------+
11
12 --如下面的查询show warnings被添加到profiles
13 root@localhost[sakila]> show profiles;
14 +----------+------------+--------------------------------+
15 | Query_ID | Duration | Query |
16 +----------+------------+--------------------------------+
17 | 1 | 0.00253600 | show variables like '%profil%' |
18 | 2 | 0.00138150 | select count(*) from customer |
19 | 3 | 0.00228600 | show warnings |
20 +----------+------------+--------------------------------+
21
22 --获取指定查询的开销(第二条查询的开销明细)
23 root@localhost[sakila]> show profile for query 2;
24 +----------------------+----------+
25 | Status | Duration |
26 +----------------------+----------+
27 | starting | 0.000148 |
28 | checking permissions | 0.000014 |
29 | Opening tables | 0.000047 |
30 | init | 0.000023 |
31 | System lock | 0.000035 |
32 | optimizing | 0.000012 |
33 | statistics | 0.000019 |
34 | preparing | 0.000014 |
35 | executing | 0.000006 |
36 | Sending data | 0.000990 |
37 | end | 0.000010 |
38 | query end | 0.000011 |
39 | closing tables | 0.000010 |
40 | freeing items | 0.000016 |
41 | cleaning up | 0.000029 |
42 +----------------------+----------+
43
44 --查看特定部分的开销，如下为CPU部分的开销
45 root@localhost[sakila]> show profile cpu for query 2 ;
46 +----------------------+----------+----------+------------+
47 | Status | Duration | CPU_user | CPU_system |
48 +----------------------+----------+----------+------------+
49 | starting | 0.000148 | 0.000000 | 0.000000 |
50 | checking permissions | 0.000014 | 0.000000 | 0.000000 |
51 | Opening tables | 0.000047 | 0.000000 | 0.000000 |
52 | init | 0.000023 | 0.000000 | 0.000000 |
53 | System lock | 0.000035 | 0.000000 | 0.000000 |
54 | optimizing | 0.000012 | 0.000000 | 0.000000 |
55 | statistics | 0.000019 | 0.000000 | 0.000000 |
56 | preparing | 0.000014 | 0.000000 | 0.000000 |
57 | executing | 0.000006 | 0.000000 | 0.000000 |
58 | Sending data | 0.000990 | 0.001000 | 0.000000 |
59 | end | 0.000010 | 0.000000 | 0.000000 |
60 | query end | 0.000011 | 0.000000 | 0.000000 |
61 | closing tables | 0.000010 | 0.000000 | 0.000000 |
62 | freeing items | 0.000016 | 0.000000 | 0.000000 |
63 | cleaning up | 0.000029 | 0.000000 | 0.000000 |
64 +----------------------+----------+----------+------------+
65
66 --如下为MEMORY部分的开销
67 root@localhost[sakila]> show profile memory for query 2 ;
68 +----------------------+----------+
69 | Status | Duration |
70 +----------------------+----------+
71 | starting | 0.000148 |
72 | checking permissions | 0.000014 |
73 | Opening tables | 0.000047 |
74 | init | 0.000023 |
75 | System lock | 0.000035 |
76 | optimizing | 0.000012 |
77 | statistics | 0.000019 |
78 | preparing | 0.000014 |
79 | executing | 0.000006 |
80 | Sending data | 0.000990 |
81 | end | 0.000010 |
82 | query end | 0.000011 |
83 | closing tables | 0.000010 |
84 | freeing items | 0.000016 |
85 | cleaning up | 0.000029 |
86 +----------------------+----------+
87
88 --同时查看不同资源开销
89 root@localhost[sakila]> show profile block io,cpu for query 2;
90 +----------------------+----------+----------+------------+--------------+---------------+
91 | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
92 +----------------------+----------+----------+------------+--------------+---------------+
93 | starting | 0.000148 | 0.000000 | 0.000000 | 0 | 0 |
94 | checking permissions | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
95 | Opening tables | 0.000047 | 0.000000 | 0.000000 | 0 | 0 |
96 | init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
97 | System lock | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
98 | optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
99 | statistics | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
100 | preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
101 | executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
102 | Sending data | 0.000990 | 0.001000 | 0.000000 | 0 | 0 |
103 | end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
104 | query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
105 | closing tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
106 | freeing items | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
107 | cleaning up | 0.000029 | 0.000000 | 0.000000 | 0 | 0 |
108 +----------------------+----------+----------+------------+--------------+---------------+

--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列

1 root@localhost[sakila]> set @query_id=2;
2
3 root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R,
4 ROUND(
5 100 * SUM(DURATION) /
6 (SELECT SUM(DURATION)
7 FROM INFORMATION_SCHEMA.PROFILING
8 WHERE QUERY_ID = @query_id
9 ), 2) AS Pct_R,
10 COUNT(*) AS Calls,
11 SUM(DURATION) / COUNT(*) AS "R/Call"
12 FROM INFORMATION_SCHEMA.PROFILING
13 WHERE QUERY_ID = @query_id
14 GROUP BY STATE
15 ORDER BY Total_R DESC;
16 +----------------------+----------+-------+-------+--------------+
17 | STATE | Total_R | Pct_R | Calls | R/Call |
18 +----------------------+----------+-------+-------+--------------+
19 | Sending data | 0.000990 | 71.53 | 1 | 0.0009900000 |--最大耗用时间部分为发送数据
20 | starting | 0.000148 | 10.69 | 1 | 0.0001480000 |
21 | Opening tables | 0.000047 | 3.40 | 1 | 0.0000470000 |
22 | System lock | 0.000035 | 2.53 | 1 | 0.0000350000 |
23 | cleaning up | 0.000029 | 2.10 | 1 | 0.0000290000 |
24 | init | 0.000023 | 1.66 | 1 | 0.0000230000 |
25 | statistics | 0.000019 | 1.37 | 1 | 0.0000190000 |
26 | freeing items | 0.000016 | 1.16 | 1 | 0.0000160000 |
27 | preparing | 0.000014 | 1.01 | 1 | 0.0000140000 |
28 | checking permissions | 0.000014 | 1.01 | 1 | 0.0000140000 |
29 | optimizing | 0.000012 | 0.87 | 1 | 0.0000120000 |
30 | query end | 0.000011 | 0.79 | 1 | 0.0000110000 |
31 | end | 0.000010 | 0.72 | 1 | 0.0000100000 |
32 | closing tables | 0.000010 | 0.72 | 1 | 0.0000100000 |
33 | executing | 0.000006 | 0.43 | 1 | 0.0000060000 |
34 +----------------------+----------+-------+-------+--------------+

--开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表

--如下面的查询，部分信息省略

1 profiling
2 root@localhost[information_schema]> select * from profiling limit 3,3\G;
3 *************************** 1. row ***************************
4 QUERY_ID: 1
5 SEQ: 5
6 STATE: init
7 DURATION: 0.000020
8 CPU_USER: 0.000000
9 CPU_SYSTEM: 0.000000
10 CONTEXT_VOLUNTARY: 0
11 CONTEXT_INVOLUNTARY: 0
12 BLOCK_OPS_IN: 0
13 BLOCK_OPS_OUT: 0
14 MESSAGES_SENT: 0
15 MESSAGES_RECEIVED: 0
16 PAGE_FAULTS_MAJOR: 0
17 PAGE_FAULTS_MINOR: 0
18 SWAPS: 0
19 SOURCE_FUNCTION: mysql_prepare_select
20 SOURCE_FILE: sql_select.cc
21 SOURCE_LINE: 1050
--停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭
1 root@localhost[sakila]> set profiling=off;
2 Query OK, 0 rows affected, 1 warning (0.00 sec)