MySQL的Profiles查看出create语句的时间消耗在sending data上面！

zz775520666

　　一，查看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 |上面啊！

　　
　　补充于2013-08-29
　　

• profiling_history_size
  The number of statements for which to maintain profiling information ifprofilingisenabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively disables profiling. SeeSection13.7.5.32,“SHOW PROFILESSyntax”.