【MySQL調優】查詢優化

在之前的文章中我們討論了優化數據庫結構與優化數據庫索引，在二者的基礎上，我們還需要足夠好的查詢來讓上兩步的優化發揮其真正的價值。

查詢優化、索引優化與數據庫結構優化齊頭并進，一個不落，才能保證系統的高效性。

查詢變差的原因：

查詢是由多個子任務所實現的，需要優化查詢，要么就是消除其中一些不必要的子任務，要么就是減少子任務的執行次數，要么就是讓子任務執行的更快。

查詢的生命周期：客戶端 -> 服務器 -> 解析 -> 執行計劃 -> 執行 -> 返回給服務器 -> 返回給客戶端

優化數據訪問

兩步分析法：a. 不要檢索大量不需要的數據，b.Mysql服務層是否分析了大量不需要的數據行

1.不要向數據庫請求不需要的數據

可能會選擇在應用層進行篩選，會給Mysql服務器帶來額外負擔，增加網絡開銷。

多表關聯時只需要返回需要的列，杜絕使用SELECT * FROM

重復查詢相同的數據也是很危險的，尤其是數據庫沒有緩存的場景下

2.MySQL是否掃描了過多的數據

通過觀察：響應時間、掃描行數、返回行數 可以分析MySQL是否掃描了過多的行

響應時間 = 等待時間 + 服務時間

掃描行數與返回行數如果差距過大，說明篩選條件不能很好的被數據庫所支持

掃描的行數與訪問類型：可以通過EXPLAIN type來查看，全表掃描，索引掃描，范圍掃描，唯一索引查詢，常數引用。速度由慢到快，掃描行數由多變少。

WHERE的三種場景:

1. 索引中使用where條件篩選不匹配記錄，直接在存儲引擎層就能夠完成；

2. 使用索引覆蓋掃描，直接從索引中過濾不需要的記錄并且返回命中的結果，不需要回表查詢其他數據，在Extra列中會出現: using index

3. 從數據表返回數據，在MySQL服務器層進行判斷。在Extra列會出現：Using Where的字段。這種情況一定會進行了回表操作。

如果存在掃描行數遠遠大于返回行數，可以嘗試：

1. 使用覆蓋索引，無需回表操作

2. 改變數據表結構，增加匯總表

3. 重寫復雜查詢

重構查詢的方法：

修改應用邏輯，通過不同的sql達到相同的效果。

一個復雜的查詢還是多個簡單的查詢

MySQL連接創建與斷開都很輕量級，返回一個小的查詢可以很高效。但是如果是把本來就很小的查詢再去進行拆分，就很傻

切分查詢

分而治之，把可能會一次性返回大量數據，或者針對大量數據進行刪改操作的查詢分批次進行

分解關聯查詢

通過上層應用程序實現關聯，而非直接使用數據，具有以下優勢：

緩存更高效

減少鎖的競爭

應用層關聯可以更容易進行數據庫拆分，實現高性能和可擴展

使用IN代替關聯查詢，維護條件有序性可以提升查詢本身的效率

減少冗余記錄的查詢

自己通過計算邏輯實現哈希關聯（增加新的哈希列）

查詢執行的基礎

客戶發起查詢時，MySQL做了什么：

1. 客戶端發送一條查詢到服務器

2. 如果命中了服務器緩存，直接返回，否則進入下一步

3. 服務器進行SQL解析、預處理，再由優化器生成執行計劃

4. MySQL根據優化器生成的執行計劃，調用存儲引擎的Api來執行查詢

5. 將結果返回給客戶端

查詢狀態： SHOW FULL PROCESSLIST

Sleep：等待客戶端發送新的請求

Query：正在執行查詢，或者正在將查詢結果發送回客戶端

Locked：MySQL服務器層該線程在等待表鎖。InnoDB的行鎖并不會在這里體現

Analyzing and statistics：線程正在收集統計信息并生成查詢的執行計劃

Copying to tmp table [on disk]：線程執行查詢并將結果復制到一個臨時表中，可能是發生了group by，文件排序，Union操作中的一種。如果出現了on disk，代表需要將結果復制到磁盤上

Sorting result：線程在對結果集進行排序

Sending data：線程在多個狀態之間傳送數據，或者生成結果集，或者在向客戶端傳送數據

查詢緩存：

如果開啟了查詢緩存，MySQL會優先對這個緩存進行檢查，看看是否命中了之前的查詢緩存中的數據。是通過對大小寫敏感的哈希查找來實現的。

查詢優化處理：（優化器）

將SQL轉換為執行計劃，MySQL通過執行計劃與存儲引擎進行交互。包含了多個子階段：解析SQL、預處理、優化SQL的執行計劃。這個過程中的任意錯誤都會終止查詢。

語法解析與預處理

MySQL通過關鍵字將SQL進行解析，生成一顆對應的解析樹，MySQL解析器將會使用MySQL語法規則驗證和解析查詢。

查詢優化器

