MySQL查詢優化的示例分析

小編給大家分享一下MySQL查詢優化的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

一、優化的思路和原則有哪些

1、 優化更需要優化的查詢
2、 定位優化對象的性能瓶頸
3、 明確優化的目標
4、 從Explain入手
5、 多使用 profile
6、 永遠用小結果集驅動大結果集
7、 盡可能在索引中完成排序
8、 只取出自己需要的字段（Columns）
9、 僅僅使用最有效的過濾條件
10、盡可能避免復雜的join

相關免費學習推薦：mysql視頻教程

1、優化更需要優化的查詢

?高并發的低消耗（相對）的查詢 對整個系統影響遠大于低并發高消耗的查詢。

2、定位優化對象的性能瓶頸

?在拿到一條需要優化的查詢時，我們首先要判斷出這個查詢的瓶頸到底是IO還是CPU。到底是數據庫訪問消耗多還是數據的運算（如分組排序）消耗多。

3、明確優化的目標

?了解數據庫目前整體狀態，就能知道數據庫所能承受的最大壓力，也就是我們知道最悲觀狀況；
?要把握該查詢相關的數據庫對象信息，我們就能知道最理想和最糟糕狀態下需要消耗多少資源；
?要知道該查詢在應用系統中的地位，我們可以分析出改查詢可以占用系統資源的比例，也能夠知道該查詢的效率對客戶的體驗影響有多大。

4、從Explain入手

Explain能夠告訴你這個查詢在數據庫中是一個什么樣的執行計劃來實現的。首先我們需要有個目標，通過不斷調整嘗試，再借助Explain來驗證結果是否滿足自己的需求，直到得到預期的結果。

5、永遠用小結果集驅動大結果集

?很多人喜歡在SQL優化的時候說用“小表驅動大表”，這個說法是不嚴謹的。因為大表經過where條件過濾后返回的結果集并不一定就比小表所返回的結果集大，這個時候還用大表驅動小表，就會得到相反的性能效果。
?這樣的結果也非常容易理解，在 MySQL 中的 Join，只有 Nested Loop 一種 Join 方式，也就是MySQL 的 Join 都是通過嵌套循環來實現的。驅動結果集越大，所需要循環的此時就越多，那么被驅動表的訪問次數自然也就越多，而每次訪問被驅動表，即使需要的邏輯 IO 很少，循環次數多了，總量自然也不可能很小，而且每次循環都不能避免的需要消耗CPU，所以 CPU 運算量也會跟著增加。所以，如果我們僅僅以表的大小來作為驅動表的判斷依據，假若小表過濾后所剩下的結果集比大表多很多，結果就是需要的嵌套循環中帶來更多的循環次數，反之，所需要的循環次數就會更少，總體 IO 量和 CPU 運算量也會少。而且，就算是非 Nested Loop 的 Join 算法，如 Oracle 中的 Hash Join，同樣是小結果集驅動大的結果集是最優的選擇。
?所以，在優化 Join Query 的時候，最基本的原則就是“小結果集驅動大結果集”，通過這個原則來減少嵌套循環中的循環次數，達到減少 IO 總量以及 CPU 運算的次數。盡可能在索引中完成排序

6、只取出自己需要的字段（Columns）

?對于任何查詢，返回的數據都是需要通過網絡數據包傳輸給客戶端，如果取出的Column越多，需要傳輸的數據量自然會越大，不論從網絡帶寬還是網絡傳輸緩沖區來看，都是一種浪費。

7、僅僅使用最有效的過濾條件

?舉個例子一個用戶表user有id和nick_name等字段，索引是id和nike_name兩個索引，下面是兩個查詢語句

#1
select * from user where id = 1 and nick_name = 'zs';
#2
selet * from user where id = 1

?兩個查詢得到結果是一樣的，但是第一個語句用到的索引占用空間是比第二個語句大很多的。占用空間大也代表著要讀取的數據量也更多。，也就是說2的查詢語句才是最優查詢。

8、避免復雜的join查詢

?我們的查詢語句所涉及到的表越多，所需要鎖定的資源就越多。也就是說，越復雜的 Join 語句，所需要鎖定的資源也就越多，所阻塞的其他線程也就越多。相反，如果我們將比較復雜的查詢語句分拆成多個較為簡單的查詢語句分步執行，每次鎖定的資源也就會少很多，所阻塞的其他線程也要少一些。
?可能很多人會有疑問，將復雜 Join 語句分拆成多個簡單的查詢語句之后，那不是我們的網絡交互就會更多了嗎？網絡延時方面的總體消耗也就更大了啊，完成整個查詢的時間不是反而更長了嗎？是的，這種情況是可能存在，但也并不是肯定就會如此。我們可以再分析一下，一個復雜的查詢語句在執行的時候，所需要鎖定的資源比較多，可能被別人阻塞的概率也就更大，如果是一個簡單的查詢，由于需要鎖定的資源較少，被阻塞的概率也會小很多。所以 較為復雜的連接查詢也有可能在執行之前被阻塞而浪費更多的時間。而且我們的數據庫所服務的并不是單單這一個查詢請求，還有很多很多其他的請求，在高并發的系統中，犧牲單個查詢的短暫響應時間而提高整體處理能力也是非常值得的。優化本身就是一門平衡與取舍的藝術，只有懂得取舍，平衡整體，才能讓系統更優。

