溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“MySQL中的InnoDB索引優化方法是什么”，在日常操作中，相信很多人在MySQL中的InnoDB索引優化方法是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL中的InnoDB索引優化方法是什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
半雙工通信：MySQL的數據傳輸采用的是半雙工通信，同一時間要么是客戶端向服務端發送數據，要么是服務端向客戶端發送數據，這兩個動作不能同時發生。MySQL對客戶端發送數據也有要求，一次發送所有數據，等服務端響應后才能發送下次數據。
順序讀寫與隨機讀寫：數據庫數據都是要落盤的，由于磁盤物理結構，尋道時間過長，故順序讀寫比隨機讀寫效率高很多。如果不太懂，可以想想平時坐車，你是坐一趟車直達（順序讀寫）好呢？還是各種換乘（隨機讀寫）好呢？
結果緩存：MySQL對查詢的結果是支持緩存的，默認關閉。（提示一下，對于頻繁更新的數據盡量不要使用MySQL本身的緩存，緩存失效造成更多性能浪費）
SQL查詢流程：客戶端發送查詢SQL，通過數據傳輸到服務端，優先查詢結果緩存，如果未命中則先后通過解析器、預處理器、優化器、執行計劃、執行引擎、存儲引擎后得到結果放入內存中并返回給客戶端。（后續專門寫一篇文章介紹下）
索引（Index）：幫助MySQL高效獲取數據的數據結構，MySQL中大部分索引都使用多路平衡查找樹。
在對索引優化之前，需要知道索引的具體結構。根據不同的存儲引擎數據的存儲結構也不一樣，存儲引擎主要使用的有InnoDB、MyISAM。
每個表都有一個聚簇索引：
主鍵存在時以主鍵為聚簇索引，
主鍵不存在時，以第一個不含有null值的唯一索引作為聚簇索引
以上索引都不存在時，MySQL會創建一個隱藏字段rowid的聚簇索引。
每個表的數據按照聚簇索引而聚集在一起形成B+樹。其中在最后的葉子節點掛載非索引數據，葉子節點之間存在有序的指針。
聚簇索引圖示1
表中除了聚簇索引外其他非聚簇索引成為二級索引或者輔助索引，輔助索引中的葉子節點不再掛載非索引數據，而是存儲聚簇索引的索引值
輔助索引圖示2
特殊的輔助索引：聯合索引，B+樹的節點存儲的不是一個列數據，而是多個列數據，按照定義的順序構成一個節點。
聯合索引圖示3
在對B+樹存儲結構有一定了解下，從實用角度來分析如何優化SQL。這也是SQL優化器要做的功能。
首先了解B+樹是有序多路平衡查找樹，也就是插入之前需要排序的，為了平衡還需要拆頁、旋轉等操作。
先說順序本身，順序是比較之后的結果，如何比較？MySQL在建立數據的時候必須指定編碼格式和排序方式，這時便有了比較順序的方式。無論主鍵是何種類型，數字、字符串都會轉換編碼,然后排序。主鍵的可比較性決定了主鍵的效率
再說順序意義，仔細觀察聚簇索引圖示1的葉子節點，也就是最后一層，這是一個有序的頁（圖示中放在一塊的數據稱為一頁）列表。每次插入都是先確定主鍵的位置，然后才記錄數據的，葉子節點的是否有序插入決定了主鍵的效率。主鍵的有序性決定的磁盤讀寫的有序性（順序寫比隨機寫效率高很多）。
以上兩點足以說明MySQL中主鍵的有序性的重要性。所以選擇主鍵優先選擇有序主鍵，自增主鍵就是有序主鍵。當然也不要這么絕對，當數據量過小時這點效率差距是基本看不出來的。
順便說下經常被問的UUID主鍵和自增主鍵的選擇，在數據量過小或者業務剛性需求時，二者皆可。在數據量過大時，推薦自增主鍵，不僅僅因為有序性，還因為字符串的存儲空間是大于整型的存儲空間的。
前面說順序，這里就使用下順序，索引樹的葉子節點本身就是有序的，在查詢時order by越匹配該順序則查詢效率越高。因此在排序時，盡量按照所使用的索引進行排序，也因此全表查詢時默認是主鍵排序。如果查詢條件中涉及到了其他索引則默認以首個索引的順序為主。如果不確定使用了什么索引，則應該主動指定排序列
同樣基于以上，推薦在頻繁排序或者分組的列上建立索引
首先先明確一點，索引樹中數據分為2種，1：索引樹非葉子節點存儲的是索引數據，2：索引葉子節點存儲的是索引數據和表非索引數據。
其次也要明確：聚簇索引是一顆存有全表數據的索引樹，每個表都是必有的。其他輔助索引每建立一個就會多一顆索引樹，只是和圖示一樣葉子節點不存儲數據
因此獲取SQL查詢數據應該從2個角度分析
從不同索引樹角度
查詢聚簇索引樹
查詢非聚簇索引樹
從查詢數據所在位置角度
查詢索引樹中非葉子節點數據（即索引數據），不查其他數據
查詢葉子節點中的數據（包含索引和非索引數據）。
SQL索引優化注重點之一在數據所處位置
如果查詢的數據全部在索引樹非葉子節點（即查詢索引列）時，此時效率是最高的，因為節點的有序性，通過高效算法能很快找到數據完成查詢，這種查詢稱為覆蓋索引查詢。這點告訴使用者：盡量不要使用select *,同時也應該知道，如果一個表列全是索引，那一定會走索引。（別再說什么 not null 、!= 一定不走索引的問題了）
如果查詢的數據不在索引樹非葉子節點（即查詢非索引列）時，注意此時SQL優化器很有可能會優化書寫的SQL，導致最終執行的SQL和客戶端傳輸的SQL不一致。
先說下此時正規的數據查找流程：
如果查詢條件存在索引，則使用第一個索引條件列（優化后的）去首次加載數據行
索引為聚簇索引，則在聚簇索引樹上，根據算法查詢到索引所處的葉子節點位置，把該位置的對應數據獲取即可
索引為非聚簇索引，則在非聚簇索引樹上，根據算法查詢引所處的葉子節點位置，獲取到該位置上的聚簇索引值，然后拿到該值在聚簇索引樹上定位其位置，再把聚簇索引樹葉子節點上對應的數據獲取即可。從非聚簇索引樹再到聚簇索引樹的過程稱為回表。
如果查詢條件不存在索引
由于沒有索引，所以會去聚簇索引樹的非葉子節點數據處進行全表掃描，逐個匹配，直至掃描完畢獲取到數據返回
從聚簇索引中獲取到的數據行，會加載到內存中，然后在進行
where其他條件的過濾，最后才返回過濾后的數據，
這點告訴使用者：where條件中首個條件應盡量精確匹配（例如主鍵、高離散度索引列）數據。
索引樹中每個頁存儲的數據個數是固定的，例如4個，當該頁新增數據時，如果數據已滿4個，則需要分裂為2個頁，每頁還是4個來保證。
節點移除時，索引樹會進行旋轉來達到平衡。具體流程可自行查詢平衡樹。這里只需要知道：索引樹調整很浪費時間，開銷很大。
因此頻繁更新的列，不適合作為主鍵或者索引
問個索引優化，都說最左匹配原則，可是否知道為什么是最左匹配，如何匹配？
在上面說順序時提到了如何排序，這里如何匹配也是類似，例如abc和abd如何匹配，這里說下通俗理解（不一定是實現），把這兩個字符逐個通過編碼、排序獲取排序值，假設a編碼后排序值為 32，b 編碼后排序值為33，c 編碼后排序值為 34，設d編碼后排序值為35;匹配時先對a比較==，如果不等則不必再進行匹配，如果相等則比較b、然后c，最終發現35>34于是結果就是不匹配。第一步的a的匹配就是最左開始匹配原則。
最左匹配的應用：
like匹配，只有左邊字符確定才能支持最左匹配原則，即不支持%xxx匹配。
聯合索引匹配，聯合索引中非葉子節點中數據存儲是安裝聯合索引定義的順序組合成一個節點的，例如
index0,index1,index2一旦順序不對則不能進行匹配。但是記住一點：組合后的索引節點是按照一個節點在索引中排序的，也就是哪怕匹配了一個索引也是能提高效率的。例如：聚合索引a,b,c查詢條件where a=1 and c=1，此時a=1是能走聚合索引的，但是c就不行了，此時等同于%c。這里也有個坑，會問這個查詢是否走索引，回答是走索引（部分走也是走）。還有查詢條件中遇到范圍查詢（like != > < 等）則會中止后續匹配。直接理解為聯合索引就是一個拼接后的字符列索引，遇到范圍查詢則會導致開銷指數級變大。
在索聚簇索引樹查詢數據行之前，匹配的數據行越少，越精確則查詢效率越高。ICP（index_condition_pushdown）技術就是優化的這部分，旨在盡量減少數據行加載到內存中。在InnoDB引擎中ICP只支持聯合索引，因為聚簇索引能直接鎖定要查詢的數據行，無法繼續再篩選（聚簇索引只有一個索引），而聯合索引則是至少2個索引，在第一個索引匹配的行數和后續其他聯合索引匹配的行數處理后，再回表到聚簇索引樹中查詢數據，這樣聚簇索引樹中的數據行就會縮減，從而提高效率。ICP技術是默認開啟的。explain提示信息為：Using index condition，設置參數為：index_condition_pushdown
ICP應用：
盡量建立聚合索引而不是多個單索引，where條件后面按照聚合索引列作為條件
MySQL函數的contract，date_format，count等
函數區分為2種，1：該函數可以得到確定的結果，這種稱為確定性函數，2：該函數不能得到確定的結果，具體的結果由參數決定，這種稱為不確定性函數
計算表達式，1+1、2*3等
函數和表達式位置分為條件左側和右側，條件左側即條件列，右側為查詢條件。
對于右側：
確定性函數大部分可以使用索引，例如： contract、pow
不確定性函數基本不能使用索引，例如： rand,uuid
對于左側：
一定導致索引失效,而且任何對左側索引列的處理都會導致索引失效，包含編碼格式、函數、表達式計算等。 例如：where age + 10 = 30 應寫為where age = 30 + 10這種寫法沒問題，MySQL會自動優化為where age = 40
MySQL支持索引列的null查詢，且支持is not null和is null,屬于范圍查詢。出現索引失效的一般都是因為回表開銷過大導致的，畢竟數據為null為少數或者多數。
非空約束列的is null查詢不會走索引，因為有比索引更高效的查詢方式。
MySQL的優化器是基于開銷的，它對客戶端的SQL會解析出多條同樣效果的SQL，最終選擇的是開銷最小的SQL。基本所有的優化都基于此。
例如：在性別sex列表建立索引，然而sex值只有0和1。如果表中數據全是男或者全是女，優化器會覺得全表掃描會由于索引查詢，畢竟不用從索引樹的根節點逐個比較。
開銷大小對索引而已外觀表現為索引列數據的離散度，離散度相當于count(distinct(column_name))/count(*)。對于這種離散度低的列不建議建立索引
例如：聚合索引a,b,c，在查詢條件中使用where a=1 or d=1,這里d為非索引列，此時會導致匹配d時必須全表掃描，既然都全表掃描了說明索引樹中的數據行都加載到了內存，因此沒必要通過索引去過濾，定位聚簇索引樹的位置了，于是最終采用的是全表掃描而不會走索引。注：如果表所有列都是索引則全表掃描也是走索引樹掃描。覆蓋索引優先級比全表掃描優先級高
例如：聚合索引a,b,c，在查詢條件中書寫順序where a=1 and b=1 and c=1和書寫順序where c=1 and a=1 and b=1不影響索引使用，SQL優化器會分析出最小的開銷，就是按照索引定義順序來糾正查詢條件。符合最左匹配原則才有意義。
MySQL優化點很多，只是列一些常見的優化
字符串類型的列一定要加單引號'',否則會隱式轉換為數字，導致索引失效
負向索引（<> 、!= 、not in）有可能使用索引，但是大部分不會使用索引，這要基于SQL優化器優化了。例如對于索引列a,如果值全是1（離散度過低），此時<>1 、!=、not in（1） 都是會走索引的。注意不走索引便意味著全表掃描。
對于負向索引（not like） 一定不走索引。
當SQL優化器優化后不是想要的SQL時，可以指定強制索引（force index(idx_name)）來讓SQL使用指定的索引查詢，不一定會采用，只有多個執行計劃中有這個索引的執行計劃時才有效（畢竟強制一個不查詢的索引也沒意義）。
前面提到MySQL是半雙工通信，客戶端需要等待服務端處理好結果且返回之后才能繼續。如果查詢結果很大，會導致后續請求阻塞。故善用limit，不要select *，也注意insert into xxx select xxx這個select結果也是越少越好
子查詢會導致多次查詢數據行，浪費IO。個人建議即使多次請求也比子查詢好。不僅能看懂，效率也不一定降低。
SQL越精確，在進行查找時讀取的數據行越少，查詢效率越高。
基于磁盤性能，隨機讀取效率差，索引樹查詢開銷大，不建議
能使用常量查詢的盡量使用常量查詢
例如:只是確認是否存在，沒必要查詢其他字段
select 1 from user where name='xx' limit 1
例如 非空約束列查詢is null
雖然說大部分數據庫和線上庫都會統一時間，但是防止埋坑，而且數據庫自身的效率會高點，當然這點性能沒什么影響。如果沒必要還是建議使用數據庫自身的時間函數來填充時間字段。
update user set modify_time=now()
針對同列的IN 和 OR 如果查詢字段是索引列，則二者性能基本一致，否則In的效率隨著數據量增大會比OR越來越高，
針對IN，MySQL會估算in范圍的條數開銷，in的范圍越大開銷越大，特別是不是唯一列的開銷更大，此時可以考慮join等方式是否可以試下，畢竟in其實也是等值比較，join連接條件也是等值比較。當然也可以考慮exists
針對不同列的OR,例如where a=1 or b=1，會被優化為union，盡量主動書寫union
select a,b from source where a=0 or b=2
推薦寫法
select a,b from source where a=0 union select a,b from source where b=2
使用IN時要保證IN中的總數據量小且in之后的數據量也很小才能操作其效率高。Exists則是exists語句中的數據量大，但是匹配后小則效率高。
在考慮in和exists時，思考下哪個遍歷的少，哪個效率就高。
平時常見的索引優化暫時就羅列這些，一旦想起來再來補充吧！
經過數據驗證，like在千萬級數據時效率很差，反而沒有instr函數效率高。
select xxx from xxx where xxx like '%abc%'
不如走索引的以下語句好
select xxx from xxx where xxx like 'abc%'
走索引的like不如以下語句好
select xxx from xxx where instr( xxx, 'abc' ) > 0
到此，關于“MySQL中的InnoDB索引優化方法是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