MySQL使用基于成本的優化器，嘗試預測一個查詢使用某種執行計劃的成本，選擇其中最小的一個來進行執行。通過SHOW STATUS LIKE ‘Last_query_cost’;可以查詢上一次當前查詢的成本。

優化器不會考慮任何緩存，它假設讀取任何數據都需要一次磁盤I/O操作。

可能會導致MySQL優化器選擇錯誤的執行計劃的原因：

統計信息不準確。

執行計劃中的成本估算不等同于實際執行的成本。（MySQL無法區分哪些數據在內存中，那些數據在磁盤中）

MySQL最優可能和想象中的最優不一樣。

MySQL從不考慮其他并發執行的查詢情況

MySQL并不會任何時候都基于成本進行優化

MySQL不會考慮諸如用戶自定義函數一類的成本

如果執行計劃過多，MySQL不會進行完整的執行計劃消耗估算，因為這可能會得不償失

優化器十分復雜，可以完成靜態優化與動態優化兩個行為，靜態優化只會執行一次，而動態優化隨著上下文的轉換，每次查詢時都需要重新評估，可以看成是一種運行時優化。

MySQL常見優化類型：

1. 重新定義關聯表的順序

2. 將外連接轉換為內連接

3. 使用等價變換規則

4. 優化COUNT()、MIN()、MAX()

5. 預估并轉化為常數表達式

6. 覆蓋索引掃描

7. 子查詢優化

8. 提前終止查詢，比如LIMIT的場景

9. 等值傳播

10. 列表IN()比較

MySQL的IN()操作復雜度為O(nlogn)，而OR操作為O(n)，MySQL會先對IN中的內容進行排序，然后通過二分查找進行查找。

數據表和索引的統計信息

統計信息是由存儲引擎來實現的。所以在每次MySQL優化之前，都會向存儲引擎詢問諸如：每個表或者索引有多少個頁面、每個表的每個索引基數是多少、數據行和索引的長度、索引的分布信息等。

MySQL的關聯查詢

UNION查詢，MySQL先將一些列的單個查詢結果放在臨時表，然后重新讀取臨時表數據完成UNION查詢。

MySQL對任何的關聯執行都是通過嵌套循環的操作來實現的。可以將這種查詢生動的用泳道圖來進行描述。

需要注意的是，這個派生表，也就是臨時表是沒有索引的。

執行計劃

與很多其他的關系型數據庫不同，MySQL不會生成字節碼的查詢計劃。最終的執行計劃包含了重構查詢的全部信息。如果對某個查詢執行了EXPLAIN EXTENDED之后，再去執行SHOW WARNINGS，就可以看到重構出的全部查詢。

MySQL的執行計劃是一顆左側深度優先的樹形結構。

關聯查詢優化器

MySQL優化器最重要的一個部分就是關聯查詢優化，他決定了多個表關聯時候的順序。

通過STRAIGHT_JOIN關鍵字可以強制指定關聯順序，但往往并沒有關聯優化器給我們優化之后的結果好。通常關聯優化器通過調整關聯順序，可以讓查詢進行更少的嵌套循環和回溯操作。

通過Last_query_cost的值，可以看到不同的關聯順序查詢后帶來的預估成本。

當需要關聯的表超過optimizer_search_depth之后，關聯優化器就會工作在“貪婪模式”

排序優化

無論如何排序都是一個很耗費性能的工作，所以應當盡量避免排序，或者盡量避免對大量數據進行排序。

對于排序，MySQL統一稱之為filesort，只不過有時會發生在內存中，更差的情況下會發生在磁盤中。

通過EXPLAIN可以看到MySQL在關聯查詢過程中的兩種排序模式，一種是所有ORDER BY的所有列都來自第一個關聯表，那么MySQL在處理第一個表的時候就會完成排序，然后進行后續的關聯操作，這時候Extra字段就會有"Using filesort"。除此之外的任何一種情況，MySQL都會先將關聯結果放在一個表里面，然后在所有的關聯都結束之后，進行文件排序。這時候在Extra字段就會出現"Using temporary; Using filesort"。如果查詢中出現了LIMIT字段，LIMIT也會在排序之后再進行限制，所以即使返回很少的數據，可能也會占用大量的內存空間。

查詢執行引擎：

查詢大部分情況下就是按照優化器給出的順序調用存儲引擎的"handler API"。這種簡單的接口模式讓MySQL的存儲引擎插件式架構成為可能，同時也會為優化器帶來一定的限制。

返回數據給客戶端：

即使不需要返回數據，MySQL也會把影響的行數返回給客戶端。

MySQL查詢優化器的局限性

關聯子查詢

MySQL針對關聯子查詢的優化非常糟糕，尤其是Where條件中包含In()的子查詢。

如何用好關聯子查詢

可以通過Not Exists提前中斷查詢，也就是MySQL的提前終止算法。

通過壓力測試可以看到Not Exists子查詢的性能是優于Left outer join的。

這個說明了不要通過自己猜想來進行驗證，而是通過實驗，benchmard來驗證自己的猜想。

UNION的限制

