溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關MySQL如何使用profiling，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
Mysql SQL優化工具我們常使用explain去解析sql的執行，根據執行計劃去評估sql的性能消耗瓶頸，而MYSQL Profiling提供我們詳細的SQL執行過程中的cpu/io/swap/memory等使用情況以及每個過程執行時間消耗。
主要用途為1：查看SQL執行消耗瓶頸位置2、查看sql的執行過程，每步操作在具體哪個源碼文件的什么位置
這里簡單介紹下其使用方式：
profiling在mysql 5.0.37版本以后支持，在mysql5.7后可以通過performance_schema替代（25.18.1 Query Profiling Using Performance Schema），但通過session級別的追蹤比較方便
1、相關變量
(root:localhost:Wed Nov 15 16:32:50 2017)[performance_schema]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES | ##是否支持profile功能
| profiling | ON | ##是否開啟profile ，0|off表示關閉，1|on表示開啟
| profiling_history_size | 15 | ##展示的歷史sql數，默認是最近的15條，最大值是100
+------------------------+-------+
2、查看語法
可以通過help show profiles查看幫助文檔
show profiles可以查看歷史執行最近的15條sql
點擊(此處)折疊或打開
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
3、使用實例
1）開啟profling
(root:localhost:Wed Nov 15 16:37:00 2017)[dbtest]> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root:localhost:Wed Nov 15 16:37:14 2017)[dbtest]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
2）查看所有profiling記錄的sql
(root:localhost:Wed Nov 15 16:37:16 2017)[dbtest]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00089900 | show variables like '%profil%' |
+----------+------------+--------------------------------+
3）查看指定profiling記錄的sql
(root:localhost:Wed Nov 15 16:39:14 2017)[dbtest]> show profile for query 2 ;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000103 |
| checking permissions | 0.000008 |
| Opening tables | 0.000060 |
| init | 0.000023 |
| System lock | 0.000011 |
| optimizing | 0.000007 |
| statistics | 0.000016 |
| preparing | 0.000015 |
| executing | 0.000007 |
| Sending data | 0.000063 |
| end | 0.000004 |
| query end | 0.000010 |
| closing tables | 0.000012 |
| freeing items | 0.000016 |
| logging slow query | 0.000003 |
| logging slow query | 0.000070 |
| cleaning up | 0.000014 |
+----------------------+----------+
4）查看指定profiling記錄的sql，并且顯示cpu/block io/的步驟消耗信息
(root:localhost:Wed Nov 15 16:43:47 2017)[dbtest]> show profile cpu ,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000103 | NULL | NULL | NULL | NULL |
| checking permissions | 0.000008 | NULL | NULL | NULL | NULL |
| Opening tables | 0.000060 | NULL | NULL | NULL | NULL |
| init | 0.000023 | NULL | NULL | NULL | NULL |
| System lock | 0.000011 | NULL | NULL | NULL | NULL |
| optimizing | 0.000007 | NULL | NULL | NULL | NULL |
| statistics | 0.000016 | NULL | NULL | NULL | NULL |
| preparing | 0.000015 | NULL | NULL | NULL | NULL |
| executing | 0.000007 | NULL | NULL | NULL | NULL |
| Sending data | 0.000063 | NULL | NULL | NULL | NULL |
| end | 0.000004 | NULL | NULL | NULL | NULL |
| query end | 0.000010 | NULL | NULL | NULL | NULL |
| closing tables | 0.000012 | NULL | NULL | NULL | NULL |
| freeing items | 0.000016 | NULL | NULL | NULL | NULL |
| logging slow query | 0.000003 | NULL | NULL | NULL | NULL |
| logging slow query | 0.000070 | NULL | NULL | NULL | NULL |
| cleaning up | 0.000014 | NULL | NULL | NULL | NULL |
+----------------------+----------+----------+------------+--------------+---------------+
5）查看指定profiling記錄的sql，并且顯示每步源碼文件信息
(root:localhost:Wed Nov 15 16:44:09 2017)[dbtest]> show profile source for query 2;
+----------------------+----------+-----------------------+------------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+----------------------+----------+-----------------------+------------------+-------------+
| starting | 0.000103 | NULL | NULL | NULL |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 5635 |
| Opening tables | 0.000060 | open_tables | sql_base.cc | 5029 |
| init | 0.000023 | mysql_prepare_select | sql_select.cc | 1051 |
| System lock | 0.000011 | mysql_lock_tables | lock.cc | 304 |
| optimizing | 0.000007 | optimize | sql_optimizer.cc | 138 |
| statistics | 0.000016 | optimize | sql_optimizer.cc | 381 |
| preparing | 0.000015 | optimize | sql_optimizer.cc | 504 |
| executing | 0.000007 | exec | sql_executor.cc | 110 |
| Sending data | 0.000063 | exec | sql_executor.cc | 187 |
| end | 0.000004 | mysql_execute_select | sql_select.cc | 1106 |
| query end | 0.000010 | mysql_execute_command | sql_parse.cc | 5307 |
| closing tables | 0.000012 | mysql_execute_command | sql_parse.cc | 5383 |
| freeing items | 0.000016 | mysql_parse | sql_parse.cc | 6676 |
| logging slow query | 0.000003 | log_slow_do | sql_parse.cc | 2077 |
| logging slow query | 0.000070 | log_slow_do | sql_parse.cc | 2078 |
| cleaning up | 0.000014 | dispatch_command | sql_parse.cc | 1878 |
+----------------------+----------+-----------------------+------------------+-------------+
17 rows in set, 1 warning (0.00 sec)
6）查看指定profiling記錄的sql，并且顯示所有的步驟消耗信息
(root:localhost:Wed Nov 15 16:40:34 2017)[dbtest]> show profile all for query 2 \G
*************************** 1. row ***************************
Status: starting
Duration: 0.000103
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: NULL
Source_file: NULL
Source_line: NULL
*************************** 2. row ***************************
Status: checking permissions
Duration: 0.000008
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: check_access
Source_file: sql_parse.cc
Source_line: 5635
*************************** 3. row ***************************
Status: Opening tables
Duration: 0.000060
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5029
*************************** 4. row ***************************
Status: init
Duration: 0.000023
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_prepare_select
Source_file: sql_select.cc
Source_line: 1051
*************************** 5. row ***************************
Status: System lock
Duration: 0.000011
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_lock_tables
Source_file: lock.cc
Source_line: 304
*************************** 6. row ***************************
Status: optimizing
Duration: 0.000007
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 138
*************************** 7. row ***************************
Status: statistics
Duration: 0.000016
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 381
*************************** 8. row ***************************
Status: preparing
Duration: 0.000015
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 504
*************************** 9. row ***************************
Status: executing
Duration: 0.000007
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: exec
Source_file: sql_executor.cc
Source_line: 110
*************************** 10. row ***************************
Status: Sending data
Duration: 0.000063
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: exec
Source_file: sql_executor.cc
Source_line: 187
*************************** 11. row ***************************
Status: end
Duration: 0.000004
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_execute_select
Source_file: sql_select.cc
Source_line: 1106
*************************** 12. row ***************************
Status: query end
Duration: 0.000010
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 5307
*************************** 13. row ***************************
Status: closing tables
Duration: 0.000012
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 5383
*************************** 14. row ***************************
Status: freeing items
Duration: 0.000016
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_parse
Source_file: sql_parse.cc
Source_line: 6676
*************************** 15. row ***************************
Status: logging slow query
Duration: 0.000003
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: log_slow_do
Source_file: sql_parse.cc
Source_line: 2077
*************************** 16. row ***************************
Status: logging slow query
Duration: 0.000070
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: log_slow_do
Source_file: sql_parse.cc
Source_line: 2078
*************************** 17. row ***************************
Status: cleaning up
Duration: 0.000014
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: dispatch_command
Source_file: sql_parse.cc
Source_line: 1878
17 rows in set, 1 warning (0.00 sec)
關于“MySQL如何使用profiling”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
