解析Mysql Profiling的應用。本站提示廣大學習愛好者：（解析Mysql Profiling的應用）文章只能為提供參考，不一定能成為您想要的結果。以下是解析Mysql Profiling的應用正文
profiling是個很好用的mysql機能剖析對象，今兒就來實驗下profiling的功效。感激 有愛玫瑰的博文：
mysql 的 sql 機能剖析器重要用處是顯示 sql 履行的全部進程中各項資本的應用情形。剖析器可以更好的展現出不良 SQL 的機能成績地點。
上面我們舉例引見一下MySQL SQL Profiler的應用辦法：
起首，開啟 MySQL SQL Profiler
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)默許情形下 profiling 的值為 0 表現 MySQL SQL Profiler 處於 OFF 狀況，開啟 SQL 機能剖析器後 profiling 的值為 1.
經由過程 sql 機能剖析器，我們來比較一下 以下語句前後 2 次履行進程的差別，對我們懂得 sql 的具體履行進程長短常有贊助的。
mysql> create table t_engines select * from t_engines1;
Query OK, 57344 rows affected (0.10 sec)
Records: 57344 Duplicates: 0 Warnings: 0
mysql> select count(*) from t_engines;
+----------+
| count(*) |
+----------+
| 57344 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from t_engines;
+----------+
| count(*) |
+----------+
| 57344 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW PROFILES;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 26 | 0.10213775 | create table t_engines select * from t_engines1 |
| 27 | 0.00032775 | select count(*) from t_engines |
| 28 | 0.00003850 | select count(*) from t_engines |
+----------+------------+-------------------------------------------------+
15 rows in set (0.01 sec)
mysql> SHOW PROFILE FOR QUERY 27;
+--------------------------------+------------+
| Status | Duration |
+--------------------------------+------------+
| (initialization) | 0.00000425 |
| checking query cache for query | 0.00004050 |
| checking permissions | 0.00001050 |
| Opening tables | 0.00018250 |
| System lock | 0.00000450 |
| Table lock | 0.00001775 |
| init | 0.00001075 |
| optimizing | 0.00000550 |
| executing | 0.00002775 |
| end | 0.00000450 |
| query end | 0.00000325 |
| storing result in query cache | 0.00000400 |
| freeing items | 0.00000400 |
| closing tables | 0.00000500 |
| logging slow query | 0.00000300 |
+--------------------------------+------------+
15 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 28;
+-------------------------------------+------------+
| Status | Duration |
+-------------------------------------+------------+
| (initialization) | 0.00000350 |
| checking query cache for query | 0.00000750 |
| checking privileges on cached query | 0.00000500 |
| checking permissions | 0.00000525 |
| sending cached result to client | 0.00001275 |
| logging slow query | 0.00000450 |
+-------------------------------------+------------+
6 rows in set (0.00 sec) mysql> SELECT sum( FORMAT(DURATION, 6)) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID =27 ORDER BY SEQ;
+----------+
| DURATION |
+----------+
| 0.000326 |
+----------+
1 row in set (0.00 sec) mysql> SELECT sum( FORMAT(DURATION, 6)) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID =28 ORDER BY SEQ;
+----------+
| DURATION |
+----------+
| 0.000039 |
+----------+
1 row in set (0.00 sec)
從下面的例子中我們可以清楚的看出 2 次履行 count 語句的差異， SHOW PROFILE FOR QUERY 27 展示的是第一次 count 統計的履行進程，包括了 Opening tables 、 Table lock 等操作 。而 SHOW PROFILE FOR QUERY 28 展現了第二次 count 統計的履行進程 , 第二次 count 直接從查詢緩存中前往 count 統計成果，經由過程比較 2 次統計的總履行時光發明，緩存讀的速度接近物理讀的 10 倍。經由過程應用 SQL 機能剖析器可以贊助我們對一些比擬難以肯定機能成績的 SQL 停止診斷，找出成績本源。
