[MySQL] 通過Profiles查看create語句的執行時間消耗，profilescreate

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

Mysql 中怎記錄某語句執行的時間？

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語句

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;

如還有問題再聯絡我