在mysql查詢性能分析中最常用的就是explain了，profile查看一些具體的性能也是不錯的
1. profile
我們可以先使用
SELECT @@profiling;
來查看是否已經啟用profile，如果profilng值為0，可以通過
SET profiling = 1;
來啟用。啟用profiling之後，我們執行一條查詢語句，比如：
select count(*) from roi_summary;
然後show profiles查看如下：
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00021500 | select @@profiling |
| 2 | 0.05522700 | select count(*) from roi_summary |
+----------+------------+----------------------------------+
2 rows in set (0.00 sec)
其中ID為5的語句是剛執行的查詢語句，這時候我們執行show profile for query 2來查看這條語句的執行過程如下;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000021 |
| checking query cache for query | 0.000045 |
| checking permissions | 0.000007 |
| Opening tables | 0.000011 |
| System lock | 0.000004 |
| Table lock | 0.000040 |
| init | 0.000012 |
| optimizing | 0.000005 |
| statistics | 0.000010 |
| preparing | 0.000010 |
| executing | 0.000005 |
| Sending data | 0.055021 |
| end | 0.000007 |
| end | 0.000004 |
| query end | 0.000003 |
| storing result in query cache | 0.000004 |
| freeing items | 0.000008 |
| closing tables | 0.000005 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
20 rows in set (0.00 sec)
可以看出此條查詢語句的執行過程及執行時間，總的時間約為0.05s。
這時候我們再執行一次
select count(*) from roi_summary;
show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00021500 | select @@profiling |
| 2 | 0.05522700 | select count(*) from roi_summary |
| 3 | 0.00006000 | select count(*) from roi_summary |
+----------+------------+----------------------------------+
然後執行show profile for query 3來查看本條語句的執行過程
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000016 |
| checking query cache for query | 0.000007 |
| checking privileges on cached | 0.000004 |
| checking permissions | 0.000005 |
| sending cached result to clien | 0.000022 |
| logging slow query | 0.000003 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
可以看出此次第二次查詢因為前一次的查詢生成了cache，所以這次無需從數據庫文件中再次讀取數據而是直接從緩存中讀取，結果查詢時間比第一次快了N倍。
