Mysql查詢效能優化
Mysql查詢效能優化
慢查詢優化基礎:優化資料訪問
- 查詢需要的記錄。查詢100條,應用層僅需要10條。
- 多表關聯時返回全部列。*,多表關聯,欄位查詢要加字首。
- 總是取出全部列。*
- 重複查詢相同的資料。例如:在使用者評論的地方需要查詢使用者頭像URL,那麼使用者多次評論的時候,可能就會反覆查詢這個資料。比較好的方案,當初次查詢的時候將這個資料快取起來,需要的時候從快取中取出,這樣效能顯然會更好。
重構查詢方式
切分查詢
- 將大查詢切分成小查詢,每個查詢功能完全一樣,只完成一小部分,每次只返回一小部分查詢結果。如果一次性完成的話,則可能需要一次鎖住很多資料、佔滿整個事務日誌、耗盡系統資源、阻塞很多小的但重要的查詢。
分解關聯查詢
- 很多高效能的應用都會對關聯查詢進行分解。
- 簡單地,可以對每一個表進行一次單表查詢,然後將結果在應用程式中進行關聯。
select * from tag join tag_post on tag_post.id = tag.id join post on post.id = tag_post.id where tag.tag = 'msyql'; 分解為: select * from tag from where tag = 'msyql'; select * from tag_post where id = 1234; select * from post where id in (1,2,3);
優勢
- 讓快取的效率更高。許多應用程式可以方便地快取單表查詢對應的結果物件。例如:上面查詢中的tag已經被快取了,那麼應用就可以跳過第一個查詢。再例如,應用中已經快取了ID為1,2的內容,那麼第三個查詢的in()中就可以少了幾個ID,對MYSQL的查詢快取來說,如果關聯中的某個表發生了變化,那麼久無法使用查詢快取了,而拆分後,如果某個表很少改變,那麼基於該表的查詢就可以重複利用查詢快取結果了。
- 將查詢分解後,執行單個查詢就可以減少鎖的競爭。
- 在應用層做關聯,可以更容易對資料庫進行拆分,更容易做到高效能和高擴充套件。
- 查詢本身效率也可能會有所提升。使用IN()代替關聯查詢,可以讓MYSQL按照ID順序進行查詢,這可能比隨機的關聯要更搞笑。
- 可以減少冗餘記錄的查詢。在應用層做關聯查詢,意味著對於某條記錄應用只需要查詢一次,而在資料庫中做關聯查詢,則可能需要重複地訪問一部分資料。從這點看,這樣的重構還可能會減少網路和記憶體的消耗。
- 更進一步,這樣做相當於在應用中實現了雜湊關聯,而不是使用MYSQL的潛逃迴圈關聯。某些場景雜湊關聯的效率要高很多。
- 在很多場景下,通過重構查詢將關聯放到應用程式中將會更加高效,這樣的場景有很多,比如:當應用能夠方便地快取單個查詢的結果的時候,當可以將資料分佈到不同的MYSQL伺服器上的時候,當能夠使用IN的方式代替關聯查詢的時候、當查詢中使用同一個資料表的時候。
查詢執行基礎
MYSQL接收到請求都做了什麼?
- 客戶端傳送一條查詢給伺服器。
- 伺服器先檢查查詢快取,如果命中了快取,則立刻返回儲存在快取中的結果。否則進入下一階段。
- 伺服器進行SQL解析、預處理,再由優化器生成對應的執行計劃。
- MYSQL根據優化器生成的執行計劃,呼叫儲存引擎的API來執行查詢。
- 將結果返回給客戶端。
MYSQLk客戶端/服務端通訊協議
MYSQL客戶端和服務端之間的通訊協議是“半雙工”的,這意味著,在任何一個時刻,要麼是由伺服器向客戶端傳送資料,要麼是由客戶端向伺服器傳送資料,這兩個動作不能同時發生。一旦一端開始傳送訊息,另一端要接收完整個訊息才能響應它。這就像來回拋球的遊戲:在任何時刻,只能一個人控制球,而且只能空值求得人才能將球拋回去。
客戶端用一個單獨的資料包將資料傳給伺服器,這也是為什麼當查詢的語句很長的時候,引數mac_allow_package就特別重要了。一旦客戶端傳送了請求,它能做的事情就只能是等待結果了。
相反的,一般伺服器響應給使用者的資料通常很多,由多個數據包組成。當伺服器開始響應客戶端請求時,客戶端必須完整地接收整個返回結果,而不能簡單地只取前面幾條結果,然後讓伺服器停止傳送資料。這種情況下,客戶端若接收完成的結果,然後取前面幾條需要的結果,或者接完幾條結果後就“粗暴”地斷開連線,都不是好主意。這也是在必要的時候一定要在查詢中加上LIMIT限制的原因。
查詢狀態
對於一個MYSQL連線,或者說一個執行緒,任何時刻都有一個狀態,該狀態表示了MYSQL當前正在做什麼。有很多方式能查詢當前狀態,最簡單的是使用show full processlist命令。一個查詢的生命週期中,狀態會變化很多次。
- Sleep
-
- 執行緒正在等待客戶端傳送新的請求。
- Query
-
- 執行緒正在執行查詢或者正在將結果傳送給客戶端。
- Locked
-
- 在MYSQL伺服器層,該執行緒正在等待表鎖。在儲存引擎級別實現的鎖。例如:Innodb的行鎖,並不會體現線上程狀態中。對於MyISAM來說這是一個比較典型的狀態,但在其他沒有行鎖的引擎中也經常出現。
- Analyzing and statistics
-
- 執行緒正在收集儲存引擎的統計資訊,並生成查詢執行計劃。
- Copying to tmp table [on disk]
-
- 執行緒正在執行查詢,並且將其結果集都複製到一個臨時表中,這種狀態一般要麼在做Group By操作,要麼是檔案排序操作,或者是UIO/">NION操作。如果這個狀態後面還有“on disk”標記,那表示MYSQL正在講一個記憶體臨時表放到磁碟上。
- Sorting result
-
- 執行緒正在對結果集進行排序。
- Sending data
-
- 這表示多種情況:執行緒可能在多個狀態之間傳送資料,或者在生成結果集,或者在客戶端返回資料。
瞭解這些狀態的基本含義非常有用,這可以讓你很好地瞭解當前“誰正在持球”。在一個繁忙的伺服器上,可能會看到大量的不正常狀態,例如statistics正佔用大量的時間。這通常表示,某個地方有異常了。
查詢優化
MYSQL如何執行關聯查詢
對於UNION查詢,MYSQL先將一系列的單個查詢結果放到一個臨時表中,然後再重新讀取臨時表資料來完成UNION查詢。
在MYSQL的概念中,每個查詢都是一次關聯,所以讀取結果臨時表也是一次關聯。
當前MYSQL關聯執行的策略很簡單:MYSQL對任何關聯都執行巢狀迴圈關聯操作,即MYSQL先在一個表中迴圈取出單條資料,然後再巢狀迴圈到下一個表中尋找匹配的行,依次下去,知道找到所有表中匹配的行為止。然後根據各個表匹配的行,返回查詢中需要的各個列。MYSQL會嘗試在最後一個關聯表中找到所有匹配的行,如果最後一個關聯表無法找到更多的行以後,MYSQL返回到上一層次關聯表,看是否能夠找到更多匹配記錄,一次類推迭代執行。
簡單的內連線查詢: select tab1.col1, tab2.col2 from tab1 inner join tab2 using(col3) where tab1.col1 in (1,2); 實際執行的偽程式碼表示: outer_iter = iterator over tabl1 where col1 in (1,2) outer_row = outer_iter.next while outer_row inner_iter = iterator over tab2 where col3 = outer_row.col3 inner_row = inner_iter.next while inner_row output [ outer_row.col1, inner_row.col2] inner_row = inner_iter.next end outer_row = outer_iter.next end
簡單的外連線查詢: select tab1.col1, tab2.col2 from tab1 outer join tab2 using(col3) where tab1.col1 in (1,2); 實際執行的偽程式碼表示: outer_iter = iterator over tabl1 where col1 in (1,2) outer_row = outer_iter.next while outer_row inner_iter = iterator over tab2 where col3 = outer_row.col3 inner_row = inner_iter.next if inner_row while inner_row output [ outer_row.col1, inner_row.col2] inner_row = inner_iter.next end else output [ outer_row.col, null ] end outer_row = outer_iter.next end
MYSQL的臨時表是沒有任何索引的,在編寫複雜的子查詢和關聯查詢的時候需要注意這一點。這一點對UNION查詢也是一樣的。
關聯子查詢
MYSQL的子查詢實現得非常糟糕。最糟糕的一類查詢是where條件中包含IN()的子查詢語句。
select * from tab1 where col1 in ( select col2 from tab2 where col3 = 1; )
MYSQL對IN()列表中的 選項有專門的優化策略,一般會認為MYSQL會先執行子查詢返回所有包含col3為1的col2。一般來說,IN()列查詢速度很快,所以我們會認為上面的查詢會這樣執行:
- SELECT GROUP_CONCAT(col2) from tab2 where col3 = 1; - Reuslt : 1,2,3,4, select * from tabl1 where col1 in (1,2,3,4);
很不幸,MYSQL不是這樣做的。MYSQL會將相關的外層表壓到子查詢中,它認為這樣可以更高效率地查詢到資料行。也就是說,MYSQL會將查詢改成下面的這樣:
select * from tab1 where exists ( select * from tab2 where col3 = 1 and tab1.col1 = tab2.col1 );
這時,子查詢需要根據col1來關聯外部表的film,因為需要到col1欄位,所以MYSQL認為無法先執行這個子查詢。
如果tab1表資料量小,效能還不是很糟糕,如果是一個非常大的表,那這個查詢效能會非常糟糕。改寫這個子查詢
select * from tab1 inner join tab2 using(col1) where col3 = 1; && select * from tab1 where exists ( select * from tab2 where col3 = 1 and tab1.col1 = tab2.col1 );
一旦使用了DISTINCT和GROUP by,在查詢執行的過程中,通常產生臨時中間表。可以使用EXISTS子查詢優化
UNION的限制
通過將兩個表查詢結果集合並取前20條
(select *from tab1 order by col1) union all (select * from tab2 order by col2) limit 20; 優化為: (select *from tab1 order by col1 limit 20) union all (select * from tab2 order by limit 20)
UNION 臨時表的資料會大大減少
優化COUNT()查詢
Count()是一個特殊的函式,有兩種非常不同的作用:它可以統計某個列的數量,也可以統計行數。在統計列值時要求列值是非空的(不統計NULL)。如果在COUNT()的括號中指定了列或者列的表示式,則統計的就是這個表示式有值的結果數。
Count()的另外一個作用是統計結果集的行數。當MYSQL確定括號內的表示式值不可能為空時,實際上就是在統計行數。最簡單的就是COUNT(*)。
簡單的優化
select count(*) from tab1 where col >5; 優化為: select (select count(*) from tab1 ) - count(*) from tab1 where col <5; 掃描的數量會減少很多 子查詢也會當成常數,使用expand可知
情景:在同一個查詢中統計一個列的不同值的數量,以減少查詢的語句量
select sum(if(color = blue), 1, 0) as blue , sum(if(color = red), 1, 0) as red from items ; 同樣也可以使用Count
優化關聯查詢
- 確保ON或者USING子句中的列有索引。
- 確保任何group by和order by只涉及到一個表中的列。
優化LIMIT分頁
select col1, col2 from tab1 order by col3 limit 50,5; 改寫成: select col1, col2 from tab1 inner join ( select col1 from tab1 order by col3 limit 50,5 ) as lim using(col1);
-
這裡的“延遲關聯”將大大提升查詢效率,它讓MYSQL掃描儘可能少的頁面,獲取需要訪問的記錄後再根據關聯列回原表查詢需要的所有列。這個技術可以優化LIMIT查詢。
-
有時候也可以將LIMIT查詢轉換為已知位置的查詢,讓MYSQL通過範圍掃描獲得到對應的結果。
select col1, col2 from tab1 where col1 between 30 and 50; select col1, col2 from tab1 where col1 < 500 order by col1 limit 20;