mysql之profile分析（简单篇）

    最近项目上要求弄一下压测，看下各个服务的瓶颈，然后进行上线前的最后冲刺，于是又拿起了2年前看的mysql优化一书，挑灯夜战，写ppt的同时想着顺道写篇博客~~ 官网资料

profile开关

profile开关默认是关闭的，所以倒腾profile之前，先得查看profile是否打开。

1. 查看profile是否打开

SELECT @@profiling //0关闭，1打开
或者 
SHOW VARIABLES LIKE 'profiling'; //OFF关闭，1打开

若查询出结果为0，则需要手动打开

2. 打开profile

SET @@profiling = 1
或者
SET profiling=ON;

分析之路

• 首先使用show profiles查出最近15条SQL语句的运行状态；
• 其次对运行慢的sql，使用show profile for query 进行单独解析；

show profiles

show profiles;

显示最近的15条sql执行语句，可以看到：

Query_ID	Duration	Query
1	0.006	select * from table1
2	2.809	select t1.id, t2.id from table1 t1 left join table2 on table2.uid = ti.id

SHOW PROFILE FOR QUERY id

执行语句说明

SHOW PROFILE … FOR QUERY id 可指定资源类型查询

资源类型	说明
all	所有的开销信息
block io	块IO相关开销
context swtichs	上下文切换相关开销
cpu	cpu 相关开销
ipc	发送和接收相关开销
memory	内存相关开销
page faults	页面错误相关开销信息
source	和Source_function ,Source_file,Source_line 相关的开销信息
swaps	交换次数相关的开销信息

执行结果说明

参数名称	说明
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu
Block_ops_in	I/O 输入
Block_ops_out	I/O 输出

SHOW PROFILE FOR QUERY 1 

执行后结果类似如下表，可以看到执行过程中的每个步骤和所消耗时间。

Status	Duration
starting	0.000272
Executing hook on transaction	0.000012
executing	2.667285
…	…
query end	0.000012
…	…
cleaning up	0.000034

需优化的4种情况

若执行show profile for query id出现如下4种情况，则需要对sql进行优化！

• converting HEAP to MyISAM
  • 查询结果过大，需将结果从内存搬到磁盘
• creating tmp table
  • 拷贝数据到临时表
  • 用完再删除
• copying to tmp table on disk
  • 内存中临时表转移到磁盘
• locked