詳解MySQL單列索引和聯合索引

一、簡介

利用索引中的附加列，可以縮小搜尋的範圍，但使用一個具有兩列的索引不同於使用兩個單獨的索引。

聯合索引的結構與電話簿類似，人名由姓和名構成，電話簿首先按姓氏進行排序，然後按名字對有相同姓氏的人進行排序。如果您知道姓，電話簿將非常有用，如果您知道姓和名，電話簿則更為有用，但如果您只知道名不知道姓，電話簿將沒有用處。

所以說建立聯合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜尋或僅對前幾列執行搜尋時，聯合索引非常有用；僅對後面的任意列執行搜尋時，聯合索引則沒有用處。

二、單列索引

多個單列索引在多條件查詢時優化器會優先選擇最優索引策略，可能只用一個索引，也可能將多個索引全用上。但多個單列索引底層會建立多個B+索引樹，比較佔用空間，也會浪費一定搜尋效率，故如果只有多條件聯合查詢時最好建聯合索引。

三、最左字首原則

顧名思義是最左優先，以最左邊的為起點任何連續的索引都能匹配上，如果第一個欄位是範圍查詢需要單獨建一個索引，在建立聯合索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。這樣的話擴充套件性比較好，比如username經常需要作為查詢條件，而age不常使用，則需要把username放在聯合索引的第一位置，即最左邊。

1、建立複合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2、滿足複合索引的最左特性，哪怕只是部分，複合索引生效

SELECT * FROM employee WHERE NAME='哪吒程式設計'

3、沒有出現左邊的欄位，則不滿足最左特性，索引失效

SELECT * FROM employee WHERE salary=5000

4、複合索引全使用，按左側順序出現 name,salary，索引生效

SELECT * FROM employee WHERE NAME='哪吒程式設計' AND salary=5000

5、雖然違背了最左特性，但MySQL執行SQL時會進行優化，底層進行顛倒優化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒程式設計'

6、理由

複合索引也稱為聯合索引，當我們建立一個聯合索引的時候，如(k1,k2,k3)，相當於建立了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。

聯合索引不滿足最左原則，索引一般會失效。

四、同時存在聯合索引和單列索引（欄位有重複）

這個時候查詢mysql會怎麼用索引呢？

這個涉及到MySQL本身的查詢優化器策略，當一個表有多條索引可走時，mysql根據查詢語句的成本來選擇走哪條索引；

有人說where查詢是按照從左到右的順序，所以篩選力度大的條件儘量放在前面。網上百度過，確實有這種說法，但我親自測試過，MySQL執行優化器會對其進行優化，當不考慮索引時，where條件順序對效率沒有影響，真正有影響的是是否用到了索引！

五、聯合索引本質

當建立**（a, b, c）聯合索引時，相當於建立了（a）單列索引，（a, b）聯合索引以及（a, b, c）聯合索引，想要索引生效的話，只能使用者三種組合；當然，我們上面測試過，a, c組合也可以，但實際上只用到了a的索引，c並沒有用到。

六、索引失效

1、like子查詢，%放前面；

2、非空判斷 is not null；or語句前後沒有同時使用索引。當or左右查詢欄位只有一個是索引，該索引失效，只有當or左右查詢欄位均為索引時，才會生效；

3、or語句（前後都有索引才行，SQL優化要避免寫or語句）；

4、資料型別出現隱式轉化。如varchar不加單引號的話可能會自動轉換為int型，使索引無效，產生全表掃描。

七、其它知識點

1、需要加索引的欄位，要在where條件中

2、資料量少的欄位不需要加索引，因為建索引有一定開銷，如果資料量小則沒有必要建索引，速度範圍慢。

3、聯合索引比每個列建索引更有優勢，因為索引建立得越多就越佔磁碟空間，在更新資料的時候速度會越慢、另外建立多列索引時，順序也是需要注意的，應該講嚴格的索引放在前面，這樣篩選的力度會更大，效率更高。

八、MySQL儲存引擎簡介

1、InnoDB

支援事務處理，支援外來鍵，支援崩潰修復能力和並行控制。如果需要對事務的完整性要求比較高（比如銀行），要求實現並行控制（比如售票），那選擇InnoDB有很大的優勢。如果需要頻繁的更新、刪除操作的資料庫，也可以選擇InnoDB，因為支援事務的提交和回滾。

2、MyISAM

插入速度快，空間和記憶體使用比較低。如果表主要是用於插入新紀錄和讀取記錄，那麼選擇MyISAM能實現處理高效率。如果應用的完整性、並行要求比較低，也可以使用。

注意，同一個資料庫也可以使用多種儲存引擎的表。如果一個表要求比較高的事務處理，可以選擇InnoDB。這個資料庫中可以將查詢要求比較高的表選擇MyISAM儲存。如果該資料庫需要一個用於查詢的臨時表，可以選擇MEMORY儲存引擎。

九、索引結構（方法、演演算法）

在mysql中常用兩種索引結構(演演算法)BTree和Hash，兩種演演算法檢索方式不一樣，對查詢的作用也不一樣。

1、Hash

Hash索引的底層實現是由Hash表來實現的，非常適合以 key-value 的形式查詢，也就是單個key 查詢，或者說是等值查詢。

