mysql语句性能开销检测profiling详解

之前我介绍过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