[MySQL] 通过Profiles查看create语句的执行时间消耗，

[MySQL] 通过Profiles查看create语句的执行时间消耗，profilescreate

一，查看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

•  profiling_history_size

  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”.

1、开启和关闭
mysql> set profiling=1;
mysql> set profiling=0;
information_schema 的 database 会建立一个PROFILING 的 table 记录.
2、执行一些语句
mysql>show databases;
mysql>use information_schema;
3、查询语句执行时间
mysql>show profiles;
mysql>help show profiles 获得更多提示
 

mysql要实现定时执行sql语句就要用到Event

具体操作如下：

先看看看event 事件是否开启
show variables like '%sche%';

如没开启，则开启。需要数据库超级权限
set global event_scheduler =1;

创建存储过程 update_a (注：就是你要执行的sql语句)
mysql> create procedure update_a() update a set a.y_avg=(select avg(b.youhao) from b where a.a_id=b.a_id);

创建一个定时任务：event e_updateA
mysql> create event if not exists e_updateA
-> on schedule every 60 second ---设置60秒执行一次

-> on schedule at date_add(now(),interval 1 minute) ---在一分钟后执行

-> on completion preserve
-> do call update_a(); ---执行update_a()存储过程

创建Event之后，sql语句就定时执行一次。

关闭事件任务
mysql> alter event e_updateA ON
-> COMPLETION PRESERVE DISABLE;

开启事件任务
mysql> alter event e_updateA ON
-> COMPLETION PRESERVE ENABLE;

如还有问题再联系我