在資料庫效能優化過程中，通常會遇到多個查詢可能需要調整，但如果只能選擇一個來優化，最有效的方式是聚焦於消耗最多執行時間的查詢。MySQL 內建的 sys 資料庫提供了一些強大的工具，讓開發者能夠快速識別並分析高延遲的查詢，從而進行有效的優化。
1. 為何聚焦單一查詢？
在大量查詢操作中，通常有少數查詢會佔用大部分的資源。這些查詢可能因為執行次數多或單次執行時間過長而成為資料庫的效能瓶頸。因此，針對這些高延遲查詢進行優化，往往能夠顯著提升整體系統效能。
2. 使用 sys 資料庫尋找高延遲查詢
MySQL 的 sys 資料庫包含了許多用來分析和優化查詢的視圖。這裡我們可以使用一個查詢來獲取那些耗時最長的查詢，並對它們進行分析：
SELECT
schema_name,
format_pico_time(total_latency) AS tot_lat,
exec_count,
format_pico_time(total_latency/exec_count) AS latency_per_call,
query_sample_text
FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2
ON t2.digest=t1.digest
WHERE schema_name NOT IN ('performance_schema', 'sys')
ORDER BY (total_latency/exec_count) DESC
LIMIT 1\G;查詢解釋
- schema_name：顯示該查詢所屬的資料庫名稱。
- tot_lat：總延遲時間，表示該查詢在資料庫中消耗的所有時間。
- exec_count：該查詢的執行次數。
- latency_per_call：每次執行的平均延遲時間，這是一個關鍵指標，用來判斷單次執行的成本。
- query_sample_text：查詢的樣本文本，幫助開發者識別具體的 SQL 語句。
通過這個查詢，我們可以迅速找到那些最耗時的查詢，並將其優先作為優化對象。
3. 聚焦優化的好處
在系統運行中，經常會有少數的查詢佔據大量的系統資源，這些查詢往往成為系統效能的瓶頸。透過 sys 資料庫的這些工具，開發者能夠將注意力集中在這些高耗能的查詢上，並有針對性地進行優化。這樣不僅能夠提升整體效能，還能避免不必要的系統資源浪費。
4. 優化策略
當識別出高延遲查詢後，接下來的優化步驟可能包括：
- 索引優化：檢查查詢中涉及的表是否有合適的索引。缺少索引可能導致全表掃描，進而大幅增加查詢延遲。
- 查詢重寫：有時，通過重新設計 SQL 查詢，可以減少資料檢索量或避免不必要的計算，從而提升效能。
- 分區表：如果查詢涉及大量歷史數據，可以考慮將表進行分區，減少單次查詢所需處理的數據量。
結論
MySQL 的 sys 資料庫提供了強大的查詢分析工具，開發者可以通過這些工具快速定位最具效能瓶頸的查詢。即便在資源有限的情況下，聚焦於優化最耗時的查詢，通常也能為整體系統效能帶來顯著提升。
按讚的人：
共有 0 則留言
文字內容提供幾種功能：
1) --- 會變成分隔線（上一行必須是空白）
2) # 會變成一級標題
3) ## 會變成二級標題
4) ### 會變成三級標題
5) **粗體文字**會顯示粗體文字
6) ```當第一行與最後一行會顯示程式碼
7) 請搜尋 Markdown 語法，了解各種格式
1) --- 會變成分隔線（上一行必須是空白）
2) # 會變成一級標題
3) ## 會變成二級標題
4) ### 會變成三級標題
5) **粗體文字**會顯示粗體文字
6) ```當第一行與最後一行會顯示程式碼
7) 請搜尋 Markdown 語法，了解各種格式
大家好，我是Hank，一名全端工程師（Full Stack Engineer），專注於網路應用程式技術，包括但不限於APP、網站及LINE官方帳號的應用。過去12年都在新創公司擔任技術長與合夥人，目前是一位自由接案者，希望拓展更多可能性。同時我也是一個樂團的鼓手，擅長打爵士鼓，如果有玩音樂的朋友，歡迎互相交流。謝謝大家！
熱門文章