MySql数据库15-提升-性能优化5-分析SQL执行带来的开销(show profiles)

一 概述

show profile是mysql提供的用来分析当前会话中sql语句执行的资源消耗情况的工具。
例如磁盘I/O，上下文切换，CPU，Memory等资源的使用情况。
默认处于关闭状态，并保存最近15次的运行结果。

二 使用show profiles

1.查看是否支持并开启该功能

• 查看

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.01 sec)

可以从结果中看到状态是OFF关闭状态。

• 开启

使用set profiling = on;来开启

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

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

2.使用该功能

• 展示出历史sql语句

mysql> show profiles;
+----------+------------+------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                  |
+----------+------------+------------------------------------------------------------------------+
|        1 | 0.00270650 | show variables like '%profiling%'                                      |
|        2 | 0.00029800 | select sleep(2), user_name, account_name from consumer_info where id=3 |
|        3 | 0.00057525 | SELECT DATABASE()                                                      |
|        4 | 0.00205325 | show databases                                                         |
|        5 | 0.00065450 | show tables                                                            |
|        6 | 2.00402375 | select sleep(2), user_name, account_name from consumer_info where id=3 |
|        7 | 0.00089800 | select  user_name, account_name from consumer_info where id=2          |
+----------+------------+------------------------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

列表中的信息有查询id、使用时间和sql语句。

• 根据query_id查询出资源消耗情况(诊断SQL)

语法： show 资源类型 io for query 查询id;
资源类型可选值：

可选值	描述（说明）
ALL	显示所有的开销信息
BLOCK IO	显示IO相关的开销
CONTEXT SWITCHES	上下文切换相关开销
CPU	显示处理器开销
IPC	显示发送和接受相关开销
MEMORY	内存开销
PAGE FAULTS	页面错误相关开销
SOURCE	source_function,source_file,source_line相关的开销
SWAPS	交换次数开销

示例：

mysql> show profile cpu,block io for query 6;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000152 | 0.000152 |   0.000000 |            0 |             0 |
| checking permissions | 0.000021 | 0.000019 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000038 | 0.000038 |   0.000000 |            0 |             0 |
| init                 | 0.000043 | 0.000043 |   0.000000 |            0 |             0 |
| System lock          | 0.000131 | 0.000130 |   0.000000 |            0 |             0 |
| optimizing           | 0.000046 | 0.000045 |   0.000000 |            0 |             0 |
| statistics           | 0.000109 | 0.000109 |   0.000000 |            0 |             0 |
| preparing            | 0.000041 | 0.000040 |   0.000000 |            0 |             0 |
| executing            | 0.000037 | 0.000037 |   0.000000 |            0 |             0 |
| Sending data         | 0.000147 | 0.000148 |   0.000000 |            0 |             0 |
| User sleep           | 2.002863 | 0.000971 |   0.002181 |            0 |             0 |
| end                  | 0.000049 | 0.000022 |   0.000023 |            0 |             0 |
| query end            | 0.000021 | 0.000011 |   0.000011 |            0 |             0 |
| closing tables       | 0.000020 | 0.000010 |   0.000011 |            0 |             0 |
| freeing items        | 0.000129 | 0.000063 |   0.000067 |            0 |             0 |
| logging slow query   | 0.000136 | 0.000068 |   0.000072 |            0 |             8 |
| cleaning up          | 0.000045 | 0.000027 |   0.000029 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

从上述示例中可以考到Status的值非常多，无从下手，常用的其实就那么几个，如下：

status值	说明
converting HEAP to MyISAM	查询结果集太大，内存已经不够用了，需要使用磁盘了
Creating tmp table	创建临时表
Copying to tmp table on disk	把内存中临时表复制到磁盘（开销大）
locked	锁

三 全局查询日志

顾名思义就是记录了所有的sql语句日志。
由于这个功能会记录所有的sql，所以都是在开发环境使用。

1.开启

• 使用修改配置文件永久开启

#开启
general_log=1  

# 使用日志文件方式记录 
# 记录日志文件的路径
#general_log_file=/xxx/xxx/xx.log
#输出方式
#log_output=FILE

# 使用表来记录
#输出方式
log_output=FILE

• 使用全局变量开启

# 开启
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)

# 输出方式
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.01 sec)

2.查看

# 查看记录表
mysql> select * from mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------------------------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                                                               |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------------------------------------------------+
| 2020-07-14 07:28:07.769289 | root[root] @ localhost [] |       102 |         0 | Query        | select * from mysql.general_log                                        |
| 2020-07-14 07:28:27.566406 | root[root] @ localhost [] |       102 |         0 | Query        | select sleep(2), user_name, account_name from consumer_info where id=3 |
| 2020-07-14 07:28:33.217762 | root[root] @ localhost [] |       102 |         0 | Query        | select * from mysql.general_log                                        |
+----------------------------+---------------------------+-----------+-----------+--------------+------------------------------------------------------------------------+
3 rows in set (0.00 sec)

表中记录了时间戳，用户，sql类型和sql语句。