MySQL 查詢分析

更多騰訊海量技術文章，請關注云加社區：https://cloud.tencent.com/developer

作者：謝慶玲

如今資料庫的操作越來越成為整個應用的性能瓶頸了。對於資料庫性能，並不僅僅是 DBA 關注的事情，也是開發和測試人員需要關注的事情。在設計資料庫表結構時，在操作資料庫時（特別是查詢 sql 語句），都需要我們考慮資料庫的性能問題。本文主要由一個案例引發對 MySQL 性能問題的思考，主要講述 MySQL 慢查詢和 explain 工具這兩個定位 MySQL 性能瓶頸的方法。索引和查詢優化部分後續將放在《MySQL 索引及查詢優化總結》中。

一個低效查詢引發的思考

上次在做銀行對賬，上傳對賬單後，出現對賬超時的情況。查看日誌發現，最後一條日誌記錄停在了對 c2c_zwdb.t_file_count 的查詢 sql 上。使用 show processlist 命令來查看當前 SQL 的執行情況，如下：

由上圖可知，原來是發生鎖表了 waiting for table level lock。

引發鎖表的 sql 語句就是上圖中 status 為 updating 的語句為：

update c2c_zwdb.t_file_count set Fcount=Fcount 1 where FFileName="1001_招商銀行 (1).txt" and Ftype=2

該條 update 語句還未執行完，給表 c2c_zwdb.t_file_count 加的寫鎖還沒釋放，又執行 select 讀操作，select 語句會等待表級鎖，導致阻塞而使銀行對賬超時。

為什麼這條 update 語句執行了如此久還沒執行完呢？這個語句不夠高效，當在數據量很大的情況下，執行效率更慢。

定位 MySQL 性能瓶頸的方法很多，主要為這兩種：慢查詢與 explain 命令。

一 慢查詢

慢查詢，顧名思義，就是查詢超過指定時間 long_query_time 的 SQL 語句查詢稱為"慢查詢"。 慢查詢幫我們找到執行慢的 SQL，方便我們對這些 SQL 進行優化。

慢查詢開啟方法

long_query_time 是用來定義慢於多少秒的才算"慢查詢"。查詢 long_query_time 的值如下：

我們可以將其設置設置 long_query_time=2，如下。

開啟慢查詢的方法，一是可以通過在配置文件 my.cnf 或 my.ini 中設置配置參數，二是可以通過命令行設置變數來即時啟動慢查詢日誌，個人比較喜歡第二種即時性的。由下圖可知，記錄慢查詢日誌已開啟，slow_query_log=ON。

slow_query_log 是否打開記錄慢查詢日誌

slow_query_log_file 日誌存放位置

MySQLdumpslow命令

接下來看看慢查詢日誌的格式是怎麼樣。例如，在 MySQL 中運行 select sleep(3);

打開慢查詢日誌文件 MySQL-slow.log 的信息格式如下，說明這條 sql 語句執行用時 5.000183s，鎖了 0s，查詢返回 1 行，一共查了 0 行。

隨著 MySQL 資料庫伺服器運行時間的增加，可能會有越來越多的 SQL 查詢被記錄到了慢查詢日誌文件中，這時要分析慢查詢日誌就顯得不是很容易了。MySQL 提供的 MySQLdumpslow 命令，可以很好地解決這個問題。

MySQLdumpslow 的主要功能是統計不同慢 sql 的：

執行次數（count）

執行最長時間（time）

累計總耗費時間（time）

等待鎖的時間（lock）

發送給客戶端的行總數（rows）

掃描的行總數（rows）

進入 MySQL/bin 目錄，輸入 MySQLdumpslow -help 或--help 可以看到這個工具的參數。

-s，是表示按照何種方式排序，c、t、l、r 分別是按照執行次數、執行時間、等待鎖時間、返回的記錄數來排序，ac、at、al、ar 表示相應的平均值；

-r，是前面排序的逆序；

-t，是 top n 的意思，即為返回排序後前面多少條的數據；

-g，後邊可以寫一個正則匹配模式，大小寫不敏感的；

比如，執行./MySQLdumpslow -s c -t 5/data/zftMySQLData/MySQL-slow.log，得到執行次數最多的前 5 個查詢，如下圖所示。

執行./MySQLdumpslow -s r -t 10 /data/zftMySQLData/MySQL-slow.log，得到返回記錄數最多的前 10 個查詢。

