當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

两个sql交集_如何使用性能分析工具定位SQL执行慢的原因？

發布時間：2023/12/20 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了两个sql交集_如何使用性能分析工具定位SQL执行慢的原因？小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

在前面的文章中就講過了查詢優化器，知道在查詢優化器中會經歷邏輯查詢優化和物理查詢優化。需要注意的是，查詢優化器只能在已經確定的情況下(SQL 語句、索引設計、緩沖池大小、查詢優化器參數等)決定最優的查詢執行計劃。

但實際上 SQL 執行起來可能還是很慢，那么到底從哪里定位 SQL 查詢慢的問題呢？是索引設計的問題？服務器參數配置的問題？還是需要增加緩存的問題呢？今天我們就從性能分析來入手，定位導致 SQL 執行慢的原因。

今天的內容主要包括以下幾個部分：

數據庫服務器的優化分析的步驟是怎樣的？中間有哪些需要注意的地方？

如何使用慢查詢日志查找執行慢的 SQL 語句？ 3.如何使用 EXPLAIN 查看 SQL 執行計劃？ 4.如何使用 SHOW PROFILING 分析 SQL 執行步驟中的每一步的執行時間？

一、數據庫服務器的優化步驟

當我們遇到數據庫調優問題的時候，該如何思考呢？我把思考的流程整理成了下面這張圖。

整個流程劃分成了觀察(Show status)和行動(Action)兩個部分。字母 S 的部分代表觀察(會使用相應的分析工具)，字母 A 代表的部分是行動(對應分析可以采取的行動)。

我們可以通過觀察了解數據庫整體的運行狀態，通過性能分析工具可以讓我們了解執行慢的SQL 都有哪些，查看具體的 SQL 執行計劃，甚至是 SQL 執行中的每一步的成本代價，這樣才能定位問題所在，找到了問題，再采取相應的行動。

我來詳細解釋一下這張圖。

首先在 S1 部分，我們需要觀察服務器的狀態是否存在周期性的波動。如果存在周期性波動，有可能是周期性節點的原因，比如雙十一、促銷活動等。這樣的話，我們可以通過 A1這一步驟解決，也就是加緩存，或者更改緩存失效策略。

如果緩存策略沒有解決，或者不是周期性波動的原因，我們就需要進一步分析查詢延遲和卡頓的原因。接下來進入 S2 這一步，我們需要開啟慢查詢。慢查詢可以幫我們定位執行慢的SQL 語句。我們可以通過設置 long_query_time 參數定義“慢”的閾值，如果 SQL 執行時間超過了 long_query_time，則會認為是慢查詢。當收集上來這些慢查詢之后，我們就可以通過分析工具對慢查詢日志進行分析。

在 S3 這一步驟中，我們就知道了執行慢的 SQL，這樣就可以針對性地用 EXPLAIN 查看對應 SQL 語句的執行計劃，或者使用 show profile 查看 SQL 中每一個步驟的時間成本。這樣我們就可以了解 SQL 查詢慢是因為執行時間長，還是等待時間長。

如果是 SQL 等待時間長，我們進入 A2 步驟。在這一步驟中，我們可以調優服務器的參數，比如適當增加數據庫緩沖池等。如果是 SQL 執行時間長，就進入 A3 步驟，這一步中我們需要考慮是索引設計的問題？還是查詢關聯的數據表過多？還是因為數據表的字段設計問題導致了這一現象。然后在這些維度上進行對應的調整。

如果 A2 和 A3 都不能解決問題，我們需要考慮數據庫自身的 SQL 查詢性能是否已經達到了瓶頸，如果確認沒有達到性能瓶頸，就需要重新檢查，重復以上的步驟。如果已經達到了性能瓶頸，進入 A4 階段，需要考慮增加服務器，采用讀寫分離的架構，或者考慮對數據庫進行分庫分表，比如垂直分庫、垂直分表和水平分表等。

以上就是數據庫調優的流程思路。如果我們發現執行 SQL 時存在不規則延遲或卡頓的時候，就可以采用分析工具幫我們定位有問題的 SQL，這三種分析工具你可以理解是 SQL 調優的三個步驟：慢查詢、EXPLAIN 和 SHOW PROFILING。

