【MySQL資料庫】第三章解讀：伺服器效能剖析（上）

前言：

保持空杯精神，使用效能剖析，專註於測量伺服器的時間花費在哪裡，思考1、如何確認伺服器是否達到了效能最佳狀態，2、某條語句為什麼不夠快，診斷被使用者描述為“停頓、堆積、卡死”的某些間歇性疑難故障；

接下來將介紹一些工具、技巧最佳化整機效能、最佳化單條語句執行速度，診斷 解決那些很難觀察到的問題，展示如何測量系統並產生剖析報告、如何分析系統的堆棧；

3.1簡介

效能：為完成某件任務所需要的時間度量，in other words 效能即回應時間

輸送量：單位時間內的查詢資料（效能定義的倒數）

第一步：弄清楚時間都去哪了，在哪消耗了時間

如果通測量沒有找到答案，測量方式錯了或不夠完善，只測量需要最佳化的活動

不要在錯誤的時間啟動或停止測試，測量的是彙總後的資訊而不是目標活動本身；需要定位和最佳化子任務

原則：無法測量便無法有效地最佳化

3.1.1通過效能剖析進行最佳化

效能剖析：測量、分析時間花費在哪裡的主要方法

1、測量任務所花費的時間；2、對結果統計、排序（重要前排）

可將相似任務分組匯總，通過效能剖析報告獲需要的結果；報告會列出all任務，每行記錄一個任務：

任務名、執行時間、消耗時間、平均執行時間，執行佔全部時間的百分比；按照任務的消耗時間降序排序；

效能剖析類型：

基於執行時間的分析：什麼任務的執行時間最長

基於等待的分析：判斷任務在什麼地方唄阻塞的時間最長

3.1.2理解效能剖析

效能剖析中缺失但是重要的資訊：

1、值得最佳化的查詢

佔總回應時間比重很小的查詢不值得最佳化；成本大於收益、停止最佳化

2、異常情況

沒有顯式要最佳化的也要最佳化，如執行次數少但每次都特別慢的任務

3、未知的未知

丟失時間：任務總時間與實際測量到的時間的差，即使沒有發現也要注意這類問題存在的可能性

4、被掩藏的細節

無法顯示all回應時間的分布，更多資訊、長條圖、百分比、標準差、偏差指數

5、無法再更高層次的堆棧中進行互動式 分析

3.2對應用程式進行效能剖析：自上而下

效能瓶頸的影響因素：

1、外部資源，調用外部web服務或搜尋引擎

2、應用需要處理大量資料，分析一個超大的xml檔案

3、迴圈中執行昂貴的操作：濫用正則

4、使用低效的演算法：暴力搜尋演算法

建議：新的項目中應考慮包含效能剖析的代碼

3.2.1測量PHP應用程式：空

3.3剖析MySQL查詢

3.3.1剖析伺服器負載

鋪獲MySQL查詢到記錄檔：

1、慢查詢日誌：開銷低、精度高，大的磁碟空間，長期開啟 注意部署日誌輪轉工具，只在收集負載樣本期間開啟即可，5.1後微秒層級；

2、通用日誌，查詢請求到伺服器時進行記錄，不包含回應時間和執行計畫

分析查詢日誌

自頂向下，先產生剖析報告（pt-query-digest），查看特別關注的部分

3.3.2剖析單條查詢

思考為什麼花費這麼長時間、如何去最佳化

使用SHOW PROFILE:MySQL5.1後

查看： show variables like "%pro%";【源】

預設禁用，開啟：set profiling=1；然後在伺服器執行語句（關閉 set profiling=off;）

文法：

SHOW PROFILE [type [, type] ... ]      [FOR QUERY n]      [LIMIT row_count [OFFSET offset]]    type:      ALL                --顯示所有的開銷資訊    | BLOCK IO           --顯示塊IO相關開銷    | CONTEXT SWITCHES   --環境切換相關開銷    | CPU                --顯示CPU相關開銷資訊    | IPC                --顯示發送和接收相關開銷資訊    | MEMORY             --顯示記憶體相關開銷資訊    | PAGE FAULTS        --顯示分頁錯誤相關開銷資訊    | SOURCE             --顯示和Source_function，Source_file，Source_line相關的開銷資訊    | SWAPS              --顯示交換次數相關開銷的資訊

實質是這些開銷資訊被記錄到information_schema.profiling表

show profiles；查看show profile for query 2; 擷取指定查詢的開銷（第二條查詢開銷明細）show profile cpu for query 2 ;查看特定部分的開銷，如下為CPU部分的開銷 show profile block io,cpu for query 2;  同時查看不同資源開銷

使用SHOW STATUS:計數器

全域show global status、基於某個串連會話層級，範圍要注意

計數器顯示活動的頻繁程度，常用：控制代碼計數器、臨時檔案、表計數器

會建立暫存資料表，通過控制代碼操作（引用、指標？）訪問此暫存資料表，影響show status結果中對應的數字

使用慢查詢日誌：【源】【源】

將MySQL中回應時間超過閾值long_query_time的語句記錄到慢查詢日誌中（日誌可以寫入檔案或者資料庫表，如果對效能要求高的話，建議寫檔案），預設是10s，需要手動開啟

查看：

（1）slow_query_log的值為ON為開啟慢查詢日誌，OFF則為關閉慢查詢日誌。

（2）slow_query_log_file 的值是記錄的慢查詢日誌到檔案中（注意：預設名為主機名稱.log，慢查詢日誌是否寫入指定檔案中，需要指定慢查詢的輸出日誌格式為檔案，相關命令為：show variables like ‘%log_output%’；去查看輸出的格式）。

（3）long_query_time 指定了慢查詢的閾值，即如果執行語句的時間超過該閾值則為慢查詢語句，預設值為10秒。

（4）log_queries_not_using_indexes 如果值設定為ON，則會記錄所有沒有利用索引的查詢（注意：如果只是將log_queries_not_using_indexes設定為ON，而將slow_query_log設定為OFF，此時該設定也不會生效，即該設定生效的前提是slow_query_log的值設定為ON），一般在效能調優的時候會暫時開啟，開啟後使用full index scan的sql也會被記錄到慢查詢日誌。

//上述命令只對當前生效，當MySQL重啟失效，如果要永久生效，需要配置my.cnf查看輸出格式：檔案？表show variables like ‘%log_output%’;開啟通用日誌查詢： set global general_log=on;關閉通用日誌查詢： set globalgeneral_log=off;設定通用日誌輸出為表方式： set globallog_output=’TABLE’;設定通用日誌輸出為檔案方式： set globallog_output=’FILE’;設定通用日誌輸出為表和檔案方式：set global log_output=’FILE,TABLE’;查詢慢查詢語句的個數：show global status like ‘%slow%’;

日誌部分內容簡介：

哪條語句導致慢查詢（sql_text），該慢查詢語句的查詢時間（query_time），鎖表時間（Lock_time），以及掃描過的行數（rows_examined）等資訊。

利用內建的慢查詢日誌分析工具：mysqldumpslow

perl mysqldumpslow –s c –t 10 slow-query.log

-s 表示按何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；-t 表示top的意思，後面跟著的資料表示返回前面多少條；-g 後面可以寫Regex匹配，大小寫不敏感。

使用Performance Schema:【源】【源】

監視MySQL伺服器，收集績效參數，且表的儲存引擎PERFORMANCE_SCHEMA，低耗能

本機伺服器，表是記憶體表，表內容在伺服器啟動時重新填充，關閉時丟棄，更改不會被複製或寫入二進位日誌

特性：

效能方案配置可被動態執行SQL修改，立即影響到資料收集

監控服務事件：事件是服務做並被感知到的任何事，時間資訊可被收集

資料庫效能方案，提供對運行時資料庫服務進行內部檢查的方式，關注效能資料

特定於一個資料庫服務，資料庫表關聯到資料服務，修改不會被備份也不寫進二進位日誌

儲存引擎用“感知點”收集事件數目據，且儲存在performance_schema資料庫，可通過select語句進行查詢

補充：資料庫初始安裝有三個基本庫

mysql

包含許可權配置，事件，儲存引擎狀態，主從資訊，日誌，時區資訊，使用者權限配置等

information_schema

對資料庫中繼資料的抽象分析，由此提供了SQL語句方式來查詢資料庫運行時狀態，每次對information_schema的查詢都產生對metadata的互斥訪問，影響其他資料庫的訪問效能。

performance_schema

記憶體型資料庫，使用performance_schema 儲存引擎，通過事件機制將mysql服務的運行時狀態採集並儲存在performace_schema資料庫。注意，兩個單詞之間用底線串連時，表示performance_schema是一個資料庫；用空格分開時，表示一個資料庫效能方案，也表示一個儲存引擎。

相關文章：

【MySQL資料庫】第三章解讀：伺服器效能剖析 （下）

【MySQL資料庫】第二章解讀：MySQL基準測試