1、首先查看此功能有没有开启,默认是OFF关闭状态
mysql> show variables like 'profiling%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
2、开启profiling
mysql> set profiling=1;
#关闭profiling
mysql> set profiling=0;
3、列出最近执行的SQL情况,默认15条
mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00058575 | show variables like 'profiling%' |
+----------+------------+----------------------------------+
1 row in set, 1 warning (0.01 sec)
4、查看某一条SQL的执行情况(query 1 是3查到的SQL ID)
语法格式: show profile [类型1,类型2] for query [ID]
类型: ALL:显示所有的开销信息
BLOCK IO:显示块IO相关开销
CONTEXT SWITCHES:上下文切换相关开销
CPU:显示CPU相关开销
IPC:显示发送和接受相关开销
MEMORY:显示内存相关开销
PAGE FAULTS:显示页面错误相关开销
SOURCE:显示和Source_function, Source_file,Source_line相关的相关开销
SWAPS:显示交换次数相关开销
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000102 | 0.000140 | 0.000015 | 0 | 0 |
| checking permissions | 0.000017 | 0.000029 | 0.000000 | 0 | 0 |
| Opening tables | 0.000044 | 0.000091 | 0.000000 | 0 | 0 |
| init | 0.000017 | 0.000032 | 0.000000 | 0 | 0 |
| System lock | 0.000014 | 0.000028 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000022 | 0.000000 | 0 | 0 |
| statistics | 0.000014 | 0.000012 | 0.000016 | 0 | 0 |
| preparing | 0.000013 | 0.000025 | 0.000000 | 0 | 0 |
| executing | 0.000227 | 0.000439 | 0.000015 | 0 | 0 |
| Sending data | 0.000020 | 0.000028 | 0.000015 | 0 | 0 |
| end | 0.000014 | 0.000026 | 0.000000 | 0 | 0 |
| query end | 0.000013 | 0.000022 | 0.000000 | 0 | 0 |
| closing tables | 0.000010 | 0.000020 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000013 | 0.000026 | 0.000000 | 0 | 0 |
| closing tables | 0.000011 | 0.000021 | 0.000000 | 0 | 0 |
| freeing items | 0.000024 | 0.000048 | 0.000000 | 0 | 0 |
| cleaning up | 0.000024 | 0.000047 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)
参数的分析
在show profile 的时候有一个字段叫status,几个重要的参数如下:
状态 | 描述 |
---|---|
System lock | 确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级 的锁引起的建议:如果耗时较大再关注即可,一般情况下都还好 |
Sending data | 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据 再发送给客户端,数据量很大时尤其经常能看见, 备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是 Writing to net。建议:通过索引或加上LIMIT,减少需要扫描并且发送 给客户端的数据量 |
Sorting result | 正在对结果进行排序,类似Creating sort index,不过是正常表, 而不是在内存表中进行排序建议:创建适当的索引 |
Table lock | 表级锁,没什么好说的,要么是因为MyISAM引擎表级锁, 要么是其他情况显式锁表 |
create sort index | 当前的SELECT中需要用到临时表在进行ORDER BY排序。 建议:创建适当的索引 |
checking query cache for querychecking privileges on cachedsending cached result to clienstoring result in query cache | 和query cache相关的状态,已经多次强烈建议关闭 |
除了上述几个字段,如果Status出现了如下几个字段,说明SQL性能问题很严重。
- converting HEAP to MyISAM :查询结果太大,内存不够用,往磁盘上存储了。
- Creating tmp table:创建临时表,首先拷贝原有数据到临时表,用完后再删除临时表,在数据量很大的情况下,异常的耗性能。
- Copying to tmp table on disk:把内存中的临时表复制到磁盘,也相当的耗费性能。
- locked:锁表了。