二、利用 Explain和Profiling

1、Explain使用

各種信息展示

2、Profiling使用

該工具可以獲取一條Query在整個執行過程中多種資源消耗情況，如CPU,IO,IPC,SWAP等，以及發生PAGE FAULTS, CONTEXT SWITCHE等等，同時還能得到該Query執行過程中MySQL所調用的各個函數在源文件中的位置。

1、開啟profiling參數 1-開啟，0-關閉

#開啟profiling參數 1-開啟，0-關閉set profiling=1;SHOW VARIABLES LIKE '%profiling%';

2、然后執行一條Query

3、獲取系統保存的profiling信息

show PROFILES;

4、通過QUERY_ID獲取profile的詳細信息（下面以獲取CPU和IO為例）

show profile cpu, block io for QUERY 7;

三、合理利用索引

1、什么是索引

?簡單來說，在關系型數據庫中，索引是一種單獨的，物理的對數據庫表中一列或者多列的值進行排序的一種存儲結構。就像書的目錄，可以根據目錄中的頁碼快速找到需要的內容。
?在MySQL中主要有四種類型索引，分別是：B-Tree索引，Hash索引，FullText索引，R-Tree索引，下面主要說一下我們常用的B-Tree索引，其他索引可以自行查找資料。

2、索引的數據結構

?一般來說，MySQL中的B-Tree索引的物理文件大多數都是以平衡樹的結構來存儲的，也就是所有實際需要存儲的數據都存儲于樹的葉子節點，二到任何一個葉子節點的最短路徑的長度都是完全相同的。MySQL中的存儲引擎也會稍作改造，比如Innodb存儲引擎的B-Tree索引實際上使用的存儲結構是B+Tree,在每個葉子節點存儲了索引鍵相關信息之外，還存儲了指向相鄰的葉子節點的指針信息，這是為了加快檢索多個相鄰的葉子節點的效率。
?在Innodb中，存在兩種形式的索引，一種是聚簇形式的主鍵索引，另外一種形式是和其他存儲引擎（如MyISAM）存放形式基本相同的普通B-Tree索引，這種索引在Innodb存儲引擎中被稱作二級索引。

?圖示中左邊為 Clustered 形式存放的 Primary Key，右側則為普通的 B-Tree 索引。兩種索引在根節點和 分支節點方面都還是完全一樣的。而 葉子節點就出現差異了。在主鍵索引中，葉子結點存放的是表的實際數據，不僅僅包括主鍵字段的數據，還包括其他字段的數據，整個數據以主鍵值有序的排列。而二級索引則和其他普通的 B-Tree 索引沒有太大的差異，只是在葉子結點除了存放索引鍵的相關信息外，還存放了 Innodb 的主鍵值。
?所以，在 Innodb 中如果通過主鍵來訪問數據效率是非常高的，而如果是通過二級索引來訪問數據的話，Innodb 首先通過二級索引的相關信息，通過相應的索引鍵檢索到葉子節點之后，需要再通過葉子節點中存放的主鍵值再通過主鍵索引來獲取相應的數據行。
?MyISAM 存儲引擎的主鍵索引和非主鍵索引差別很小，只不過是主鍵索引的索引鍵是一個唯一且非空的鍵而已。而且 MyISAM 存儲引擎的索引和 Innodb 的二級索引的存儲結構也基本相同，主要的區別只是 MyISAM 存儲引擎在葉子節點上面除了存放索引鍵信息之外，再存放能直接定位MyISAM 數據文件中相應的數據行的信息（如 Row Number），但并不會存放主鍵的鍵值信息。

3、索引的利弊

優點： 提高數據的檢索速度，降低數據庫的IO成本；
缺點：查詢需要更新索引信息帶來額外的資源消耗，索引還會占用額外的存儲空間

4、如何判斷是否需要建立索引

?上面說了索引的利弊，我們知道索引并不是越多越好，索引也會帶來副作用。那么我們該怎么判斷是否需要建立索引呢？
1、 較頻繁的作為查詢條件的字段應該創建索引；
2、更新頻繁的字段不適合建立索引；
3、唯一性太差的不適合創建索引，如狀態字段；
4、不出現在where中的字段不適合創建索引；

5、單索引還是組合索引？

?在一般的應用場景，只要不是其中某個過濾字段在大多數場景下都能過濾90%以上的數據，而且其他的過濾字段會頻繁更新，我一般更傾向于創建組合索引，尤其是在并發量較高的場景下更是如此。因為并發量搞的時候，即使我們為每個查詢節省很少IO消耗，但因為執行量非常大，所節省的資源總量還是很大的。
?但是我們創建組合索引并不是說查詢條件中的所有字段都要放在一個索引中，我們應該讓一個索引被多個查詢所利用，盡量減少索引的數量，以此來減少更新的成本和存儲成本。
?MySQL為我們提供了一個減少優化索引自身的功能，那就是“前綴索引”。也就是我們可以僅僅使用某個字段的前面部分內容作為索引鍵來索引該字段,減少索引所占用的空間和提高索引的訪問效率。當然前綴索引只適合前綴比較隨機重復很少的字段。

