MySQL查詢優(yōu)化的示例分析

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

一、優(yōu)化的思路和原則有哪些

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

相關(guān)免費學(xué)習(xí)推薦：mysql視頻教程

1、優(yōu)化更需要優(yōu)化的查詢

?高并發(fā)的低消耗（相對）的查詢 對整個系統(tǒng)影響遠(yuǎn)大于低并發(fā)高消耗的查詢。

2、定位優(yōu)化對象的性能瓶頸

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

3、明確優(yōu)化的目標(biāo)

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

4、從Explain入手

Explain能夠告訴你這個查詢在數(shù)據(jù)庫中是一個什么樣的執(zhí)行計劃來實現(xiàn)的。首先我們需要有個目標(biāo)，通過不斷調(diào)整嘗試，再借助Explain來驗證結(jié)果是否滿足自己的需求，直到得到預(yù)期的結(jié)果。

5、永遠(yuǎn)用小結(jié)果集驅(qū)動大結(jié)果集

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

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

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

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

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

8、避免復(fù)雜的join查詢

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

二、利用 Explain和Profiling

1、Explain使用

各種信息展示

2、Profiling使用

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

1、開啟profiling參數(shù) 1-開啟，0-關(guān)閉

#開啟profiling參數(shù) 1-開啟，0-關(guān)閉set profiling=1;SHOW VARIABLES LIKE '%profiling%';

2、然后執(zhí)行一條Query

3、獲取系統(tǒng)保存的profiling信息

show PROFILES;

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

show profile cpu, block io for QUERY 7;

三、合理利用索引

1、什么是索引

?簡單來說，在關(guān)系型數(shù)據(jù)庫中，索引是一種單獨的，物理的對數(shù)據(jù)庫表中一列或者多列的值進(jìn)行排序的一種存儲結(jié)構(gòu)。就像書的目錄，可以根據(jù)目錄中的頁碼快速找到需要的內(nèi)容。
?在MySQL中主要有四種類型索引，分別是：B-Tree索引，Hash索引，F(xiàn)ullText索引，R-Tree索引，下面主要說一下我們常用的B-Tree索引，其他索引可以自行查找資料。

2、索引的數(shù)據(jù)結(jié)構(gòu)

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

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

3、索引的利弊

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

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

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

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

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

6、索引的選擇

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

7、MySQL索引的限制

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

8、Join原理以及優(yōu)化

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

9、ORDER BY優(yōu)化

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

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

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

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

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

?第二種算法相較于第一種算法，主要就是減少了數(shù)據(jù)的二次訪問。在排序好后，不需要再次回到表中取數(shù)據(jù)，節(jié)省了IO操作。當(dāng)然第二種算法會消耗更多的內(nèi)存，一種典型的以空間換取時間的優(yōu)化方式。

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

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

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

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

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

四、最后

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

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