一、主鍵索引

主鍵索引：按照主鍵數(shù)據(jù)從小到大按照從左到右進(jìn)行排序，葉節(jié)點(diǎn)只存儲(chǔ)數(shù)據(jù)區(qū)；
接著將上面的頁(yè)生成出來(lái)，頁(yè)只存儲(chǔ)索引和指針，指針指向數(shù)據(jù)域，當(dāng)通過(guò)主鍵查找數(shù)據(jù)時(shí)，從B+樹(shù)的頭部開(kāi)始尋址數(shù)據(jù)、讀取數(shù)據(jù)。

上面為索引頁(yè)
下面為數(shù)據(jù)頁(yè)

查詢(xún)select * from table where a=6，會(huì)從上到下走法，找索引

查詢(xún)select * from table where a<6，會(huì)從上到下走法，找索引；
原因：先找a=6是走索引的，找到a=6數(shù)據(jù)后，將左邊的數(shù)據(jù)全部返回即可。

為了方便把前面數(shù)據(jù)給你采用雙向指針

查詢(xún)select * from table where b=6，會(huì)從左到右走法，全表掃描，因?yàn)閎未有命中索引。

二、什么是聯(lián)合索引? 對(duì)應(yīng)的B+樹(shù)是如何生成的?

聯(lián)合索引：將數(shù)據(jù)庫(kù)表中多個(gè)字段組成一個(gè)索引。bcd聯(lián)合索引；bcd三個(gè)字段進(jìn)行排序

1、建立索引方式

create index idx_t1_bcd on t1(b,c,d);bcd三個(gè)字段進(jìn)行排序

2、什么是最左前綴原則?

復(fù)合索引，也叫聯(lián)合索引，用戶(hù)可以在多個(gè)列上建立索引,這種索引叫做復(fù)合索引。
當(dāng)我們創(chuàng)建一個(gè)組合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了(k1)、
(k1,k2)和(k1,k2,k3)三個(gè)索引，這就是最左匹配原則。

select * from table where k1=A and k2=B and k3=C

3、回表

select * from t1 where b=1 and c=1 and d=1執(zhí)行邏輯：
通過(guò)聯(lián)合索引找到一條數(shù)據(jù)的3個(gè)字段，但是select * 查找多個(gè)字段，所以需要存儲(chǔ)主鍵字段，通過(guò)主鍵字段去主鍵索引里面找到完整的這條數(shù)據(jù)，并將這條數(shù)據(jù)返回。

通過(guò)主鍵再去主鍵索引里面找數(shù)據(jù)的過(guò)程叫做回表

4、為什么要遵守最左前綴原則才能利用到索引?

explain select * from t1 where c=1 and d=1 and b=1 ;
最左前綴原則和后面條件的順序沒(méi)有關(guān)系

不符合最左匹配原則：*11

符合最左匹配原則:1*1

b=1：走聯(lián)合索引 (需要回表1次)

b>1:不走聯(lián)合索引（因?yàn)樾枰乇砗芏啻危?/p>

拿到主鍵會(huì)到主鍵索引里面拿數(shù)據(jù)（回表7次）

5、什么是覆蓋索引?

查詢(xún)的字段正好在聯(lián)合索引字段里面，不需要回表

select b from t1 where b>1;(不需要回表了，直接從聯(lián)合索引字段中將該字段取出來(lái))

select a,b,c,d from t1 where b>1;

同樣的也符合聯(lián)合索引，也不需要回表，因?yàn)閍字段（主鍵）在聯(lián)合索引中。

select a,b,c,d,e from t1 where b>1;

不走聯(lián)合索引，需要回表，因?yàn)閑字段不在聯(lián)合索引中，需要回表很多次

6、索引掃描底層原理

從bcd索引的葉子節(jié)點(diǎn)開(kāi)始遍歷
主鍵索引存儲(chǔ)的是8條完整的數(shù)據(jù)
聯(lián)合索引中存儲(chǔ)的是8條不完整的數(shù)據(jù)，并且b字段在聯(lián)合索引中，不會(huì)去回表到主鍵索引中。

從葉子節(jié)點(diǎn)開(kāi)始掃描不需要符合最左匹配原則。

