MySql-show profile

show profile 进行SQL分析

show profile由mysql提供的，可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优的测量。默认情况下，该参数处于关闭状态，并保存最近15次的运行结果。

show profile分析步骤

• 确保当前版本支持
• 开启功能

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+

-- 随便写几条sql语句，然后查看profile
show profiles；

mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00309675 | show variables like 'profiling'             |
|        2 | 0.05118375 | select * from students                      |
|        3 | 0.00114500 | select * from students group by gender      |
|        4 | 0.00073025 | select gender from students group by gender |
|        5 | 1.20525125 | select * from emp                           |
+----------+------------+---------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

-- 查看第一条条语句执行的明细
show profile cpu,block io for query 1;

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000159 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000060 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000116 | 0.000000 |   0.015625 |         NULL |          NULL |
| statistics           | 0.000071 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000015 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000046 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.002242 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000015 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table   | 0.000022 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000170 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000081 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
22 rows in set, 1 warning (0.00 sec)

type

主要关注的字段

all         显示所有的开销信息
block io    显示块io相关开销
cpu         显示cpu相关开销信息
ipc         显示发送和接受相关开销信息
memory      显示内存相关开销信息
page faluts 显示页面错误相关开销信息

参数注意

converting HEAP to MyISAM 查询结果太大，内存溢出，向磁盘迁移(内存不够用，往磁盘上搬)
creating tmp table 创建临时表

copying to tmp table on disk 把内存中临时表复制到磁盘(危险)

locked 锁

全局查询日志

全局查询日志会记录所有的sql语句，极少情况下会用的，因为非常影响性能。

-- 开启命令
set global general_log=1;

-- 将sql语句写到表中
set global log_output='table';

-- 所编写的sql语句，会记录到mysql库里的general_log表中
select * from mysql.general_log;