mysql 資料庫效能分析工具簡介

劣技砖猿發表於2024-04-17
MySql資料庫

分析 sql 效能整體步驟

  1. 觀察伺服器狀態(系統效能)，看是否存在週期性波動。
  2. 如果存在波動，則可以增加快取或修改快取失效策略。
  3. 如果沒有周期性波動，而是不規則的延遲或卡頓，又或者是新增了快取或修改了快取失效策略之後問題仍然未解決，我們就可以開啟慢查詢功能，透過慢查詢日誌找到執行較慢的 sql 語句。
  4. 透過 explain 檢視對於 sql 的執行計劃，或透過 show profiling 檢視 sql 語句執行過程中每一步的時間成本。
  5. 如果是 sql 等待的時間過長，則可以調整伺服器引數。
  6. 如果是執行時間較久，則可以觀察索引是否需要最佳化、sql 語句是否需要最佳化、資料表設計是否需要最佳化。
  7. 如果透過最佳化後問題仍然沒有得到解決，則需要增加伺服器數量進行讀寫分離或分庫分表，來分散伺服器的壓力。

檢視系統效能引數

在 mysql 中，可以使用以下語句檢視資料庫伺服器的效能引數、執行頻率:

-- 如果不明確指定 global 或 session 則會先在 session 中查詢，沒查詢到才會在 gobal 中查詢
show [global | session] status like '引數'

一些常用的引數如下：

  • Connection：連線 mysql 伺服器的次數
  • Uptime：mysql 伺服器的上線時間
  • Slow_queries：慢查詢的次數
  • Last_query_cost：查詢最後一次執行的 sql 語句的所花費的資料頁成本

sql 查詢時一個動態的過程，從頁載入的角度來看，我們可以得到兩點結論：
1.位置決定效率(如果頁就在資料庫緩衝池中，那麼效率時最高的，否則還需要從記憶體或磁碟中進行讀取，當然針對單個頁的讀取來說，如果頁存在記憶體中會比在磁碟中讀取效率高很多)。
2.批次決定效率(如果我們從磁碟中對單一頁進行隨機讀取，那麼效率很低，而採用順序讀取的方式批次對頁進行讀取，平均一頁的讀取效率就會提升很多，甚至要快於單個頁面在記憶體中的隨機讀取)。
所以說，遇到 I/O 並不用擔心，方法找對了，效率還是很高的。我們首先要考慮資料存放的位置，如果是經常使用的資料就要儘量放到緩衝池中，其次我們可以充分利用磁碟的吞吐能力，一次性批次讀取資料，這樣單個頁的讀取效率也就得到了提升。

  • Innodb_rows_read：Select 查詢返回的行數
  • Innodb_rows_inserted：執行 Insert 操作插入的行數
  • Innodb_rows_updated：執行 update 操作更新的行數
  • Innodb_rows_deleted：執行 delete 操作刪除的行數
  • Com_select：查詢操作的次數
  • Com_insert：插入操作的次數(對於批次插入的 insert 操作，只累加一次)
  • Com_update：更新操作的次數
  • Com_delete：刪除操作的次數

定位執行慢的 sql 語句：慢查詢日誌

開啟慢查詢日誌功能

預設情況下，mysql 資料庫沒有開啟慢查詢日誌，需要我們手動來設定這個引數。如果不是調優的話，一般不建議啟動該引數，因為開啟慢查詢日誌會對效能有所影響。慢查詢日誌支援將日誌寫入檔案中。
可以透過以下命令來查詢/開啟慢查詢日誌相關功能

-- 查詢慢查詢日誌是否開啟
show variables like 'slow_query_log';
-- 開啟慢查詢功能
set global slow_query_log = on;
-- 查詢慢查詢時間閾值(單位：秒)
show variables like 'long_query_time';
-- 設定慢查詢時間閾值
set [global | session] long_query_time = 8;
-- 查詢慢查詢日誌檔案所在目錄
show variables like 'slow_query_log_file';
-- 關閉慢查詢功能
set global slow_query_log = off;

除了上述變數，控制慢查詢日誌的還有一個系統變數:min_examined_row_limit。這個變數的意思是查詢掃描過的最少記錄數。這個變數和查詢實現時間共同組成了判別一個查詢是否是慢查詢的條件。如果查詢掃描過的記錄數大於等於這個變數的值，並且執行時間超過 long_query_time 的值，那麼這個查詢就會被記錄到慢查詢日誌中，反之咋不被記錄到慢查詢日誌中。
這個預設值是 0，與 long_query_time=8 結合在一起，表示只要查詢的執行時間超過 8 秒鐘，哪怕一個記錄也沒有掃描過，都要被記錄到慢查詢日誌中。可以根據需要用 set 語句修改 min_examined_row_limit 的值。

使用慢查詢日誌分析工具：mysqldumpslow

