mysql中主鍵索引和聯(lián)合索引的原理與區(qū)別
一、主鍵索引
主鍵索引:按照主鍵數(shù)據(jù)從小到大按照從左到右進(jìn)行排序,葉節(jié)點(diǎn)只存儲(chǔ)數(shù)據(jù)區(qū);
接著將上面的頁生成出來,頁只存儲(chǔ)索引和指針,指針指向數(shù)據(jù)域,當(dāng)通過主鍵查找數(shù)據(jù)時(shí),從B+樹的頭部開始尋址數(shù)據(jù)、讀取數(shù)據(jù)。
上面為索引頁
下面為數(shù)據(jù)頁
查詢select * from table where a=6,會(huì)從上到下走法,找索引
查詢select * from table where a<6,會(huì)從上到下走法,找索引;
原因:先找a=6是走索引的,找到a=6數(shù)據(jù)后,將左邊的數(shù)據(jù)全部返回即可。
為了方便把前面數(shù)據(jù)給你采用雙向指針
查詢select * from table where b=6,會(huì)從左到右走法,全表掃描,因?yàn)閎未有命中索引。
二、什么是聯(lián)合索引? 對(duì)應(yīng)的B+樹是如何生成的?
聯(lián)合索引:將數(shù)據(jù)庫表中多個(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)合索引,用戶可以在多個(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í)行邏輯:
通過聯(lián)合索引找到一條數(shù)據(jù)的3個(gè)字段,但是select * 查找多個(gè)字段,所以需要存儲(chǔ)主鍵字段,通過主鍵字段去主鍵索引里面找到完整的這條數(shù)據(jù),并將這條數(shù)據(jù)返回。
通過主鍵再去主鍵索引里面找數(shù)據(jù)的過程叫做回表
4、為什么要遵守最左前綴原則才能利用到索引?
explain select * from t1 where c=1 and d=1 and b=1 ;
最左前綴原則和后面條件的順序沒有關(guān)系
不符合最左匹配原則:*11
符合最左匹配原則:1*1
b=1:走聯(lián)合索引 (需要回表1次)
b>1:不走聯(lián)合索引(因?yàn)樾枰乇砗芏啻危?/p>
拿到主鍵會(huì)到主鍵索引里面拿數(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)合索引,也不需要回表,因?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)開始遍歷
主鍵索引存儲(chǔ)的是8條完整的數(shù)據(jù)
聯(lián)合索引中存儲(chǔ)的是8條不完整的數(shù)據(jù),并且b字段在聯(lián)合索引中,不會(huì)去回表到主鍵索引中。
從葉子節(jié)點(diǎn)開始掃描不需要符合最左匹配原則。
7、order by為什么會(huì)導(dǎo)致索引消失?
因?yàn)樽呷頀呙栊矢撸恍枰乇?br />走索引會(huì)回表多次,效率低
8、有哪些情況會(huì)導(dǎo)致索引失效?
1、索引列使用!=、not、is null、is not null查詢的時(shí)候,由于索引數(shù)據(jù)的檢索效率非常低,因此Mysql引擎會(huì)判斷不走索引。
2、使用like通配符匹配后綴%xxx的時(shí)候,由于這種方式不符合索引的最左匹配原則,所以也不會(huì)走索引。
- 但是反過來,如果通配符匹配的是前綴xxx%,符合最左匹配,也會(huì)走索引。
3、對(duì)索引列上進(jìn)行函數(shù)運(yùn)算、導(dǎo)致mysql無法識(shí)別索引列,就不會(huì)走索引了。
4、使用or連接查詢的時(shí)候,or語句前后沒有同時(shí)使用索引,那么索引會(huì)失效。只有or左右查詢字段都是索引列的時(shí)候,才會(huì)生效。
5、當(dāng)索引列存在隱式轉(zhuǎn)化的時(shí)候, 比如索引列是字符串類型,但是在sql查詢中沒有使用引號(hào)。
9、索引設(shè)計(jì)原則
查詢更快、占用空間更小
1、選擇合適的列作為索引
- 1.經(jīng)常作為查詢條件(where子句)、排序條件(order by子句) 分組條件(group by子句)的列建立索引。
- 2、區(qū)分度低的字段,例如性別,不要建立索引
- 3、更新頻繁字段不適合創(chuàng)建索引
- 4、對(duì)于定義為text、image和bit的數(shù)據(jù)類型的列不要建立索引
- 5、定義有外鍵的數(shù)據(jù)列一定要建立索引。
- 6、數(shù)據(jù)量較小的表,索引效果較差,沒有必要在此列建立索引
- 7、盡量使用擴(kuò)展索引,不要新建索引。比如表中已經(jīng)有a的索引,現(xiàn)在要加(a,b)的索引,那么只需要修改原來的索引即可。
- 8、使用短索引,如果對(duì)長字符串列進(jìn)行索引,應(yīng)該指定一個(gè)前綴長度,這樣能夠節(jié)省大量索引空間,如果搜索詞
超過索引前綴長度,則使用索引排除不匹配的行,然后檢查其余行是否可能匹配。
2、避免使用過多的索引
- 1、每個(gè)索引都需要占用額外的磁盤空間
- 2、更新表(update、insert、delete操作時(shí)),所有的索引都需要被更新
- 3、維護(hù)索引需要成本
三、InnoDB 與MyISAM 的區(qū)別
聚簇索引:將數(shù)據(jù)存儲(chǔ)與索引放到了一塊、并且是按照一定的順序組織的,找到索引也就找到了數(shù)據(jù),數(shù)據(jù)的物理存放順序與索引順序是一致的,即:只要索引是相鄰的,那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。
非聚簇索引:葉子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)、存儲(chǔ)的是數(shù)據(jù)行地址,也就是說根據(jù)索引查找到數(shù)據(jù)行的位置再取磁盤查找數(shù)據(jù),這個(gè)就有點(diǎn)類似一本樹的目錄,比如我們要找第三章第一節(jié),那我們先在這個(gè)目錄里面找,找到對(duì)應(yīng)的頁碼后再去對(duì)應(yīng)的頁碼看文章。
優(yōu)勢:
1、查詢通過聚簇索引可以直接獲取數(shù)據(jù),相比非聚簇索引需要第二次查詢(非覆蓋索引的情況下)效率要高
2、聚簇索引對(duì)于范圍查詢的效率很高,因?yàn)槠鋽?shù)據(jù)是按照大小排列的
3、聚簇索引適合用在排序的場合,非聚簇索引不適合
到此這篇關(guān)于mysql中主鍵索引和聯(lián)合索引的原理與區(qū)別的文章就介紹到這了,更多相關(guān)mysql 主鍵索引和聯(lián)合索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作實(shí)例詳解
對(duì)于刪除操作來說,是將表單個(gè)或者多個(gè)數(shù)據(jù)進(jìn)行刪除,而截?cái)鄤t是對(duì)整個(gè)表進(jìn)行操作,會(huì)將整個(gè)表數(shù)據(jù)都清除,本文給大家介紹MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作大全,感興趣的朋友一起看看吧2025-04-04關(guān)于MySQL Memory存儲(chǔ)引擎的相關(guān)知識(shí)
這篇文章主要介紹了關(guān)于MySQL Memory存儲(chǔ)引擎的相關(guān)知識(shí),幫助大家更好的理解和使用MySQL數(shù)據(jù)庫,感興趣的朋友可以了解下2020-11-11簡單了解MySQL union all與union的區(qū)別
這篇文章主要介紹了簡單了解MySQL union all與union的區(qū)別,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-03-03解決mysql數(shù)據(jù)庫設(shè)置遠(yuǎn)程連接權(quán)限執(zhí)行g(shù)rant all privileges on&n
這篇文章主要介紹了解決mysql數(shù)據(jù)庫設(shè)置遠(yuǎn)程連接權(quán)限執(zhí)行g(shù)rant all privileges on *.* to 'root'@'%' identified by '密碼' with grant optio報(bào)錯(cuò),通過本文給大家分享問題原因解析及解決方法,需要的朋友可以參考下2022-11-11MySQL:explain結(jié)果中Extra:Impossible?WHERE?noticed?after?rea
這篇文章主要介紹了MySQL:explain結(jié)果中Extra:Impossible?WHERE?noticed?after?reading?const?tables問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12MySQL中create_time和update_time實(shí)現(xiàn)自動(dòng)更新時(shí)間
mysql建表的時(shí)候有兩個(gè)列,一個(gè)是createtime、另一個(gè)是updatetime,這兩個(gè)都是mysql自動(dòng)填充時(shí)間的方式,本文就詳細(xì)的介紹這兩種方式的實(shí)現(xiàn),感興趣的可以了解一下2023-05-05MySQL里實(shí)現(xiàn)類似SPLIT的分割字符串的函數(shù)
SQL對(duì)字符串的處理能力比較弱,比如我要循環(huán)遍歷象1,2,3,4,5這樣的字符串,如果用數(shù)組的話,遍歷很簡單,但是T-SQL不支持?jǐn)?shù)組,所以處理下來比較麻煩2012-09-09