【51CTO.com原創稿件】Query Profiling，即查詢分析技術，是 MySQL 數據庫提供的一種診斷 SQL 性能的方法，同時也被視為分析數據庫整體性能的有效技術。

[[352232]] 

圖片來自 Pexels

用戶可以在開啟 Profiling 的情況下，查看當前會話中 SQL 執行時間消耗分布，系統時間，CPU 用戶時間，以及過程中涉及到的關鍵函數在源代碼文件中的定位等。

由于單個大中型應用程序可以在單位時間內完成多個查詢，因此 Query Profiling 是數據庫優化調整的重要組成部分，它既可作為數據庫性能優化的積極主動措施，亦可用于診當前斷數據庫性能是否存在問題。

在實際工作場景中，如果不采用可靠的查詢分析技術，相關技術人員往往很難定位數據庫中性能瓶頸及性能不佳問題的根源所在。

作為 MySQL 的一個分支，MariaDB Server 自帶的內置工具中為我們提供了 Query Profiling 相關的查詢概要分析技術。

我們以 Slow Query Log(慢速查詢日志)和 Performance Schema(性能策略模型)這兩類 MariaDB Server 內置工具為例，深入探索查詢分析技術的價值。

MariaDB vs MySQL

首先讓我們來回顧一下 MariaDB 和 MySQL 這兩種產品間的親屬關系。

早在 2010 甲骨文宣布收購 Sun 公司的那天，MySQL 之父 Michael“Monty”Widenius就派生了 MySQL，進而推出 MariaDB，從此便吸引了一大批 MySQL 開發人員為之效力。

如今 MariaDB 已經成為了 MySQL 發展最快的一個分支，相較于 MySQL 本身，具有更豐富的功能及更優越的性能。

MariaDB 并非孤立的一個分支，它是基于相應的 MySQL 版本而存在的。例如，MariaDB 5.1.53 是在 MySQL 5.1.53 基礎上，修復了之前的 Bug，添加了存儲引擎，新功能等，性能方面也做了相應改進。

Slow Query Log(慢速查詢日志)

MariaDB 和 MySQL 都有 Slow Query Log(慢速查詢日志)這一功能。該日志中記錄了一些被認為執行速度非常緩慢且可能存在問題的查詢語句。

這里的“慢速”查詢定義為運行時間比 [long_query_time] 全局系統變量值(默認為 10 秒)長的查詢語句。

值得一提的是在文件記錄中允許使用“微秒”，而在表記錄中卻不行，因而這里的時間單位為“秒”。

通過全局系統變量配置慢查詢日志

除了上面提到的 [long_query_time] 全局系統變量外，還有一些其他變量用來確定 Slow Query Log(慢查詢日志)的行為狀態。

在默認情況下，Slow Query Log 是禁用的，若要啟用，則需要將 [slow_query_log] 系統變量值設置為 1。

此外“log_output”服務器系統變量決定了輸出是以什么形式被寫入的，這個變量值也可以設置為禁用。在默認情況下，日志允許被寫入文件，也可以寫入表。

[log_output] 服務器系統變量的有效取值為 [TABLE”，“FILE”或“NONE]。

該文件的默認名稱為 [host_name-slow.log]，也可以使用 [–slow_query_log_file = file_name] 選項進行設置，這里使用的表是 MySQL 系統數據庫中的 [slow_log] 表。

建議這些變量最好在“my.cnf”或“mariadb.cnf”配置文件中進行設置，這類文件通常存儲在 Linux 的“/ etc / mysql /”目錄。

如果是 Windows 系統，那么就存儲在 Windows 系統目錄(通常為 C：\ Windows\System)中。

在配置文件中做如下設置：

• 啟用慢查詢日志：slow_query_log = 1
• 以秒/微秒為單位設置定義慢查詢的時間：long_query_time = 5
• 提供慢速查詢日志文件的名稱：slow_query_log_file = /var/log/mysql/slow-query.log
• 需要記錄不使用索引的查詢語句：log_queries_not_using_indexes

以上設置在服務器重啟后生效。

查看 Slow Query Log(慢查詢日志)

已寫入文件的慢查詢日志可以通過任何文本編輯器打開進行查看，下面是一則慢查詢日志的示例內容：

 

通過文本編輯器來查看慢查詢日志看似非常方便，但隨著日志內容(數據量)的增長，很可能存在內容丟失的情況。

即顯示不完整，這是由于文本編輯器自身無法承載越來越大的日志容量，而造成日志中部分內容解析缺失的風險。

為了避免這類情況的發生，MariaDB 為我們提供了 mysqldumpslow 工具，該工具可以通過匯總信息來簡化過程，從而更可靠且有效地展示日志內容。

