使用profile對Mysql調優的方法

下文主要給大家帶來使用profile對Mysql調優的方法，希望這些內容能夠帶給大家實際用處，這也是我編輯使用profile對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分析

對于以上關于使用profile對Mysql調優的方法，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。