显示SQL语句执行时间

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

使用方法如下：

1.启动

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

2.查询

• mysql> show profiles; # 從啟動之後所有語法及使用時間, 含錯誤語法都會紀錄.

 

• 查詢所有花費時間加總

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

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

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

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

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

   其它屬性列表

• 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筆

3.关闭

• mysql> set profiling=0;