MySQL查詢優化

wanghang96 發表於 2020-09-24

一、概述

查詢優化是一個非常大的話題,它可以從表設計,索引,查詢技巧等多個方面去討論,對於表設計,索引已經有專門的文章介紹(Mysql高效索引策略, Mysql庫表設計優化)。本文將介紹查詢優化的思路和一些實用的查詢優化策略。

二、如何執行查詢

1、整體流程

1.客戶端傳送查詢請求給伺服器。
Mysql客戶端/服務端通訊協議時半雙工的,任意一個時間點,只有一方傳送資料,傳送請求後就只能等待結果;另一方接收資料,只有接受完資料才能響應結果。客戶端使用一個單獨的資料包傳送給伺服器,當查詢語句超出max_allowed_packet,則會報錯。服務端響應資料時,則可能由多個資料包組成。

2.伺服器檢查查詢快取,若命中則返回快取資料。否則進行下一步
伺服器收到查詢請求後,先經過查詢快取,如果命中,則返回結果。否則進行SQL的解析以及後續流程。
3.服務端進行SQL解析和預處理
Mysql解析器將使用語法規則驗證和解析查詢,得到一個解析樹。前處理器會檢查解析樹中的列,別名。
4.優化器生成查詢計劃,並呼叫儲存引擎API執行查詢。
Mysql使用基於成本的優化器,它預測一個查詢使用某種執行計劃的成本,並選擇成本最小的一個。執行計劃的依據來自統計資訊,統計資訊由儲存引擎API提供。優化器會根據查詢上下文進行優化,比如重新定義關聯表順序,覆蓋索引掃描,子查詢優化等。
5.返回結果給客戶端。
如果查詢可以被快取,那麼Mysql會將結果放到查詢快取中。Mysql結果集的返回是一個增量逐步返回的過程。在生成第一條結果集時就開始逐步向客戶端返回。每一行都會以一個滿足Mysql客戶端/服務端通訊協議的包傳送出去。在TCP協議傳輸層可能會對Mysql的包進行快取然後批量傳輸。
整體流程如下圖所示(圖片引自《高效能Mysql》)
查詢過程

2、關聯查詢

Mysql是巢狀迴圈來執行關聯操作的,即先在一張表中迴圈取單條資料,再巢狀關聯表迴圈取出匹配的行,依次下去直到所有表都掃描完成。然後根據各個表匹配的行返回查詢欄位。其實就是k張表分別放在巢狀迴圈的不同層,第一張表在第一層迴圈裡遍歷,第二張表在第二層迴圈裡,…,第k張表在第k層迴圈裡。如果沒有索引的話就是一個O(n ^ k)的複雜的演算法。(圖片引自《高效能Mysql》)
關聯查詢可能會被優化器重寫關聯順序,選擇更小的表為驅動表,以提高查詢效率。

--檢視查詢執行計劃
EXPLAIN SELECT ...
--檢視不被優化器重寫關聯順序的執行計劃
EXPLAIN SELECT  STRAIGHT_JOIN ...
--檢視最後一次執行的成本,該數值越小越優
SHOW STATUS LIKE `last_query_cost`;

在這裡插入圖片描述

3、子查詢與UNION

Mysql在FROM子句中遇到子查詢時,先執行子查詢並將結果放到一張臨時表中,然後將這個臨時表當做一個普通表對待。MySQL在執行UNION查詢時也使用類似的臨時表。臨時表是沒有索引的,所以在執行這類查詢時需要注意複雜度不要太高。

4、執行排序

MySQL索引優化中有介紹利用索引排序,當不能使用索引排序時,MySQL需要在服務層排序。如果資料量小則在記憶體中進行快速排序,如果資料量大則進行歸併排序,統稱為檔案排序。有兩種排序策略
兩次傳輸排序
先讀取行指標及排序欄位,在記憶體中完成排序後再回表查詢資料。如果記錄較多會產生大量隨機IO,但是這種方式單次能進行更多記錄的排序。
單次傳輸排序
將資料一次性讀入記憶體,再排序。這種方式更加佔記憶體空間。當查詢需要所有列的總長度不超過 max_length_for_sort_data時,使用該排序策略。
關聯查詢排序
如果排序欄位來自關聯查詢的第一張表,則在查詢第一個表的時候就排序,EXPLAIN的Extra欄位會有“Using filesort”;否則需要將關聯的結果存放到一張臨時表,所有的關聯結束後再進行檔案排序,EXPLAIN結果的Extra欄位為“Using temporary, Using filesort”。

