在我们做mysql机能剖析的时刻,最经常运用的有三种体式格局:
(1)慢查询 (剖析涌现出问题的sql)
(2)Explain (显现了mysql怎样运用索引来处置惩罚select语句以及衔接表。能够协助挑选更好的索引和写出更优化的查询语句)
(3)Profile(查询到 SQL 会实行若干时候, 并看出 CPU/Memory 运用量, 实行过程当中 Systemlock, Table lock 花若干时候等等.)本章主如果对profile做简朴的概述,用来对某一条sql语句举行机能剖析。
Profiling是从 mysql5.0.3版本今后才开放的。但是在mysql5.7以后,profile信息将逐步被烧毁,mysql引荐运用performance schema。
profile此东西可用来查询SQL实行状况,System lock和Table lock 花若干时候等等,对定位一条语句的I/O斲丧和CPU斲丧 非常重要。(SQL 语句实行所斲丧的最大两部分资本就是IO和CPU)
profile东西运用
检察本身的mysql版本:
mysql> select version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+
检察是不是开启profile功用(profiling=on代表开启):
mysql> show variables like '%profil%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+
开启profile:
mysql> set profile=1;
开启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;
检察生成的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` | +----------+------------+--------------------------------------------------------------------------------------------------------+
猎取指定的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 | +----------------------+----------+
封闭profile:
mysql> set profiling=0;
相干细致的参数:
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)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的运用方法的细致内容,更多请关注ki4网别的相干文章!