查看 MySQL 語法 詳細執行時間 與 CPU/記憶體使用量: MySQL Query Profiler

MySQL 的 SQL 語法調整主要都是使用 EXPLAIN, 但是這個並沒辦法知道詳細的 Ram(Memory)/CPU 等使用量.

於 MySQL 5.0.37 以上開始支援 MySQL Query Profiler, 可以查詢到此 SQL 會執行多少時間, 並看出 CPU/Memory 使用量, 執行過程中 System lock, Table lock 花多少時間等等.

MySQL Query Profile 詳細介紹可見: Using the New MySQL Query Profiler (2007.04.05 發表)

效能分析主要分下述三種(轉載自上篇):

• Bottleneck analysis – focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?
• Workload analysis – examines the server and who is logged on to determine the resource usage and activity of each.
• Ratio-based analysis – utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.

MySQL Query Profile 使用方法

啟動

• mysql> set profiling=1; # 此命令於 MySQL 會於 information_schema 的 database 建立一個 PROFILING 的 table 來紀錄.

SQL profiles show

• mysql> show profiles; # 從啟動之後所有語法及使用時間, 含錯誤語法都會紀錄.
• ex: (root@localhost) [test]> show profiles; # 注意 Query_ID, 下面執行時間統計等, 都是依 Query_ID 在紀錄

  	+----------+------------+---------------------------+
  	| Query_ID | Duration   | Query                     |
  	+----------+------------+---------------------------+
  	|        1 | 0.00090400 | show profile for query 1  |
  	|        2 | 0.00008700 | select * from users       |
  	|        3 | 0.00183800 | show tables               |
  	|        4 | 0.00027600 | mysql> show profiles      |
  	+----------+------------+---------------------------+

查詢所有花費時間加總

• mysql> select sum(duration) from information_schema.profiling where query_id=1; # Query ID = 1

  	+---------------+
  	| sum(duration) |
  	+---------------+
  	|      0.000447 |
  	+---------------+

查詢各執行階段花費多少時間

• mysql> show profile for query 1; # Query ID = 1

  	+--------------------+------------+
  	| Status             | Duration   |
  	+--------------------+------------+
  	| (initialization)   | 0.00006300 |
  	| Opening tables     | 0.00001400 |
  	| System lock        | 0.00000600 |
  	| Table lock         | 0.00001000 |
  	| init               | 0.00002200 |
  	| optimizing         | 0.00001100 |
  	| statistics         | 0.00009300 |
  	| preparing          | 0.00001700 |
  	| executing          | 0.00000700 |
  	| Sending data       | 0.00016800 |
  	| end                | 0.00000700 |
  	| query end          | 0.00000500 |
  	| freeing items      | 0.00001200 |
  	| closing tables     | 0.00000800 |
  	| logging slow query | 0.00000400 |
  	+--------------------+------------+

查詢各執行階段花費的各種資源列表

• mysql> show profile cpu for query 1; # Query ID = 1

  	+--------------------------------+----------+----------+------------+
  	| Status                         | Duration | CPU_user | CPU_system |
  	+--------------------------------+----------+----------+------------+
  	| (initialization)               | 0.000007 | 0        | 0          |
  	| checking query cache for query | 0.000071 | 0        | 0          |
  	| Opening tables                 | 0.000024 | 0        | 0          |
  	| System lock                    | 0.000014 | 0        | 0          |
  	| Table lock                     | 0.000055 | 0.001    | 0          |
  	| init                           | 0.000036 | 0        | 0          |
  	| optimizing                     | 0.000013 | 0        | 0          |
  	| statistics                     | 0.000021 | 0        | 0          |
  	| preparing                      | 0.00002  | 0        | 0          |
  	| executing                      | 0.00001  | 0        | 0          |
  	| Sending data                   | 0.015072 | 0.011998 | 0          |
  	| end                            | 0.000021 | 0        | 0          |
  	| query end                      | 0.000011 | 0        | 0          |
  	| storing result in query cache  | 0.00001  | 0        | 0          |
  	| freeing items                  | 0.000018 | 0        | 0          |
  	| closing tables                 | 0.000019 | 0        | 0          |
  	| logging slow query             | 0.000009 | 0        | 0          |
  	+--------------------------------+----------+----------+------------+

