Mysql調優之profile詳解

繁星落眼眶發表於2018-11-22
MySql

前言

  1. 在我們做mysql效能分析的時候，最常用的有三種方式：

    （1）慢查詢 （分析出現出問題的sql）
（2）Explain （顯示了mysql如何使用索引來處理select語句以及連線表。可以幫助選擇更好的索引和寫出更優化的查詢語句）
（3）Profile（查詢到 SQL 會執行多少時間, 並看出 CPU/Memory 使用量, 執行過程中 Systemlock, Table lock 花多少時間等等.）
  2. 本章主要是對profile做簡單的概述，用來對某一條sql語句進行效能分析。
  3. Profiling是從 mysql5.0.3版本以後才開放的。但是在mysql5.7之後，profile資訊將逐漸被廢棄，mysql推薦使用performance schema。
  4. profile此工具可用來查詢SQL執行狀態，System lock和Table lock 花多少時間等等，對定位一條語句的I/O消耗和CPU消耗 非常重要。(SQL 語句執行所消耗的最大兩部分資源就是IO和CPU)

profile工具使用

  1. 檢視自己的mysql版本：

    mysql> select version(); 
+------------+
| version()  |
+------------+
| 5.6.35-log |
+------------+

  2. 檢視是否開啟profile功能（profiling=on代表開啟）：

    mysql> show variables like `%profil%`;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling        | YES  |
| profiling              | OFF    |
| profiling_history_size | 15    |
+------------------------+-------+

  3. 開啟profile：

    mysql> set profile=1;

  4. 開啟profile之後，執行要分析的sql語句：

    mysql> select t1.*,t2.action from pre_forum_thread as t1
left join 
(select a.* from pre_forum_threadmod as a,(select tid,max(dateline) as dateline from pre_forum_threadmod group by tid) as b
where a.tid=b.tid and a.dateline=b.dateline) as t2
on t1.tid=t2.tid
where t1.displayorder>=0 and t1.fid in (47,49) and t1.tid > 100318 
and (t1.authorid =7683017 or t2.action<>`DWN` or t2.action is null )
order by t1.dateline desc limit 20;

  5. 檢視生成的profile資訊：

    mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------+
| Query_ID | Duration  | Query                                                                                                  |
+----------+------------+--------------------------------------------------------------------------------------------------------+
|        1 | 1.37183777 | select t1.*,t2.action from pre_forum_thread as t1  |
|        2 | 0.00078796 | show columns from `bbs`.`t2` |
|        3 | 0.00150425 | show columns from `bbs`.`pre_forum_thread` |
+----------+------------+--------------------------------------------------------------------------------------------------------+

  6. 獲取指定的query語句的開銷：

    mysql> show profile for query 2; 
+----------------------+----------+
| Status              | Duration |
+----------------------+----------+
| starting            | 0.000147 |
| checking permissions | 0.000023 |
| Opening tables      | 0.000047 |
| init                | 0.000081 |
| System lock          | 0.000031 |
| optimizing          | 0.000034 |
| statistics          | 0.001650 |
| preparing            | 0.000046 |
| executing            | 0.000018 |
| Sending data        | 2.460588 |
| end                  | 0.000041 |
| query end            | 0.000019 |
| closing tables      | 0.000022 |
| freeing items        | 0.000055 |
| cleaning up          | 0.000085 |
+----------------------+----------+

  7. 關閉profile：

    mysql> set profiling=0;

  8. 相關具體的引數：

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

例如，想要檢視cpu和io開銷可以執行命令：
mysql> SHOW profile CPU,BLOCK IO  FOR query 2;

總結

  1. 一般簡易的流程：

    （1）set profiling=1; //開啟profile分析
（2）run your sql1;
（3）run your sql2;
（4）show profiles;    //檢視sql1,sql2的語句分析
（5）SHOW profile CPU,BLOCK IO io FOR query 1; //檢視CPU、IO消耗
（6）set profiling=0; //關閉profile分析

參考連結

https://blog.csdn.net/ty_hf/a…
https://www.linuxidc.com/Linu…

相關文章