[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


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。