程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中應用SHOW PROFILE敕令剖析機能的用法整頓

MySQL中應用SHOW PROFILE敕令剖析機能的用法整頓

編輯:MySQL綜合教程

MySQL中應用SHOW PROFILE敕令剖析機能的用法整頓。本站提示廣大學習愛好者:(MySQL中應用SHOW PROFILE敕令剖析機能的用法整頓)文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL中應用SHOW PROFILE敕令剖析機能的用法整頓正文


show profile是由Jeremy Cole募捐給MySQL社區版本的。默許的是封閉的,然則會話級別可以開啟這個功效。開啟它可讓MySQL搜集在履行語句的時刻所應用的資本。為了統計報表,把profiling設為1
 

mysql> SET profiling = 1;

 
以後在運轉一個查詢

mysql> SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt
-> FROM sakila.film_actor
-> INNER JOIN sakila.actor USING(actor_id)
-> GROUP BY sakila.film_actor.film_id
-> ORDER BY cnt_name DESC;
...
997 rows in set (0.03 sec)

 
這個履行語句的分析信息存儲在這個會話中。應用SHOW PROFILES停止檢查。

mysql> SHOW PROFILES\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.02596900
Query: SELECT COUNT(DISTINCT actor.first_name) AS cnt_name,...

 
你可使用SHOW PROFILE語句來獲得曾經存儲的分析數據。假如不加參數,會顯示狀況和它們連續的時光。

mysql> SHOW PROFILE;
+------------------------+-----------+
| Status | Duration |
+------------------------+-----------+
| (initialization) | 0.000005 |
| Opening tables | 0.000033 |
| System lock | 0.000037 |
| Table lock | 0.000024 |
| init | 0.000079 |
| optimizing | 0.000024 |
| statistics | 0.000079 |
| preparing | 0.00003 |
| Creating tmp table | 0.000124 |
| executing | 0.000008 |
| Copying to tmp table | 0.010048 |
| Creating sort index | 0.004769 |
| Copying to group table | 0.0084880 |
| Sorting result | 0.001136 |
| Sending data | 0.000925 |
| end | 0.00001 |
| removing tmp table | 0.00004 |
| end | 0.000005 |
| removing tmp table | 0.00001 |
| end | 0.000011 |
| query end | 0.00001 |
| freeing items | 0.000025 |
| removing tmp table | 0.00001 |
| freeing items | 0.000016 |
| closing tables | 0.000017 |
| logging slow query | 0.000006 |
+------------------------+-----------+

 
每行都是狀況變更的進程和它們連續的時光。Status那一列和SHOW FULL PROCESSLIST的State應當是分歧的。
這個值是來自於thd->proc_info變量。是以你所檢查的這個值是直接來自MySQL外部的。雖然這些數值是比擬直接易懂的,你也能夠檢查MySQL手冊。
 
你可以給SHOW PROFILES指定一個Query_ID來檢查指定的語句,還可以給輸入添加新的列。如,檢查用戶和CPU應用。可以用以下敕令。
 

mysql> SHOW PROFILE CPU FOR QUERY 1;

 
SHOW PROFILE可以深刻的檢查辦事器履行語句的任務情形。和也能贊助你懂得履行語句消費時光的情形。一些限制是它沒有完成的功效,不克不及檢查和分析其他銜接的語句,和分析時所惹起的消費。

SHOW PROFILES顯示比來發給辦事器的多條語句,條數依據會話變量profiling_history_size界說,默許是15,最年夜值為100。設為0等價於封閉剖析功效。

SHOW PROFILE FOR QUERY n,這裡的n就是對應SHOW PROFILES輸入中的Query_ID。


例如:

mysql> show profiles;
+----------+-------------+---------------------------------------+
| Query_ID | Duration | Query     |
+----------+-------------+---------------------------------------+
| 1 | 0.00037700 | alter table table1 drop column c3 int |
| 2 | 70.37123800 | alter table table1 drop column c3 |
| 3 | 0.00124500 | show tables    |
| 4 | 0.00569800 | select * from table1 where id=2 |
| 5 | 0.00068500 | select count(1) from tables1  |
| 6 | 0.00197900 | select count(1) from table1  |
| 7 | 0.00105900 | alter table tables1 drop c1  |
| 8 | 0.00800200 | alter table table1 drop c1  |
+----------+-------------+---------------------------------------+
8 rows in set (0.00 sec)

 