• mysql> show profile IPC for query 1;

  	+--------------------------------+----------+---------------+-------------------+
  	| Status                         | Duration | Messages_sent | Messages_received |
  	+--------------------------------+----------+---------------+-------------------+
  	| (initialization)               | 0.000007 |             0 |                 0 |
  	| checking query cache for query | 0.000071 |             0 |                 0 |
  	| Opening tables                 | 0.000024 |             0 |                 0 |
  	| System lock                    | 0.000014 |             0 |                 0 |
  	| Table lock                     | 0.000055 |             0 |                 0 |
  	| init                           | 0.000036 |             0 |                 0 |
  	| optimizing                     | 0.000013 |             0 |                 0 |
  	| statistics                     | 0.000021 |             0 |                 0 |
  	| preparing                      | 0.00002  |             0 |                 0 |
  	| executing                      | 0.00001  |             0 |                 0 |
  	| Sending data                   | 0.015072 |             0 |                 0 |
  	| end                            | 0.000021 |             0 |                 0 |
  	| query end                      | 0.000011 |             0 |                 0 |
  	| storing result in query cache  | 0.00001  |             0 |                 0 |
  	| freeing items                  | 0.000018 |             0 |                 0 |
  	| closing tables                 | 0.000019 |             0 |                 0 |
  	| logging slow query             | 0.000009 |             0 |                 0 |
  	+--------------------------------+----------+---------------+-------------------+

其它屬性列表

• ALL – displays all information
• BLOCK IO – displays counts for block input and output operations
• CONTEXT SWITCHES – displays counts for voluntary and involuntary context switches
• IPC – displays counts for messages sent and received
• MEMORY – is not currently implemented
• PAGE FAULTS – displays counts for major and minor page faults
• SOURCE – displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
• SWAPS – displays swap counts

設定 Profiling 存的 Size

• mysql> show variables where variable_name=’profiling_history_size’; # 預設是 15筆

關閉

• mysql> set profiling=0;

資料來源：http://plog.longwin.com.tw/my_note-unix/2008/10/03/mysql-query-profiler-cpu-ram-time-2008

套件名稱：Mysqlcft
版本：
官網：http://code.google.com/p/mysqlcft/
下載：http://code.google.com/p/mysqlcft/
demo：
執行結果畫面：

軟體介紹：

MySQL在高並發連接、數據庫記錄數較多的情況下，SELECT … WHERE … LIKE ‘%…%’的全文搜索方式不僅效率差，而且以通配符%開頭作查詢時，使用不到索引，需要全表掃瞄，對數據庫的壓力也很大。MySQL針對這一問題提供 了一種全文索引解決方案，這不僅僅提高了性能和效率（因為MySQL對這些字段做了索引來優化搜索），而且實現了更高質量的搜索。但是，至今為 止，MySQL對中文全文索引無法正確支持。

Mysqlcft 是為 MySQL 5.1.22 ~ 5.1.25 RC 開發的中文全文索引插件，用於解決MySQL無法正確支持中文全文檢索的問題。

更多的信息見：《MySQL中文全文索引插件 mysqlcft 1.0.0 安裝使用文檔》

一、MySQL中文全文索引插件mysqlcft的特點：

1、優點：

①、精準度很高：採用自創的「三字節交叉切分算法」，對中文語句進行分割，無中文分詞詞庫，搜索精準度遠比中文分詞算法高，能達到LIKE ‘%…%”的準確率。

②、查詢速度快：查詢速度比LIKE ‘%…%”搜索快3～50倍，文章末尾有測試結果；

③、標準插件式：以MySQL 5.1全文索引的標準插件形式開發，不修改MySQL源代碼，不影響MySQL的其他功能，可快速跟進MySQL新版本；

④、支持版本多：支持所有的MySQL 5.1 Release Candidate版本，即MySQL 5.1.22 RC～最新的MySQL 5.1.25 RC；

⑤、支持字符集：支持包括GBK、GB2312、UTF-8、Latin1、BIG5在內的MySQL字符集（其他字符集沒有測試過）；

