之前我介绍过msyql查询优化explain检查命令的使用，explain主要是检查sql语句的基本性能，sql是否优秀，但不能查看具体的涉及硬件资源的开销，今天要介绍的这个profiling工具可以更细节的查看资源的开销，比较详细。
首先这款性能检查工具是针对每个session生效的，session结束了就要重要发起查询检测。
默认是关闭的，需要手动开启：
SET profiling = 1;
开启之后，发往mysql服务器的语句可以通过SHOW PROFILES显示出来，默认显示15条，最大设置为100，通过设置变量profiling_history_size实现，设置为0将会禁用profiling。
语法
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
关于type的定义英文也简单：
ALL displays all information
BLOCK IO displays counts for block input and output operations
CONTEXT SWITCHES displays counts for voluntary and involuntary context switches
CPU displays user and system CPU usage times
IPC displays counts for messages sent and received
MEMORY is not currently implemented
PAGE FAULTS displays counts for major and minor page faults
SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
SWAPS displays swap counts
使用示例
查看有没有启用profiling
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
开启profiling
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
运行要分析的SQL语句
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)
你也可以查看CPU或者其他资源消耗信息
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)
其他使用方式
也可以通过查表的方式查看分析语句的性能，所有show能看到的都会记录在INFORMATION_SCHEMA表中，比如：
SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;
SHOW与INFORMATION_SCHEMA对应关系表：
INFORMATION_SCHEMANameSHOWNameRemarks
QUERY_IDQuery_ID
SEQ
STATEStatus
DURATIONDuration
CPU_USERCPU_user
CPU_SYSTEMCPU_system
CONTEXT_VOLUNTARYContext_voluntary
CONTEXT_INVOLUNTARYContext_involuntary
BLOCK_OPS_INBlock_ops_in
BLOCK_OPS_OUTBlock_ops_out
MESSAGES_SENTMessages_sent
MESSAGES_RECEIVEDMessages_received
PAGE_FAULTS_MAJORPage_faults_major
PAGE_FAULTS_MINORPage_faults_minor
SWAPSSwaps
SOURCE_FUNCTIONSource_function
SOURCE_FILESource_file
SOURCE_LINESource_line
注意
INFORMATION_SCHEMA这个表的使用方式已经在mysql5.7.2已经标记废除了，在未来的版本将会彻底删除掉，SHOW的使用方式在未来的版本也会替代掉，替代使用方式为MySQL Performance Schema，具体的参考官网的使用：https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html
以上profiling所有介绍翻译来源于官网，原版可以参考：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
网友评论