mysql profile作用_4.mysql profile的使用方法

profile的使用

1、作用

使用profile可以对某一条sql性能进行分析

2、语法

mysql> show variables like ‘%profil%‘;

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| have_profiling | YES |

| profiling | ON | 开启profile功能

| profiling_history_size | 15 | profile能记录多少条sql

+------------------------+-------+

set profiling = 0/1; 关闭或开启profile功能

mysql> show profiles; 查看当前profile记录的所有sql

+----------+-------------+------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------------------+

| 1 | 0.00163850 | show variables like ‘%profil%‘ |

| 2 | 0.00005825 | show warnings |

| 3 | 23.85836475 | select * from course c left join study s on c.age = s.snum |

| 4 | 0.23801475 | select * from study s left join course c on c.age = s.snum |

| 5 | 0.00010450 | set profiling=1 |

| 6 | 0.00190400 | show variables like ‘%prifil%‘ |

| 7 | 0.00175100 | show variables like ‘%profil%‘ |

+----------+-------------+------------------------------------------------------------+

mysql> show profile for query 4; 查看某个Query的执行持续时间分布

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000131 |

| checking permissions | 0.000008 |

| checking permissions | 0.000005 |

| Opening tables | 0.000015 |

| init | 0.000023 |

| System lock | 0.000009 |

| optimizing | 0.000008 |

| statistics | 0.000027 |

| preparing | 0.000012 |

| executing | 0.000004 |

| Sending data | 0.237726 |

| end | 0.000010 |

| query end | 0.000007 |

| closing tables | 0.000008 |

| freeing items | 0.000014 |

| cleaning up | 0.000010 |

+----------------------+----------+

show profile [all|cpu|block io|……] for query number; 可以查看[全部|cpu相关|io相关]细节

3、含义分析

show profile all for query n;

横向栏意义

+----------------------+----------+----------+------------+

"Status": "query end", 状态

"Duration": "1.751142", 持续时间

"CPU_user": "0.008999", cpu用户

"CPU_system": "0.003999", cpu系统

"Context_voluntary": "98", 上下文主动切换

"Context_involuntary": "0", 上下文被动切换

"Block_ops_in": "8", 阻塞的输入操作

"Block_ops_out": "32", 阻塞的输出操作

"Messages_sent": "0", 消息发出

"Messages_received": "0", 消息接受

"Page_faults_major": "0", 主分页错误

"Page_faults_minor": "0", 次分页错误

"Swaps": "0", 交换次数

"Source_function": "mysql_execute_command", 源功能

"Source_file": "sql_parse.cc", 源文件

"Source_line": "4465" 源代码行

+----------------------+----------+----------+------------+

纵向栏意义

+----------------------+----------+----------+------------+

starting：开始

checking permissions：检查权限

Opening tables：打开表

init ： 初始化

System lock ：系统锁

optimizing ： 优化

statistics ： 统计

preparing ：准备

executing ：执行

Sending data ：发送数据

Sorting result ：排序

end ：结束

query end ：查询 结束

closing tables ： 关闭表 ／去除TMP 表

freeing items ： 释放物品

cleaning up ：清理

+----------------------+----------+----------+------------+

4.mysql profile的使用方法

标签：ring   duration   clean   执行   准备   swap   lock   func   statistic

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/unsigned1995/p/14154031.html