二、使用慢查詢定位執行慢的SQL

好慢詢可以幫我們找到執行慢的 SQL，在使用前，我們需要先看下慢查詢是否已經開啟，使用下面這條命令即可：

mysql > show variables like '%slow_query_log';

我們能看到 slow_query_log=OFF，也就是說慢查詢日志此時是關上的。我們可以把慢查詢日志打開，注意設置變量值的時候需要使用 global，否則會報錯：

mysql > set global slow_query_log='ON';

然后我們再來查看下慢查詢日志是否開啟，以及慢查詢日志文件的位置：

你能看到這時慢查詢分析已經開啟，同時文件保存在 DESKTOP-4BK02RP-slow 文件中。

接下來我們來看下慢查詢的時間閾值設置，使用如下命令：

mysql > show variables like '%long_query_time%';

這里如果我們想把時間縮短，比如設置為 3 秒，可以這樣設置：

mysql > set global long_query_time = 3;

我們可以使用 MySQL 自帶的 mysqldumpslow 工具統計慢查詢日志(這個工具是個 Perl腳本，你需要先安裝好 Perl)。

mysqldumpslow 命令的具體參數如下：

-s：采用 order 排序的方式，排序方式可以有以下幾種。分別是 c(訪問次數)、t(查詢時間)、l(鎖定時間)、r(返回記錄)、ac(平均查詢次數)、al(平均鎖定時間)、ar(平均返回記錄數)和 at(平均查詢時間)。其中 at 為默認排序方式。
-t：返回前 N 條數據。
-g：后面可以是正則表達式，對大小寫不敏感。

比如我們想要按照查詢時間排序，查看前兩條 SQL 語句，這樣寫即可：

perl mysqldumpslow.pl -s t -t 2 "C:ProgramDataMySQLMySQL Server 8.0DataDESKTOP-4BK0

你能看到開啟了慢查詢日志，并設置了相應的慢查詢時間閾值之后，只要大于這個閾值的SQL 語句都會保存在慢查詢日志中，然后我們就可以通過 mysqldumpslow 工具提取想要查找的 SQL 語句了。

三、如何使用EXPLAIN查看執行計劃

定位了查詢慢的 SQL 之后，我們就可以使用 EXPLAIN 工具做針對性的分析，比如我們想要了解 product_comment 和 user 表進行聯查的時候所采用的的執行計劃，可以使用下面這條語句：

EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name

EXPLAIN 可以幫助我們了解數據表的讀取順序、SELECT 子句的類型、數據表的訪問類型、可使用的索引、實際使用的索引、使用的索引長度、上一個表的連接匹配條件、被優化器查詢的行的數量以及額外的信息(比如是否使用了外部排序，是否使用了臨時表等)等。

SQL 執行的順序是根據 id 從大到小執行的，也就是 id 越大越先執行，當 id 相同時，從上到下執行。

數據表的訪問類型所對應的 type 列是我們比較關注的信息。type 可能有以下幾種情況：

在這些情況里，all 是最壞的情況，因為采用了全表掃描的方式。index 和 all 差不多，只不過 index 對索引表進行全掃描，這樣做的好處是不再需要對數據進行排序，但是開銷依然很大。如果我們在 extra 列中看到 Using index，說明采用了索引覆蓋，也就是索引可以覆蓋所需的 SELECT 字段，就不需要進行回表，這樣就減少了數據查找的開銷。

比如我們對 product_comment 數據表進行查詢，設計了聯合索引 composite_index(user_id, comment_text)，然后對數據表中的 comment_id、comment_text、user_id這三個字段進行查詢，最后用 EXPLAIN 看下執行計劃：

EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment

你能看到這里的訪問方式采用了 index 的方式，key 列采用了聯合索引，進行掃描。Extral列為 Using index，告訴我們索引可以覆蓋 SELECT 中的字段，也就不需要回表查詢了。

