轉(zhuǎn)載自?mysql語句性能開銷檢測profiling詳解

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

首先這款性能檢查工具是針對每個session生效的，session結(jié)束了就要重要發(fā)起查詢檢測。

默認(rèn)是關(guān)閉的，需要手動開啟：

SET profiling = 1;

開啟之后，發(fā)往mysql服務(wù)器的語句可以通過SHOW PROFILES顯示出來，默認(rèn)顯示15條，最大設(shè)置為100，通過設(shè)置變量profiling_history_size實現(xiàn)，設(shè)置為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

??

關(guān)于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)

運(yùn)行要分析的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)

檢查所有抓取到的分析語句性能指標(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)

顯示單個分析語句性能指標(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)

具體查看某條分析語句的性能

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對應(yīng)關(guān)系表：

INFORMATION_SCHEMA?Name SHOW?Name Remarks

QUERY_ID	Query_ID
SEQ
STATE	Status
DURATION	Duration
CPU_USER	CPU_user
CPU_SYSTEM	CPU_system
CONTEXT_VOLUNTARY	Context_voluntary
CONTEXT_INVOLUNTARY	Context_involuntary
BLOCK_OPS_IN	Block_ops_in
BLOCK_OPS_OUT	Block_ops_out
MESSAGES_SENT	Messages_sent
MESSAGES_RECEIVED	Messages_received
PAGE_FAULTS_MAJOR	Page_faults_major
PAGE_FAULTS_MINOR	Page_faults_minor
SWAPS	Swaps
SOURCE_FUNCTION	Source_function
SOURCE_FILE	Source_file
SOURCE_LINE	Source_line

注意

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

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

總結(jié)

以上是生活随笔為你收集整理的mysql语句性能开销检测profiling详解的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。