mysqldumpslow 工具在 mysql 安裝目錄的 bin 目錄下，可以使用命令mysqldumpslow -help檢視該工具的使用方法。
比如我們用mysqldumpslow -s t -t 5 /var/lib/mysql/liejizhuanyuan-slow.log來查詢最近5條觸發慢查詢的 sql 語句。
image

檢視 sql 執行成本：show profile

show profile 是 mysql 提供的可以用來分析當前會話中 sql 都做了什麼、執行的資源消耗情況的工具，可用於 sql 調優的測量。預設情況下處於關閉狀態。相關命令如下

-- 檢視 show profile 功能是否開啟
show variables like 'profiling';
-- 開啟/關閉 show profile
set profiling = 'on | off';
-- 檢視最近執行的查詢 sql 資訊
show profiles;
-- 檢視最後一次查詢 sql 的執行成本
show profile [查詢引數];
-- 檢視某一次的查詢 sql 的執行成本
show profile [查詢引數] for query 查詢id;

show profile常用的查詢引數:

  • all:顯示所有開銷資訊
  • block io:顯示塊 IO 開銷
  • context switches：上下文切換開銷
  • cpu：顯示 CPU 開銷資訊
  • memory：顯示記憶體開銷資訊
  • page faults：顯示也沒錯誤開銷資訊
  • source：顯示 source_function、source_file、source_line相關的開銷資訊
  • swaps：顯示交換冊數開銷資訊

分析語句執行計劃：explain

定位了查詢慢的 sql 之後，我們就可以使用 explain 或 describe 工具檢視語句的執行計劃(檢視標的讀取順序、資料讀取操作的操作型別、哪些索引可以使用、哪些索引被實際使用、表之間的引用、每張表有多少行被最佳化查詢)，從而做針對性的分析。describe 語句的使用方法與 explain 語句是一樣的。
使用方法就是在需要分析的 sql 語句前加上 explain 或 describe 關鍵字即可。

-- explain 語法示例
explain [format=JSON | TREE] select * from demo_table where id=1;

mysql 中有專門負責最佳化 select 語句的最佳化器模組，主要功能：透過計算分析系統中收集到的統計資訊，為客戶端請求的 Query 提供它認為最優的執行計劃(它認為最優的資料檢索方式不一定是 DBA 認為最優的，這部分最耗費時間)。

mysql 5.6.3 以前 explain 只能分析 select 語句，5.6.3 之後就可以分析 select、update、delete
explain 語句輸出的各個列的含義如下：

  • id：
  • select_type：select 關鍵字對於的查詢型別
  • table：表名
  • partitions：匹配的分割槽資訊，代表分割槽表的命中情況，非分割槽表該項為 NULL。
  • type：針對單表的訪問方法，常見值如下(從好到壞)：
    • system：當表中只有一條記錄並且該表使用的儲存引擎的統計資料時精確的(如：MyISAM、Memory)，那麼對該表的訪問方法就是 system
    • const：當我們根據主鍵或唯一索引列與常數進行等值匹配時，對單表的訪問方法就是 const
    • eq_ref：在連線查詢時，如果被驅動表是透過主鍵或唯一二級索引等值匹配的方式進行訪問的(如果該主鍵或唯一二級索引時聯合索引的話，所有的索引列都必須進行等值比較)，則對該被驅動表的訪問方法就是 eq_ref。
    • ref：當透過普通二級索引與常量進行等值匹配時來查詢某個表，那麼該表的訪問方法就是 ref。
    • ref_or_null：當對普通二級索引進行等值匹配查詢，該索引列的值也可以時 null 值時，那麼對該表的訪問方法就可能是 ref_or_null。
    • index_merge：單表訪問方法時在某些場景下可以使用 Intersection、Union、Sort-Union 這三種索引合併的方式來執行，那麼對該表的訪問方法就是 index_merge。
    • unique_subquery：針對在一些包含 in 子查詢的查詢語句中，如果查詢最佳化器決定將 in 子查詢轉換為 exists 子查詢，且子查詢可以使用到主鍵進行等值匹配的話，那麼該子查詢執行計劃的型別就是 unique_subquery。
    • range：如果使用索引獲取某些範圍區間的記錄，那麼就可能使用到 range 訪問方法。
    • index：當我們可以使用索引覆蓋，但需要掃描圈閉的索引記錄時，該表的訪問方法就是 index。
    • ALL：全表掃描

sql 效能最佳化的目標至少要達到 range 級別

  • possible_keys：可能用到的索引
  • key：實際用到的索引
  • key_len：實際使用到的索引長度(即位元組數)，主要針對於聯合索引有一定參考意義。
  • ref：當使用索引列等值查詢時，與索引列進行等值匹配的物件資訊。
  • rows：預估的需要讀取的記錄條數，該數值越小越好
  • filtered：某個經過搜尋條件過濾後剩餘記錄數的百分比，該數值越大越好
  • extra：額外資訊，包含不適合在其他列中顯示但十分重要的資訊。我們可以透過這些資訊來更準確的理解 mysql 到底將如何執行給定的查詢語句。

