最近项目上要求弄一下压测,看下各个服务的瓶颈,然后进行上线前的最后冲刺,于是又拿起了2年前看的mysql优化一书,挑灯夜战,写ppt的同时想着顺道写篇博客~~ 官网资料
profile开关
profile开关默认是关闭的,所以倒腾profile之前,先得查看profile是否打开。
- 查看profile是否打开
SELECT @@profiling //0关闭,1打开
或者
SHOW VARIABLES LIKE 'profiling'; //OFF关闭,1打开
若查询出结果为0,则需要手动打开
- 打开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