6、索引的選擇

1、對于單鍵索引 ，盡量針對當前查詢過濾最好的索引；
2、在選擇組合索引的時候，當前查詢中過濾性最好的字段在索引字段順序中排列越靠前越好；
3、在選擇組合索引的時候，盡量選擇可以能夠包含當前查詢的where字句中更多字段的索引；
4、盡可能通過分析統計信息和調整查詢的寫法來達到選擇合適的的索引來減少通過人為Hint控制索引的選擇，以為這樣后期維護成本會很高。

7、MySQL索引的限制

1、MyISAM存儲引擎索引鍵長總和不能超過1000字節；
2、BLOB和TEXT類型字段只能創建前綴索引；
3、MySQL不支持函數索引；
4、使用 != 或者<>時候，MySQL索引無法使用；
5、過濾字段使用函數運算后，MySQL索引無法使用；
6、jion語句中近字段類型不一致的時候，MySQL索引無法使用；
7、使用like如果是前匹配（如：’%aaa’），MySQL索引無法使用；
8、使用非等值查詢的時候，MySQL無法使用HASH索引；
9、字符類型是數字的時候要使用 =‘1’ 不可以直接使用 = 1；
10、不要使用or可以用in代替或者 union all；

8、Join原理以及優化

Join原理：在MySQL中，只有一種join算法，就是大名鼎鼎的嵌套循環，實際上就是通過驅動表的結果集作為循環基礎數據，然后一條一條的通過該結果集中的數據作為過濾條件到下一個表中查詢數據，然后合并結果。如果還有近參與，再通過前面的近結果集作為循環基礎數據，再循環遍歷，如此往復。
優化：
1、盡可能減少Join語句中的循環總次數（還記得前面說過的小結果集驅動大結果集嗎）；
2、優先優化內層循環；
3、保證Join語句中被驅動表上的Join條件字段已經被索引；
4、當無法保證被驅動表的Join條件字段被索引且內存資源充足條件下，不要吝嗇Join buffer的設置（join buffer只會在 All,index，range才能夠用的上）;

9、ORDER BY優化

在MySQL中，ORDER BY的實現只有兩種類型：
?1、通過有序的索引直接取得有序的數據，這樣不用進行任何排序操作即可得到客戶端要求的有序數據；

?2、通過MySQL排序算法將存儲的引擎中返回的數據進行排序然后再將排序后的數據返回給客戶端。

利用索引排序是最佳的方法，但是如果沒有索引林勇的時候，MySQL主要兩種算法實現：

?1、取出滿足過濾條件的用于排序條件的字段以及可以直接定位到行數據的行指針信息，在 Sort Buffer 中進行實際的排序操作，然后利用排好序之后的數據根據行指針信息返回表中取得客戶端請求的其他字段的數據，再返回給客戶端；

?2、根據過濾條件一次取出排序字段以及客戶端請求的所有其他字段的數據，并將不需要排序的字段存放在一塊內存區域中，然后在 Sort Buffer 中將排序字段和行指針信息進行排序，最后再利用排序后的行指針與存放在內存區域中和其他字段一起的行指針信息進行匹配合并結果集，再按照順序返回給客戶端。

?第二種算法相較于第一種算法，主要就是減少了數據的二次訪問。在排序好后，不需要再次回到表中取數據，節省了IO操作。當然第二種算法會消耗更多的內存，一種典型的以空間換取時間的優化方式。

?對于多表Join排序是先通過一個臨時表將之前 Join 的結果集存放入臨時表之后再將臨時表的數據取到 Sort Buffer 中進行操作。

對于非索引排序的時候，盡量選擇第二種算法來進行排序，手段有：

?1、加大max_length_for_sort_data參數設置：
?MySQL決定使用哪個算法是通過參數max_length_for_sort_data來決定的，當我們返回字段的最大長度小于這個參數時候，MySQL就會選擇第二中算法，相反則第一種算法。所以在有充足內存情況下，加大這個參數值可以讓MySQL選擇第二種算法；

?2、減少不必要的返回字段
?上面一樣的道理，字段少了，就會盡量小于max_length_for_sort_data參數；

?3、增大sort_buffer_size參數設置：
?增大 sort_buffer_size 并不是為了讓 MySQL 可以選擇改進版的排序算法，而是為了讓 MySQL可以盡量減少在排序過程中對需要排序的數據進行分段，因為這樣會造成 MySQL 不得不使用臨時表來進行交換排序。

四、最后

?調優其實是件很難的事情，調優也不限于上面的查詢調優。諸如表的設計優化，數據庫參數的調優，應用程序調優（減少循環操作數據庫，批量新增；數據庫連接池；緩存；）等等。當然還有很多調優技巧只有在實際實踐中才能真正體會。只有自己以理論為基礎，事實為依據，不斷嘗試去提升自己，才能成為一個真正的調優高手。

看完了這篇文章，相信你對“MySQL查詢優化的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！