當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL中show profile详解

發(fā)布時間：2023/12/16 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL中show profile详解小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

Show Profile官方文檔地址：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

當(dāng)我們在優(yōu)化sql的時候可以使用explain來查看執(zhí)行計劃，可當(dāng)根據(jù)執(zhí)行計劃優(yōu)化完畢之后我們的sql的運(yùn)行時間還是比較長，這時我們可以使用show profile

更細(xì)粒度的分析，show-profile，可以提高用來分析當(dāng)前會話中語句執(zhí)行的資源消耗情況,可以用于sql調(diào)優(yōu)的測量.

1. 基本語法：

查看是否開啟：show variables like "%pro%";
開啟：set profiling = 1
查看SQL執(zhí)行時間：show profiles;
查看記錄中第一條SQL詳細(xì)時間：show profile for query 1;

關(guān)于show Profile的基本用法請看上篇文章：通過Query Profiler查看MySQL語句運(yùn)行時間

2. 通過type指定顯示其他信息

我們看一下官方提供的profile語法：

SHOW PROFILE [type [, type] ... ][FOR QUERY n][LIMIT row_count [OFFSET offset]]type: {ALL 顯示所有信息| BLOCK IO 顯示塊輸入和輸出操作的數(shù)量| CONTEXT SWITCHES 顯示自愿上下文切換和非自愿上下文切換的數(shù)量| CPU 顯示用戶和系統(tǒng)的CPU使用時間| IPC 顯示已發(fā)送和已接收消息（messages）的數(shù)量| MEMORY -- 尚未生效| PAGE FAULTS 顯示主要和次要頁面錯誤的數(shù)量| SOURCE 顯示源代碼中函數(shù)名稱以及該函數(shù)所在文件的名稱和行號| SWAPS 顯示SWAP數(shù)量 }

3. show profile返回的行和列

如下圖所示，是執(zhí)行一個show profile all for query 3;命令顯示的結(jié)果集。我們根據(jù)行和列分別來分析一個每個字段的含義。

行字段的含義：

列字段的含義：

