11. 創(chuàng)建索引時(shí)需要注意什么？

[if !supportLists]·?????[endif]非空字段：應(yīng)該指定列為NOT NULL，除非你想存儲(chǔ)NULL。在mysql中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪沟盟饕⑺饕慕y(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值；

[if !supportLists]·?????[endif]取值離散大的字段：（變量各個(gè)取值之間的差異程度）的列放到聯(lián)合索引的前面，可以通過count()函數(shù)查看字段的差異值，返回值越大說明字段的唯一值越多字段的離散程度高；

[if !supportLists]·?????[endif]索引字段越小越好：數(shù)據(jù)庫(kù)的數(shù)據(jù)存儲(chǔ)以頁(yè)為單位一頁(yè)存儲(chǔ)的數(shù)據(jù)越多一次IO操作獲取的數(shù)據(jù)越大效率越高。

12. 使用索引查詢一定能提高查詢的性能嗎？為什么

通常，通過索引查詢數(shù)據(jù)比全表掃描要快。但是我們也必須注意到它的代價(jià)。

[if !supportLists]·?????[endif]索引需要空間來存儲(chǔ)，也需要定期維護(hù)， 每當(dāng)有記錄在表中增減或索引列被修改時(shí)，索引本身也會(huì)被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁盤I/O。因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢。使用索引查詢不一定能提高查詢性能，索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:

[if !supportLists]·?????[endif]基于一個(gè)范圍的檢索，一般查詢返回結(jié)果集小于表中記錄數(shù)的30%

[if !supportLists]·?????[endif]基于非唯一性索引的檢索

13. 百萬(wàn)級(jí)別或以上的數(shù)據(jù)如何刪除

關(guān)于索引：由于索引需要額外的維護(hù)成本，因?yàn)樗饕募菃为?dú)存在的文件,所以當(dāng)我們對(duì)數(shù)據(jù)的增加,修改,刪除,都會(huì)產(chǎn)生額外的對(duì)索引文件的操作,這些操作需要消耗額外的IO,會(huì)降低增/改/刪的執(zhí)行效率。所以，在我們刪除數(shù)據(jù)庫(kù)百萬(wàn)級(jí)別數(shù)據(jù)的時(shí)候，查詢MySQL官方手冊(cè)得知?jiǎng)h除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。

[if !supportLists]1.?? [endif]所以我們想要?jiǎng)h除百萬(wàn)數(shù)據(jù)的時(shí)候可以先刪除索引（此時(shí)大概耗時(shí)三分多鐘）

[if !supportLists]2.?? [endif]然后刪除其中無(wú)用數(shù)據(jù)（此過程需要不到兩分鐘）

[if !supportLists]3.?? [endif]刪除完成后重新創(chuàng)建索引(此時(shí)數(shù)據(jù)較少了)創(chuàng)建索引也非常快，約十分鐘左右。

[if !supportLists]4.?? [endif]與之前的直接刪除絕對(duì)是要快速很多，更別說萬(wàn)一刪除中斷,一切刪除會(huì)回滾。那更是坑了。

14. 前綴索引

語(yǔ)法：index(field(10))，使用字段值的前10個(gè)字符建立索引，默認(rèn)是使用字段的全部?jī)?nèi)容建立索引。

前提：前綴的標(biāo)識(shí)度高。比如密碼就適合建立前綴索引，因?yàn)槊艽a幾乎各不相同。

實(shí)操的難度：在于前綴截取的長(zhǎng)度。

我們可以利用select count(*)/count(distinct left(password,prefixLen));，通過從調(diào)整prefixLen的值（從1自增）查看不同前綴長(zhǎng)度的一個(gè)平均匹配度，接近1時(shí)就可以了（表示一個(gè)密碼的前prefixLen個(gè)字符幾乎能確定唯一一條記錄）

15. 什么是最左前綴原則？什么是最左匹配原則

[if !supportLists]·?????[endif]顧名思義，就是最左優(yōu)先，在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。

[if !supportLists]·?????[endif]最左前綴匹配原則，非常重要的原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3

and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

[if !supportLists]·?????[endif]=和in可以亂序，比如a = 1

and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式

16. B樹和B+樹的區(qū)別

[if !supportLists]·?????[endif]在B樹中，你可以將鍵和值存放在內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)；但在B+樹中，內(nèi)部節(jié)點(diǎn)都是鍵，沒有值，葉子節(jié)點(diǎn)同時(shí)存放鍵和值。

[if !supportLists]·?????[endif]B+樹的葉子節(jié)點(diǎn)有一條鏈相連，而B樹的葉子節(jié)點(diǎn)各自獨(dú)立。

17. 使用B樹的好處

