1. 索引是什么？
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)纠炮，它們包含著對數(shù)據(jù)表里所有記錄的引用指針孝宗。
索引是一種數(shù)據(jù)結構问潭。數(shù)據(jù)庫索引狡忙，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結構灾茁，以協(xié)助快速查詢北专、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用B樹及其變種B+樹驶睦。更通俗的說场航，索引是表的目錄，在查找內容之前可以先在目錄中查找索引位置适室，以此快速定位查詢數(shù)據(jù)蔬螟。對于索引旧巾，會保存在額外的文件中鲁猩，它是要占據(jù)物理空間的。
MySQL索引的建立對于MySQL的高效運行是很重要的隙券，索引可以大大提高MySQL的檢索速度娱仔。比如我們在查字典的時候，前面都有檢索的拼音和偏旁恩溅、筆畫等脚乡，然后找到對應字典頁碼，這樣然后就打開字典的頁數(shù)就可以知道我們要搜索的某一個key的全部值的信息了锌订。
2. 索引有哪些優(yōu)缺點啦辐？
索引的優(yōu)點
- 當表中的數(shù)據(jù)量越來越大時芹关，索引對于性能的影響愈發(fā)重要侥衬。索引能夠輕易將查詢性能提高好幾個數(shù)量級，總的來說就是可以明顯的提高查詢效率怀樟。
- 可以大大加快數(shù)據(jù)的檢索速度漂佩，這也是創(chuàng)建索引的最主要的原因征堪。
- 通過使用索引佃蚜，可以在查詢的過程中，使用優(yōu)化隱藏器洲脂，提高系統(tǒng)的性能。
- 通過創(chuàng)建唯一索引
- 加快表與表之間的連接
索引的缺點
- 時間方面：創(chuàng)建索引和維護索引要耗費時間一铅，具體地肮之，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候暴凑，索引也要動態(tài)的維護，會降低增/改/刪的執(zhí)行效率嗦篱；
- 空間方面：索引需要占物理空間灸促。
3. 哪些列上適合創(chuàng)建索引荒叼？創(chuàng)建索引有哪些開銷被廓？
經(jīng)常需要作為條件查詢的列上適合創(chuàng)建索引，并且該列上也必須有一定的區(qū)分度。
創(chuàng)建索引需要維護挎春，在插入數(shù)據(jù)的時候會重新維護各個索引樹（數(shù)據(jù)頁的分裂與合并），對性能造成影響
4. 索引這么多優(yōu)點痴突，為什么不對表中的每一個列創(chuàng)建一個索引呢搂蜓？
- 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候辽装，索引也要動態(tài)的維護帮碰，這樣就降低了數(shù)據(jù)的維護速度。
- 索引需要占物理空間拾积，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間傻唾，如果要建立聚簇索引抱既，那么需要的空間就會更大。
- 創(chuàng)建索引和維護索引要耗費時間拭卿，這種時間隨著數(shù)據(jù)量的增加而增加。
5. MySQL有哪幾種索引類型呐馆？
1、從存儲結構上來劃分：BTree索引（B-Tree或B+Tree索引），Hash索引扛或，full-index全文索引，R-Tree索引柳爽。這里所描述的是索引存儲時保存的形式俩功，
2洽腺、從應用層次來分：普通索引炼彪，唯一索引萄唇，復合索引泛源。
- 普通索引：即一個索引只包含單個列伐蒂，一個表可以有多個單列索引
- 唯一索引：索引列的值必須唯一部逮，但允許有空值
- 復合索引：多列值組成一個索引，專門用于組合搜索，其效率大于索引合并
3秦踪、根據(jù)中數(shù)據(jù)的物理順序與鍵值的邏輯（索引）順序關系： 聚集索引，非聚集索引门岔。
- 聚簇索引(聚集索引)：并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式棋凳。具體細節(jié)取決于不同的實現(xiàn)蠢箩，InnoDB的聚簇索引其實就是在同一個結構中保存了B-Tree索引(技術上來說是B+Tree)和數(shù)據(jù)行邦马。
- 非聚簇索引： 不是聚簇索引，就是非聚簇索引
6. 說一說索引的底層實現(xiàn)屁桑？
Hash索引
基于哈希表實現(xiàn)域携，只有精確匹配索引所有列的查詢才有效蒲凶，對于每一行數(shù)據(jù)气筋，存儲引擎都會對所有的索引列計算一個哈希碼（hash code），并且Hash索引將所有的哈希碼存儲在索引中旋圆，同時在索引表中保存指向每個數(shù)據(jù)行的指針宠默。
圖片來源：https://www.javazhiyin.com/40232.html
[圖片上傳失敗...(image-f8109c-1653141194846)]
B-Tree索引（MySQL使用B+Tree）
B-Tree能加快數(shù)據(jù)的訪問速度，因為存儲引擎不再需要進行全表掃描來獲取數(shù)據(jù)灵巧，數(shù)據(jù)分布在各個節(jié)點之中搀矫。
[圖片上傳失敗...(image-b502fc-1653141194846)]
B+Tree索引
是B-Tree的改進版本，同時也是數(shù)據(jù)庫索引索引所采用的存儲結構刻肄。數(shù)據(jù)都在葉子節(jié)點上瓤球，并且增加了順序訪問指針，每個葉子節(jié)點都指向相鄰的葉子節(jié)點的地址敏弃。相比B-Tree來說卦羡，進行范圍查找時只需要查找兩個節(jié)點，進行遍歷即可麦到。而B-Tree需要獲取所有節(jié)點绿饵，相比之下B+Tree效率更高。
B+tree性質：
- n棵子tree的節(jié)點包含n個關鍵字瓶颠，不用來保存數(shù)據(jù)而是保存數(shù)據(jù)的索引拟赊。
- 所有的葉子結點中包含了全部關鍵字的信息，及指向含這些關鍵字記錄的指針粹淋，且葉子結點本身依關鍵字的大小自小而大順序鏈接吸祟。
- 所有的非終端結點可以看成是索引部分瑟慈，結點中僅含其子樹中的最大（或最小）關鍵字屋匕。
- B+ 樹中封豪，數(shù)據(jù)對象的插入和刪除僅在葉節(jié)點上進行。
- B+樹有2個頭指針炒瘟，一個是樹的根節(jié)點，一個是最小關鍵碼的葉節(jié)點第步。
[圖片上傳失敗...(image-a89abf-1653141194846)]
7. 為什么索引結構默認使用B+Tree疮装，而不是B-Tree，Hash粘都，二叉樹廓推，紅黑樹？
B-tree：因為B樹不管葉子節(jié)點還是非葉子節(jié)點翩隧，都會保存數(shù)據(jù)樊展，這樣導致在非葉子節(jié)點中能保存的指針數(shù)量變少（有些資料也稱為扇出），指針少的情況下要保存大量數(shù)據(jù)堆生，只能增加樹的高度专缠，導致IO操作變多，查詢性能變低淑仆；
B+tree： 從兩個方面來回答
- B+樹的磁盤讀寫代價更低：B+樹的內部節(jié)點并沒有指向關鍵字具體信息的指針涝婉，因此其內部節(jié)點相對B樹更小，如果把所有同一內部節(jié)點的關鍵字存放在同一盤塊中蔗怠，那么盤塊所能容納的關鍵字數(shù)量也越多墩弯，一次性讀入內存的需要查找的關鍵字也就越多，相對IO讀寫次數(shù)就降低了渔工。
- 由于B+樹的數(shù)據(jù)都存儲在葉子結點中桥温，分支結點均為索引，方便掃庫策治，只需要掃一遍葉子結點即可脓魏，但是B樹因為其分支結點同樣存儲著數(shù)據(jù)通惫，我們要找到具體的數(shù)據(jù)，需要進行一次中序遍歷按序來掃履腋，所以B+樹更加適合在區(qū)間查詢的情況珊燎，所以通常B+樹用于數(shù)據(jù)庫索引。
Hash：
- 雖然可以快速定位晚吞，但是沒有順序谋国，IO復雜度高芦瘾；
- 基于Hash表實現(xiàn)，只有Memory存儲引擎顯式支持哈希索引 缅糟；
- 適合等值查詢祷愉，如=二鳄、in()、<=>句占，不支持范圍查詢 纱烘；
- 因為不是按照索引值順序存儲的祈餐，就不能像B+Tree索引一樣利用索引完成排序 ；
- Hash索引在查詢等值時非撤簦快 蜒谤；
- 因為Hash索引始終索引的所有列的全部內容鳍徽，所以不支持部分索引列的匹配查找 ；
- 如果有大量重復鍵值得情況下绷杜，哈希索引的效率會很低鞭盟，因為存在哈希碰撞問題 。
二叉樹： 樹的高度不均勻筝野，不能自平衡遗座，查找效率跟數(shù)據(jù)有關（樹的高度）俊扳，并且IO代價高馋记。
紅黑樹： 樹的高度隨著數(shù)據(jù)量增加而增加梯醒，IO代價高腌紧。
不使用平衡二叉樹的原因如下：
最大原因：深度太大(因為一個節(jié)點最多只有2個子節(jié)點)壁肋，一次查詢需要的I/O復雜度為O(lgN),而b+tree只需要O(log_mN),而其出度m非常大，其深度一般不會超過4 平衡二叉樹邏輯上很近的父子節(jié)點猫胁，物理上可能很遠弃秆，無法充分發(fā)揮磁盤順序讀和預讀的高效特性菠赚。
8. MyISAM和InnoDB實現(xiàn)BTree索引方式的區(qū)別
1）MyISAM
B+Tree葉節(jié)點的data域存放的是數(shù)據(jù)記錄的地址郑藏。在索引檢索的時候译秦，首先按照B+Tree搜索算法搜索索引击碗，如果指定的Key存在稍途，則取出其 data 域的值械拍，然后以 data 域的值為地址讀取相應的數(shù)據(jù)記錄装盯。這被稱為“非聚簇索引”埂奈。 索引文件和數(shù)據(jù)文件是分離的
2）InnoDB
- InnoDB 的 B+Tree 索引分為主索引（聚集索引）和輔助索引(非聚集索引)账磺。一張表一定包含一個聚集索引構成的 B+ 樹以及若干輔助索引的構成的 B+ 樹。
- 輔助索引的存在并不會影響聚集索引氏捞，因為聚集索引構成的 B+ 樹是數(shù)據(jù)實際存儲的形式液茎，而輔助索引只用于加速數(shù)據(jù)的查找捆等，所以一張表上往往有多個輔助索引以此來提升數(shù)據(jù)庫的性能续室。
- 就很容易明白為什么不建議使用過長的字段作為主鍵猎贴，因為所有輔助索引都引用主索引她渴，過長的主索引會令輔助索引變得過大。再例如沉唠，用非單調的字段作為主鍵在InnoDB中不是個好主意满葛，因為InnoDB數(shù)據(jù)文件本身是一顆B+Tree嘀韧，非單調的主鍵會造成在插入新記錄時數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調整，十分低效译蒂，而使用自增字段作為主鍵則是一個很好的選擇柔昼。
9. 主鍵索引和非主鍵索引
| TYPE | INDEX | |
|---|---|---|
| id | int | id(primary key) |
| k | int | k |
| name | varchar |
假設有如上表結構, 那么建立起的索引結構如下圖
[圖片上傳失敗...(image-3adc58-1653141194846)]
從圖中看出, 根據(jù)葉子節(jié)點內容的不同, 索引類型分為主鍵索引和非主鍵索引.
- 主鍵索引的葉子節(jié)點存儲的是整行數(shù)據(jù). 在InnoDB里, 主鍵索引也被稱為聚簇索引
- 非主鍵索引的葉子節(jié)點存儲的是主鍵的值. 在InnoDB里, 非主鍵索引也被稱為二級索引 / 非聚簇索引
10. 講一講聚簇索引與非聚簇索引捕透？
在 InnoDB 里激率，索引B+ Tree的葉子節(jié)點存儲了整行數(shù)據(jù)的是主鍵索引勿决，也被稱之為聚簇索引低缩，即將數(shù)據(jù)存儲與索引放到了一塊咆繁，找到索引也就找到了數(shù)據(jù)顶籽。
而索引B+ Tree的葉子節(jié)點存儲了主鍵的值的是非主鍵索引礼饱，也被稱之為非聚簇索引镊绪、二級索引。
聚簇索引與非聚簇索引的區(qū)別：
- 非聚集索引與聚集索引的區(qū)別在于非聚集索引的葉子節(jié)點不存儲表中的數(shù)據(jù)够颠，而是存儲該列對應的主鍵（行號）
- 對于InnoDB來說履磨，想要查找數(shù)據(jù)我們還需要根據(jù)主鍵再去聚集索引中進行查找剃诅，這個再根據(jù)聚集索引查找數(shù)據(jù)的過程综苔，我們稱為回表。第一次索引一般是順序IO堡牡，回表的操作屬于隨機IO晤柄。需要回表的次數(shù)越多芥颈，即隨機IO次數(shù)越多赚抡，我們就越傾向于使用全表掃描 涂臣。
- 通常情況下赁遗， 主鍵索引（聚簇索引）****查詢只會查一次岩四，而非主鍵索引（非聚簇索引）****需要回表查詢多次。當然材鹦，如果是覆蓋索引的話侠姑，查一次即可
- 注意：MyISAM無論主鍵索引還是二級索引都是非聚簇索引莽红，而InnoDB的主鍵索引是聚簇索引安吁，二級索引是非聚簇索引。我們自己建的索引基本都是非聚簇索引网棍。
11. 非聚簇索引一定會回表查詢嗎滥玷？
不一定惑畴，這涉及到查詢語句所要求的字段是否全部命中了索引如贷，如果全部命中了索引杠袱，那么就不必再進行回表查詢窝稿。一個索引包含（覆蓋）所有需要查詢字段的值伴榔，被稱之為"覆蓋索引"潮梯。
舉個簡單的例子秉馏，假設我們在員工表的年齡上建立了索引萝究，那么當進行select score from student where score > 90的查詢時锉罐，在索引的葉子節(jié)點上脓规，已經(jīng)包含了score 信息，不會再次進行回表查詢绢陌。
12. 聯(lián)合索引是什么脐湾？為什么需要注意聯(lián)合索引中的順序秤掌？
MySQL可以使用多個字段同時建立一個索引闻鉴，叫做聯(lián)合索引椒拗。在聯(lián)合索引中获黔，如果想要命中索引玷氏，需要按照建立索引時的字段順序挨個使用盏触，否則無法命中索引赞辩。
具體原因為:
MySQL使用索引時需要索引有序辨嗽，假設現(xiàn)在建立了"name糟需，age，school"的聯(lián)合索引武花，那么索引的排序為: 先按照name排序体箕，如果name相同干旁，則按照age排序争群，如果age的值也相等换薄，則按照school進行排序轻要。
當進行查詢時冲泥，此時索引僅僅按照name嚴格有序凡恍，因此必須首先使用name字段進行等值查詢嚼酝，之后對于匹配到的列而言，其按照age字段嚴格有序钧舌，此時可以使用age字段用做索引查找洼冻，以此類推撞牢。因此在建立聯(lián)合索引的時候應該注意索引列的順序普泡，一般情況下，將查詢需求頻繁或者字段選擇性高的列放在前面垒酬。此外可以根據(jù)特例的查詢或者表結構進行單獨的調整勘究。
13. 回表
當執(zhí)行 SELECT * FROM t WHERE id = 500 時, 即主鍵查詢方式, 則需要搜索ID這顆B+樹; 當執(zhí)行 SELECT * FROM t WHERE k = 5 時, 即普通索引查詢方式, 則先在k這棵樹查找到主鍵的值, 再從ID這棵樹中查找到對應的行.
當我們執(zhí)行SQL搜索數(shù)據(jù)時, 如果需要先從非主鍵索引中查詢到主鍵的值, 再從主鍵索引中查詢到對應的數(shù)據(jù), 這個過程就被稱為回表. 所以應該盡量使用主鍵查詢.
14. 索引維護 (頁分裂與頁合并)
B+樹為了有序性, 需要對插入和刪除數(shù)據(jù)時做出對應的維護. 當插入數(shù)據(jù)時, 如在上圖中插入ID=400的數(shù)據(jù), 那么從邏輯上來說, 需要移動后面的數(shù)據(jù), 空出位置.
若此時R5所在數(shù)據(jù)頁滿了, 則需要申請一個新的數(shù)據(jù)頁, 然后移動部分數(shù)據(jù)到新數(shù)據(jù)頁中, 這個過程被稱為頁分裂. 頁分裂影響了數(shù)據(jù)頁的空間利用率, 而且在分裂過程中, 性能也會有所影響.
若相鄰兩個數(shù)據(jù)頁因為刪除導致利用率很低后, 那么會將這兩個數(shù)據(jù)頁的數(shù)據(jù)合并到一個數(shù)據(jù)頁中, 這個過程被稱為頁合并. 即頁分裂的逆過程.
15. 覆蓋索引
如果執(zhí)行了語句 SELECT id FROM t WHERE k between 3 and 5 時, 只需要查詢 id 的值, 而 id 已經(jīng)在 k 的索引樹上, 所以不需要再回表去查詢整行, 直接返回查詢結果, 索引 k 已經(jīng)覆蓋了這條SQL查詢的需求, 被稱為 覆蓋索引. 覆蓋索引能夠減少樹的搜索次數(shù), 不需要再次回表查詢整行, 所以是一個常用的性能優(yōu)化手段.
16. 講一講MySQL的最左前綴原則?
最左前綴原則 就是利用索引列中最左的字段優(yōu)先進行匹配
最左前綴原則就是最左優(yōu)先缅阳，在創(chuàng)建多列索引時十办，要根據(jù)業(yè)務需求向族，where子句中使用最頻繁的一列放在最左邊件相。
mysql會一直向右匹配直到遇到范圍查詢(>夜矗、<侯养、between逛揩、like)就停止匹配辩稽。
例如：b = 2 如果建立(a,b)順序的索引逞泄，是匹配不到(a,b)索引的喷众；但是如果查詢條件是a = 1 and b = 2,就可以到千，因為優(yōu)化器會自動調整a,b的順序憔四。
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引了赵，d是用不到索引的柿汛，因為c字段是一個范圍查詢络断，它之后的字段會停止匹配妓羊。如果建立(a,b,d,c)的索引則都可以用到躁绸，a,b,d的順序可以任意調整净刮。
=和in可以亂序淹父，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序暑认，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式蘸际。
最左匹配原則的原理
MySQL中的索引可以以一定順序引用多列粮彤，這種索引叫作聯(lián)合索引.最左匹配原則都是針對聯(lián)合索引來說的
- 我們都知道索引的底層是一顆B+樹导坟，那么聯(lián)合索引當然還是一顆B+樹惫周，只不過聯(lián)合索引的健值數(shù)量不是一個，而是多個闯两。構建一顆B+樹只能根據(jù)一個值來構建褥伴，因此數(shù)據(jù)庫依據(jù)聯(lián)合索引最左的字段來構建B+樹。 例子：假如創(chuàng)建一個（a,b)的聯(lián)合索引漾狼，那么它的索引樹是這樣的可以看到a的值是有順序的重慢，1，1逊躁，2，2稽煤，3核芽，3，而b的值是沒有順序的1酵熙，2轧简，1，4匾二，1哮独，2。所以b = 2這種查詢條件沒有辦法利用索引察藐，因為聯(lián)合索引首先是按a排序的皮璧，b是無序的。
同時我們還可以發(fā)現(xiàn)在a值相等的情況下分飞，b值又是按順序排列的悴务，但是這種順序是相對的。所以最左匹配原則遇上范圍查詢就會停止譬猫，剩下的字段都無法使用索引讯檐。例如a = 1 and b = 2 a,b字段都可以使用索引，因為在a值確定的情況下b是相對有序的染服，而a>1and b=2裂垦，a字段可以匹配上索引，但b值不可以肌索，因為a的值是一個范圍蕉拢，在這個范圍中b是無序的。
優(yōu)點：最左前綴原則的利用也可以顯著提高查詢效率诚亚，是常見的MySQL性能優(yōu)化手段晕换。
17. 講一講前綴索引？
在對字符串創(chuàng)建索引, 如INDEX(name)中, 若字符串非常大, 那么響應的空間使用和維護開銷也非常大, 就可以使用字符串從左開始的部分字符創(chuàng)建索引（把很長字段的前面的公共部分作為一個索引站宗，就會產生超級加倍的效果）, 減少空間和維護的成本, 但是也會降低索引的選擇性闸准。但是，我們需要注意梢灭，order by不支持前綴索引 夷家。
索引的選擇性指的是 : 不重復的索引值和數(shù)據(jù)表的記錄總數(shù)(#T)的比值, 范圍為 1/#T 到 1 之間, 索引選擇性越高則查詢效率越高. 對于BLOB, TEXT, VARCHAR等類型的列, 必須使用前綴索引, MySQL不允許索引這些列的完整長度.
流程是：
- 先計算完整列的選擇性
SELECT COUNT(DISTINCT name)/COUNT(1) FROM t - 在計算不同前綴長度N的選擇性
SELECT COUNT(DISCTINCT LEFT(name, N)) / COUNT(1) FROM t - 看哪個N更靠近1, 進行索引的創(chuàng)建
18. 了解索引下推嗎蒸其？
對于SQL語句 SELECT * FROM t WHERE name LIKE '陳%' AND age = 10 , INDEX(name, age) 情況來說
在 MySQL5.6 之前沒有引入索引下推優(yōu)化時, 執(zhí)行流程如下圖, 在定位完name字段的索引后, 需要一條條進行回表查詢, 然后再判斷其他字段是否滿足條件.
[圖片上傳失敗...(image-4324e3-1653141194846)]
而 MySQL5.6 引入了索引下推優(yōu)化后, 可以在所有遍歷過程中, 對索引中包含的字段先進行判斷過濾, 然后再進行后續(xù)操作, 減少了回表次數(shù).
[圖片上傳失敗...(image-b4dbc3-1653141194846)]
19. 怎么查看MySQL語句有沒有用到索引？
通過explain库快，如以下例子：
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | filtered | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | titles | null | const | PRIMARY | PRIMARY | 59 | const,const,const | 10 | 1 |
- id：在?個?的查詢語句中每個SELECT關鍵字都對應?個唯?的id 摸袁，如explain select * from s1 where id = (select id from s1 where name = 'egon1');第一個select的id是1，第二個select的id是2义屏。有時候會出現(xiàn)兩個select靠汁，但是id卻都是1，這是因為優(yōu)化器把子查詢變成了連接查詢 闽铐。
- select_type：select關鍵字對應的那個查詢的類型蝶怔，如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION 。
- table：每個查詢對應的表名 兄墅。
- type：type 字段比較重要, 它提供了判斷查詢是否高效的重要依據(jù)依據(jù). 通過 type 字段, 我們判斷此次查詢是 全表掃描 還是 索引掃描 等踢星。如const(主鍵索引或者唯一二級索引進行等值匹配的情況下),ref(普通的?級索引列與常量進?等值匹配),index(掃描全表索引的覆蓋索引) 。通常來說, 不同的 type 類型的性能關系如下:ALL < index < range ~ index_merge < ref < eq_ref < const < systemALL 類型因為是全表掃描, 因此在相同的查詢條件下, 它是速度最慢的.而 index 類型的查詢雖然不是全表掃描, 但是它掃描了所有的索引, 因此比 ALL 類型的稍快.
- possible_key：查詢中可能用到的索引(可以把用不到的刪掉隙咸，降低優(yōu)化器的優(yōu)化時間) 斩狱。
- key：此字段是 MySQL 在當前查詢時所真正使用到的索引。
- filtered：查詢器預測滿足下一次查詢條件的百分比 扎瓶。
- rows 也是一個重要的字段. MySQL 查詢優(yōu)化器根據(jù)統(tǒng)計信息, 估算 SQL 要查找到結果集需要掃描讀取的數(shù)據(jù)行數(shù).這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好所踊。
- extra：表示額外信息，如Using where,Start temporary,End temporary,Using temporary等概荷。
20. 為什么官方建議使用自增長主鍵作為索引秕岛？
結合B+Tree的特點，自增主鍵是連續(xù)的误证，在插入過程中盡量減少頁分裂继薛，即使要進行頁分裂，也只會分裂很少一部分愈捅。并且能減少數(shù)據(jù)的移動遏考，每次插入都是插入到最后±督鳎總之就是減少分裂和移動的頻率灌具。
插入連續(xù)的數(shù)據(jù)：
圖片來自：https://www.javazhiyin.com/40232.html
[圖片上傳失敗...(image-b06b90-1653141194846)]
插入非連續(xù)的數(shù)據(jù)：
[圖片上傳失敗...(image-689d34-1653141194846)]
21. 如何創(chuàng)建索引？
創(chuàng)建索引有三種方式譬巫。
1咖楣、 在執(zhí)行CREATE TABLE時創(chuàng)建索引
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
2、 使用ALTER TABLE命令去增加索引芦昔。
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE用來創(chuàng)建普通索引诱贿、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名，column_list指出對哪些列進行索引珠十，多列時各列之間用逗號分隔料扰。
索引名index_name可自己命名，缺省時焙蹭，MySQL將根據(jù)第一個索引列賦一個名稱晒杈。另外，ALTER TABLE允許在單個語句中更改多個表壳嚎，因此可以在同時創(chuàng)建多個索引。3末早、 使用CREATE INDEX命令創(chuàng)建烟馅。
CREATE INDEX index_name ON table_name (column_list);
22. 創(chuàng)建索引時需要注意什么？
- 非空字段：應該指定列為NOT NULL纸泄，除非你想存儲NULL构订。在mysql中纬凤，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引寡润、索引的統(tǒng)計信息以及比較運算更加復雜。你應該用0舅柜、一個特殊的值或者一個空串代替空值梭纹；
- 取值離散大的字段：（變量各個取值之間的差異程度）的列放到聯(lián)合索引的前面，可以通過count()函數(shù)查看字段的差異值致份，返回值越大說明字段的唯一值越多字段的離散程度高变抽；
- 索引字段越小越好：數(shù)據(jù)庫的數(shù)據(jù)存儲以頁為單位一頁存儲的數(shù)據(jù)越多一次IO操作獲取的數(shù)據(jù)越大效率越高。
23. 建索引的原則有哪些氮块？
1绍载、最左前綴匹配原則，非常重要的原則滔蝉，mysql會一直向右匹配直到遇到范圍查詢(>击儡、<、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的順序可以任意調整。
2谅年、=和in可以亂序茧痒，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式融蹂。
3旺订、盡量選擇區(qū)分度高的列作為索引弄企，區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復的比例区拳，比例越大我們掃描的記錄數(shù)越少拘领，唯一鍵的區(qū)分度是1，而一些狀態(tài)樱调、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0约素，那可能有人會問，這個比例有什么經(jīng)驗值嗎笆凌？使用場景不同圣猎，這個值也很難確定，一般需要join的字段我們都要求是0.1以上乞而，即平均1條掃描10條記錄送悔。
4、索引列不能參與計算爪模，保持列“干凈”欠啤，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單屋灌，b+樹中存的都是數(shù)據(jù)表中的字段值洁段，但進行檢索時，需要把所有元素都應用函數(shù)才能比較共郭，顯然成本太大眉撵。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’)。
5落塑、盡量的擴展索引纽疟，不要新建索引。比如表中已經(jīng)有a的索引憾赁，現(xiàn)在要加(a,b)的索引污朽，那么只需要修改原來的索引即可。
24. 使用索引查詢一定能提高查詢的性能嗎龙考？
通常通過索引查詢數(shù)據(jù)比全表掃描要快蟆肆。但是我們也必須注意到它的代價。
索引需要空間來存儲晦款，也需要定期維護炎功， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改缓溅。 這意味著每條記錄的I* NSERT蛇损，DELETE，UPDATE將為此多付出4，5 次的磁盤I/O淤齐。 因為索引需要額外的存儲空間和處理股囊，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能更啄，索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:
- 基于一個范圍的檢索稚疹，一般查詢返回結果集小于表中記錄數(shù)的30%。
- 基于非唯一性索引的檢索祭务。
25. 什么情況下不走索引（索引失效）内狗？
1、使用!= 或者 <> 導致索引失效
2义锥、類型不一致導致的索引失效
3柳沙、函數(shù)導致的索引失效
如：
SELECT * FROM user WHERE DATE(create_time) = '2020-09-03';
如果使用函數(shù)在索引列，這是不走索引的缨该。
4偎行、運算符導致的索引失效
SELECT * FROM user WHERE age - 1 = 20;
如果你對列進行了（+川背，-贰拿，*，/熄云，!）, 那么都將不會走索引膨更。
5、OR引起的索引失效
SELECT * FROM user WHERE name = '張三' OR height = '175';
OR導致索引是在特定情況下的缴允，并不是所有的OR都是使索引失效荚守，如果OR連接的是同一個字段，那么索引不會失效练般，反之索引失效矗漾。
6、模糊搜索導致的索引失效
SELECT * FROM user WHERE name LIKE '%冰';
當%放在匹配字段前是不走索引的薄料，放在==后面==才會走索引敞贡。
7、NOT IN摄职、NOT EXISTS導致索引失效
26. 自適應哈希索引
InnoDB中不存在哈希索引, 但是哈希索引確實有利于快速查找, 于是InnoDB引入了"自適應哈希索引", 在某些索引值被使用的非常頻繁時, InnoDB會在內存中的B+樹結構之上創(chuàng)建一個哈希索引, 用于這些頻繁使用的索引值的快速查找, 使得其存有哈咸芤郏快速查找的特點.
27 .索引相關高頻面試題
- 索引是什么? 索引優(yōu)缺點?
- 索引類似于目錄, 進行數(shù)據(jù)的快速定位
- 優(yōu)點: 加快數(shù)據(jù)檢索速度
- 缺點: 創(chuàng)建索引和維護索引需要消耗空間和時間
- MySQL索引類型
- 按存儲結構劃分 : B+Tree索引, Hash索引, FULLINDEX全文索引, R-TREE索引
- 按應用層次劃分: 普通索引, 唯一索引, 聯(lián)合索引, 聚簇索引, 非聚簇索引
- 索引底層實現(xiàn)? 為什么使用B+樹, 而不是B樹, BST, AVL, 紅黑樹等等?
- 什么是聚簇索引和非聚簇索引?
- 非聚簇索引一定會回表嗎?
(不一定, 覆蓋索引不會回表) - 什么是聯(lián)合索引?為什么需要注意聯(lián)合索引中的字段順序?
- 什么是最左前綴原則?
- 什么是前綴索引?
- 什么是索引下推?
- 如何查看MySQL語句是否使用到索引?
EXPLAIN SQL語句
possible_key: 可能用到的索引(可以查看是否有冗余索引)
key: 真正使用到的索引 - 為什么建議使用自增主鍵作為索引?
(索引維護可能造成頁分裂, 自增主鍵減少數(shù)據(jù)的移動和分裂) - 建立索引的原則
- 建立索引的字段最好為NOT NULL
- 索引字段占用空間越小越好
- 最左匹配原則
- =和in建立索引時順序可以任意, 比如a = 1 and b = 2 and c = 3 建立(a, b, c)和(b, a, c)索引效果是一樣的, MySQL查詢優(yōu)化器會進行優(yōu)化
- 建立的索引讓索引的選擇性盡可能接近1, 唯一索引的索引選擇性為1
- 盡量擴展索引, 不要讓索引冗余, 如有SQL需要對單個a進行索引, 那么上述條件建立的索引應該為(a, b, c)或(a, c, b)
- 索引列不能參與計算
- 什么情況下索引失效?
- 使用 != 或 <>
- 類型不一致導致索引失效
- 函數(shù)導致的索引失效, 函數(shù)用在索引列時, 不走索引
如SELECT * FROM t WHERE DATE(create_time) = 'yyyy-MM-dd' - 運算符導致的索引失效
如SELECT * FROM t WHERE k - 1 = 2, 若有INDEX(k), 則不走索引 - OR引起的索引失效
如SELECT * FROM t WHERE k = 1 OR j = 2, 若有INDEX(k), 則不走索引, 如果OR連接的時同一個字段, 則不會失效 - 模糊查詢導致的索引失效
如SELECT * FROM t WHERE name = '%三', %放字符串字段前匹配不走索引 - NOT IN, NOT EXISTS導致索引失效
【面向校招】全力備戰(zhàn)2023Golang實習與校招
歡迎群共同進步：
QQ群：1007576722