“mysqldumpslow”的可執行文件與 MariaDB 是捆綁在一起的，所以只需通過命令行將需要顯示的日志路徑傳遞給它即可。

從下面的 Demo 中可以獲悉，通過“mysqldumpslow”呈現的日志內容可讀性更強，并且還支持分組顯示。

 

“mysqldumpslow”命令可以通過指定不同的參數來定制化輸出格式，如下示例中將顯示按平均查詢時間排序的前 5 個查詢：

[ mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log ]  

slow_log 一覽表

如果你對上述 log 日志中顯示的內容不熟悉，可以結合 slow_log 表來幫助理解。

如下是日志中每個字段對應的詳情描述：

 

下圖所示是針對 slow_log 表的 SELECT ALL 示例結果：

 

還可以通過 slow_log 表來模擬 Linux 的“ tail -100 log-slow.log”命令，列出最新查詢記錄(最后 100 個查詢)，如下圖所示：

 

為了方便日后頻繁調用，我們也可以專門創建一個存儲過程(如SHOW_LATEST_SLOW_QUERIES)，需要顯示的查詢個數可以通過輸入參數傳遞給這個存儲過程。

這樣一來當我們需要列出指定數量的查詢記錄時，就不需要每次都重復鍵入相同的 SELECT 語句了。

測試 Slow Query Log(慢查詢日志)

為了更有效地在生產環境中獲取我們想要的慢查詢日志信息，通常情況下，我們需要做一些設置，例如規定哪些查詢必須被寫入 Slow Query Log(慢查詢日志)。

正如上文中提到，在啟用日志記錄后，根據 log_output 變量值的設定，運行時間比 [long_query_time] 全局系統變量值長的那些查詢將記錄在 Slow Query Log(慢查詢日志)或 slow_log 表中。

除了指定 [long_query_time] 時間外，我們還可以通過 select 語句根據不同的需求指定相應的可變時間。

這個操作需要結合 sleep() 函數使用，該函數(根據傳入的 duration 參數值 N)會暫停當前查詢 N 秒，然后返回 0， 如果 sleep() 函數被中斷，則返回 1。

如下所示，假設尚未指定 [long_query_time] 全局系統變量的值，那么默認值為 10 秒。

因此，[SELECT SLEEP(11);] 這條 select 語句會被記錄到慢日志中：

  

通過 Performance Schema 進行查詢分析

我們可以通過另一種服務器性能工具 Performance Schema 來監視服務器性能。

Performance Schema 是 MariaDB 5.5 中被引入的，以存儲引擎的方式實現;因此，在 MariaDB 的存儲引擎列表中可以找到 Performance Schema。

 

圖中的“Performance Schema”的功能默認情況下是禁用的，我們可以通過如下設置逐一開啟：

①在 my.cnf 或 my.ini 文件的 [mysqld] 部分中添加以下行：

performance_schema = on 

需要注意的是，“performance schema”無法在運行時被激活，它必須在服務器啟動時通過配置文件進行設置。

Performance Schema 存儲引擎包含一個名為 performance_schema 的數據庫，該數據庫又由許多表組成，可以使用常規 SQL 語句查詢這些表以獲取各種性能信息。

②消費者數據設置

為了收集數據，我們需要對收集哪些消費者觸發的數據進行設置，這些設置可以在服務器啟動時或在運行時進行。

通過以下語句在運行時對所需數據進行設置并檢測：

UPDATE performance_schema.setup_consumers  
SET ENABLED = 'YES';  
UPDATE performance_schema.setup_instruments  
SET ENABLED = ‘YES’, TIMED = ‘YES’;  

通過 WHERE NAME 啟用/禁用對應的查詢語句，通過將 ENABLED 設置為“ NO”來禁用檢測。

以下將啟用配置文件中所有階段的所有檢測：

 

通過更新 setup_instruments 表，確保啟用了語句和階段檢測：

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%'; 
 
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%stage/%'; 

啟用 events_statements_ * 和 events_stages_ * 使用者：

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements_%'; 
 
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_stages_%'; 

在縮小了感興趣的范圍后，有兩種方法可以進行監控：

• 在摘要視圖中查看原始數據，從而全面了解實例的用法。
• 快照數據，并計算隨時間變化的增量，進而了解事件的變化率。

下面我們以查看原始摘要數據為例：

運行要分析的語句：

 

通過查詢“events_statements_history_long”這張表，來標識語句的 EVENT_ID。此步驟類似于運行 SHOW PROFILES 來標識 Query_ID。

以下查詢將產生類似于 SHOW PROFILES 的輸出：

 

查詢“events_stages_history_long”這張表，來檢索語句的階段事件。階段使用事件嵌套鏈接到語句。

