MySQL使用profile分析SQL語句執(zhí)行過程

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

1、有關(guān)profile的描述

查看profiling系統(tǒng)變量

mysql> show variables like '%profil%';??

+------------------------+-------+

| Variable_name??????????| Value |

+------------------------+-------+

| have_profiling???????? | YES?? |??#只讀變量,用于控制是否由系統(tǒng)變量開啟或禁用profiling;

| profiling??????????????| OFF?? |??#開啟或關(guān)閉SQL語句剖析功能;

| profiling_history_size | 15????|??#設(shè)置保留profiling的數(shù)目,缺省為15,范圍為0至100,為0時將禁用profiling;

+------------------------+-------+

3 rows in set (0.00 sec)

獲取profile的幫助

mysql> help profile;??

Name: 'SHOW PROFILE'??

Description:??

Syntax:??

SHOW PROFILE [type [, type] ... ]??

????[FOR QUERY n]??

????[LIMIT row_count [OFFSET offset]]??


type:??

????ALL????????????????#顯示所有的開銷信息;

??| BLOCK IO?????????? #顯示塊IO相關(guān)開銷;

??| CONTEXT SWITCHES?? #上下文切換相關(guān)開銷;

??| CPU????????????????#顯示CPU相關(guān)開銷信息;

??| IPC????????????????#顯示發(fā)送和接收相關(guān)開銷信息;

??| MEMORY???????????? #顯示內(nèi)存相關(guān)開銷信息;

??| PAGE FAULTS????????#顯示頁面錯誤相關(guān)開銷信息;

??| SOURCE???????????? #顯示和Source_function,Source_file,Source_line相關(guān)的開銷信息;

??| SWAPS??????????????#顯示交換次數(shù)相關(guān)開銷的信息;

2、開啟porfiling

啟用session級別的profiling

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

驗證修改后的結(jié)果

mysql> show variables like '%profil%';

+------------------------+-------+

| Variable_name??????????| Value |

+------------------------+-------+

| have_profiling???????? | YES?? |

| profiling??????????????| ON????|

| profiling_history_size | 15????|

+------------------------+-------+

3 rows in set (0.00 sec)

發(fā)布SQL查詢

mysql> select * from zabbix.history_uint where clock<1488466329 limit 1;

查看當(dāng)前session所有已產(chǎn)生的profile

mysql> show profiles;

+----------+------------+------------------------------------------------------------------+

| Query_ID | Duration?? | Query????????????????????????????????????????????????????????????|

+----------+------------+------------------------------------------------------------------+

|????????1 | 0.00044625 | show variables like '%profil%'?????????????????????????????????? |

|????????2 | 6.43230200 | select * from zabbix.history_uint where clock<1488466329 limit 1 |

+----------+------------+------------------------------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

我們看到有2個warning，之前一個，現(xiàn)在一個

mysql> show warnings;

+---------+------+--------------------------------------------------------------------------------------------------------------+

| Level?? | Code | Message??????????????????????????????????????????????????????????????????????????????????????????????????????|

+---------+------+--------------------------------------------------------------------------------------------------------------+

| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |

+---------+------+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

告警是說SHOW?PROFILES命令將來會被Performance?Schema替換掉。

3、獲取SQL語句的開銷信息

開啟profiling后，我們可以通過show?profile等方式查看，其實這些開銷信息被記錄到information_schema.profiling表中。注show?profile之類的語句不會被profiling，即自身不會產(chǎn)生Profiling。

我們下面的這個show?profile查看的是show?warnings產(chǎn)生的相應(yīng)開銷。

mysq> show profile;????

+----------------+----------+??

| Status???????? | Duration |??

+----------------+----------+??

| starting?????? | 0.000141 |??

| query end??????| 0.000058 |??

| closing tables | 0.000014 |??

| freeing items??| 0.001802 |??

| cleaning up????| 0.000272 |??

+----------------+----------+

如下面的查詢show?warnings被添加到profiles

mysql> show profiles;

+----------+------------+------------------------------------------------------------------+

| Query_ID | Duration?? | Query????????????????????????????????????????????????????????????|

+----------+------------+------------------------------------------------------------------+

|????????1 | 0.00041150 | show variables like '%profil%'?????????????????????????????????? |