B樹可以在內(nèi)部節(jié)點(diǎn)同時(shí)存儲(chǔ)鍵和值，因此，把頻繁訪問的數(shù)據(jù)放在靠近根節(jié)點(diǎn)的地方將會(huì)大大提高熱點(diǎn)數(shù)據(jù)的查詢效率。這種特性使得B樹在特定數(shù)據(jù)重復(fù)多次查詢的場(chǎng)景中更加高效。

18. 使用B+樹的好處

由于B+樹的內(nèi)部節(jié)點(diǎn)只存放鍵，不存放值，因此，一次讀取，可以在內(nèi)存頁(yè)中獲取更多的鍵，有利于更快地縮小查找范圍。B+樹的葉節(jié)點(diǎn)由一條鏈相連，因此，當(dāng)需要進(jìn)行一次全數(shù)據(jù)遍歷的時(shí)候，B+樹只需要使用O(logN)時(shí)間找到最小的一個(gè)節(jié)點(diǎn)，然后通過鏈進(jìn)行O(N)的順序遍歷即可。而B樹則需要對(duì)樹的每一層進(jìn)行遍歷，這會(huì)需要更多的內(nèi)存置換次數(shù)，因此也就需要花費(fèi)更多的時(shí)間

19. Hash索引和B+樹所有有什么區(qū)別或者說優(yōu)劣呢?

首先要知道Hash索引和B+樹索引的底層實(shí)現(xiàn)原理：

hash索引底層就是hash表，進(jìn)行查找時(shí)，調(diào)用一次hash函數(shù)就可以獲取到相應(yīng)的鍵值，之后進(jìn)行回表查詢獲得實(shí)際數(shù)據(jù)。B+樹底層實(shí)現(xiàn)是多路平衡查找樹。對(duì)于每一次的查詢都是從根節(jié)點(diǎn)出發(fā)，查找到葉子節(jié)點(diǎn)方可以獲得所查鍵值，然后根據(jù)查詢判斷是否需要回表查詢數(shù)據(jù)。

那么可以看出他們有以下的不同：

[if !supportLists]·?????[endif]hash索引進(jìn)行等值查詢更快(一般情況下)，但是卻無(wú)法進(jìn)行范圍查詢。

因?yàn)樵趆ash索引中經(jīng)過hash函數(shù)建立索引之后，索引的順序與原順序無(wú)法保持一致，不能支持范圍查詢。而B+樹的的所有節(jié)點(diǎn)皆遵循(左節(jié)點(diǎn)小于父節(jié)點(diǎn)，右節(jié)點(diǎn)大于父節(jié)點(diǎn)，多叉樹也類似)，天然支持范圍。

[if !supportLists]·?????[endif]hash索引不支持使用索引進(jìn)行排序，原理同上。

[if !supportLists]·?????[endif]hash索引不支持模糊查詢以及多列索引的最左前綴匹配。原理也是因?yàn)閔ash函數(shù)的不可預(yù)測(cè)。AAAA和AAAAB的索引沒有相關(guān)性。

[if !supportLists]·?????[endif]hash索引任何時(shí)候都避免不了回表查詢數(shù)據(jù)，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時(shí)候可以只通過索引完成查詢。

[if !supportLists]·?????[endif]hash索引雖然在等值查詢上較快，但是不穩(wěn)定。性能不可預(yù)測(cè)，當(dāng)某個(gè)鍵值存在大量重復(fù)的時(shí)候，發(fā)生hash碰撞，此時(shí)效率可能極差。而B+樹的查詢效率比較穩(wěn)定，對(duì)于所有的查詢都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)，且樹的高度較低。

因此，在大多數(shù)情況下，直接選擇B+樹索引可以獲得穩(wěn)定且較好的查詢速度。而不需要使用hash索引。

20. 數(shù)據(jù)庫(kù)為什么使用B+樹而不是B樹

[if !supportLists]·?????[endif]B樹只適合隨機(jī)檢索，而B+樹同時(shí)支持隨機(jī)檢索和順序檢索；

[if !supportLists]·?????[endif]B+樹空間利用率更高，可減少I/O次數(shù)，磁盤讀寫代價(jià)更低。一般來說，索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗。B+樹的內(nèi)部結(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針，只是作為索引使用，其內(nèi)部結(jié)點(diǎn)比B樹小，盤塊能容納的結(jié)點(diǎn)中關(guān)鍵字?jǐn)?shù)量更多，一次性讀入內(nèi)存中可以查找的關(guān)鍵字也就越多，相對(duì)的，IO讀寫次數(shù)也就降低了。而IO讀寫次數(shù)是影響索引檢索效率的最大因素；

[if !supportLists]·?????[endif]B+樹的查詢效率更加穩(wěn)定。B樹搜索有可能會(huì)在非葉子結(jié)點(diǎn)結(jié)束，越靠近根節(jié)點(diǎn)的記錄查找時(shí)間越短，只要找到關(guān)鍵字即可確定記錄的存在，其性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機(jī)檢索時(shí)，任何關(guān)鍵字的查找都必須走一條從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的路，所有關(guān)鍵字的查找路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)關(guān)鍵字的查詢效率相當(dāng)。

