之前我介紹過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

點擊下面存眷我的頭條號,每天一篇干貨!