MySQL调优--03---show profiles 和 show processlist

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

show profiles

概念:

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

查看是否开启

show variables like “%profiling%”;

开启

set profiling = 1;

开始分析

1. 先执行要分析的SQL语句
2. 执行show profiles;会出现如下结果

show profiles

show profile ----分析某一条SQL语法

SQL语法

show profile type1,type2… for query Query_ID

show profile type 选项

• all：显示所有的性能开销信息
• block io：显示块 IO 相关的开销信息
• context switches: 上下文切换相关开销
• cpu：显示 CPU 相关的信息
• ipc：显示发送和接收相关的开销信息
• memory：显示内存相关的开销信息
• page faults：显示页面错误相关开销信息
• source：显示和 Source_function、Source_file、Source_line 相关的开销信息
• swaps：显示交换次数的相关信息

案例 1

SHOW profile ALL FOR QUERY 90

案例 2

SHOW profile cpu,block io FOR QUERY 90

显示 查询id= 90的 语句 执行时 , CPU 和 IO 相关的开销信息

show profile返回结果字段含义

• Status ： sql 语句执行的状态
• Duration: sql 执行过程中每一个步骤的耗时
• CPU_user: 当前用户占有的 cpu
• CPU_system: 系统占有的 cpu
• Block_ops_in : I/O 输入
• Block_ops_out : I/O 输出
  

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排序
• 建议：创建适当的索引

Creating tmp table

• 创建临时表。先拷贝数据到临时表，用完后再删除临时表。消耗内存，数据来回拷贝删除，消耗时间
• 建议：优化索引

converting HEAP to MyISAM

• 查询结果太大，内存不够，数据往磁盘上搬了。
• 建议：优化索引，可以调整max_heap_table_size

Copying to tmp table on disk

• 把内存中临时表复制到磁盘上，危险！！！
• 建议：优化索引，可以调整tmp_table_size参数，增大内存临时表大小

小结:

学会了explain和show profile这两个命令，足以应用于一些比较简单的性能分析场景。分析出SQL语句存在的问题，从而写出更优质的SQL语句。

MySQL8中SQL执行原理—profiling

1. 确认profiling 是否开启

2. 多次执行相同SQL查询

3. 查看profiles

4. 查看profile

此外，还可以查询更丰富的内容：

show processlist

概念:

• show processlist命令可以查看当前MySQL实例的连接情况，
• 用于观察是否有大量的连接处于非正常状态。用法非常简单，直接使用就行

用法

show processlist

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host           | db   | Command | Time | State | Info             |
+----+------+----------------+------+---------+------+-------+------------------+
|  7 | root | localhost:2353 | test | Sleep   |   57 |       | NULL             |
|  8 | root | localhost:3811 | NULL | Query   |    0 | init  | show processlist |
+----+------+----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

字段的含义

Command字段，对应的状态

• sleep：正在等待客户端发送新的请求
• query：正在执行查询或者正在将结果发送给客户端
• locked：在MySQL服务层，线程正在等待表锁
• analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
• copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
• sorting result：正在对结果集进行排序
• sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据

show full processlist

• show processlist命令默认Info字段最多显示每条语句的前100个字符，如果想完全显示，可以使用show full processlist

小结:

show processlist命令则是用来管理MySQL实例的连接情况，如果收到类似too manyconnections的错误，使用此命令将非常有用。