7、order by為什么會(huì)導(dǎo)致索引消失？

因?yàn)樽呷頀呙栊矢?，不需要回?br />走索引會(huì)回表多次，效率低

8、有哪些情況會(huì)導(dǎo)致索引失效?

1、索引列使用!=、not、is null、is not null查詢(xún)的時(shí)候，由于索引數(shù)據(jù)的檢索效率非常低，因此Mysql引擎會(huì)判斷不走索引。

2、使用like通配符匹配后綴%xxx的時(shí)候，由于這種方式不符合索引的最左匹配原則，所以也不會(huì)走索引。

• 但是反過(guò)來(lái)，如果通配符匹配的是前綴xxx%，符合最左匹配，也會(huì)走索引。

3、對(duì)索引列上進(jìn)行函數(shù)運(yùn)算、導(dǎo)致mysql無(wú)法識(shí)別索引列，就不會(huì)走索引了。

4、使用or連接查詢(xún)的時(shí)候，or語(yǔ)句前后沒(méi)有同時(shí)使用索引，那么索引會(huì)失效。只有or左右查詢(xún)字段都是索引列的時(shí)候，才會(huì)生效。

5、當(dāng)索引列存在隱式轉(zhuǎn)化的時(shí)候， 比如索引列是字符串類(lèi)型，但是在sql查詢(xún)中沒(méi)有使用引號(hào)。

9、索引設(shè)計(jì)原則

查詢(xún)更快、占用空間更小

1、選擇合適的列作為索引

• 1.經(jīng)常作為查詢(xún)條件（where子句）、排序條件（order by子句） 分組條件（group by子句）的列建立索引。
• 2、區(qū)分度低的字段，例如性別，不要建立索引
• 3、更新頻繁字段不適合創(chuàng)建索引
• 4、對(duì)于定義為text、image和bit的數(shù)據(jù)類(lèi)型的列不要建立索引
• 5、定義有外鍵的數(shù)據(jù)列一定要建立索引。
• 6、數(shù)據(jù)量較小的表，索引效果較差，沒(méi)有必要在此列建立索引
• 7、盡量使用擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來(lái)的索引即可。
• 8、使用短索引，如果對(duì)長(zhǎng)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，這樣能夠節(jié)省大量索引空間，如果搜索詞
  超過(guò)索引前綴長(zhǎng)度，則使用索引排除不匹配的行，然后檢查其余行是否可能匹配。

2、避免使用過(guò)多的索引

• 1、每個(gè)索引都需要占用額外的磁盤(pán)空間
• 2、更新表（update、insert、delete操作時(shí)），所有的索引都需要被更新
• 3、維護(hù)索引需要成本

三、InnoDB 與MyISAM 的區(qū)別

聚簇索引:將數(shù)據(jù)存儲(chǔ)與索引放到了一塊、并且是按照一定的順序組織的，找到索引也就找到了數(shù)據(jù)，數(shù)據(jù)的物理存放順序與索引順序是一致的，即:只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤(pán)上的。

非聚簇索引:葉子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)、存儲(chǔ)的是數(shù)據(jù)行地址，也就是說(shuō)根據(jù)索引查找到數(shù)據(jù)行的位置再取磁盤(pán)查找數(shù)據(jù)，這個(gè)就有點(diǎn)類(lèi)似一本樹(shù)的目錄，比如我們要找第三章第一節(jié)，那我們先在這個(gè)目錄里面找，找到對(duì)應(yīng)的頁(yè)碼后再去對(duì)應(yīng)的頁(yè)碼看文章。

優(yōu)勢(shì):
1、查詢(xún)通過(guò)聚簇索引可以直接獲取數(shù)據(jù)，相比非聚簇索引需要第二次查詢(xún)(非覆蓋索引的情況下)效率要高
2、聚簇索引對(duì)于范圍查詢(xún)的效率很高，因?yàn)槠鋽?shù)據(jù)是按照大小排列的
3、聚簇索引適合用在排序的場(chǎng)合，非聚簇索引不適合

到此這篇關(guān)于mysql中主鍵索引和聯(lián)合索引的原理與區(qū)別的文章就介紹到這了,更多相關(guān)mysql 主鍵索引和聯(lián)合索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論