MySQL性能分析工具profile使用教程

2024-07-24 12:46:37

字體：大中小

來源：轉載

供稿：網友

分析SQL執行帶來的開銷是優化SQL的重要手段。在MySQL數據庫中，可以通過配置profiling參數來啟用SQL剖析。該參數可以在全局和session級別來設置。對于全局級別則作用于整個MySQL實例，而session級別緊影響當前session。該參數開啟后，后續執行的SQL語句都將記錄其資源開銷，諸如IO，上下文切換，CPU，Memory等等。根據這些開銷進一步分析當前SQL瓶頸從而進行優化與調整。本文描述了如何使用MySQL profile，不涉及具體的樣例分析。

1、有關profile的描述

復制代碼代碼如下:

--當前版本
root@localhost[sakila]> show variables like 'version';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| version       | 5.6.17-enterprise-commercial-advanced |
+---------------+---------------------------------------+

--查看profiling系統變量
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |   --只讀變量，用于控制是否由系統變量開啟或禁用profiling
| profiling              | OFF   |   --開啟SQL語句剖析功能
| profiling_history_size | 15    |   --設置保留profiling的數目，缺省為15，范圍為0至100，為0時將禁用profiling
+------------------------+-------+

profiling [539]
If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement prof
is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to prof
information. See Section 13.7.5.32, “SHOW PROFILES Syntax”.

This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.
profiling_history_size [539]
The number of statements for which to maintain profiling information if profiling [539] is
enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively
disables profiling. See Section 13.7.5.32, “SHOW PROFILES Syntax”.
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.

--獲取profile的幫助
root@localhost[sakila]> help profile;
Name: 'SHOW PROFILE'
Description:
Syntax:
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL                --顯示所有的開銷信息
| BLOCK IO           --顯示塊IO相關開銷
| CONTEXT SWITCHES   --上下文切換相關開銷
| CPU                --顯示CPU相關開銷信息
| IPC                --顯示發送和接收相關開銷信息
| MEMORY             --顯示內存相關開銷信息
| PAGE FAULTS        --顯示頁面錯誤相關開銷信息
| SOURCE             --顯示和Source_function，Source_file，Source_line相關的開銷信息
| SWAPS              --顯示交換次數相關開銷的信息

The SHOW PROFILE and SHOW PROFILES statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.

*Note*: These statements are deprecated as of MySQL 5.6.7 and will be
removed in a future MySQL release. Use the Performance Schema instead;
see http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html.
--上面描述從5.6.7開始該命令將會被移除，用Performance Schema instead代替
--在Oracle數據庫中，是通過autotrace來剖析單條SQL并獲取真實的執行計劃以及其開銷信息

上一篇：MAC系統中添加MYSQL開機啟動的方法

下一篇：MySql服務器系統變量和狀態變量介紹