[MySQL] 通过Profiles查看create语句的执行时间消耗
一,查看profiles的状态值
1,查看profiles是否已经打开了,默认是不打开的。
mysql> show profiles;
Empty set (0.02 sec)
mysql> show variables like ‘%pro%‘;
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| proxy_user | |
| slave_compressed_protocol | OFF |
+---------------------------+-------+
6 rows in set (0.00 sec)
我查看一下profiles里面没有东西,所以公司服务器里面profile是没有打开的,我查看了一下mysql变量,果然是OFF的。
二,打开profiles,然后测试
1,开启profiles
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
2,check下状态值,果然已经开启了,为ON
mysql> show variables like ‘%pro%‘;
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
| protocol_version | 10 |
| proxy_user | |
| slave_compressed_protocol | OFF |
+---------------------------+-------+
6 rows in set (0.00 sec)
3,测试
mysql> drop table if exists test_cpny.listed_cpny_fin_rpt_prd;
Query OK, 0 rows affected (0.08 sec)
mysql> create table test_cpny.listed_cpny_fin_rpt_prd
-> SELECT dat.*
-> FROM test.qa_cpny es
-> inner join cdp.listed_cpny_fin_rpt_prd dat
-> on (es.excel_id = dat.excel_id)
-> ;
Query OK, 60960 rows affected (30.00 sec)
Records: 60960 Duplicates: 0 Warnings: 0
mysql>
4,查看结果值:
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------
| Query_ID | Duration | Query
+----------+------------+-----------------------------------------------------------------------------------------------------
| 1 | 0.00044300 | show variables like ‘%pro%‘
| 2 | 0.05818800 | drop table if exists test_cpny.listed_cpny_fin_rpt_prd
| 3 | 30.0030300 | create table test_cpny.listed_cpny_fin_rpt_prd
SELECT dat.*
FROM test.qa_cpny es
inner join cdp.listed_cpny_fin_rpt_prd dat
on (es.excel_id = dat.excel_id) |
+----------+------------+-----------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)
mysql>
5,再check
mysql> show profile for query 3;
+------------------------------+----------+
| Status | Duration |
+------------------------------+----------+
| starting | 0.000072 |
| checking permissions | 0.000007 |
| checking permissions | 0.000003 |
| checking permissions | 0.000007 |
| Opening tables | 0.000161 |
| System lock | 0.000013 |
| init | 0.000006 |
| checking permissions | 0.000065 |
| creating table | 0.003520 |
| After create | 0.000089 |
| System lock | 0.000011 |
| optimizing | 0.000011 |
| statistics | 0.000016 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 29.96599 |
| Waiting for query cache lock | 0.000014 |
| Sending data | 0.010215 |
| end | 0.000016 |
| query end | 0.000004 |
| closing tables | 0.000016 |
| freeing items | 0.000042 |
| logging slow query | 0.000004 |
| cleaning up | 0.000004 |
+------------------------------+----------+
24 rows in set (0.00 sec)
看来时间消耗在| Sending data | 29.96599 |上面啊!
补充于2014-09-16
The number of statements for which to maintain profiling information if
profiling
is enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively disables profiling. See Section 13.7.5.32, “SHOW PROFILES
Syntax”.
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。