一渔彰、開啟show profile功能
- 查看當(dāng)前的狀態(tài)
是否啟用缩焦，看看當(dāng)前的MySQL版本是否支持
默認(rèn)是關(guān)閉锯厢，使用前需要開啟show variables like 'profiling';
或者
示例：show variables like 'profiling%';mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show variables like 'profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+ 2 rows in set, 1 warning (0.00 sec) - 開啟
默認(rèn)該功能是關(guān)閉的苹威，使用前需要開啟
示例：set profiling=on;
目前只是臨時設(shè)置腕铸，退出當(dāng)前連接之后，設(shè)置失效躬厌。這種很耗性能的操作马昨，沒有必要一直開啟。mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | ON | | profiling_history_size | 15 | +------------------------+-------+ 2 rows in set, 1 warning (0.00 sec)
二扛施、運(yùn)行SQL并查看結(jié)果
- 運(yùn)行SQL語句
運(yùn)行需要測試SQL語句鸿捧，比如這里我運(yùn)行的SQL語句如下：select item_title from tb_orders group by item_title; select sleep(3); select * from tb_orders where id=1; - 查看結(jié)果
這里的記錄了所有sql的執(zhí)行記錄的信息。mysql> show profiles; +----------+------------+------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------+ | 1 | 0.00151275 | select item_title from tb_orders group by item_title | | 2 | 3.00110125 | select sleep(3) | | 3 | 0.00091525 | select * from tb_orders where id=1 | +----------+------------+------------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)Duration是該條SQL的運(yùn)行時間疙渣〕着可以根據(jù)這個值來分辨哪個SQL比較耗時。
三昌阿、診斷SQL
在上面的粗略結(jié)果展示出來之后饥脑，我們能看出哪條SQL比較耗時。如果我們想知道耗時具體耗在哪里的話懦冰，可以使用SQL診斷獲取更多更詳細(xì)的信息灶轰。
我們以Query ID為1的那條語句為例。語法如下：
show profile [選項] for query [上一步前面的問題SQL數(shù)字號碼];
示例：
mysql> show profile for query 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000073 |
| Executing hook on transaction | 0.000003 |
| starting | 0.000004 |
| checking permissions | 0.000004 |
| Opening tables | 0.000146 |
| init | 0.000004 |
| System lock | 0.000005 |
| optimizing | 0.000003 |
| statistics | 0.000009 |
| preparing | 0.000010 |
| Creating tmp table | 0.000022 |
| executing | 0.000002 |
| Sending data | 0.001181 |
| end | 0.000003 |
| query end | 0.000001 |
| waiting for handler commit | 0.000005 |
| removing tmp table | 0.000004 |
| waiting for handler commit | 0.000002 |
| closing tables | 0.000005 |
| freeing items | 0.000022 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
21 rows in set, 1 warning (0.00 sec)
這里列舉了數(shù)據(jù)庫處理你的SQL語句詳細(xì)步驟刷钢，包括每一步驟的耗時笋颤。可以參照這些數(shù)據(jù)對比分析自己的SQL的問題具體出在哪里内地。
分析的時候伴澄，CUP消耗和阻塞IO也是很重要的參考標(biāo)準(zhǔn)，可以增加參數(shù)以展示數(shù)據(jù)阱缓，示例如下：
mysql> show profile cpu, block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000073 | 0.000000 | 0.000000 | NULL | NULL |
| Executing hook on transaction | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| starting | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| Opening tables | 0.000146 | 0.000000 | 0.000000 | NULL | NULL |
| init | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| System lock | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| statistics | 0.000009 | 0.000000 | 0.000000 | NULL | NULL |
| preparing | 0.000010 | 0.000000 | 0.000000 | NULL | NULL |
| Creating tmp table | 0.000022 | 0.000000 | 0.000000 | NULL | NULL |
| executing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL |
| Sending data | 0.001181 | 0.000000 | 0.000000 | NULL | NULL |
| end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000001 | 0.000000 | 0.000000 | NULL | NULL |
| waiting for handler commit | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| removing tmp table | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| waiting for handler commit | 0.000002 | 0.000000 | 0.000000 | NULL | NULL |
| closing tables | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000022 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000008 | 0.000000 | 0.000000 | NULL | NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
21 rows in set, 1 warning (0.00 sec)
參數(shù)備注：
| type | 說明 | |
|---|---|---|
| ALL | 顯示所有的開銷信息 | |
| BLOCKIO | 顯示塊℃相關(guān)開銷 | |
| CONTEXTSWITCHES | 上下文切換相關(guān)開銷 | |
| CPU | 顯示CPU相關(guān)開銷信息 | |
| IPC | 顯示發(fā)送和接收相關(guān)開銷信息 | |
| MEMORY | 顯示內(nèi)存相關(guān)開銷信息 | |
| PAGE FAULTS | 顯示頁面錯誤相關(guān)開銷信息 | |
| SOURCE | 顯示和Sourcefunction非凌，Source蒯e，Source | ine相關(guān)的開銷信息 |
| SWAPS | 顯示交換次數(shù)相關(guān)開銷的信息 |
四荆针、開發(fā)需要注意的事項
-
converting HEAP to MyISAM：查詢結(jié)果太大敞嗡，內(nèi)存都不夠用了往磁盤上搬了。 -
Creating tmp table：
創(chuàng)建臨時表
拷貝數(shù)據(jù)到臨時表
用完再刪除 -
Copying to tmp table on disk：把內(nèi)存中臨時表復(fù)制到磁盤航背，危險：磴病！玖媚！ -
locked
出現(xiàn)以上情況的時候箕肃，表示你的SQL很耗性能，必須要優(yōu)化處理了今魔。