mysql> SHOW PROFILE FOR QUERY 2; #檢查alter table table1 drop column c3的剖析
+------------------------------+-----------+
| Status   | Duration |
+------------------------------+-----------+
| starting   | 0.000183 |
| checking permissions  | 0.000057 |
| checking permissions  | 0.000059 |
| init    | 0.000060 |
| Opening tables  | 0.000071 |
| System lock   | 0.000062 |
| setup   | 0.000080 |
| creating table  | 0.005052 |
| After create   | 0.000220 |
| copy to tmp table  | 0.000244 |
| rename result table  | 70.364027 |
| end    | 0.000575 |
| Waiting for query cache lock | 0.000062 |
| end    | 0.000075 |
| query end   | 0.000057 |
| closing tables  | 0.000061 |
| freeing items  | 0.000080 |
| logging slow query  | 0.000056 |
| logging slow query  | 0.000098 |
| cleaning up   | 0.000059 |
+------------------------------+-----------+
20 rows in set (0.00 sec)

假如沒有指定FOR QUERY,那末輸入比來一條語句的信息。

LIMIT部門的用法與SELECT中LIMIT子句分歧,不贅述。

type是可選的,取值規模可以以下:

  • ALL 顯示一切機能信息
  • BLOCK IO 顯示塊IO操作的次數
  • CONTEXT SWITCHES 顯示高低文切換次數,不論是自動照樣主動
  • CPU 顯示用戶CPU時光、體系CPU時光
  • IPC 顯示發送和吸收的新聞數目
  • MEMORY [暫未完成]
  • PAGE FAULTS 顯示頁毛病數目
  • SOURCE 顯示源碼中的函數稱號與地位
  • SWAPS 顯示SWAP的次數

例:

mysql> show profile cpu for query 2;
+------------------------------+-----------+----------+------------+
| Status   | Duration | CPU_user | CPU_system |
+------------------------------+-----------+----------+------------+
| starting   | 0.000183 | 0.000000 | 0.000000 |
| checking permissions  | 0.000057 | 0.000000 | 0.000000 |
| checking permissions  | 0.000059 | 0.000000 | 0.000000 |
| init    | 0.000060 | 0.000000 | 0.000000 |
| Opening tables  | 0.000071 | 0.000000 | 0.000000 |
| System lock   | 0.000062 | 0.000000 | 0.000000 |
| setup   | 0.000080 | 0.000000 | 0.001000 |
| creating table  | 0.005052 | 0.003000 | 0.001000 |
| After create   | 0.000220 | 0.000000 | 0.000000 |
| copy to tmp table  | 0.000244 | 0.000000 | 0.000000 |
| rename result table  | 70.364027 | 7.470864 | 41.612674 |
| end    | 0.000575 | 0.000000 | 0.001000 |
| Waiting for query cache lock | 0.000062 | 0.000000 | 0.000000 |
| end    | 0.000075 | 0.000000 | 0.000000 |
| query end   | 0.000057 | 0.000000 | 0.000000 |
| closing tables  | 0.000061 | 0.000000 | 0.000000 |
| freeing items  | 0.000080 | 0.000000 | 0.000000 |
| logging slow query  | 0.000056 | 0.000000 | 0.000000 |
| logging slow query  | 0.000098 | 0.000000 | 0.000000 |
| cleaning up   | 0.000059 | 0.000000 | 0.000000 |
+------------------------------+-----------+----------+------------+
20 rows in set (0.00 sec)

ps:
SHOW PROFILE ALL FOR QUERY 2;的信息還可以經由過程SELECT * FROM information_schema.profiling WHERE query_id = 2 ORDER BY seq;獲得。

感化規模
這個敕令只是在本會話內起感化,即沒法剖析本會話外的語句。

開啟剖析功效後,一切本會話中的語句都被剖析(乃至包含履行毛病的語句),除SHOW PROFILE和SHOW PROFILES兩句自己。

運用示例:應用SHOW PROFILE剖析查詢緩存射中的機能優勢。

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

 

