性能监控

show profile

**使用show profile查询剖析工具，可以指定具体的type **

官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

开启工具

此工具默认是禁用的，可以通过服务器变量在会话级别动态的修改

set profiling=1;

查询语句

当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。

select * from test;

使用工具分析查询语句各个阶段耗时

在mysql的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间

show profiles;

执行如下命令可以查看详细的每个步骤的时间：

show profile for query 19;

使用工具分析查询语句各个阶段具体类型资源的消耗

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
    

type: {
    ALL                      显示所有开销信息
  | BLOCK IO                显示块IO相关开销
  | CONTEXT SWITCHES        显示上下文切换相关开销
  | CPU                        显示cup开销
  | IPC                        显示发送与接受相关开销
  | MEMORY                    显示内存相关开销
  | PAGE FAULTS                显示也买你错误相关开销
  | SOURCE                    显示和source_function,source_file,source_line相关的开销
  | SWAPS                    显示交换次数相关开销
}

eg: show profile cpu,block io for query 28;

分析数据定性sql的优化方向

如果show profile … for query id；出现了如下四个，则必须优化这条sql。

• converting HEAP to MyISAM 查询结果太大， 内存都不够用了转而使用磁盘存储结果
• Creating tmp table 创建了临时表。
• Copying to tmp table on disk 把内存中临时表复制到磁盘.
• locked

查询的临时表大小超过了tmp_table_size设置的值（默认16M），导致基于内存的临时表变更成基于磁盘的临时表。读写速度剧降。

show processlist

使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。

官网地址：https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

属性说明

• id: session id
• user: 操作的用户
• host: 操作的主机
• db: 操作的数据库
• command: 当前状态
  • sleep：线程正在等待客户端发送新的请求
  • query：线程正在执行查询或正在将结果发送给客户端
  • locked：在mysql的服务层，该线程正在等待表锁
  • analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
  • sorting result：线程正在对结果集进行排序
  • sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据
• time: 相应命令执行时间
• state: 命令执行状态
• info: 详细的sql语句

可以手动删除status是query且时间过长时连接。

kill ID id编号；