每個階段事件記錄都有一個 NESTING_EVENT_ID 列，其中包含父語句的 EVENT_ID。

 

Monyog 監控工具

現在我們已經知道 Slow Query Log(慢查詢日志)專門用于記錄那些運行時間過久且被認為存在問題的查詢語句，即運行時間超過 long_query_time 全局系統變量值的查詢語句。

而 Performance Schema 是一個存儲引擎，可用于在摘要視圖中查看原始數據以及隨時間推移的過程中涉及到的性能狀態。

這兩種工具都有其自身的長處。例如，Slow Query Log(慢查詢日志)易于使用，且可以通過任何文本編輯器進行查看。

Performance Schema 允許我們使用常規的 SQL 語句對其一系列表進行查詢，以獲取各類性能信息。

然而，此二者都不可避免會產生大量的數據信息，從而導致冗余且加重各自數據處理的負擔。

慶幸的是，Monyog 監控工具可以有效地為我們緩解這個問題，不僅如此，作為專業的監控工具，它還能給我們帶來巨大的價值。

Monyog 是一款優秀的 MySQL 監控工具，可以實時監測 MySQL 服務器，查看 MySQL 服務器的運行狀態，支持查詢分析功能，還可以幫助用戶掌握服務器的運行狀態，查看在任意時間點繪制的具有詳細查詢信息的圖表。

Monyog 不僅是實時監視工具，還具有 RDS OS 和基于文件的日志監視功能，包括在單個視圖中的常規查詢，慢速查詢和錯誤日志，還能通過 CloudWatch API 查看 RDS OS 指標，例如 CPU 利用率，RAM 利用率等。

由于在 MariaDB 中，默認情況下禁用慢查詢日志。必須將 slow_query_log 全局系統變量設置為 1 來啟用它。

此外還有一些其他系統變量的應用設置，如：

• 以秒/微秒為單位設置定義慢查詢的時間
• 是否寫入文件或表
• 用于提供慢速查詢日志文件的名稱
• 記錄那些不使用索引的查詢

在 Monyog 中的 Slow Query Log(慢查詢日志)設置，可以直接通過“Server Settings dialog”對話框的“ADVANCED ”選項卡配置上述所有設置。

步驟如下：

• 點擊服務器圖標
• 點擊服務器摘要框上的省略號
• 彈出“編輯服務器”窗口
• 點擊 ADVANCED
• 點擊 MySQL 查詢日志項

MySQL 查詢日志項的 ADVANCED 選項卡包含常規查詢，慢速查詢和錯誤日志的設置。

該 Server Settings dialog (服務器設置對話框)可以讓我們把 Slow Query Log(慢查詢日志)的設置應用到當前服務器，或與當前服務器擁有相同標簽的其余服務器。

最后單擊“Save”按鈕關閉對話框，并保存 Slow Query Log(慢查詢日志)設置。

 

Monyog 監控圖表

①Dashboard Metrics

DBA 通過 Dashboard 顯示的一組圖表，就可以輕松了解所有 MySQL 服務器的安全性，可用性以及性能狀況。

Monyog 自帶默認的 Dashboard“Performance metrics”性能指標，DBA 也可以為一個或多個服務器指定數據庫和操作系統指標，創建一組自己的專屬圖表。

例如查詢性能指標中包含“Queries Executed”，“ Statements”和“Query Cache Efficiency”。

Dashboard 上顯示的所有圖表均可以 PDF/JPG/PNG 格式導出：

 

②查看 MySQL 日志詳細信息

Monyog Monitors 頁面顯示服務器參數和指標的詳細顯示。

單擊“ MONITOR GROUP”下的“MySQL Logs”項，將會顯示被監控下的服務器對應的“常規查詢”，“慢速查詢”(紅色框標注)和錯誤日志的詳細信息。

Slow Query 慢查詢信息包括：

• 慢日志——是否已啟用?(是/否)
• 最慢查詢執行時間，以秒為單位
• 慢查詢的數
• 是否記錄那些不使用索引的查詢?(是/否)

 

③趨勢值圖

與原始數據相比，利用圖表顯示大量數據以及數據間不同部分的相關性，更加簡潔明了，易于理解，可讀性也更強。

趨勢值圖就是這樣一類圖表，用于顯示一段時間內數據的變化趨勢。由于數據的波動，單點測量可能會不準確，產生誤差。

因而，隨著時間推移來呈現數據的趨勢走向，可以使我們更有效地獲取實際性能，有針對性地基于已建立的目標監控實際性能狀況。

下圖是某主服務器趨勢圖示例：

 

上圖中“SERVERS”圖例列出了 SQL 日志中的所有服務器。每個服務器圖例都有各自的顏色，以便在圖表中能輕松識別。