mysql> select count(1) as cnt from tran_excution;
+-------+
| cnt |
+-------+
| 14225 |
+-------+
1 row in set (0.00 sec)

因為曾經啟用了查詢緩存,雷同查詢(非分區表)可以直接在查詢緩存中射中。

mysql> select count(1) as cnt from tran_excution;

我們細心看下兩個異樣的語句的剖析。

mysql> show profile source for query 1;
+--------------------------------+----------+-----------------------+---------------+-------------+
| Status    | Duration | Source_function | Source_file | Source_line |
+--------------------------------+----------+-----------------------+---------------+-------------+
| starting   | 0.000048 | NULL   | NULL  | NULL |
| Waiting for query cache lock | 0.000013 | try_lock  | sql_cache.cc |  454 |
| checking query cache for query | 0.000040 | send_result_to_client | sql_cache.cc | 1561 |
| checking permissions  | 0.000023 | check_access  | sql_parse.cc | 4751 |
| Opening tables   | 0.000040 | open_tables  | sql_base.cc | 4831 |
| System lock   | 0.000020 | mysql_lock_tables | lock.cc |  299 |
| Waiting for query cache lock | 0.000037 | try_lock  | sql_cache.cc |  454 |
| init    | 0.000020 | mysql_select  | sql_select.cc | 2579 |
| optimizing   | 0.000015 | optimize  | sql_select.cc |  865 |
| statistics   | 0.000017 | optimize  | sql_select.cc | 1056 |
| preparing   | 0.000016 | optimize  | sql_select.cc | 1078 |
| executing   | 0.000015 | exec   | sql_select.cc | 1836 |
| Sending data   | 0.003875 | exec   | sql_select.cc | 2380 |
| end    | 0.000018 | mysql_select  | sql_select.cc | 2615 |
| query end   | 0.000015 | mysql_execute_command | sql_parse.cc | 4440 |
| closing tables   | 0.000016 | mysql_execute_command | sql_parse.cc | 4492 |
| freeing items   | 0.000016 | mysql_parse  | sql_parse.cc | 5640 |
| Waiting for query cache lock | 0.000012 | try_lock  | sql_cache.cc |  454 |
| freeing items   | 0.000032 | NULL   | NULL  | NULL |
| Waiting for query cache lock | 0.000017 | try_lock  | sql_cache.cc |  454 |
| freeing items   | 0.000016 | NULL   | NULL  | NULL |
| storing result in query cache | 0.000017 | end_of_result  | sql_cache.cc | 1020 |
| logging slow query  | 0.000018 | log_slow_statement | sql_parse.cc | 1461 |
| logging slow query  | 0.000050 | log_slow_statement | sql_parse.cc | 1470 |
| cleaning up   | 0.000018 | dispatch_command | sql_parse.cc | 1417 |
+--------------------------------+----------+-----------------------+---------------+-------------+
25 rows in set (0.00 sec)

 

mysql> show profile source for query 2;
+--------------------------------+----------+-----------------------+--------------+-------------+
| Status    | Duration | Source_function | Source_file | Source_line |
+--------------------------------+----------+-----------------------+--------------+-------------+
| starting   | 0.000051 | NULL   | NULL  | NULL |
| Waiting for query cache lock | 0.000014 | try_lock  | sql_cache.cc |  454 |
| checking query cache for query | 0.000016 | send_result_to_client | sql_cache.cc | 1561 |
| checking privileges on cached | 0.000013 | send_result_to_client | sql_cache.cc | 1652 |
| checking permissions  | 0.000015 | check_access  | sql_parse.cc | 4751 |
| sending cached result to clien | 0.000036 | send_result_to_client | sql_cache.cc | 1749 |
| logging slow query  | 0.000017 | log_slow_statement | sql_parse.cc | 1461 |
| cleaning up   | 0.000018 | dispatch_command | sql_parse.cc | 1417 |
+--------------------------------+----------+-----------------------+--------------+-------------+
8 rows in set (0.00 sec)

可以清楚地看到緩存中射中時,年夜年夜節儉了後台的開支。固然緩存的應用也須要依據各類場景(表的數據范圍,更新頻率等)考核應用,其實不是啟用緩存就必定可以或許進步查詢效力。這裡僅僅作為SHOW PROFILE的一個運用示例。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved