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

点击下面存眷我的头条号,每天一篇干货!