⑥、系統兼容好：具有i386和x86_64兩個版本，支持32位（i386）和64位（x86_64）CPU及Linux系統；

⑦、適合分佈式：非常適合MySQL Slave分佈式系統架構，無詞庫維護成本，不存在詞庫同步問題。

2、缺點：

①、mysqlcft中文全文索引只適用於MyISAM表，因為MySQL只支持對MyISAM表建立FULLTEXT索引；

②、MySQL不能靜態編譯安裝，否則無法安裝mysqlcft插件；

③、基於「三字節交叉切分算法」的索引文件會比海量、ft-hightman等基於「中文分詞算法」的索引文件稍大，但不是大很多。根據我的測試，mysqlcft全文索引的.MYI索引文件是.MYD數據文件的2～6倍。

二、mysqlcft的核心思想──「三字節交叉切分算法」

註：本文以0～7數字序號代表「英文」、「數字」和「半個漢字」，以便說明。

1、按三字節對中文語句進行切分，建立全文索引：

例如：「全文索引」或「1台x光機」四個字會被交叉分拆為6份，建立反向索引：

012　　123　　234　　345　　456　　567

2、按三字節對搜索的關鍵字進行切分，在全文索引中找出對應信息：

例①：搜索關鍵字「文索」，用數字序號表示就是「2～5」，那麼它將被切分成：

234　　345

這樣，就與全文索引對上了。

例②：搜索關鍵字「x光機」，用數字序號表示就是「3～7」，那麼它將被切分成：

345　　456　　567

這樣，也與全文索引對上了。

例③：搜索關鍵字「1台 光機」，用數字序號表示就是「0～2」和「4～7」，那麼它將被切分成：

012　　456　　567

這樣，多關鍵字搜索也與全文索引對上了。

套件名稱：phpStudy
版本：2009
官網：http://www.3527.com/phpstudy/
下載：http://www.3527.com/phpstudy/phpstudy2009.exe
demo：
執行結果畫面：

軟體介紹：
phpStudy是一款多合一web伺服器，集合各個套件為一身，其中包含的套件有Apache+PHP+MySQL+phpMyAdmin+ZendOptimizer，一次性安裝，安裝完後不用額外的設定即可使用，是非常方便、好用的PHP調試環境。
對學習PHP的新手來說，WINDOWS下環境配置是一件很困難的事；對老手來說也是一件煩瑣的事。因此無論你是新手還是老手，phpStudy是一個不錯的選擇。

1、全面適合 Win2000/XP/2003 操作系統，IIS和Apache二選一安裝。

2、該套件集合以下軟件，均為最新版本。

PHP 5.2.11 新型的CGI程序編寫語言，易學易用、速度快、跨平台。

Apache 2.2.13 最流行的HTTP服務器軟件，快速、可靠、開源。

MySQL 5.0.85 執行性能高，運行速度快，容易使用，非常棒數據庫。

phpMyAdmin 3.2.2 開源、基於WEB而小巧的MySQL管理程序。

ZendOptimizer 3.3.3 免費的PHP優化引擎，性能提高30%以上。

OpenSSL 0.9.8k 密碼算法庫、SSL協議庫以及應用程序。

3、如果作為web服務器請稍修改配置文件即可。MySQL數據庫用戶名：root，密碼root，安裝後請重新設置密碼。

注意：安裝過程中，如有防火牆開啟，註冊啟動服務時，會提示是否信任httpd、mysqld-nt運行，以及端口80、3306等，請選擇允許。

套件名稱：SQLyog
版本：8.13
官網：http://www.webyog.com/en/index.php
下載： http://www.webyog.com/en/downloads.php#sqlyog
demo：
執行結果畫面： 官網畫面

軟體介紹：

SQLyog 是一個很容易使用、快速且簡單的MySQL圖形化界面管理工具。

套件名稱：SQLyog
版本：8.13 RC2
官網：http://www.webyog.com/en/index.php
下載： http://www.webyog.com/en/downloads.php#sqlyog
demo：
執行結果畫面： 官網畫面

軟體介紹：

SQLyog 是一個很容易使用、快速且簡單的MySQL圖形化界面管理工具。