MySQL SHOW PROFILE（剖析報告）的查看

MySQL SHOW PROFILE（剖析報告）的查看

前言：SHOW PROFIL命令是MySQL提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於SQL的調優的測量。

一、參數的開啟和關閉設定

1.1  參數的查看

預設情況下，參數處於關閉狀態，並儲存最近15次的運行結果

mysql>  show variables like 'profiling%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| profiling              | OFF  |

| profiling_history_size | 15    |

+------------------------+-------+

2 rows in set 

1.2 參數的開啟和關閉（參數為會話級參數，只對當前會話有效）

開啟操作如下：

mysql> SET profiling=1;或 SET profiling=on;

mysql>  SET profiling=on;

Query OK, 0 rows affected

 

mysql>  show variables like 'profiling%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| profiling              | ON    |

| profiling_history_size | 15    |

+------------------------+-------+

2 rows in set

關閉的操作：
mysql> SET profiling=0;或 SET profiling=off;

二、操作步驟

2.1 進行開啟操作： SET profiling=on;

2.2 運行相應的SQL語句；

2.3 查看總體結果：show profiles;

2.4 查看詳細的結果：SHOW PROFILE FOR QUERY n，這裡的n就是對應SHOW PROFILES輸出中的Query_ID；

mysql> show profiles;

+----------+------------+------------------------------------+

| Query_ID | Duration  | Query                              |

+----------+------------+------------------------------------+

|        1 | 0.00077425 | select count(*) from tab_user_info |

|        2 |  0.0013575 | select count(*) from tab_tel_area  |

|        3 |    9.7E-5 | select count(*) from tab_tel_area  |

|        4 |  0.005193 | show variables like 'profiling%'  |

+----------+------------+------------------------------------+

4 rows in set

 

mysql> show profile for query 2;

+--------------------------------+----------+

| Status                        | Duration |

+--------------------------------+----------+

| starting                      | 2E-5    |

| checking query cache for query | 4.7E-5  |

| Opening tables                | 0.001163 |

| System lock                    | 4E-6    |

| Table lock                    | 4.1E-5  |

| init                          | 1.6E-5  |

| optimizing                    | 6E-6    |

| executing                      | 1.4E-5  |

| end                            | 5E-6    |

| query end                      | 3E-6    |

| freeing items                  | 3.1E-5  |

| storing result in query cache  | 5E-6    |

| logging slow query            | 3E-6    |

| cleaning up                    | 2E-6    |

+--------------------------------+----------+

14 rows in set

說明：報告給出了查詢執行的每個步驟及花費的時間，當語句是很簡單的一次執行的時候，可以很清楚的看出語句每個順序花費的時間，但是當語句是嵌套迴圈等操作的時候，看這個報告就會變得很痛苦，因此整理了以下語句對相同類型的操作進行匯總，指令碼如下：

mysql> SET @QUERY_ID=1;

mysql> SELECT STATE,SUM(DURATION) AS TOTAL_R,

ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION)

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID=@QUERY_ID),2) AS PCT_R,

COUNT(*) AS CALLS,

SUM(DURATION)/COUNT(*) AS "R/CALL"

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID=@QUERY_ID

GROUP BY STATE

ORDER BY TOTAL_R DESC;

總結：這個工具又讓我聯想到了Oracle的autotrace工具，如果有相應的執行計畫一起帶出來，那麼對語句的調優協助將更大；

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：