Hash 索引可以比較方便的提供等值查詢的場景,由於是一次定位資料，不像BTree索引需 要從根節點到枝節點，最後才能存取到頁節點這樣多次IO存取，所以檢索效率遠高於BTree索引。但是對於範圍查詢的話，就需要進行全表掃描了。

但為什麼我們使用BTree比使用Hash多呢？主要Hash本身由於其特殊性，也帶來了很多限制和弊端：

• Hash索引僅僅能滿足“=”,“IN”,“<=>”查詢，不能使用範圍查詢。
• 聯合索引中，Hash索引不能利用部分索引鍵查詢。 對於聯合索引中的多個列，Hash是要麼全部使用，要麼全部不使用，並不支援BTree支援的聯合索引的最優字首，也就是聯合索引的前面一個或幾個索引鍵進行查詢時，Hash索引無法被利用。
• Hash索引無法避免資料的排序操作 由於Hash索引中存放的是經過Hash計算之後的Hash值，而且Hash值的大小關係並不一定和Hash運算前的鍵值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算。
• Hash索引任何時候都不能避免表掃描 Hash索引是將索引鍵通過Hash運算之後，將Hash運算結果的Hash值和所對應的行指標資訊存放於一個Hash表中，由於不同索引鍵存在相同Hash值，所以即使滿足某個Hash鍵值的資料的記錄條數，也無法從Hash索引中直接完成查詢，還是要通過存取表中的實際資料進行比較，並得到相應的結果。
• Hash索引遇到大量Hash值相等的情況後效能並不一定會比BTree高 對於選擇性比較低的索引鍵，如果建立Hash索引，那麼將會存在大量記錄指標資訊存於同一個Hash值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料存取，而造成整體效能底下。

2、B+ Tree

B+Tree索引是最常用的mysql資料庫索引演演算法，因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用於like操作符，只要它的查詢條件是一個不以萬用字元開頭的常數，

例如：

select * from user where name like 'jack%'; select * from user where name like 'jac%k%';

如果一萬用字元開頭，或者沒有使用常數，則不會使用索引，

例如：

select * from user where name like '%jack'; select * from user where name like simply_name;

3、 B+/-Tree原理

在資料庫中，資料量相對較大，多路查詢樹顯然更加適合資料庫的應用場景，接下來我們就介紹這兩類多路查詢樹，畢竟作為程式設計師，心裡沒點B樹怎麼能行呢？

B樹：B樹就是B-樹，他有著如下的特性：

• B樹不同於二元樹，他們的一個節點可以儲存多個關鍵字和多個子樹指標，這就是B+樹的特點；
• 一個m階的B樹要求除了根節點以外，所有的非葉子子節點必須要有[m/2,m]個子樹；
• 根節點必須只能有兩個子樹，當然，如果只有根節點一個節點的情況存在；
• B樹是一個查詢二元樹，這點和二叉查詢樹很像，他都是越靠前的子樹越小，並且，同一個節點內，關鍵字按照大小排序；
• B樹的一個節點要求子樹的個數等於關鍵字的個數+1；

B+樹就是B樹的plus版

• B+樹將所有的查詢結果放在葉子節點中，這也就意味著查詢B+樹，就必須到葉子節點才能返回結果；
• B+樹每一個節點的關鍵字個數和子樹指標個數相同；
• B+樹的非葉子節點的每一個關鍵字對應一個指標，而關鍵字則是子樹的最大，或者最小值；

將上一節中的B-Tree優化，由於B+Tree的非葉子節點只儲存鍵值資訊，假設每個磁碟塊能儲存4個鍵值及指標資訊，則變成B+Tree後其結構如下圖所示：

通常在B+Tree上有兩個頭指標，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鏈式環結構。因此可以對B+Tree進行兩種查詢運算：一種是對於主鍵的範圍查詢和分頁查詢，另一種是從根節點開始，進行隨機查詢。

可能上面例子中只有22條資料記錄，看不出B+Tree的優點，下面做一個推算：

InnoDB儲存引擎中頁的大小為16KB，一般表的主鍵型別為INT（佔用4個位元組）或BIGINT（佔用8個位元組），指標型別也一般為4或8個位元組，也就是說一個頁（B+Tree中的一個節點）中大概儲存16KB/(8B+8B)=1K個鍵值（因為是估值，為方便計算，這裡的K取值為〖10〗^3）。
也就是說一個深度為3的B+Tree索引可以維護10^3 * 10^3 * 10^3 = 10億條記錄。

實際情況中每個節點可能不能填充滿，因此在資料庫中，B+Tree的高度一般都在2-4層。MySQL的InnoDB儲存引擎在設計時是將根節點常駐記憶體的，也就是說查詢某一鍵值的行記錄時最多隻需要1~3次磁碟I/O操作。

資料庫中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree範例圖在資料庫中的實現即為聚集索引，聚集索引的B+Tree中的葉子節點存放的是整張表的行記錄資料。輔助索引與聚集索引的區別在於輔助索引的葉子節點並不包含行記錄的全部資料，而是儲存相應行資料的聚集索引鍵，即主鍵。當通過輔助索引來查詢資料時，InnoDB儲存引擎會遍歷輔助索引找到主鍵，然後再通過主鍵在聚集索引中找到完整的行記錄資料。

到此這篇關於MySQL單列索引和聯合索引的文章就介紹到這了,更多相關MySQL單列索引和聯合索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！