一、簡介

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

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

所以說創(chuàng)建聯(lián)合索引時，應該仔細考慮列的順序。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時，聯(lián)合索引非常有用；僅對后面的任意列執(zhí)行搜索時，聯(lián)合索引則沒有用處。

二、單列索引

多個單列索引在多條件查詢時優(yōu)化器會優(yōu)先選擇最優(yōu)索引策略，可能只用一個索引，也可能將多個索引全用上。但多個單列索引底層會創(chuàng)建多個B+索引樹，比較占用空間，也會浪費一定搜索效率，故如果只有多條件聯(lián)合查詢時最好建聯(lián)合索引。

三、最左前綴原則

顧名思義是最左優(yōu)先，以最左邊的為起點任何連續(xù)的索引都能匹配上，如果第一個字段是范圍查詢需要單獨建一個索引，在創(chuàng)建聯(lián)合索引時，要根據(jù)業(yè)務需求，where子句中使用最頻繁的一列放在最左邊。這樣的話擴展性比較好，比如username經(jīng)常需要作為查詢條件，而age不常使用，則需要把username放在聯(lián)合索引的第一位置，即最左邊。

1、創(chuàng)建復合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

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

SELECT * FROM employee WHERE NAME='哪吒編程'

3、沒有出現(xiàn)左邊的字段，則不滿足最左特性，索引失效

SELECT * FROM employee WHERE salary=5000

4、復合索引全使用，按左側(cè)順序出現(xiàn) name,salary，索引生效

SELECT * FROM employee WHERE NAME='哪吒編程' AND salary=5000

5、雖然違背了最左特性，但MySQL執(zhí)行SQL時會進行優(yōu)化，底層進行顛倒優(yōu)化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒編程'

6、理由

復合索引也稱為聯(lián)合索引，當我們創(chuàng)建一個聯(lián)合索引的時候，如(k1,k2,k3)，相當于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。

聯(lián)合索引不滿足最左原則，索引一般會失效。

四、同時存在聯(lián)合索引和單列索引（字段有重復）

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

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

有人說where查詢是按照從左到右的順序，所以篩選力度大的條件盡量放在前面。網(wǎng)上百度過，確實有這種說法，但我親自測試過，MySQL執(zhí)行優(yōu)化器會對其進行優(yōu)化，當不考慮索引時，where條件順序?qū)π蕸]有影響，真正有影響的是是否用到了索引！

五、聯(lián)合索引本質(zhì)

當創(chuàng)建**（a, b, c）聯(lián)合索引時，相當于創(chuàng)建了（a）單列索引，（a, b）聯(lián)合索引以及（a, b, c）聯(lián)合索引，想要索引生效的話，只能使用者三種組合；當然，我們上面測試過，a, c組合也可以，但實際上只用到了a的索引，c并沒有用到。

六、索引失效

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

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

3、or語句（前后都有索引才行，SQL優(yōu)化要避免寫or語句）；

4、數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)化。如varchar不加單引號的話可能會自動轉(zhuǎn)換為int型，使索引無效，產(chǎn)生全表掃描。

七、其它知識點

1、需要加索引的字段，要在where條件中

2、數(shù)據(jù)量少的字段不需要加索引，因為建索引有一定開銷，如果數(shù)據(jù)量小則沒有必要建索引，速度范圍慢。

3、聯(lián)合索引比每個列建索引更有優(yōu)勢，因為索引建立得越多就越占磁盤空間，在更新數(shù)據(jù)的時候速度會越慢、另外建立多列索引時，順序也是需要注意的，應該講嚴格的索引放在前面，這樣篩選的力度會更大，效率更高。

八、MySQL存儲引擎簡介

1、InnoDB

支持事務處理，支持外鍵，支持崩潰修復能力和并發(fā)控制。如果需要對事務的完整性要求比較高（比如銀行），要求實現(xiàn)并發(fā)控制（比如售票），那選擇InnoDB有很大的優(yōu)勢。如果需要頻繁的更新、刪除操作的數(shù)據(jù)庫，也可以選擇InnoDB，因為支持事務的提交和回滾。