二、查詢優化分析

1.請求太多記錄

儘量請求所需的資料就好了。當然在設計介面時,有時候為了提高介面的通用性,會在SQL種包含很多查詢條件,很多返回欄位。需要在兩者之間權衡,對於大多數普通的應用來說,可能選擇通用的設計,減少開發工作量。

2.掃描過多的記錄

如果儲存引擎層面沒能過濾掉資料,那麼只能到服務層做過濾。而關於Where條件的應用是:
第一,在索引中應用Where條件來過濾記錄。
第二,使用索引覆蓋,不需要回表。
第三,在服務層應用Where條件。
這三種方式從好到壞。當發現掃描過多的行後,新增合適的索引可以避免過多的記錄需要返回到服務層過濾(導致大量隨機IO)。

3.切分複雜查詢

現在一般的網路代價較低,相比而言,將一個複雜的查詢切分成幾個小的簡單查詢更有利於Mysql執行。

4.切分大事務

將一個大事務切分成小事務,比如在定期清理歷史資料時,如果用一個大的SQL可能會一次鎖定很多資料,可以將歷史資料分批刪除,並且每執行一個SQL間隔一段時間,平緩的傳送請求,這樣可以降低Mysql的壓力。

5.切分關聯查詢

將關聯查詢分解為簡單查詢,Mysql能很好的執行小查詢。單個查詢能夠減少鎖的競爭,並且可以更好的利用快取。將關聯的邏輯放到伺服器做就可以了。

三、常見SQL優化

1.COUNT

COUNT可以統計某個列(非空)的數量,如果單單統計行數,則應當使用COUNT(*)。對於COUNT可以有以下優化。
a.如果不需要精確值,使用EXPLAIN估算出來的行數做近似值,EXPLAIN沒有真正執行查詢,消耗很低。
b.如果不需要精確值,可以定期執行COUNT,將結果快取到Redis中。
c.如果需要精確值,在有效能壓力的時候,使用計數器表單獨計數。

2.關聯查詢

a.在關聯表中給關聯欄位加索引,例如優化器的結果是A join B ,則給B的關聯欄位加索引,而A不需要。
b.確保GROUP BY 和 ORDER BY中的表示式只涉及一個表中的列。

3.GROUP BY和ORDER BY

在索引優化一文中有將過利用索引進行分組和排序。如果不能用到索引,那麼只能使用臨時表或者檔案排序。

4.深度分頁

物理分頁是通過LIMIT實現的,當頁碼很大時,例如 LIMIT 10000 10,將查詢滿足條件的10010條記錄,然後排序,返回最後10行,這樣的代價非常大。可以嘗試如下方式進行優化。
a.索引覆蓋,即使深度分頁,也不會有大量的隨機IO。但是大多數分頁是很難滿足這個條件的。查詢的欄位很多,條件很雜,難以走索引覆蓋。
b.延遲關聯,先用過濾條件和分頁條件查詢出id,這個查詢過程需要保證有合適的索引。由於只查詢id不用回表,不會有大量隨機IO。然後,用這批id去關聯查詢需要的欄位。注意需要Where條件走索引覆蓋。

SELECT * FROM t_table t1
	INNER JOIN 
		(SELECT id FROM t_table WHERE ... LIMIT 1000 10) t2 
	ON t1.id = t2.id;

c.利用ORDER BY的最值分頁。比如ORDER BY id ASC,先查詢出第一頁資料,之後每次查詢都帶上上一次查詢結果的最大id。

SELECT * FROM t_table WHERE id > 1000 AND .. ORDER BY field ASC LIMIT 10

d.冗餘表,該表只包含主鍵和需要查詢和排序的資料列。先到冗餘表中查詢id,再根據id去原表查資料。這種方式和延遲關聯思想類似。

5.UNION

a.所有UNION的條件,WHERE,LIMIT,ORDER BY等字句下沉到各個子查詢中。
b.儘量使用UNION ALL,然後在應用層做去重。