|????????2 | 6.41118075 | select * from zabbix.history_uint where clock<1488466329 limit 1 |

|????????3 | 0.00003900 | show warnings????????????????????????????????????????????????????|

+----------+------------+------------------------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

獲取指定查詢的開銷（Druation表示持續(xù)時間）

mysql> show profile for query 2;

+----------------------+----------+

| Status?????????????? | Duration |

+----------------------+----------+

| starting???????????? | 0.000043 |

| checking permissions | 0.000004 |

| Opening tables?????? | 0.000014 |

| init???????????????? | 0.000019 |

| System lock??????????| 0.000004 |

| optimizing?????????? | 0.000009 |

| statistics?????????? | 0.000062 |

| preparing????????????| 0.000012 |

| executing????????????| 0.000002 |

| Sending data???????? | 6.410967 |

| end??????????????????| 0.000009 |

| query end????????????| 0.000005 |

| closing tables?????? | 0.000010 |

| freeing items????????| 0.000011 |

| cleaning up??????????| 0.000011 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

Sending data：這個狀態(tài)的名稱很具有誤導(dǎo)性，所謂的“Sending data”并不是單純的發(fā)送數(shù)據(jù)，而是包括“收集 + 發(fā)送 數(shù)據(jù)”。

query end：表示語句執(zhí)行完畢了，但是還有一些后續(xù)工作沒做完時的狀態(tài)。

freeing items：釋放查詢緩存里面的空間，如果是DML操作，所以相應(yīng)的緩存里的記錄就無效了，所以需要有這一步做處理。

查看所有開銷

mysql> show profile all for query 2 ;

查看特定部分的開銷，如下為CPU部分的開銷

mysql> show profile cpu for query 2 ;

如下為MEMORY部分的開銷

mysql> show profile memory for query 2 ;

同時查看不同資源開銷

mysql> show profile block io,cpu for query 2;

4、INFORMATION_SCHEMA.PROFILING

上面已經(jīng)看到了，show profile命令即將移除，所以可以直接去information_schema.profiling表查看，靈活度更大，其表結(jié)構(gòu)信息如下：

mysql> desc information_schema.profiling;

+---------------------+--------------+------+-----+----------+-------+

| Field?????????????? | Type???????? | Null | Key | Default??| Extra |

+---------------------+--------------+------+-----+----------+-------+

| QUERY_ID????????????| int(20)??????| NO?? |???? | 0????????|?????? |

| SEQ???????????????? | int(20)??????| NO?? |???? | 0????????|?????? |

| STATE?????????????? | varchar(30)??| NO?? |???? |??????????|?????? |

| DURATION????????????| decimal(9,6) | NO?? |???? | 0.000000 |?????? |

| CPU_USER????????????| decimal(9,6) | YES??|???? | NULL???? |?????? |

| CPU_SYSTEM??????????| decimal(9,6) | YES??|???? | NULL???? |?????? |

| CONTEXT_VOLUNTARY?? | int(20)??????| YES??|???? | NULL???? |?????? |

| CONTEXT_INVOLUNTARY | int(20)??????| YES??|???? | NULL???? |?????? |

| BLOCK_OPS_IN????????| int(20)??????| YES??|???? | NULL???? |?????? |

| BLOCK_OPS_OUT?????? | int(20)??????| YES??|???? | NULL???? |?????? |

| MESSAGES_SENT?????? | int(20)??????| YES??|???? | NULL???? |?????? |

| MESSAGES_RECEIVED?? | int(20)??????| YES??|???? | NULL???? |?????? |

| PAGE_FAULTS_MAJOR?? | int(20)??????| YES??|???? | NULL???? |?????? |

| PAGE_FAULTS_MINOR?? | int(20)??????| YES??|???? | NULL???? |?????? |

| SWAPS?????????????? | int(20)??????| YES??|???? | NULL???? |?????? |

| SOURCE_FUNCTION???? | varchar(30)??| YES??|???? | NULL???? |?????? |

| SOURCE_FILE???????? | varchar(20)??| YES??|???? | NULL???? |?????? |

| SOURCE_LINE???????? | int(20)??????| YES??|???? | NULL???? |?????? |

+---------------------+--------------+------+-----+----------+-------+