2、MyISAM

插入速度快，空間和內(nèi)存使用比較低。如果表主要是用于插入新紀錄和讀取記錄，那么選擇MyISAM能實現(xiàn)處理高效率。如果應用的完整性、并發(fā)要求比較低，也可以使用。

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

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

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

1、Hash

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

Hash 索引可以比較方便的提供等值查詢的場景,由于是一次定位數(shù)據(jù)，不像BTree索引需 要從根節(jié)點到枝節(jié)點，最后才能訪問到頁節(jié)點這樣多次IO訪問，所以檢索效率遠高于BTree索引。但是對于范圍查詢的話，就需要進行全表掃描了。

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

• Hash索引僅僅能滿足“=”,“IN”,“<=>”查詢，不能使用范圍查詢。
• 聯(lián)合索引中，Hash索引不能利用部分索引鍵查詢。 對于聯(lián)合索引中的多個列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的聯(lián)合索引的最優(yōu)前綴，也就是聯(lián)合索引的前面一個或幾個索引鍵進行查詢時，Hash索引無法被利用。
• Hash索引無法避免數(shù)據(jù)的排序操作 由于Hash索引中存放的是經(jīng)過Hash計算之后的Hash值，而且Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算。
• Hash索引任何時候都不能避免表掃描 Hash索引是將索引鍵通過Hash運算之后，將Hash運算結果的Hash值和所對應的行指針信息存放于一個Hash表中，由于不同索引鍵存在相同Hash值，所以即使?jié)M足某個Hash鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從Hash索引中直接完成查詢，還是要通過訪問表中的實際數(shù)據(jù)進行比較，并得到相應的結果。
• Hash索引遇到大量Hash值相等的情況后性能并不一定會比BTree高 對于選擇性比較低的索引鍵，如果創(chuàng)建Hash索引，那么將會存在大量記錄指針信息存于同一個Hash值相關聯(lián)。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數(shù)據(jù)訪問，而造成整體性能底下。

2、B+ Tree

B+Tree索引是最常用的mysql數(shù)據(jù)庫索引算法，因為它不僅可以被用在=,>,>=,<,<=和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原理

在數(shù)據(jù)庫中，數(shù)據(jù)量相對較大，多路查找樹顯然更加適合數(shù)據(jù)庫的應用場景，接下來我們就介紹這兩類多路查找樹，畢竟作為程序員，心里沒點B樹怎么能行呢？

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

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

B+樹就是B樹的plus版

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

將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結構如下圖所示：

通常在B+Tree上有兩個頭指針，一個指向根節(jié)點，另一個指向關鍵字最小的葉子節(jié)點，而且所有葉子節(jié)點（即數(shù)據(jù)節(jié)點）之間是一種鏈式環(huán)結構。因此可以對B+Tree進行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找，另一種是從根節(jié)點開始，進行隨機查找。

可能上面例子中只有22條數(shù)據(jù)記錄，看不出B+Tree的優(yōu)點，下面做一個推算：

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

實際情況中每個節(jié)點可能不能填充滿，因此在數(shù)據(jù)庫中，B+Tree的高度一般都在2-4層。MySQL的InnoDB存儲引擎在設計時是將根節(jié)點常駐內(nèi)存的，也就是說查找某一鍵值的行記錄時最多只需要1~3次磁盤I/O操作。

數(shù)據(jù)庫中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree示例圖在數(shù)據(jù)庫中的實現(xiàn)即為聚集索引，聚集索引的B+Tree中的葉子節(jié)點存放的是整張表的行記錄數(shù)據(jù)。輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點并不包含行記錄的全部數(shù)據(jù)，而是存儲相應行數(shù)據(jù)的聚集索引鍵，即主鍵。當通過輔助索引來查詢數(shù)據(jù)時，InnoDB存儲引擎會遍歷輔助索引找到主鍵，然后再通過主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

到此這篇關于MySQL單列索引和聯(lián)合索引的文章就介紹到這了,更多相關MySQL單列索引和聯(lián)合索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論