Mysql调优之profile的使用方法

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分析

以上就是Mysql调优之profile的使用方法的详细内容，更多请关注php中文网其它相关文章！