数据库高级知识——查询截取分析（二）

3.Show Profile

3.1 show profile是什么

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

官网

3.2 分析步骤

1.是否支持，看看当前的mysql版本是否支持

mysql> Show  variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

默认是关闭，使用前需要开启

2.开启功能，默认是关闭，使用前需要开启

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

mysql> Show  variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

3.运行SQL

mysql> select * from students;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhao1 |
|    2 | zhao2 |
|    3 | zhao3 |
+------+-------+
3 rows in set (0.03 sec)

4.查看结果

show profiles;

mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration   | Query                            |
+----------+------------+----------------------------------+
|        1 | 0.00189725 | Show  variables like 'profiling' |
|        2 | 0.00037950 | SELECT DATABASE()                |
|        3 | 0.00436850 | show databases                   |
|        4 | 0.00493400 | show tables                      |
|        5 | 0.00287150 | show tables                      |
|        6 | 0.02086075 | select * from students           |
+----------+------------+----------------------------------+
6 rows in set, 1 warning (0.00 sec)

5.诊断SQL

show profile cpu,block io for query  n  #(n为上一步前面的问题SQL数字号码);

mysql> show profile cpu,block io for query 6;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000111 | 0.000110 |   0.000000 |            0 |             0 |
| Executing hook on transaction  | 0.000012 | 0.000011 |   0.000000 |            0 |             0 |
| starting                       | 0.000018 | 0.000018 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000016 | 0.000016 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000062 | 0.000062 |   0.000000 |            0 |             0 |
| init                           | 0.000016 | 0.000016 |   0.000000 |            0 |             0 |
| System lock                    | 0.000021 | 0.000021 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000073 | 0.000056 |   0.000000 |            0 |             0 |
| statistics                     | 0.000029 | 0.000029 |   0.000000 |            0 |             0 |
| preparing                      | 0.000035 | 0.000035 |   0.000000 |            0 |             0 |
| executing                      | 0.020360 | 0.000411 |   0.000000 |           32 |             0 |
| end                            | 0.000016 | 0.000014 |   0.000000 |            0 |             0 |
| query end                      | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| waiting for handler commit     | 0.000012 | 0.000012 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000013 | 0.000013 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000027 | 0.000028 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000033 | 0.000033 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

参数备注：

type:
| ALL --显示所有的开销信息
| BLOCK IO --显示块IO相关开销
| CONTEXT SWITCHES --上下文切换相关开销
| CPU --显示CPU相关开销信息
| IPC --显示发送和接收相关开销信息
| MEMORY --显示内存相关开销信息
| PAGE FAULTS --显示页面错误相关开销信息
| SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS --显示交换次数相关开销的信息

6.日常开发需要注意的结论

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

Creating tmp table 创建临时表
	拷贝数据到临时表
	用完再删除
	
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

locked

4.全局查询日志

4.1配置启用

/etc/mysql/mysql.conf.d/mysqld.cnf

# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1

4.2编码启用

#命令   尽量不要在生产环境开启这个功能。
set global general_log=1;
 
 #全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中
set global log_output='TABLE';


#此后 ，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
 
select * from mysql.general_log;