mysql query profile_Mysql调优之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;