前言

文章將闡述 MySQL 查詢最佳化相關的東西，瞭解查詢最佳化器、查詢最佳化的基本思路、查詢的基本原則、索引相關的最佳化。

一、查詢最佳化器模組

查詢最佳化器的任務是發現執行 SQL 查詢的最佳方案。大多數查詢最佳化器，要麼基於規則、要麼基於成本。大多數查詢最佳化器，包含 MySQL 的查詢最佳化器，總或多或少地在所有可能的查詢評估方案中搜索最佳方案。

MySQL 中 MySQL Query Optimizer 是最佳化器的核心，當 MySQL 資料拿到一個 Query 語句之後會交給 Query Optimizer 去解析，併產生一個最優的執行計劃（這個是 Optimizer 認為是最優的，但不一定是真正最優的，就跟 Oracle 資料庫會估算錯 rows 一樣），然後資料庫按照這個執行計劃去執行查詢語句。在 SQL 語句整個執行過程中，Optimizer 是最耗時的，但是也有第三方工具為了提高效能繞開 MySQL 的 Query Optimizer 模組，比如：handlersocket。

對於多表關聯查詢，MySQL 最佳化器所查詢的可能方案數隨查詢中引用的表的數目成指數增長。對於小數量的表，這不是一個問題。然而，當提交的查詢需要的結果集很大時，查詢最佳化所花的時間會很容易地成為伺服器效能的瓶頸。

查詢最佳化的一個更加靈活的方案時容許使用者控制最佳化器詳細地搜尋最佳查詢評估方案。一般思想是調查的方案越少，它編譯一個查詢所花費的時間越少。另外，由於最佳化器跳過一些方案，它可能錯過一個最佳方案。最佳化器關於方案數量評估的行為可以透過兩個系統變數來控制：

optimizer_prune_level 變數告訴最佳化器根據對每個表訪問的行數的估計跳過一些方案。我們的試驗顯示該類 “有根據的猜測” 很少錯過最佳方案，並且可以大大降低查詢編輯次數。這就是為什麼預設情況該選項為 on(optimizer_prune_level=1)。然而，如果你認為最佳化器錯過了一個更好的查詢方案，則該選項可以關閉 (optimizer_prune_level=0)，風險是查詢編輯花費的時間更長。請注意即使使用該啟發，最佳化器仍然可以探測呈指數數目的方案。

timizer_search_depth 變數告訴最佳化器對於每個未完成的 “未來的” 方案，應檢視多深，以評估是否應對它進一步擴大。optimizer_search_depth 值較小會使查詢編輯次數大大減小。例如，如果 optimizer_search_depth 接近於查詢中表的數量，對 12、13 或更多表的查詢很可能需要幾小時甚至幾天的時間來編譯。同時，如果用 optimizer_search_depth 等於 3 或 4 編輯，對於同一個查詢，編譯器編譯時間可以少於 1 分鐘。如果不能確定合理的 optimizer_search_depth 值，該變數可以設定為 0，告訴最佳化器自動確定該值。

二、查詢最佳化的基本思路

不管做專案設計還是產品設計都需要先有思路，才能規避一些問題。當然 MySQL 查詢最佳化也需要研發或者 DBA 擁有一些思路，唯有思路指導書寫，才會更加合理。

1. 最佳化更需要最佳化的 Query 語句

應該最佳化併發高的 Query 語句，不至於高併發下，由於 SQL 導致應用程式卡死，比如 php-fpm 的大量等待，而且一個高併發的 Query 語句，如果走錯執行計劃，本來只需要掃描幾百行，結果掃描了幾百萬行，可能會有災難性的後果，更加會導致業務卡頓，尤其是核心業務下出現的高併發 Query 語句。

2. 檢視執行計劃調整 Query 語句

根據 explain extended SQL 分析查詢語句，就能檢視執行計劃，這個時候需要關注執行計劃中的一些要素：

id：查詢的序列化

select type

depent subquery：說明該查詢是子查詢中的第一個 Select, 依賴與外部查詢的結果集PRIMARY：子查詢的最外層查詢，注意不是主鍵查詢simple：除子查詢或者 UNION 之外的其它查詢table：訪問資料表的名稱，書寫 SQL 的人，需要明確此表是否是核心表、是否是大資料量表等

type 掃描方式

all：全表掃描const：讀常量，且最多隻有一條記錄匹配。由於是常量只需要讀一次index：全索引掃描eq_ref：最多隻有一條匹配結果 透過主鍵和唯一索引來訪問的range：索引範圍掃描possible_keys：該查詢可以利用到的索引有哪些key：最佳化器模組選擇用了哪個索引，有索引不一定就會用到，看執行計劃才知道用了哪個。key_len：索引長度rows：返回的行數extra：附加資訊，比如 using filesort---> 說明用了排序演算法filtered：列給出了一個百分比的值，這個百分比值和 rows 列的值一起使用，可以估計出那些將要和 QEP 中的前一個表進行連線的行的數目。前一個表就是指 id 列的值比當前表的 id 小的表。這一列只有在 EXPLAIN EXTENDED 語句中才會出現。3. 學會檢視效能損耗（cpu 消耗、io 消耗）

當發現有慢 Query 語句時，需要定位到底是哪裡慢，CPU 還是 IO 等：

mysql>set profiling=1;mysql>show profiles;mysql>show profile cpu,block io for query n;

