一、主鍵索引

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

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

查詢select * from table where a=6，會從上到下走法，找索引

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

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

查詢select * from table where b=6，會從左到右走法，全表掃描，因為b未有命中索引。

二、什么是聯(lián)合索引? 對應的B+樹是如何生成的?

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

1、建立索引方式

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

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

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

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í)行邏輯：
通過聯(lián)合索引找到一條數(shù)據(jù)的3個字段，但是select * 查找多個字段，所以需要存儲主鍵字段，通過主鍵字段去主鍵索引里面找到完整的這條數(shù)據(jù)，并將這條數(shù)據(jù)返回。

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

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

explain select * from t1 where c=1 and d=1 and b=1 ;
最左前綴原則和后面條件的順序沒有關系

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

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

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

b>1:不走聯(lián)合索引（因為需要回表很多次）

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

5、什么是覆蓋索引?

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

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

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

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

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

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

6、索引掃描底層原理

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

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

7、order by為什么會導致索引消失？

因為走全表掃描效率更高，不需要回表
走索引會回表多次，效率低

8、有哪些情況會導致索引失效?

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

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

• 但是反過來，如果通配符匹配的是前綴xxx%，符合最左匹配，也會走索引。

3、對索引列上進行函數(shù)運算、導致mysql無法識別索引列，就不會走索引了。

4、使用or連接查詢的時候，or語句前后沒有同時使用索引，那么索引會失效。只有or左右查詢字段都是索引列的時候，才會生效。

5、當索引列存在隱式轉化的時候， 比如索引列是字符串類型，但是在sql查詢中沒有使用引號。

9、索引設計原則

查詢更快、占用空間更小

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

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

2、避免使用過多的索引

• 1、每個索引都需要占用額外的磁盤空間
• 2、更新表（update、insert、delete操作時），所有的索引都需要被更新
• 3、維護索引需要成本

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

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

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

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

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

最新評論