1、show profile是什么意思
Show Profile 是 MySQL 提供的可以用来分析当前查询 SQL 语句执行的资源消耗情况的工具,可用于 SQL 调优的测量。默认情况下处于关闭状态,开启会消耗一定的性能,一般在 SQL 分析和优化的时候使用,只保存最近15次的运行结果。
2、查看当前的mysql版本是否开启profile
运行命令:
SHOW VARIABLES LIKE 'profiling%';
运行结果:
Variable_name | Value |
---|---|
profiling | OFF |
profiling_history_size | 15 |
从运行结果可以看到,profiling是off,也就是关闭的,同时也可以看到profiling_history_size是15,这也是我们在文章开头 show profile是什么意思 中描述的:一般在 SQL 分析和优化的时候使用,只保存最近15次的运行结果。
3、开启 profile
运行命令:
SET profiling=ON
或者
SET profiling=1
4、查看 SQL 语句执行情况
在查看 SQL 语句执行情况前,我们随意运行几条sql语句,然后运行命令
show profiles
注意,profiles是复数,不是profile
运行结果:
Query_ID | Duration | Query |
---|---|---|
1 | 0.00325675 | show variables like "%pro%" |
2 | 0.00023750 | use sakila |
3 | 0.00365825 | select * from city |
4 | 0.00125100 | select * from country |
5 | 0.00782125 | select * from customer |
运行结果中的每列,分别表示的意思如下:
Query_ID:SQL编号ID; Duration:SQL执行时间; Query:SQL语句。
5、查看某条记录的运行明细
运行命令:
show profile for query 3
注意,这里是 profile,不是profiles
这条语句的意思是,查看 Query_ID = 3 的sql语句的执行情况
运行结果:
Status | Duration |
---|---|
starting | 0.000059 |
checking permissions | 0.000010 |
Opening tables | 0.001210 |
init | 0.000026 |
System lock | 0.000010 |
optimizing | 0.000008 |
statistics | 0.000020 |
preparing | 0.000011 |
executing | 0.000002 |
Sending data | 0.002218 |
end | 0.000014 |
query end | 0.000010 |
closing tables | 0.000008 |
freeing items | 0.000042 |
cleaning up | 0.000013 |
如果想知道每个过程中的更具体的信息,可以执行以下命令:
show profile all for query 3
运行结果
State | Duration | CPU_User | CPU_system | 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 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
starting | 0.000048 | 0 | 0 | ||||||||||||
checking permissions | 0.000016 | 0 | 0 | check_access | sql_authorization.cc | 809 | |||||||||
Opening tables | 0.000014 | 0 | 0 | open_tables | sql_base.cc | 5815 | |||||||||
init | 0.000051 | 0 | 0 | handle_query | sql_select.cc | 128 | |||||||||
System lock | 0.000007 | 0 | 0 | mysql_lock_tables | lock.cc | 330 | |||||||||
optimizing | 0.000003 | 0 | 0 | JOIN::optimize | sql_optimizer.cc | 158 | |||||||||
optimizing | 0.000003 | 0 | 0 | JOIN::optimize | sql_optimizer.cc | 158 | |||||||||
statistics | 0.000011 | 0 | 0 | JOIN::optimize | sql_optimizer.cc | 374 | |||||||||
preparing | 0.000009 | 0 | 0 | JOIN::optimize | sql_optimizer.cc | 482 | |||||||||
statistics | 0.000005 | 0 | 0 | JOIN::optimize | sql_optimizer.cc | 374 | |||||||||
preparing | 0.000005 | 0 | 0 | JOIN::optimize | sql_optimizer.cc | 482 | |||||||||
executing | 0.000015 | 0 | 0 | JOIN::exec | sql_executor.cc | 126 | |||||||||
Sending data | 0.000008 | 0 | 0 | JOIN::exec | sql_executor.cc | 202 | |||||||||
executing | 0.000004 | 0 | 0 | JOIN::exec | sql_executor.cc | 126 | |||||||||
Sending data | 0.000897 | 0 | 0 | JOIN::exec | sql_executor.cc | 202 | |||||||||
end | 0.000009 | 0 | 0 | handle_query | sql_select.cc | 206 | |||||||||
query end | 0.000024 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4957 | |||||||||
closing tables | 0.000008 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5010 | |||||||||
removing tmp table | 0.000018 | 0 | 0 | free_tmp_table | sql_tmp_table.cc | 2413 | |||||||||
closing tables | 0.000006 | 0 | 0 | free_tmp_table | sql_tmp_table.cc | 2442 | |||||||||
freeing items | 0.000076 | 0 | 0 | mysql_parse | sql_parse.cc | 5627 | |||||||||
cleaning up | 0.000012 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
6、其他
show profile语句已经弃用,并将在以后版本中移除,建议使用 Performance Schema
调优完毕后,关闭参数:set profiling=0