如果是 (SELECT table1) UNION ALL (SELECT table2) LIMIT 20的操作，會先從兩個表中查出所有的數據，然后執行UNION ALL然后再去LIMIT篩選，性能很差，建議根據order by條件，分別查處需要的數據然后 UNION ALL

索引下推

可以直接把where條件中的內容下推到索引中，即使索引沒有使用這個字段，這樣可以減少回表次數

并行執行

MySQL無法通過多核處理器來完成并行查詢

哈希關聯

InnoDB存儲引擎不支持哈希索引，通過人工進行哈希字段的方式可以幫助MySQL實現哈希關聯，有助于加快數據查詢速度

松散索引掃描

MySQL在掃描過程中一定要指明一個起點和一個終點。

最大值與最小值優化

MySQL對MIN()、MAX()函數優化的很差

同一個表上進行查詢與更新

必須通過子查詢才能實現

查詢優化器提示（hint）

通過提示可以指引優化器完成操作行為，提示的用法可以參考MySQL官方手冊。

HIGH_PRIORITY和LOW_PRIORITY

控制查詢對于表的訪問順序，僅針對表級鎖存儲引擎有效，在InnoDB存儲引擎使用會造成問題。

DELAYED

針對INSERT和REPLACE有效，代表操作可以延遲寫入，會影響LAST_INSERT_ID()的工作行為

STRAIGHT_JOIN

可以放置在SELECT語句和SELECT關鍵字之后，也可以放置在任何兩個關聯表的名字之間。用于固定數據表之間的關聯順序。

SQL_SMALL_RESULT和SQL_BIG_RESULT

只針對select語句有效，告訴優化器通過group by或者distince查詢的時候，使用的是內存臨時表還是磁盤臨時表進行排序

SQL_BUFFER_RESULT

告訴優化器將查詢結果放入一個臨時表，然后盡可能快的釋放表鎖。

SQL_CAHCE和SQL_NO_CACHE

告訴MySQL的查詢結果是否需要放入查詢緩存中

SQL_CALC_FOUND_ROWS

加上會去計算limit之后具體的總行數

FOR UPDATE和LOCK IN SHARE MODE

控制SELECT語句的鎖機制，只針對行鎖級存儲引擎有效。唯一支持這兩個提示的存儲引擎就是InnoDB。

USE INDEX、IGNORE INDEX和FORCE INDEX

告訴優化器使用或者不使用哪些索引來進行記錄查詢。FORCE INDEX告訴優化器全表掃描的成本會遠遠高于索引掃描。

Optimizer_search_depth

控制優化器窮舉執行計劃的限度，如果查詢長時間處在"statistics"狀態，就可以考慮調低這個參數

Optimizer_prune_level

默認打開，優化器根據掃描行數決定是否跳過某些執行計劃

Optimizer_switch

開關優化器特性的標志位

優化特定類型的查詢

優化COUNT()

Count()是聚合函數，可以統計所有count字段非null的行，所以沒有特殊情況需要統計所有行數的話，建議優先使用count(*)。

MyISAM只有在查找沒有where的count才會特別快，否則還是會通過where條件進行全表掃描和統計

某些場景下可能只是需要一個近似值而非那么準確的值，這時候直接使用explain中的統計估算結果也是一個不錯的選擇

通過修改應用層邏輯可以避免針對大范圍數據進行count掃描操作。

時刻記住：快速、準確與實現簡單，往往只能實現其中的兩個。

優化關聯查詢

1. 確保ON或者USING的字句中的列上面都有索引

2. 確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列

3. 當升級MySQL的時候需要注意：關聯語法，運算符優先級等其他可能會在不同MySQL版本中發生變化的地方

優化子查詢

盡可能用關聯去替代掉子查詢

優化Group By和Distinct

在無法使用索引時，MySQL會通過臨時表或者文件排序來完成分組操作。

建議使用ONLY_FULL_GROUP_BY的SQL_MODE來對數據庫的分組策略進行約束。

優化LIMIT分頁

如果在進行大范圍數據分頁的后幾頁查詢的時候，可能會導致大量的回表，回表的過程中會造成大量的磁盤I/O和內存操作，比如：

Select film_id, description from sakila.film order by title limit 50, 5;

可以通過"延遲關聯"來解決此類問題：

Select film.film_id, film.description from sakila.film INNER JOIN (SELECT film_id from sakila.film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);

延遲關聯可以大大提升查詢效率，減少回表次數。

優化SQL_CALC_FOUND_ROWS

會掃描所有的行，統計出一個結果。

優化UNION查詢

MySQL總是通過創建并填充臨時表的方式來完成UNION查詢，可以手工將where, limit, order by等子句下推到UNION的各個子句中完成。

靜態查詢分析

通過pt-query-advisor能夠解析查詢日志，分析查詢模式，然后給出所有可能存在潛在問題的查詢，并給出足夠詳細的建議。

使用用戶自定義變量

用戶自定義變量的生命周期是一次連接，使用連接池可能會造成問題。

作用：

優化排名語句，實現行號功能

避免重復查詢剛剛更新的數據

統計更新和插入的數量

確定取值的順序

編寫偷懶的UNION

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。