Mysql-性能分析语句show profile和show pr
2020-10-07 本文已影响0人
牧码人zhouz
作用
show profile 和 show profiles语句用于追踪并展示 当前会话 中所执行语句的资源消耗信息，用于sql语句的性能分析，方便后续sql调优。
格式
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}
说明
profiling默认关闭，如需使用，需手动开启，开启命令如下：
mysql> SET profiling = 1;
SHOW PROFILES 显示最近发送给mysql server 的语句列表，列表大小由 profiling_history_size 变量控制，默认为15条，最大为100条，设置为0则实际关闭该选项。
SHOW PROFILE和SHOW PROFILES语句本身不会被统计，但是非法的或错误的语句会被统计。
SHOW PROFILE显示单条语句的详细信息。如果没有FOR QUERY n语句，输出会包含最近执行过的多条语句；包括FOR QUERY n语句的话，SHOW PROFILE会只显示第n条语句的信息；n的值对应于Query_ID，改值可通过SHOW PROFILES查看。
默认情况下，SHOW PROFILE 只显示 Status and Duration 列.
各个type值得意义如下：
- ALL 显示所有信息
- BLOCK IO 显示块输入输出操作的次数
- CONTEXT SWITCHES 显示上下文切换次数
- CPU 显示用户和系统CPU使用时间
- IPC 显示发送和接收消息的次数
- MEMORY 暂未实现
- PAGE FAULTS 显示缺页次数
- SOURCE 显示源文件中的函数名以及函数在文件中的行号
- SWAPS 显示swap次数
profiling信息 是与session关联的，当session结束时, 它的profiling信息就会被丢弃。
使用示例
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| query end | 0.000107 |
| freeing items | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating table | 0.000056 | 0.000028 | 0.000028 |
| After create | 0.011363 | 0.000217 | 0.001571 |
| query end | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow query | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
在某些架构上，性能分析只有一部分能起作用。对于那些依赖于
getrusage()系统调用的值，在不支持这些调用的系统上（例如windows）会返回NULL。此外，性能分析是按每个进程而不是每个线程进行的。这意味着，服务器上除了你自己线程外的其他线程的活动信息，也会影响你所看到的统计信息。
profiling信息也可从INFORMATION_SCHEMA 的PROFILING表中查到，例如，下面的查询是等价的：
SHOW PROFILE FOR QUERY 2;
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;