mysql 5.7 以前的版本總，要想顯示 partitions 需要使用 explain partitions 命令，想要顯示 filtered 需要使用 explain extended 命令。在 5.7 版本後，預設顯示中就包含了 partitions 和 filtered 資訊。

注意：

  • explain 不考慮各種 Cache
  • explain 不能顯示 mysql 在執行查詢時最佳化器所做的工作
  • explain 不會告訴你關於觸發器、儲存過程的資訊或使用者自定義函式時對查詢的影響情況
  • 部分統計資訊時估算的，並非精確值

分析最佳化器執行計劃：trace

OPTIMIZER_TRACE是 mysql 5.6 引入的一項跟蹤功能，它可以跟蹤最佳化器做出的各種決策(如訪問表的方法、各種開銷計算、各種轉換等)，並將跟蹤結果記錄到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
此功能預設關閉，可以透過以下語句開啟/關閉：

-- 開啟 OPTIMIZER_TRACE 並設定格式為 json
SET [SESSION | GLOBAL] optimizer_trace="enabled=on,end_markers_in_json=on";
-- 設定 trace 最大能夠使用的記憶體大小，避免解析過程因預設記憶體太小而不能完整展示
set optimizer_trace_max_mem_size=1000000;
-- 開啟 OPTIMIZER_TRACE
SET [SESSION | GLOBAL] optimizer_trace="enabled=off";

開啟後，我們只需要查詢 INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道 mysql 時如何執行 sql 的。可分析的語句包括：select、insert、replace、update、delete、explain、set、declare、case、if、return、call

select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

記錄的資訊分為以下四部分：

  1. QUERY：查詢語句
  2. TRACE：QUERY 欄位對於語句的跟蹤資訊
  3. MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟蹤資訊過長時被截斷的跟蹤資訊的位元組數
  4. INSUFFICIENT_PRIVILEGES：執行跟蹤語句的使用者是否有檢視物件的許可權。當不具有許可權時，該列資訊為1且 TRACE 欄位為空，一般在帶有 SQL SECURITY DEFINER 的仕途或者是儲存過程的情況下會出現此問題。
    通常情況下，MySQL 預設設定下只會記錄最後一個被執行的 SQL 查詢的最佳化跟蹤資訊，這是因為 optimizer_trace_limit 引數預設設定為 1，表示僅儲存最近一次查詢的跟蹤資料。如果你想跟蹤並儲存更多查詢的最佳化資訊，需要修改此引數以及其他相關引數的設定。

mysql 高階效能監控和診斷工具：sys

sys 庫是在 MySQL 5.6 版本以後引入的，專門為 MySQL 資料庫管理員（DBA）和開發人員提供了一套高階效能監控和診斷工具。它透過組合和擴充套件 performance_schema 資料庫中的資訊，以更加友好的檢視形式呈現出來，幫助使用者更方便地瞭解和分析資料庫效能、資源使用情況以及查詢執行計劃等關鍵指標。sys 庫包含了一系列預定義的檢視，這些檢視簡化了對效能資料的訪問，使得使用者無需直接查詢複雜的 performance_schema 表就能獲取到有用的效能監控資訊。例如：

-- 查詢冗餘索引
select * from sys.schema_redundant_indexes;
-- 查詢未使用過的索引
select * from sys.schema_unused_indexes;
-- 查詢索引的使用情況
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='資料庫名';
-- 查詢表的訪問量
select table_schema,table_name,SUN(io_read_requests+io_write_requests) as io from sys.schma_table_statistics group by table_schema,table_name order by io desc;
-- 查詢佔用 bufferpool 較多的表
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
-- 檢視錶的全表掃描情況
select * from sys.statements_with_full_table_scans shere db='資料庫名';
-- 監控 sql 執行的頻率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;
-- 監控使用了排序的 sql
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;
-- 監控使用了臨時表或者磁碟臨時表的 sql
select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_table>0 order by (tmp_tables+tmp_disk_tables) desc;
-- 檢視消耗磁碟io的檔案
select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;
-- 行鎖阻塞情況
select * from sys.innodb_lock_waits;

風險提示：透過 sys 庫查詢時，MySQL 會消耗大量資源去收集相關資訊，嚴重的可能會導致業務請求被阻塞，從而引起故障。建議生產上不要頻繁去查詢 sys 或者 performance_schema、information_schema 來完成監控、巡檢等工作。

補充說明

當我們調優結束後，我們可以刪除相關的慢查詢日誌檔案來節約磁碟空間。
刪除慢查詢日誌後，需要使用命令 mysqladmin -u root -p flush-logs slow 來重置我們的慢查詢日誌檔案。

相關文章