* Sending data (最重要的一個過程★★★★★)線程正在讀取和處理一條SELECT語句的行，并且將數(shù)據(jù)發(fā)送至客戶端。由于在此期間會執(zhí)行大量的磁盤訪問（讀操作），這個狀態(tài)在一個指定查詢的生命周期中經(jīng)常是耗時最長的。這個字段才是SQL真正運(yùn)行采集+相應(yīng)數(shù)據(jù)的時間，而非executing；--以下按照首字母順序依次排序* After create這個狀態(tài)當(dāng)線程創(chuàng)建一個表（包括內(nèi)部臨時表）時，在這個建表功能結(jié)束時出現(xiàn)。即使某些錯誤導(dǎo)致建表失敗，也會使用這個狀態(tài)。* Analyzing當(dāng)計算MyISAM表索引分布時。（比如進(jìn)行ANALYZE TABLE時）* checking permissions這個線程檢查服務(wù)器是否有具有執(zhí)行該語句的所需權(quán)限。* Checking table線程正在執(zhí)行表檢查操作。* cleaning up線程處理一個命令，并正準(zhǔn)備釋放內(nèi)存和重置某些狀態(tài)變量。* closing tables線程正在將變更的表中的數(shù)據(jù)刷新到磁盤上并正在關(guān)閉使用過的表。這應(yīng)該是一個快速的操作。如果不是這樣的話則應(yīng)該檢查硬盤空間是否已滿或者硬盤IO是否達(dá)到瓶頸。 * converting HEAP to MyISAM線程將一個內(nèi)部臨時表轉(zhuǎn)換為磁盤上的MyISAM表。* copy to tmp table 線程正在處理一個ALTER TABLE語句。這個狀態(tài)發(fā)生在新的表結(jié)構(gòu)已經(jīng)創(chuàng)建之后，但是在數(shù)據(jù)被復(fù)制進(jìn)入之前。* Copying to group table如果一個語句有不同的ORDER BY和GROUP BY條件，數(shù)據(jù)會被復(fù)制到一個臨時表中并且按組排序。* Copying to tmp table線程將數(shù)據(jù)寫入內(nèi)存中的臨時表。正在創(chuàng)建臨時表以存放部分查詢結(jié)果* Copying to tmp table on disk線程正在將數(shù)據(jù)寫入磁盤中的臨時表。臨時表的結(jié)果集過大。所以線程將臨時表由基于內(nèi)存模式改為基于磁盤模式，以節(jié)省內(nèi)存。但是這個過程會異常的緩慢！！* Creating index線程正在對一個MyISAM表執(zhí)行ALTER TABLE ... ENABLE KEYS語句。* Creating sort index線程正在使用內(nèi)部臨時表處理一個SELECT 操作。* creating table線程正在創(chuàng)建一個表，包括創(chuàng)建臨時表。* Creating tmp table線程正在創(chuàng)建一個臨時表在內(nèi)存或者磁盤上。如果這個表創(chuàng)建在內(nèi)存上但是之后被轉(zhuǎn)換到磁盤上，這個狀態(tài)在運(yùn)行Copying to tmp table on disk 的時候保持。* deleting from main table線程正在執(zhí)行多表刪除的第一部分，只從第一個表中刪除。并且保存列和偏移量用來從其他（參考）表刪除。* deleting from reference tables線程正在執(zhí)行多表刪除的第二部分，并從其他表中刪除匹配的行。* discard_or_import_tablespace線程正在執(zhí)行ALTER TABLE ... DISCARD TABLESPACE 或 ALTER TABLE ... IMPORT TABLESPACE語句。* end這個狀態(tài)出現(xiàn)在結(jié)束時，但是在對ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, 或者 UPDATE 語句進(jìn)行清理之前。* executing該線程已開始執(zhí)行一條語句。* Execution of init_command線程正在執(zhí)行處于init_command系統(tǒng)變量的值中的語句。* freeing items * 線程已經(jīng)執(zhí)行了命令。在這個狀態(tài)中涉及的查詢緩存可以得到一些釋放。這個狀態(tài)通常后面跟隨cleaning up狀態(tài)。* Flushing tables線程正在執(zhí)行FLUSH TABLES 并且等待所有線程關(guān)閉他們的表。* FULLTEXT initialization服務(wù)器正在準(zhǔn)備進(jìn)行自然語言全文檢索。* init這個狀態(tài)出現(xiàn)在線程初始化ALTER TABLE, DELETE, INSERT, SELECT, 或 UPDATE語句之前。服務(wù)器在這種狀態(tài)下進(jìn)行的操作，包括：刷新全日志、Innodb日志，和一些查詢緩存清理操作。* Killed程序?qū)€程發(fā)送了KILL語句，并且它應(yīng)該放棄下一次對KILL標(biāo)記的檢查。這個標(biāo)記在每一個MySQL的主要循環(huán)中被檢查，但在某些情況下，它可能需要令線程在很短的時間內(nèi)死亡。如果這個線程被其他線程鎖住了，這個KILL操作會在其他線程釋放鎖的瞬時執(zhí)行。* logging slow query這個線程正在將語句寫入慢查詢?nèi)罩尽?span id="ze8trgl8bvbq" class="token operator">* NULL沒有操作的狀態(tài)。* login線程連接的初始狀態(tài)。直到客戶端已經(jīng)成功驗(yàn)證。* manage keys服務(wù)器啟用或禁用表索引。* Opening tables, Opening table線程正試圖打開一張表* optimizing服務(wù)器執(zhí)行查詢的初步優(yōu)化。* preparing在查詢優(yōu)化過程中出現(xiàn)這個狀態(tài)。* Purging old relay logs線程正在移除不必要的中繼日志文件。* query end這個狀態(tài)出現(xiàn)在處理一個查詢之后，但是在freeing items狀態(tài)之前。* Reading from net服務(wù)器正在從網(wǎng)絡(luò)閱讀數(shù)據(jù)包。* Removing duplicates查詢正在使用SELECT DISTINCT，這種情況下MySQL不能在早期階段優(yōu)化掉一些distinct操作。因此，MySQL需要一個額外的階段，在將結(jié)果發(fā)送到客戶端之前刪除所有重復(fù)的行。* removing tmp table線程正在移除一個內(nèi)置臨時表，在執(zhí)行一條SELECT語句之后。如果沒有臨時表產(chǎn)生，那么這個狀態(tài)不被使用。* rename * 線程正在重命名一張表。* rename result table線程正在處理ALTER TABLE語句，創(chuàng)建新的表，并且重命名它來代替原有的表。* Reopen tables線程獲得了表鎖，但是在取得表鎖之后才發(fā)現(xiàn)該表的底層結(jié)構(gòu)已經(jīng)發(fā)生了變化。線程釋放這個鎖，關(guān)閉表，并試圖重新打開該表。* Repair by sorting修復(fù)代碼正在使用一個分類來創(chuàng)建索引。* Repair done線程完成一個多線程的MyISAM表的修復(fù)。* Repair with keycache修復(fù)代碼正在通過索引緩存一個接一個地使用創(chuàng)建索引。這比通過分類修復(fù)要慢很多。* Rolling back線程正在回滾一個事務(wù)* Searching rows for update線程正在進(jìn)行第一階段，在更新前尋找所有匹配的行。如果update正在更改用于查找相關(guān)行的索引，則必須這么做。* setup線程正開始進(jìn)行一個ALTER TABLE操作。* Sorting for group線程正在執(zhí)行一個由GROUP BY指定的排序。* Sorting for order線程正在執(zhí)行一個由ORDER BY指定的排序。* Sorting index線程正在對索引頁進(jìn)行排序，為了對MyISAM表進(jìn)行操作時獲得更優(yōu)的性能。* Sorting result對于一個SELECT語句，這與創(chuàng)建排序索引相似，但是是對非臨時表。* statistics服務(wù)器計算統(tǒng)計去規(guī)劃一個查詢。如果一個線程長時間處于這個狀態(tài)，這個服務(wù)器的磁盤可能在執(zhí)行其他工作。* System lock這個線程正在請求或者等待一個內(nèi)部的或外部的系統(tǒng)表鎖。如果這個狀態(tài)是由于外部鎖的請求產(chǎn)生的，并且你沒有使用多個正在訪問相同的表的mysql服務(wù)器* Waiting for table level lock系統(tǒng)鎖定后的下一個線程狀態(tài)。線程已獲得外部鎖并且將請求內(nèi)部表鎖。* Updating線程尋找更新匹配的行并進(jìn)行更新。* updating main table線程正在執(zhí)行多表更新的第一部分，只從第一個表中更新。并且保存列和偏移量用來從其他（參考）表更新。* updating reference tables線程正在執(zhí)行多表更新的第二部分，并從其他表中更新匹配的行。* User lock線程正在請求或等待一個GET_LOCK()調(diào)用所要求的咨詢鎖。對于SHOW PROFILE，這個狀態(tài)意味這線程正在請求鎖。（而非等待）* User sleep線程調(diào)用了一個SLEEP()。* Waiting for commit lock一個顯式或隱式語句在提交時等待釋放讀鎖* Waiting for global read lock等待全局讀鎖。* Waiting for release of readlock等待釋放讀鎖。* Waiting for tables, Waiting for table, Waiting for table flush線程獲得一個通知，底層表結(jié)構(gòu)已經(jīng)發(fā)生變化，它需要重新打開表來獲取新的結(jié)構(gòu)。然而，重新打開表，它必須等到所有其他線程關(guān)閉這個有問題的表。這個通知產(chǎn)生通常因?yàn)榱硪粋€線程對問題表執(zhí)行了FLUSH TABLES或者以下語句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.* Waiting for lock_type lock等待各個種類的表鎖。* Waiting on cond一個普通的狀態(tài)，線程正在等待一個條件為真。沒有特定的狀態(tài)信息可用。* Writing to net服務(wù)器正在寫一個網(wǎng)絡(luò)數(shù)據(jù)包。

