MySQL單列索引和聯(lián)合索引的知識(shí)點(diǎn)有哪些

本篇內(nèi)容主要講解“MySQL單列索引和聯(lián)合索引的知識(shí)點(diǎn)有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL單列索引和聯(lián)合索引的知識(shí)點(diǎn)有哪些”吧!

一、簡(jiǎn)介

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

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

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

二、單列索引

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

三、最左前綴原則

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

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

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

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

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

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

SELECT * FROM employee WHERE salary=5000

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

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

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

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

6、理由

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

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

四、同時(shí)存在聯(lián)合索引和單列索引（字段有重復(fù)），這個(gè)時(shí)候查詢mysql會(huì)怎么用索引呢？

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

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

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

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

六、索引失效

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

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

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

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

七、其它知識(shí)點(diǎn)

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

2、數(shù)據(jù)量少的字段不需要加索引，因?yàn)榻ㄋ饕幸欢ㄩ_銷，如果數(shù)據(jù)量小則沒有必要建索引，速度范圍慢。

3、聯(lián)合索引比每個(gè)列建索引更有優(yōu)勢(shì)，因?yàn)樗饕⒌迷蕉嗑驮秸即疟P空間，在更新數(shù)據(jù)的時(shí)候速度會(huì)越慢、另外建立多列索引時(shí)，順序也是需要注意的，應(yīng)該講嚴(yán)格的索引放在前面，這樣篩選的力度會(huì)更大，效率更高。

八、MySQL存儲(chǔ)引擎簡(jiǎn)介

1、InnoDB

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

2、MyISAM

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

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

九、索引結(jié)構(gòu)（方法、算法）

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

1、Hash

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

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

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

• Hash索引僅僅能滿足“=”,“IN”,“<=>”查詢，不能使用范圍查詢。

• 聯(lián)合索引中，Hash索引不能利用部分索引鍵查詢。 對(duì)于聯(lián)合索引中的多個(gè)列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的聯(lián)合索引的最優(yōu)前綴，也就是聯(lián)合索引的前面一個(gè)或幾個(gè)索引鍵進(jìn)行查詢時(shí)，Hash索引無法被利用。

• Hash索引無法避免數(shù)據(jù)的排序操作 由于Hash索引中存放的是經(jīng)過Hash計(jì)算之后的Hash值，而且Hash值的大小關(guān)系并不一定和Hash運(yùn)算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運(yùn)算。

• Hash索引任何時(shí)候都不能避免表掃描 Hash索引是將索引鍵通過Hash運(yùn)算之后，將Hash運(yùn)算結(jié)果的Hash值和所對(duì)應(yīng)的行指針信息存放于一個(gè)Hash表中，由于不同索引鍵存在相同Hash值，所以即使?jié)M足某個(gè)Hash鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從Hash索引中直接完成查詢，還是要通過訪問表中的實(shí)際數(shù)據(jù)進(jìn)行比較，并得到相應(yīng)的結(jié)果。

• Hash索引遇到大量Hash值相等的情況后性能并不一定會(huì)比BTree高 對(duì)于選擇性比較低的索引鍵，如果創(chuàng)建Hash索引，那么將會(huì)存在大量記錄指針信息存于同一個(gè)Hash值相關(guān)聯(lián)。這樣要定位某一條記錄時(shí)就會(huì)非常麻煩，會(huì)浪費(fèi)多次表數(shù)據(jù)訪問，而造成整體性能底下。

2、B+ Tree

B+Tree索引是最常用的mysql數(shù)據(jù)庫索引算法，因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個(gè)不以通配符開頭的常量，

例如：

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

如果一通配符開頭，或者沒有使用常量，則不會(huì)使用索引，

例如：

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

3、 B+/-Tree原理

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

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

• B樹不同于二叉樹，他們的一個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)關(guān)鍵字和多個(gè)子樹指針，這就是B+樹的特點(diǎn)；

• 一個(gè)m階的B樹要求除了根節(jié)點(diǎn)以外，所有的非葉子子節(jié)點(diǎn)必須要有[m/2,m]個(gè)子樹；

• 根節(jié)點(diǎn)必須只能有兩個(gè)子樹，當(dāng)然，如果只有根節(jié)點(diǎn)一個(gè)節(jié)點(diǎn)的情況存在；

• B樹是一個(gè)查找二叉樹，這點(diǎn)和二叉查找樹很像，他都是越靠前的子樹越小，并且，同一個(gè)節(jié)點(diǎn)內(nèi)，關(guān)鍵字按照大小排序；

• B樹的一個(gè)節(jié)點(diǎn)要求子樹的個(gè)數(shù)等于關(guān)鍵字的個(gè)數(shù)+1；

B+樹就是B樹的plus版

• B+樹將所有的查找結(jié)果放在葉子節(jié)點(diǎn)中，這也就意味著查找B+樹，就必須到葉子節(jié)點(diǎn)才能返回結(jié)果；

• B+樹每一個(gè)節(jié)點(diǎn)的關(guān)鍵字個(gè)數(shù)和子樹指針個(gè)數(shù)相同；

• B+樹的非葉子節(jié)點(diǎn)的每一個(gè)關(guān)鍵字對(duì)應(yīng)一個(gè)指針，而關(guān)鍵字則是子樹的最大，或者最小值；

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

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

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

InnoDB存儲(chǔ)引擎中頁的大小為16KB，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)），指針類型也一般為4或8個(gè)字節(jié)，也就是說一個(gè)頁（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐担瑸榉奖阌?jì)算，這里的K取值為〖10〗^3）。
也就是說一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 10^3 * 10^3 = 10億條記錄。

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

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

到此，相信大家對(duì)“MySQL單列索引和聯(lián)合索引的知識(shí)點(diǎn)有哪些”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！