MySQL执行语句性能分析


Show?profiles是5.0.37之后添加的,要想使用此功能,要确保版本在5.0.37之后。SHOW?PROFILE?[type?[,?type]?...?][FOR?QUERY?n]
????[LIMIT?row_count?[OFFSET?offset]]type:
????ALL
??|?BLOCK?IO
??|?CONTEXT?SWITCHES
??|?CPU
??|?IPC
??|?MEMORY
??|?PAGE?FAULTS
??|?SOURCE
??|?SWAPS
>?SET?profiling?=?1;
SELECT?@@profiling;+-------------+
|?@@profiling?|
+-------------+
|???????????0?|
+-------------+
1?row?in?set?(0.00?sec)

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

mysql>?DROP?TABLE?IF?EXISTS?t1;Query?OK,?0?rows?affected,?1?warning?(0.00?sec)

mysql>?CREATE?TABLE?T1?(id?INT);Query?OK,?0?rows?affected?(0.01?sec)

mysql>?SHOW?PROFILES;+----------+----------+--------------------------+
|?Query_ID?|?Duration?|?Query????????????????????|
+----------+----------+--------------------------+
|????????0?|?0.000088?|?SET?PROFILING?=?1????????|
|????????1?|?0.000136?|?DROP?TABLE?IF?EXISTS?t1??|
|????????2?|?0.011947?|?CREATE?TABLE?t1?(id?INT)?|
+----------+----------+--------------------------+
3?rows?in?set?(0.00?sec)

mysql>?SHOW?PROFILE;+----------------------+----------+
|?Status???????????????|?Duration?|
+----------------------+----------+
|?checking?permissions?|?0.000040?|
|?creating?table???????|?0.000056?|
|?After?create?????????|?0.011363?|
|?query?end????????????|?0.000375?|
|?freeing?items????????|?0.000089?|
|?logging?slow?query???|?0.000019?|
|?cleaning?up??????????|?0.000005?|
+----------------------+----------+
7?rows?in?set?(0.00?sec)

mysql>?SHOW?PROFILE?FOR?QUERY?1;+--------------------+----------+
|?Status?????????????|?Duration?|
+--------------------+----------+
|?query?end??????????|?0.000107?|
|?freeing?items??????|?0.000008?|
|?logging?slow?query?|?0.000015?|
|?cleaning?up????????|?0.000006?|
+--------------------+----------+
4?rows?in?set?(0.00?sec)

mysql>?SHOW?PROFILE?CPU?FOR?QUERY?2;+----------------------+----------+----------+------------+
|?Status???????????????|?Duration?|?CPU_user?|?CPU_system?|
+----------------------+----------+----------+------------+
|?checking?permissions?|?0.000040?|?0.000038?|???0.000002?|
|?creating?table???????|?0.000056?|?0.000028?|???0.000028?|
|?After?create?????????|?0.011363?|?0.000217?|???0.001571?|
|?query?end????????????|?0.000375?|?0.000013?|???0.000028?|
|?freeing?items????????|?0.000089?|?0.000010?|???0.000014?|
|?logging?slow?query???|?0.000019?|?0.000009?|???0.000010?|
|?cleaning?up??????????|?0.000005?|?0.000003?|???0.000002?|
+----------------------+----------+----------+------------+
7?rows?in?set?(0.00?sec)