3. SQL執(zhí)行過程中可能導(dǎo)致時間慢的原因

1. Sending data (最重要的一個過程★★★★★)線程正在讀取和處理一條SELECT語句的行，并且將數(shù)據(jù)發(fā)送至客戶端。由于在此期間會執(zhí)行大量的磁盤訪問（讀操作），這個狀態(tài)在一個指定查詢的生命周期中經(jīng)常是耗時最長的。對于一個普通查詢來說，這個參數(shù)過大可分為兩種情況1. 第一種是SQL本身，比如沒有建立正確的索引，索引失效等等情況，這種數(shù)據(jù)體現(xiàn)在CPU_user 和CPU_sysyem字段時間過長；2. 第二種是相應(yīng)數(shù)據(jù)量過大，導(dǎo)致CPU調(diào)度時上下文頻繁切換。這種數(shù)據(jù)體現(xiàn)在CONTEXT_INVOLUNTARY和CONTEXT_VOLUNTARY字段時間過長;像：外網(wǎng)使用Navicat連接到遠(yuǎn)程數(shù)據(jù)庫中。查詢一個普通的SQL，在本地MySQL執(zhí)行速度很快，但是使用遠(yuǎn)程服務(wù)器的MySQL就異常的緩慢。這時若查詢profile詳情，就會發(fā)現(xiàn)大量相應(yīng)數(shù)據(jù)傳輸IO導(dǎo)致頻繁的上下文切換消耗了大量的時間。2. converting HEAP to MyISAM原譯指的是：線程將一個內(nèi)部臨時表轉(zhuǎn)換為磁盤上的MyISAM表。我們實(shí)際操作中可能出現(xiàn)的問題就是查詢結(jié)果太大了導(dǎo)致內(nèi)存不夠，往磁盤上搬。3.Creating tmp table創(chuàng)建了臨時表4.Coping to tmp table on disk把內(nèi)存中臨時表復(fù)制到磁盤5.locked加鎖------------------------------------------------ 2，4 可以修改一下tmp_table_size和max_heap_table_size兩個參數(shù)來調(diào)整

show profile語句已經(jīng)棄用，并將在以后版本中移除，建議使用 Performance Schema

Performance Schema文檔：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html

總結(jié)

以上是生活随笔為你收集整理的MySQL中show profile详解的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇：大数据时代，你应该知道的生活真相（下）
下一篇：【马三北漂记】之终章