range 表示采用了索引范圍掃描，這里不進行舉例，從這一級別開始，索引的作用會越來越明顯，因此我們需要盡量讓 SQL 查詢可以使用到 range 這一級別及以上的 type 訪問方式。

index_merge 說明查詢同時使用了兩個或以上的索引，最后取了交集或者并集。比如想要對 comment_id=500000 或者 user_id=500000 的數據進行查詢，數據表中comment_id 為主鍵，user_id 是普通索引，我們可以查看下執行計劃：

EXPLAIN SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE

你能看到這里同時使用到了兩個索引，分別是主鍵和 user_id，采用的數據表訪問類型是index_merge，通過 union 的方式對兩個索引檢索的數據進行合并。

ref 類型表示采用了非唯一索引，或者是唯一索引的非唯一性前綴。比如我們想要對user_id=500000 的評論進行查詢，使用 EXPLAIN 查看執行計劃：

EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE user_id = 50

這里 user_id 為普通索引(因為 user_id 在商品評論表中可能是重復的)，因此采用的訪問類型是 ref，同時在 ref 列中顯示 const，表示連接匹配條件是常量，用于索引列的查找。

eq_ref 類型是使用主鍵或唯一索引時產生的訪問方式，通常使用在多表聯查中。假設我們對 product_comment 表和 usre 表進行聯查，關聯條件是兩張表的 user_id 相等，使用EXPLAIN 進行執行計劃查看：

EXPLAIN SELECT * FROM product_comment JOIN user WHERE product_comment.user_id = user.use

const 類型表示我們使用了主鍵或者唯一索引(所有的部分)與常量值進行比較，比如我們想要查看 comment_id=500000，查看執行計劃：

EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE comment_id =

需要說明的是 const 類型和 eq_ref 都使用了主鍵或唯一索引，不過這兩個類型有所區別，const 是與常量進行比較，查詢效率會更快，而 eq_ref 通常用于多表聯查中。

system 類型一般用于 MyISAM 或 Memory 表，屬于 const 類型的特例，當表只有一行時連接類型為 system。我們查看下執行計劃：

EXPLAIN SELECT * FROM test_myisam

你能看到除了 all 類型外，其他類型都可以使用到索引，但是不同的連接方式的效率也會有所不同，效率從低到高依次為 all < index < range < index_merge < ref < eq_ref

四、使用SHOW PROFILE查看SQL的具體執行成本

SHOW PROFILE 相比 EXPLAIN 能看到更進一步的執行解析，包括 SQL 都做了什么、所花費的時間等。默認情況下，profiling 是關閉的，我們可以在會話級別開啟這個功能。

mysql > show variables like 'profiling';

通過設置 profiling='ON’來開啟 show profile：

mysql > set profiling = 'ON';

我們可以看下當前會話都有哪些 profiles，使用下面這條命令：

mysql > show profiles;

你能看到當前會話一共有 2 個查詢，如果我們想要查看上一個查詢的開銷，可以使用：

mysql > show profile;

我們也可以查看指定的 Query ID 的開銷，比如 show profile for query 2 查詢結果是一樣的。在 SHOW PROFILE 中我們可以查看不同部分的開銷，比如 cpu、block.io 等：

通過上面的結果，我們可以弄清楚每一步驟的耗時，以及在不同部分，比如 CPU、block.io 的執行時間，這樣我們就可以判斷出來 SQL 到底慢在哪里。

不過 SHOW PROFILE 命令將被棄用，我們可以從 information_schema 中的 profiling 數據表進行查看。

五、總結

本文梳理了 SQL 優化的思路，從步驟上看，我們需要先進行觀察和分析，分析工具的使用在日常工作中還是很重要的。今天只介紹了常用的三種分析工具，實際上可以使用的分析工具還有很多。

在這里總結一下今天文章里提到的三種分析工具。我們可以通過慢查詢日志定位執行慢的SQL，然后通過 EXPLAIN 分析該 SQL 語句是否使用到了索引，以及具體的數據表訪問方式是怎樣的。我們也可以使用 SHOW PROFILE 進一步了解 SQL 每一步的執行時間，包括I/O 和 CPU 等資源的使用情況。