之前我介紹過(guò)msyql查詢優(yōu)化explain檢查命令的使用,explain主要是檢查sql語(yǔ)句的基本性能,sql是否優(yōu)秀,但不能查看具體的涉及硬件資源的開銷,本日要介紹的這個(gè)profiling工具可以更細(xì)節(jié)的查看資源的開銷,比較詳細(xì).

首先這款性能檢查工具是針對(duì)每個(gè)session生效的,session結(jié)束了就要重要發(fā)起查詢檢測(cè).

默認(rèn)是關(guān)閉的,必要手動(dòng)開啟：

SET profiling = 1;

開啟之后,發(fā)往mysql服務(wù)器的語(yǔ)句可以通過(guò)SHOW PROFILES顯示出來(lái),默認(rèn)顯示15條,最大設(shè)置為100,通過(guò)設(shè)置變量profiling_history_size實(shí)現(xiàn),設(shè)置為0將會(huì)禁用profiling.

語(yǔ)法

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

關(guān)于type的定義英文也簡(jiǎn)單：

• 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

使用示例

查看有沒(méi)有啟用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)

運(yùn)行要闡發(fā)的SQL語(yǔ)句

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)

檢查所有抓取到的闡發(fā)語(yǔ)句性能指標(biāo)

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)

顯示單個(gè)闡發(fā)語(yǔ)句性能指標(biāo),指最近執(zhí)行次數(shù)最多的那一條

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)

具體查看某條闡發(fā)語(yǔ)句的性能

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)

其他使用方式

也可以通過(guò)查表的方式查看分析語(yǔ)句的性能,所有show能看到的都會(huì)記錄在INFORMATION_SCHEMA表中,好比：

SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;

SHOW與INFORMATION_SCHEMA對(duì)應(yīng)關(guān)系表：

注意

INFORMATION_SCHEMA這個(gè)表的使用方式已經(jīng)在mysql5.7.2已經(jīng)標(biāo)志廢除了,在未來(lái)的版本將會(huì)徹底刪除掉,SHOW的使用方式在未來(lái)的版本也會(huì)替代掉,替代使用方式為MySQL Performance Schema,具體的參考官網(wǎng)的使用：https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html

以上profiling所有介紹翻譯來(lái)源于官網(wǎng),原版可以參考：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

點(diǎn)擊下面存眷我的頭條號(hào),每天一篇干貨!