show profile

查看profiling系统变量

  1. mysql> show variables like '%profil%'; 
  2. +------------------------+-------+
  3. | Variable_name          | Value |
  4. +------------------------+-------+
  5. | have_profiling         | YES   |
  6. | profiling              | OFF   |
  7. | profiling_history_size | 15    |
  8. +------------------------+-------+
  • have_profiling：当前版本是否支持profiling功能
  • profiling:是否开启profiling功能,开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况
  • profiling_history_size：保留profiling的数目，默认是15，范围为0~100，为0时代表禁用profiling

开启profiling需要设置profiling变量为1，该变量的默认值是0

  1. mysql> SET profiling = 1;

然后运行sql,查看下

  1. mysql> select sleep(3);
  2. +----------+
  3. | sleep(3) |
  4. +----------+
  5. |        0 |
  6. +----------+
  7. 1 row in set (3.00 sec)
  9. mysql> show profiles;
  10. +----------+------------+-----------------+
  11. | Query_ID | Duration   | Query           |
  12. +----------+------------+-----------------+
  13. |        1 | 3.00183550 | select sleep(3) |
  14. +----------+------------+-----------------+
  15. 1 row in set, 1 warning (0.00 sec)
  1. Query_ID 表示执行SQL的唯一标识。
  2. Duration 表示持续时间，默认单位为秒。
  3. Query 就是我们所执行的SQL语句。
  1. show profiles 语句 默认显示的是服务端接收到的最新的15条语句。
    我们可以通过以下语句进行修改默认值：
    set profiling_history_size =20;
    profiling_history_size最大取值取值范围为[0,100]。
  2. 当超过100时，则会设置自动设置为最大值100。
  3. 当小于0时，则会自动设置最小值为0。
  4. 当其等于0时，其效果等同于set profiling=0，关闭性能分析模式。

查sql

可以根据查询的id去查询
可以显示更详细的,指定资源查询

  1. 语法：Show profile type ， block type for query 问题sql数字号码

其中type:

  • ALL: 显示所有的开销信息
  • BLOCK IO ： 显示块IO相关开销
  • CONTEXT SWITCHS: 上下文切换相关开销
  • CPU : 显示cpu 相关开销
  • IPC: 显示发送和接收相关开销
  • MEMORY： 显示内存相关开销
  • PAGE FAULTS：显示页面错误相关开销信息
  • SOURCE ： 显示和Source_function ,Source_file,Source_line 相关的开销信息
  • SWAPS：显示交换次数相关的开销信息
  • Status ： sql 语句执行的状态
  • Duration: sql 执行过程中每一个步骤的耗时
  • CPU_user: 当前用户占有的cpu
  • CPU_system: 系统占有的cpu
  • Block_ops_in : I/O 输入
  • Block_ops_out : I/O 输出
  1. show profile block io,cpu for query 1;
  3. show profile cpu,block io,memory,swaps for query 1;
  5. show profile cpu,block io,memory,swaps,context switches,source for query 1;
  1. mysql> show profile for query 1;
  2. +----------------------+----------+
  3. | Status               | Duration |
  4. +----------------------+----------+
  5. | starting             | 0.000055 |
  6. | checking permissions | 0.000005 |    //权限检查
  7. | Opening tables       | 0.000004 |    //打开表
  8. | init                 | 0.000008 |    //初始化
  9. | optimizing           | 0.000004 |    //锁系统
  10. | executing            | 0.000006 |    //优化查询
  11. | User sleep           | 3.001711 |
  12. | end                  | 0.000010 |    
  13. | query end            | 0.000004 |
  14. | closing tables       | 0.000003 |
  15. | freeing items        | 0.000019 |
  16. | cleaning up          | 0.000008 |
  17. +----------------------+----------+
  18. 12 rows in set, 1 warning (0.00 sec)
  20. mysql> show profile block io,cpu for query 1;
  21. +----------------------+----------+----------+------------+--------------+---------------+
  22. | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
  23. +----------------------+----------+----------+------------+--------------+---------------+
  24. | starting             | 0.000055 | 0.000013 |   0.000035 |            0 |             0 |
  25. | checking permissions | 0.000005 | 0.000001 |   0.000004 |            0 |             0 |
  26. | Opening tables       | 0.000004 | 0.000001 |   0.000002 |            0 |             0 |
  27. | init                 | 0.000008 | 0.000002 |   0.000006 |            0 |             0 |
  28. | optimizing           | 0.000004 | 0.000001 |   0.000002 |            0 |             0 |
  29. | executing            | 0.000006 | 0.000001 |   0.000005 |            0 |             0 |
  30. | User sleep           | 3.001711 | 0.000126 |   0.000343 |            0 |             0 |
  31. | end                  | 0.000010 | 0.000002 |   0.000005 |            0 |             0 |
  32. | query end            | 0.000004 | 0.000001 |   0.000003 |            0 |             0 |
  33. | closing tables       | 0.000003 | 0.000001 |   0.000002 |            0 |             0 |
  34. | freeing items        | 0.000019 | 0.000005 |   0.000014 |            0 |             0 |
  35. | cleaning up          | 0.000008 | 0.000002 |   0.000006 |            0 |             0 |
  36. +----------------------+----------+----------+------------+--------------+---------------+
  37. 12 rows in set, 1 warning (0.00 sec)

各个字段含义

  1. show profile for query 1;
  1. all： 展示所有信息。
  2. block io： 展示io的输入输出信息。
  3. context switches： 展示线程的上线文切换信息。
  4. cpu ：显示SQL 占用的CPU信息。
  5. ipc： 显示统计消息的发送与接收计数信息。
  6. page faults：显示主要与次要的页面错误。
  7. memory：本意是显示内存信息，但目前还未实现。
  8. swaps： 显示交换次数。
  9. sources：显示源代码中的函数名称，以及函数发生的文件的名称和行。

一条query每个阶段的资源开销可以从information_schema.profiling表查询

  • state : 当前query所在的阶段
  • CPU_user : CPU用户
  • CPU_system : CPU系统
  • Context_voluntary : 上下文主动切换
  • Context_involuntary : 上下文被动切换
  • Block_ops_in : 阻塞的输入操作
  • Block_ops_out : 阻塞的输出操作
  • Messages_sent : 消息发出
  • Messages_received : 消息接受
  • Page_faults_major : 主分页错误
  • Page_faults_minor : 次分页错误
  • Swaps : 交换次数
  • Source_function : 源功能
  • Source_file : 源文件
  • Source_line : 源代码行

纵向(可以看到sql完整的生命周期):
optimizing: 智能优化器
sending data: 发送数据

mysql文档中字段含义:

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

日常开发需要注意的

里面查出是这个要注意了

  • converting HEAP to MyISAM ： 查询结果太大，内存都不够用了，往磁盘上搬了；
  • creating tmp table ：创建临时表，拷贝数据到临时表，然后再删除；
  • copying to tmp table on disk ：把内存中临时表复制到磁盘，危险！！！
  • locked

注：以上四个中若出现一个或多个，表示sql 语句 必须优化。