由于當前圖中只顯示了主服務器趨勢數據，其余對應服務器的趨勢值均未出現在圖中，所以呈灰色。通過單擊服務器圖例，可以隨意切換需要顯示的服務器數據趨勢。

④顯示特定時間范圍內的趨勢值

在上面的趨勢圖中，僅僅顯示了時間段內某服務器上所有的趨勢數據。

在 Monyog Professional，Enterprise 和 Ultimate 版本中，我們還可以通過 TIMEFRAME 下拉列表中的“History”選項來指定特定時間的范圍。

可選擇的時間范圍包含多個時間間隔，例如“Today”，“Yesterday”和“Last 2 Days” ，也可以自定義范圍，設置開始和結束字段;單擊任何一個自定義范圍字段都會顯示日歷，用于選擇確切的日期時間。

 

下圖所示基于特定時間范圍內的各服務器慢查詢數量趨勢值：

 

⑤查詢分析器

在“查詢分析器”選項卡中，選擇所需的 MySQL 服務器以及要分析的日志類型(包括慢查詢日志)，單擊分析按鈕開始分析。

 

幾秒鐘后，將顯示如下分析結果，頁面上半部分包含基于總時間的“熱門查詢”，而下半部分顯示了使用結果分頁的所有查詢：

 

基于總時間的“熱門查詢”部分顯示排名靠前的查詢，以便最慢的查詢可以在頂部顯示。

包括：

• 查詢語句。
• COUNT：該語句在日志中出現多少次。
• 總時間：執行查詢所需的時間，格式為 hh：mm：ss：ms。
• 平均延遲：平均查詢執行時間，格式為 hh：mm：ss：ms。
• USER @ HOST：執行查詢的用戶及主機。

每條語句在查詢數據最上方以條形圖的形式顯示，因此每個查詢都對應唯一的顏色。

每個查詢的總執行時間按照從左到右的方式顯示，最慢的顯示在最左邊。條形圖有助于快速評估每個慢查詢語句間的對比。

在上圖中，我們可以看到最慢的查詢比所有其他慢查詢時間的總和慢了好幾個數量級。

單擊某一行將顯示對應慢查詢的詳細信息，例如查詢首次和最后一次執行的時間點，查詢所花費的最大時間：

 

⑥Query 查詢面板中的過濾設置

“查詢”部分為我們提供了更完整的已分析查詢列表，除了通過分頁導航遍歷所有查詢外，還可以自定義過濾條件，從而將顯示列表的內容縮小到我們感興趣的范圍。

過濾條件共有以下四種選項：

• Containing：包含。
• Not containing：不包含。
• Matching regex：匹配的正則表達式。
• Not matching regex：不匹配的正則表達式。 

例如，將結果限制為匹配正則表達式“ sakila *”語句的過濾器：

 

通過單擊標題，按任意列進行排序，箭頭顯示排序順序(即升序，降序)：

通過單擊標題，按任意列進行排序，箭頭顯示排序順序(即升序，降序)：

 

⑦以 CSV 形式導出

單擊 Query 面板上的“Export as CSV”可以將查詢數據保存到“.csv”文件中：

 

保存后的 CSV 文件可以通過 EXCEL 打開預覽：

 

總結

查詢分析是用于分析數據庫整體性能的有效技術，文中該技術采用了 MariaDB 服務器內置工具：Slow Query Log 慢查詢日志和 Performance Schema 性能模式。

慢查詢日志通過設置 long_query_time 全局系統變量值，來跟蹤記錄運行超時且存在問題的查詢語句。

性能模式則是一個存儲引擎，其中 Performance_schema 數據庫，又由多個表組成，我們可以使用常規 SQL 語句查詢這些表以獲取更廣泛的性能信息。

然而以上這兩種工具都會產生大量的數據，引起繁瑣的工作，Monyog 的引入為我們很好地解決此類問題，利用 Monyog 來監視 MariaDB 慢查詢日志和性能模式是最有效的方法之一。

作者：羅小羅

簡介：英國 TOP10 計算機專業，計算機科學與技術碩士，先后就職于匯豐，JPMorgan，HP，交行，阿里等國內外知名企業。涉及項目領域主要有：互聯網金融，電商，教育，醫療等。現任就職于某世界 500 強公司，擔任測試開發團隊負責人，帶領團隊構建并持續優化自動化測試框架，研發自動化測試輔助類工具;擅長領域：單元/接口/性能/安全/自動化測試/CD/CI/DevOps;個人持續研究領域：自動化測試模型/數據分析/算法/機器學習等。

編輯：陶家龍

征稿：有投稿、尋求報道意向技術人請聯絡小編微信：gordonlonglong

【51CTO原創稿件，合作站點轉載請注明原文作者和出處為51CTO.com】