為什么用自增列作為主鍵
1、如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì)選擇主鍵作為聚集索引。
如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引。
如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。
2、數(shù)據(jù)記錄本身被存于主索引(一顆B+Tree)的葉子節(jié)點(diǎn)上，這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)(大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè))的各條數(shù)據(jù)記錄按主鍵順序存放
因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子(InnoDB默認(rèn)為15/16)，則開(kāi)辟一個(gè)新的頁(yè)(節(jié)點(diǎn))
3、如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)
4、如果使用非自增主鍵(如果身份證號(hào)或?qū)W號(hào)等)，由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置
此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時(shí)又要從磁盤上讀回來(lái)，這增加了很多開(kāi)銷
同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。
為什么使用數(shù)據(jù)索引能提高效率
數(shù)據(jù)索引的存儲(chǔ)是有序的
在有序的情況下，通過(guò)索引查詢一個(gè)數(shù)據(jù)是無(wú)需遍歷索引記錄的
極端情況下，數(shù)據(jù)索引的查詢效率為二分法查詢效率，趨近于 log2(N)
哈希索引的優(yōu)勢(shì)：
等值查詢，哈希索引具有絕對(duì)優(yōu)勢(shì)(前提是：沒(méi)有大量重復(fù)鍵值，如果大量重復(fù)鍵值時(shí)，哈希索引的效率很低，因?yàn)榇嬖谒^的哈希碰撞問(wèn)題。)
哈希索引不適用的場(chǎng)景：
不支持范圍查詢
不支持索引完成排序
不支持聯(lián)合索引的最左前綴匹配規(guī)則
通常，B+樹(shù)索引結(jié)構(gòu)適用于絕大多數(shù)場(chǎng)景，像下面這種場(chǎng)景用哈希索引才更有優(yōu)勢(shì)：
在HEAP表中，如果存儲(chǔ)的數(shù)據(jù)重復(fù)度很低(也就是說(shuō)基數(shù)很大)，對(duì)該列數(shù)據(jù)以等值查詢?yōu)橹?，沒(méi)有范圍查詢、沒(méi)有排序的時(shí)候，特別適合采用哈希索引，例如這種SQL：
# 僅等值查詢
select id, name from table where name='李明';
而常用的 InnoDB 引擎中默認(rèn)使用的是B+樹(shù)索引，它會(huì)實(shí)時(shí)監(jiān)控表上索引的使用情況。
如果認(rèn)為建立哈希索引可以提高查詢效率，則自動(dòng)在內(nèi)存中的“自適應(yīng)哈希索引緩沖區(qū)”建立哈希索引(在InnoDB中默認(rèn)開(kāi)啟自適應(yīng)哈希索引)。
通過(guò)觀察搜索模式，MySQL會(huì)利用index key的前綴建立哈希索引，如果一個(gè)表幾乎大部分都在緩沖池中，那么建立一個(gè)哈希索引能夠加快等值查詢。
注意：在某些工作負(fù)載下，通過(guò)哈希索引查找?guī)?lái)的性能提升遠(yuǎn)大于額外的監(jiān)控索引搜索情況和保持這個(gè)哈希表結(jié)構(gòu)所帶來(lái)的開(kāi)銷。
但某些時(shí)候，在負(fù)載高的情況下，自適應(yīng)哈希索引中添加的read/write鎖也會(huì)帶來(lái)競(jìng)爭(zhēng)，比如高并發(fā)的join操作。like操作和%的通配符操作也不適用于自適應(yīng)哈希索引，可能要關(guān)閉自適應(yīng)哈希索引。
為什么說(shuō)B+比B樹(shù)更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫(kù)索引?
1、B+的磁盤讀寫代價(jià)更低。
B+的內(nèi)部結(jié)點(diǎn)并沒(méi)有指向關(guān)鍵字具體信息的指針，因此其內(nèi)部結(jié)點(diǎn)相對(duì)B樹(shù)更小。
如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對(duì)來(lái)說(shuō)IO讀寫次數(shù)也就降低了。
2、B+-tree的查詢效率更加穩(wěn)定。
由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢的路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢效率相當(dāng)。
MySQL聯(lián)合索引
1、聯(lián)合索引是兩個(gè)或更多個(gè)列上的索引。
對(duì)于聯(lián)合索引:Mysql從左到右的使用索引中的字段，一個(gè)查詢可以只使用索引中的一部份，但只能是最左側(cè)部分。
例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3種組合進(jìn)行查找，但不支持 b,c進(jìn)行查找 .當(dāng)最左側(cè)字段是常量引用時(shí)，索引就十分有效。
2、利用索引中的附加列，您可以縮小搜索的范圍，但使用一個(gè)具有兩列的索引不同于使用兩個(gè)單獨(dú)的索引。
復(fù)合索引的結(jié)構(gòu)與電話簿類似，人名由姓和名構(gòu)成，電話簿首先按姓氏對(duì)進(jìn)行排序，然后按名字對(duì)有相同姓氏的人進(jìn)行排序。
如果您知道姓，電話簿將非常有用;如果您知道姓和名，電話簿則更為有用，但如果您只知道名不知道姓，電話簿將沒(méi)有用處。
以上就是長(zhǎng)沙達(dá)內(nèi)教育Java培訓(xùn)機(jī)構(gòu)小編介紹的“Java面試題數(shù)據(jù)庫(kù)部分合集”的內(nèi)容，希望對(duì)大家有幫助，如有疑問(wèn)，請(qǐng)?jiān)诰€咨詢，有專業(yè)老師隨時(shí)為你服務(wù)。
相關(guān)推薦
最新最全java面試題及答案（初級(jí)到高級(jí)）
史上最全的中高級(jí)JAVA工程師面試題及答案匯總
Java高級(jí)開(kāi)發(fā)工程師面試題
2019史上最全java面試題題庫(kù)大全800題
哪有資深java工程師面試題
Java面試題