[if !supportLists]·?????[endif]B-樹在提高了磁盤IO性能的同時(shí)并沒有解決元素遍歷的效率低下的問題。B+樹的葉子節(jié)點(diǎn)使用指針順序連接在一起，只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫(kù)中基于范圍的查詢是非常頻繁的，而B樹不支持這樣的操作。

[if !supportLists]·?????[endif]增刪文件（節(jié)點(diǎn)）時(shí)，效率更高。因?yàn)锽+樹的葉子節(jié)點(diǎn)包含所有關(guān)鍵字，并以有序的鏈表結(jié)構(gòu)存儲(chǔ)，這樣可很好提高增刪效率。

21. B+樹在滿足聚簇索引和覆蓋索引的時(shí)候不需要回表查詢數(shù)據(jù)

在B+樹的索引中，葉子節(jié)點(diǎn)可能存儲(chǔ)了當(dāng)前的key值，也可能存儲(chǔ)了當(dāng)前的key值以及整行的數(shù)據(jù)，這就是聚簇索引和非聚簇索引。在InnoDB中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個(gè)唯一鍵建立聚簇索引。如果沒有唯一鍵，則隱式的生成一個(gè)鍵來建立聚簇索引。

當(dāng)查詢使用聚簇索引時(shí)，在對(duì)應(yīng)的葉子節(jié)點(diǎn)，可以獲取到整行數(shù)據(jù)，因此不用再次進(jìn)行回表查詢。

22. 什么是聚簇索引？何時(shí)使用聚簇索引與非聚簇索引

[if !supportLists]·?????[endif]聚簇索引：將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，找到索引也就找到了數(shù)據(jù)

[if !supportLists]·?????[endif]非聚簇索引：將數(shù)據(jù)存儲(chǔ)于索引分開結(jié)構(gòu)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)的對(duì)應(yīng)行，myisam通過key_buffer把索引先緩存到內(nèi)存中，當(dāng)需要訪問數(shù)據(jù)時(shí)（通過索引訪問數(shù)據(jù)），在內(nèi)存中直接搜索索引，然后通過索引找到磁盤相應(yīng)數(shù)據(jù)，這也就是為什么索引不在key buffer命中時(shí)，速度慢的原因

澄清一個(gè)概念：innodb中，在聚簇索引之上創(chuàng)建的索引稱之為輔助索引，輔助索引訪問數(shù)據(jù)總是需要二次查找，非聚簇索引都是輔助索引，像復(fù)合索引、前綴索引、唯一索引，輔助索引葉子節(jié)點(diǎn)存儲(chǔ)的不再是行的物理位置，而是主鍵值

何時(shí)使用聚簇索引與非聚簇索引

23. 非聚簇索引一定會(huì)回表查詢嗎？

不一定，這涉及到查詢語(yǔ)句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進(jìn)行回表查詢。

舉個(gè)簡(jiǎn)單的例子，假設(shè)我們?cè)趩T工表的年齡上建立了索引，那么當(dāng)進(jìn)行select age from employee where age < 20的查詢時(shí)，在索引的葉子節(jié)點(diǎn)上，已經(jīng)包含了age信息，不會(huì)再次進(jìn)行回表查詢。

24. 聯(lián)合索引是什么？為什么需要注意聯(lián)合索引中的順序？

MySQL可以使用多個(gè)字段同時(shí)建立一個(gè)索引，叫做聯(lián)合索引。在聯(lián)合索引中，如果想要命中索引，需要按照建立索引時(shí)的字段順序挨個(gè)使用，否則無(wú)法命中索引。

具體原因?yàn)?

MySQL使用索引時(shí)需要索引有序，假設(shè)現(xiàn)在建立了"name，age，school"的聯(lián)合索引，那么索引的排序?yàn)? 先按照name排序，如果name相同，則按照age排序，如果age的值也相等，則按照school進(jìn)行排序。

當(dāng)進(jìn)行查詢時(shí)，此時(shí)索引僅僅按照name嚴(yán)格有序，因此必須首先使用name字段進(jìn)行等值查詢，之后對(duì)于匹配到的列而言，其按照age字段嚴(yán)格有序，此時(shí)可以使用age字段用做索引查找，以此類推。因此在建立聯(lián)合索引的時(shí)候應(yīng)該注意索引列的順序，一般情況下，將查詢需求頻繁或者字段選擇性高的列放在前面。此外可以根據(jù)特例的查詢或者表結(jié)構(gòu)進(jìn)行單獨(dú)的調(diào)整。

小編分類整理了許多java進(jìn)階學(xué)習(xí)材料和BAT面試題，需要資料的請(qǐng)加QQ群：731611386就能領(lǐng)取2019年java進(jìn)階學(xué)習(xí)資料和BAT面試題以及《Effective Java》（第3版）電子版書籍。