三、查詢的基本原則1. 永遠用小結果集驅動大結果集（很多研發不會注重這些，都是習慣性的 left join）

做 join 查詢時，驅動表，一定是條件限定後記錄較少的表。

MySQL 的 join 只有一種演算法 nested loop 也就是程式中的 for 迴圈，透過巢狀迴圈實現，驅動結果集越大，所需要迴圈的次數越多，訪問被驅動表的次數也越多。降低 IO 同時降低 CPU。

2. 只查詢需要的列（研發人員早起可能為了專案大多是 select _，後期最佳化必須關注）

只查詢需要的列，可以讓 IO 降低，列和排序演算法也有關係。

3. 僅僅使用最有效的過濾條件

前提是用 a 條件 查詢出結果 用 b 條件查詢出結果，a、b 都用查詢出結果，這三次結果都一樣。

到底是用 a 條件還是 b 條件，還是兩個條件都限定，只能看執行計劃。

4. 儘量避免複雜的 join 和子查詢5. 儘量在索引列上完成排序和查詢在索引列上排序：索引列上是排好序的，不需要啟動額外的排序的演算法降低了 CPU 的損耗。在索引列上查詢：降低了 IO 的損耗建立索引，最佳化器模組並不一定會用，但可以 SQL 中加上 force index（強制走那個索引）.四：索引利弊及索引分類

萬事萬物都有利弊，一個東西的出現，比如會在不同場景下有好好壞，就看如何權衡。

好處：

透過索引列查詢資料，能夠提高資料檢索的效率，降低資料庫的 IO 成本。透過索引列對資料進行排序，降低資料排序的成本，降低了 CPU 的消耗。

壞處：

假設表 a 其中有列 column ca 給其建立索引 indxaca：每次更新 ca 的操作，都會調整因為更新所帶來的鍵值變化後的索引資訊，這樣就會增加 IO 損耗，索引列也是要佔用空間的，a 列資料的增多，indxaca 索引佔用的空間也會不斷增長。所以索引還會帶來儲存空間資源的消耗。五、索引分類b-tree 索引：根據平衡二叉樹演變來的hash 索引：hash 索引只能滿足 "="、"in" <> 查詢，不能支援範圍查詢hash 索引無法被利用進行排序操作hash 索引不能利用部分索引鍵查詢hash 索引不能避免表掃描full-text 索引：只有 myisam 儲存引擎支援 ---> 只有 char 、varchar、text 支援，但是在 MySQL 5.7，innodb 儲存引擎也支援啦。R-Tree 索引：主要解決空間資料檢索問題，極少使用。六、索引相關最佳化1. 如何判斷是否需要建立索引頻繁作為查詢條件的欄位應該建立索引。唯一性太差的欄位不適合單獨建立索引。比如該欄位重複上千萬；即使你建立了索引最佳化器模組是不會選擇使用的；會有極大的效能問題 有很多重複值，會帶來大量的隨機 IO 甚至是重複 IO。更新非常頻繁的欄位不適合建立索引：不僅僅更新表中的資料，還需要更新索引資料 IO 訪問增大。不會出現在 where 字句中的欄位不該建立索引。單鍵索引還是組合索引。2. MySQL 中索引的限制（下面的原理在 Oracle 當中基本都成立）是否用到了索引可以檢視執行計劃在任何索引列上做計算、函式、型別轉換（哪怕是自動的）都會使得索引失效而轉向全表掃描操作：不要在索引列上做任何操作因為可能為導致索引失效。MySQL 在使用不等於 (!= or <>) 的時候無法使用索引會導致全表掃描。is null ,is not null 也無法使用索引。join 語句中 join 條件欄位型別不一致的時候 MySQL 無法使用索引。模糊查詢的時候 (like 操作) 如果以萬用字元開頭 ('%abc...')MySQL 索引失效會變成全表掃描的操作。如果使用的是 hash 索引，在做非等值連線時候無法使用索引，會是全表掃描的操作。在 MySQL 中 BLOB 和 Text 型別的列只能建立字首索引。MyISAM 儲存引擎的話索引鍵長度總和不能超過 1000 位元組。（好像從 5.7 之後，大多預設 innodb 儲存引擎）當有唯一性索引和非唯一性索引都存在時，往往只會選擇唯一性索引。組合索引，查詢時組合索引第一列出現的時候會使用索引。3. 使用索引的一些建議對於單鍵索引，儘量選擇針對當前 Query 過濾性更好的索引。在選擇組合索引的時候，當前 Query 中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。在選擇組合索引的時候，儘量選擇可以能夠包含當前 Query 中的 where 字句中更多欄位的索引。儘可能透過分析統計資訊和調整 Query 的寫法來達到選擇合適索引的目的。減少透過使用 Hint 認為控制索引的選擇，如果使用 Hint 會使得後期維護成本比較高。

綜上所述，大致簡單明瞭的闡述了 MySQL 查詢最佳化一些相關的東西，至少對於中小型企業，可以作為研發人員的資料庫規範，避免後期遷移或擴容時的一些問題。一切相關問題可以後續讀者圈交流，謝謝大家耐心看完。

文章到這裡就結束了！

總結

2020馬上就要過去了，小編這裡整理一些 MySQL效能調優的面試題 微服務、SSM、 Redis、等技術真題資料，關注小編+轉發文章+私信【MySQL】獲取上述資料~ 重要的事情說三遍，轉發+轉發+轉發，一定要記得轉發哦！！！