慢查詢 MySQL 定位優化技巧,從10s優化到300ms

語言: CN / TW / HK

今天分享一下如何快速定位慢查詢SQL以及優化。

一、如何定位並優化慢查詢SQL?

一般有3個思考方向:

  • 根據慢日誌定位慢查詢sql。
  • 使用explain等工具分析sql執行計劃。
  • 修改sql或者儘量讓sql走索引。

二、如何使用慢查詢日誌?

先給出步驟,後面說明,有3個步驟

1. 開啟慢查詢日誌

首先開啟慢查詢日誌,由引數slow_query_log決定是否開啟,在MySQL命令列下輸入下面的命令:

set global slow_query_log=on;

預設環境下,慢查詢日誌是關閉的,所以這裡開啟。

2. 設定慢查詢閾值

set global long_query_time=1;

只要你的SQL實際執行時間超過了這個閾值,就會被記錄到慢查詢日誌裡面。這個閾值預設是10s,線上業務一般建議把long_query_time設定為1s,如果某個業務的MySQL要求比較高的QPS,可設定慢查詢為0.1s。

發現慢查詢及時優化或者提醒開發改寫。一般測試環境建議long_query_time設定的閥值比生產環境的小,比如生產環境是1s,則測試環境建議配置成0.5s。便於在測試環境及時發現一些效率的SQL。

甚至某些重要業務測試環境long_query_time​可以設定為0,以便記錄所有語句。並留意慢查詢日誌的輸出,上線前的功能測試完成後,分析慢查詢日誌每類語句的輸出,重點關注Rows_examined(語句執行期間從儲存引擎讀取的行數),提前優化。

3.確定慢查詢日誌的檔名和路徑

show global variables like 'slow_query_log_file'

結果會發現慢日誌預設路徑就是MySQL的資料目錄,我們可以來看一下MySQL資料目錄。

show global variables like 'datadir';

不用關注這裡為什麼不是MySQL 8.0,這和版本沒什麼關係的。

來,直接上菜,乾巴巴的定義我自己都看不下去。

我們先來檢視一下變數,我框出了需要注意的點。

查詢帶有quer的相關變數:

show global variables like '%quer%';

這裡設定慢查詢閾值為1s:

set global long_query_time=1;

可以看到已經修改過來了:

但是重啟mysql客戶端設定和統計慢查詢日誌條數就會清零,即所有配置修改會還原。

命令修改配置之後,在命令列net stop mysql​關閉MySQL服務,再net start mysql​開啟MySQL服務,接著執行show global variables like '%quer%';會發現配置還原了。

在配置檔案修改才能永久改變,否則重啟資料庫就還原了。

3.慢查詢例子演示,新手都能看懂

資料表結構,偷懶沒寫comment:

CREATE TABLE `person_info_large` (  
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
    `account` VARCHAR (10),   
    `name` VARCHAR (20),  
    `area` VARCHAR (20),  
    `title` VARCHAR (20), 
    `motto` VARCHAR (50),
    PRIMARY KEY (`id`),  
    UNIQUE(`account`),
    KEY `index_area_title`(`area`,`title`) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

這裡的資料是200W條。請注意表結構,記住哪幾個欄位有索引即可,後續圍繞這個表進行分析。

這個3.36s並不是實際執行時間,實際執行時間得去慢查詢日誌去看Query_time引數。

可以看到Query_time: 6.337729s,超過了1s,所以會被記錄,一個select語句查詢這麼久,簡直無法忍受。

圖中其他的引數解釋如下:

  • Time:慢查詢發生的時間
  • Query_time:查詢時間
  • Lock_time:等待鎖表的時間
  • Rows_sent:語句返回的行數
  • Rows_exanined:語句執行期間從儲存引擎讀取的行數。

上面這種方式是用系統自帶的慢查詢日誌檢視的,如果覺得系統自帶的慢查詢日誌不方便檢視,可以使用pt-query-digest​或者mysqldumpslow等工具對慢查詢日誌進行分析。

:有的慢查詢正在執行,結果已經導致資料庫負載過高,而由於慢查詢還沒執行完,因此慢查詢日誌看不到任何語句,此時可以使用show processlist​命令檢視正在執行的慢查詢。show processlist​顯示哪些執行緒正在執行,如果有PROCESS許可權,則可以看到所有執行緒。否則,只能看到當前會話執行緒。

四、查詢語句慢怎麼辦?explain帶你分析sql執行計劃

根據上一節的表結構可以知道,account是添加了唯一索引的欄位。explain分析一下執行計劃。

我們重點需要關注select_type、type、possible_keys、key、Extra​這些列,我們來一一說明,看到select_type​列,這裡是SIMPLE簡單查詢,其他值下面給大家列出。

type列,這裡是index,表示全索引掃描。

表格從上到下代表了sql查詢效能從最優到最差,如果是type型別是all,說明sql語句需要優化。

注意:如果type = NULL​,則表明個MySQL不用訪問表或者索引,直接就能得到結果,比如explain select sum(1+2);

possible_keys代表可能用到的索引列,key表示實際用到的索引列,以實際用到的索引列為準,這是查詢優化器優化過後選擇的,然後我們也可以根據實際情況強制使用我們自己的索引列來查詢。

Extra列,這裡是Using index

一定要注意,Extra中出現Using filesort、Using temporary代表MySQL根本不能使用索引,效率會受到嚴重影響,應當儘可能的去優化。

出現Using filesort說明MySQL對結果使用一個外部索引排序,而不是從表裡按索引次序讀到相關內容,有索引就維護了B+樹,資料本來就已經排好序了,這說明根本沒有用到索引,而是資料讀完之後再排序,可能在記憶體或者磁碟上排序。也有人將MySQL中無法利用索引的排序操作稱為“檔案排序”。

出現Using temporary​表示MySQL在對查詢結果排序時使用臨時表,常見於order by​和分組查詢group by。

回到上一個話題,我們看到account是添加了唯一索引的欄位。explain分析了執行計劃後。

直接按照account降序來查:

檢視慢查詢日誌發現,使用索引之後,查詢200W條資料的速度快了2s。

接著我們分析一下查詢name的sql執行計劃。

然後給name欄位加上索引:

加上索引之後,繼續看看查詢name的sql執行計劃:

對比一下前面name不加索引時的執行計劃就會發現,加了索引後,type由ALL全表掃描變成index索引掃描。order by​並沒有 using filesort​,而是using index,這裡B+樹已經將這個非聚集索引的索引欄位的值排好序了,而不是等到查詢的時候再去排序。

接著我們繼續執行查詢語句,此時name已經是添加了索引的。

結果發現,name新增索引之前,降序查詢name是花費6.337729s,新增索引之後,降序查詢name花費了3.479827s,原因就是B+樹的結果集已經是有序的了。

五、當主鍵索引、唯一索引、普通索引都存在,查詢優化器如何選擇?

查詢一下資料的條數,這裡count(id),分析一下sql執行計劃:

這裡實際使用的索引是account唯一索引。

分析一下:實際使用哪個索引是查詢優化器決定的,B+樹的葉子結點就是連結串列結構,遍歷連結串列就可以統計數量,但是這張表,有主鍵索引、唯一索引、普通索引,優化器選擇了account這個唯一索引,這肯定不會使用主鍵索引,因為主鍵索引是聚集索引,每個葉子包含具體的一個行記錄(很多列的資料都在裡面),而非聚集索引每個葉子只包含下一個主鍵索引的指標,很顯然葉子結點包含的資料是越少越好,查詢優化器就不會選擇主鍵索引。

當然,也可以強制使用主鍵索引,然後分析sql執行計劃。

我們看一下優化器預設使用唯一索引大致執行時間676ms:

強制使用主鍵索引大致執行時間779ms:

我們可以用force index強制指定索引,然後去分析執行計劃看看哪個索引是更好的,因為查詢優化器選擇索引不一定是百分百準確的,具體情況可以根據實際場景分析來確定是否使用查詢優化器選擇的索引。