18 rows in set (0.00 sec)

下面的SQL語句用于查詢query_id為2的SQL開銷，且按最大耗用時間倒序排列

mysql> set @query_id=2;

SELECT STATE, SUM(DURATION) AS Total_R,??

ROUND(??

?? 100 * SUM(DURATION) /??

???? (SELECT SUM(DURATION)??

??????????FROM INFORMATION_SCHEMA.PROFILING??

??????????WHERE QUERY_ID = @query_id??

??????), 2) AS Pct_R,??

?? COUNT(*) AS Calls,??

?? SUM(DURATION) / COUNT(*) AS "R/Call"??

FROM INFORMATION_SCHEMA.PROFILING??

WHERE QUERY_ID = @query_id??

GROUP BY STATE??

ORDER BY Total_R DESC;

+----------------------+----------+--------+-------+--------------+

| STATE????????????????| Total_R??| Pct_R??| Calls | R/Call?????? |

+----------------------+----------+--------+-------+--------------+

| Sending data???????? | 6.410967 | 100.00 |???? 1 | 6.4109670000 |

| statistics?????????? | 0.000062 |?? 0.00 |???? 1 | 0.0000620000 |

| starting???????????? | 0.000043 |?? 0.00 |???? 1 | 0.0000430000 |

| init???????????????? | 0.000019 |?? 0.00 |???? 1 | 0.0000190000 |

| Opening tables?????? | 0.000014 |?? 0.00 |???? 1 | 0.0000140000 |

| preparing????????????| 0.000012 |?? 0.00 |???? 1 | 0.0000120000 |

| cleaning up??????????| 0.000011 |?? 0.00 |???? 1 | 0.0000110000 |

| freeing items????????| 0.000011 |?? 0.00 |???? 1 | 0.0000110000 |

| closing tables?????? | 0.000010 |?? 0.00 |???? 1 | 0.0000100000 |

| optimizing?????????? | 0.000009 |?? 0.00 |???? 1 | 0.0000090000 |

| end??????????????????| 0.000009 |?? 0.00 |???? 1 | 0.0000090000 |

| query end????????????| 0.000005 |?? 0.00 |???? 1 | 0.0000050000 |

| System lock??????????| 0.000004 |?? 0.00 |???? 1 | 0.0000040000 |

| checking permissions | 0.000004 |?? 0.00 |???? 1 | 0.0000040000 |

| executing????????????| 0.000002 |?? 0.00 |???? 1 | 0.0000020000 |

+----------------------+----------+--------+-------+--------------+

15 rows in set (0.01 sec)

停止profile,可以設(shè)置profiling參數(shù)，或者在session退出之后,profiling會被自動關(guān)閉。


轉(zhuǎn)自：http://www.ywnds.com/?p=8677

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 【MySQL】Linux下MySQL 5.5、5.6和5.7的RPM、二進(jìn)制和源碼安裝 1.1BLOG文檔結(jié)構(gòu)圖 ...
    小麥苗DB寶閱讀 10,897評論 0贊 31
  • 什么是數(shù)據(jù)庫？ 數(shù)據(jù)庫是存儲數(shù)據(jù)的集合的單獨的應(yīng)用程序。每個數(shù)據(jù)庫具有一個或多個不同的API，用于創(chuàng)建，訪問，管理...
    chen_000閱讀 4,144評論 0贊 19
  • 更新不穩(wěn)定，抱歉。 上一章 這群刺客僵置在了徐福的門前，因為徐福房間中的凌亂與刺鼻的嘔吐味道都使的他們厭惡，他們沒...
    島主王仙客閱讀 383評論 0贊 0
  • 一 “我要成為武林盟主，行俠仗義，懲惡除奸！” 我站在磨盤上，手拿一把竹劍。毛驢站在一邊，悠閑地甩著尾巴。它是我唯...
    閻浮小學(xué)僧閱讀 896評論 26贊 11
  • 今天去剪頭發(fā)，聽到旁邊的首席理發(fā)師跟他的老客戶聊天。 大致內(nèi)容是他越來越瘦的秘密是每天只睡4小時，事情太多睡不著。...
    數(shù)據(jù)之美閱讀 311評論 0贊 0

友情鏈接更多精彩內(nèi)容