使用 MySQLdumpslow 命令可以非常明確的得到各種我們需要的查詢語句，對 MySQL 查詢語句的監控、分析、優化是 MySQL 優化的第一步，也是非常重要的一步。

二 explain 分析查詢

在分析查詢性能時，EXPLAIN 關鍵字同樣很管用。EXPLAIN 關鍵字一般放在 SELECT 查詢語句的前面，使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理 SQL 語句的。這可以幫助分析查詢語句效率低下的原因或是表結構的性能瓶頸。通過 explain 命令可以得到：

– 表的讀取順序

– 數據讀取操作的操作類型

– 哪些索引可以使用

– 哪些索引被實際使用

– 表之間的引用

– 每張表有多少行被優化器查詢

Explain的用法

Explain tablename 或

Explain [EXTENDED] SELECT select_options

前者可以得出一個表的欄位結構等等，後者主要是給出相關的一些索引信息，本文要講述的重點是後者。

首先看看 explain 的輸出參數：

這些參數中，各個參數的含義如下,

Id：本次 select 的標識符。在查詢中每個 select 都有一個順序的數值。

Select_type：select 類型，主要是區別普通查詢和聯合查詢、子查詢之類的複雜查詢。主要有這幾種：

SIMPLE：這個是簡單的 sql 查詢，不使用 UNION 或者子查詢。

PRIMARY：子查詢中最外層的 select。

UNION：UNION 中的第二個或後面的 SELECT 語句。

DEPENDENT UNION：UNION 中的第二個或後面的 SELECT 語句，取決於外面的查詢。

UNION RESULT：UNION 的結果。

SUBQUERY：子查詢中的第一個 SELECT。

DEPENDENT SUBQUERY：子查詢中的第一個 SELECT，取決於外面的查詢。

DERIVED：派生表的 SELECT(FROM 子句的子查詢)。

Table：輸出行所引用的表。

Type：聯合查詢所使用的類型。

type 顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來說，得保證查詢至少達到 range 級別，最好能達到 ref。

possible_keys：指出 MySQL 能使用哪個索引在該表中找到行。如果是空的，沒有相關的索引。這時要提高性能，可通過檢驗 WHERE 子句，看是否引用某些欄位，或者檢查欄位不是適合索引。

Key：顯示 MySQL 實際決定使用的鍵。如果沒有索引被選擇，鍵是 NULL。

key_len：顯示 MySQL 決定使用的鍵長度。如果鍵是 NULL，長度就是 NULL。文檔提示特別注意這個值可以得出一個多重主鍵里 MySQL 實際使用了哪一部分。

Ref：顯示哪個欄位或常數與 key 一起被使用。

Rows：這個數表示 MySQL 要遍歷多少數據才能找到，在 innodb 上是不準確的。

Extra：如果是 Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃描整個表要快。

如果是 where used，就是使用上了 where 限制。

如果是 impossible where 表示用不著 where，一般就是沒查出來啥。

如果此信息顯示 Using filesort 或者 Using temporary 的話會很吃力，WHERE 和 ORDER BY 的索引經常無法兼顧，如果按照 WHERE 來確定索引，那麼在 ORDER BY 時，就必然會引起 Using filesort，這就要看是先過濾再排序划算，還是先排序再過濾划算。

現在我們再用 explain 來看看前面案例的 sql 執行情況。首先，先看看 t_file_count 的表結構如下，該表的索引是 FId。

GIF

未執行完的 sql 語句是

update c2c_zwdb.t_file_count set Fcount=Fcount 1 where FFileName="1001_招商銀行 (1).txt" and Ftype=2

將其轉換為 select 語句，

select count(*) from c2c_zwdb.t_file_count where FFileName="1001_招商銀行 (1).txt" and Ftype=2。

執行explain命令如下：

GIF

由上圖可見，type=all，key=NULL，該 sql 未使用索引，是一個效率非常低的全表掃描，在數據量很大的情況下，性能情況可想而知。

上述講的幾種方法是用來定位 MySQL 的性能瓶頸，那定位出性能問題後，接下來就是對這些低效的 sql 語句進行優化。問題是怎麼優化？根據什麼原理來優化？這涉及到索引問題，這一部分將會後續放在《MySQL 索引及查詢優化總結》中講述，本文主要講述定位 MySQL 性能瓶頸的兩種方法：慢查詢和 MySQLdumpslow 工具，explain 命令。

全文鏈接：https://cloud.tencent.com/developer/article/1005175

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！