文章內(nèi)容來自個人學(xué)習(xí)總結(jié)整理筆記、有不對或錯誤的地方歡迎指正峦睡，部分插圖引用來自其他大佬的博客韧骗，感謝貢獻(xiàn)嘉抒！
1、索引是什么袍暴？
索引是幫助Mysql高效獲取數(shù)據(jù)的排好序的數(shù)據(jù)結(jié)構(gòu)些侍，類似一本書的目錄，通過索引可以快速找到對應(yīng)的數(shù)據(jù)政模。
常見的索引數(shù)據(jù)結(jié)構(gòu)有二叉樹岗宣、紅黑樹、Hash淋样、B-Tree耗式、B+Tree，為什么MySQL數(shù)據(jù)庫索引選擇使用B+樹？刊咳？彪见？
不妨先來看看這些結(jié)構(gòu)是什么樣的
紅黑樹：紅黑樹是平衡二叉樹的一種實(shí)現(xiàn)，會自動做平衡處理娱挨，即單邊數(shù)量超過2個以上再插入的時候會做一次節(jié)點(diǎn)的平衡余指；可以看出當(dāng)數(shù)據(jù)量特別大的時候，可能會造成樹的層級特別高跷坝，也就意味著 IO 的次數(shù)越多,所以不是最優(yōu)方案酵镜。
二叉樹：左小右大，如果數(shù)據(jù)一直在右側(cè)柴钻，則二叉樹會失去效果淮韭，變成鏈表的結(jié)構(gòu)。如下圖所示贴届，當(dāng)數(shù)據(jù)量特別大或者極端情況下靠粪，索引的意義也不大，也會有掃描全表的可能性粱腻。
Hash：其檢索效率非常高庇配，索引的檢索可以一次定位斩跌，不像B-Tree 索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)绍些，最后才能訪問到頁節(jié)點(diǎn)這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠(yuǎn)高于 B-Tree 索引耀鸦，但是Hash不能進(jìn)行范圍查詢和排序等操作柬批，所以只在一些固定的情況下會用該索引算法。
B樹和B+樹：在B樹中袖订，你可以將鍵和值存放在內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)氮帐；但在B+樹中，內(nèi)部節(jié)點(diǎn)都是鍵洛姑，沒有值上沐，葉子節(jié)點(diǎn)同時存放鍵和值。B+樹的葉子節(jié)點(diǎn)有一條鏈相連楞艾，而B樹的葉子節(jié)點(diǎn)各自獨(dú)立参咙。
同時樹中每個結(jié)點(diǎn)可以有多個孩子，意味著可以放更多的索引硫眯；（當(dāng)容量大于15/16時會做一次平衡）
再來看問題蕴侧，為什么用B/B+樹這種結(jié)構(gòu)來實(shí)現(xiàn)索引呢？
其實(shí)不難看出紅黑樹等結(jié)構(gòu)也可以用來實(shí)現(xiàn)索引两入，但是文件系統(tǒng)及數(shù)據(jù)庫系統(tǒng)普遍使用B/B+樹結(jié)構(gòu)來實(shí)現(xiàn)索引净宵。mysql是基于磁盤的數(shù)據(jù)庫，索引是以索引文件的形式存在于磁盤中的，索引的查找過程就會涉及到磁盤IO(為什么涉及到磁盤IO請看文章后面的附加理解部分)消耗择葡，磁盤IO的消耗相比較于內(nèi)存IO的消耗要高好幾個數(shù)量級紧武，所以索引的組織結(jié)構(gòu)要設(shè)計得在查找關(guān)鍵字時要盡量減少磁盤IO的次數(shù)。
2敏储、MyISAM索引與InnoDB索引的區(qū)別脏里？
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引虹曙。
InnoDB的主鍵索引的葉子節(jié)點(diǎn)存儲著行數(shù)據(jù)迫横，因此主鍵索引非常高效。
MyISAM索引的葉子節(jié)點(diǎn)存儲的是行數(shù)據(jù)地址酝碳，需要再尋址一次才能得到數(shù)據(jù)矾踱。
InnoDB非主鍵索引的葉子節(jié)點(diǎn)存儲的是主鍵和其他帶索引的列數(shù)據(jù)，因此查詢時做到覆蓋索引會非常高效.
InnoDB支持hash索引疏哗，不支持全文索引呛讲，MyISAM則相反。
InnoDB支持事務(wù)返奉、外鍵贝搁、MyISAM不支持
InnoDB可以支持行級鎖、表級鎖芽偏，MyISAM只支持表級鎖雷逆。
3、MyISAM和InnoDB存儲引擎的數(shù)據(jù)結(jié)構(gòu)
這個問題不妨先看看這2種引擎的文件在磁盤上的表現(xiàn)形式污尉。
使用ISAM存儲引擎和InnoDB存儲引擎的表在磁盤上具有不同的擴(kuò)展名膀哲，其所表示的意義也不一樣，ISAM存儲引擎的表在磁盤上會有3個不同的擴(kuò)展文件被碗，.MYD某宪、.MYI、.frm锐朴；以表名叫student的表為例兴喂，他們說代表的意義如下：
student.frm：代表framework，存儲的是表結(jié)構(gòu)
student.MYD：D是Data的簡寫焚志，存儲的是數(shù)據(jù)行的記錄
student.MYI：I就是Index衣迷，存儲的是表里面的索引字段
如果要查找的字段是索引，則會通過MYI找到MYD,根據(jù)MYI存儲的文件磁盤地址娩嚼，直接定位到MYD蘑险；即ISAM引擎是將索引和數(shù)據(jù)分開存在2個文件。
Innodb存儲引擎的表在磁盤上只有一個文件.idb岳悟；以表名叫teacher存儲引擎為InnoDB的表為例
teacher.idb：i是index的縮寫佃迄，db是Data base的簡寫泼差，存儲的是索引和數(shù)據(jù)行的記錄；可以看出innodb是將索引和數(shù)據(jù)存在一個文件中的呵俏。
可以看到主鍵索引的葉子節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄堆缘，非主鍵索引葉子節(jié)點(diǎn)存儲的是主鍵的值，也就是說使用非主鍵索引查詢數(shù)據(jù)會先找到該數(shù)據(jù)的主鍵值普碎，然后通過主鍵找到對應(yīng)的數(shù)據(jù)吼肥。
4、為什么InnoDB表必須要有主鍵麻车，并且推薦使用整型的自增主鍵缀皱？
InnoDB使用聚集索引，數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點(diǎn)上动猬。這就要求同一個葉子節(jié)點(diǎn)內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放啤斗，因此每當(dāng)有一條新的記錄插入時，MySQL會根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置赁咙，如果頁面達(dá)到裝載因子（InnoDB默認(rèn)為15/16）钮莲，則開辟一個新的頁（節(jié)點(diǎn)）。如果表使用自增主鍵彼水，那么每次插入新的記錄崔拥，記錄就會順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁寫滿凤覆，就會自動開辟一個新的頁链瓦。如下圖所示：
這樣就會形成一個緊湊的索引結(jié)構(gòu)，近似順序填滿叛赚。由于每次插入時也不需要移動已有數(shù)據(jù)澡绩，因此效率很高，也不會增加很多開銷在維護(hù)索引上俺附。
如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機(jī)溪掀，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個位置事镣；此時MySQL不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，甚至目標(biāo)頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉揪胃，此時又要從磁盤上讀回來璃哟，這增加了很多開銷，同時頻繁的移動喊递、分頁操作造成了大量的碎片随闪，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁面骚勘。
因此铐伴，只要可以撮奏，請盡量在InnoDB上采用自增字段做主鍵。
5当宴、主鍵自增帶來的劣勢是什么畜吊？
對于高并發(fā)工作負(fù)載，在InnoDB中按主鍵順序插入可能會造成明顯的爭用户矢。主鍵上界會成為”熱點(diǎn)”玲献，因?yàn)樗械牟迦攵及l(fā)生在這里，所以并發(fā)插入可能導(dǎo)致間隙鎖競爭梯浪。另一個熱點(diǎn)可能是AUTO_INCREMENT鎖機(jī)制：如果遇到這個問題捌年，則可能需要考慮重新設(shè)計表或者應(yīng)用，或者更改innodb_autoinc_lock_mode配置挂洛。
6延窜、為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲的是主鍵值？
InnoDB索引非主鍵索引存儲的是主鍵ID抹锄，這樣可以保證數(shù)據(jù)一致性和節(jié)省存儲空間逆瑞，可以這么理解：商城系統(tǒng)訂單表會存儲一個用戶ID作為關(guān)聯(lián)外鍵，而不推薦存儲完整的用戶信息伙单，因?yàn)楫?dāng)我們用戶表中的信息（真是名稱获高、手機(jī)號、收貨地址···）修改后吻育，不需要再次維護(hù)訂單表的用戶數(shù)據(jù)念秧，同時也節(jié)省了存儲空間。（主要因?yàn)閕nnerDB的索引和數(shù)據(jù)是在一個文件中布疼，如果非主鍵索引不用主鍵的值摊趾，那么就意味做需要存多份數(shù)據(jù)文件）
7、為什么Mysql大多數(shù)都是用B+tree索引游两，而不使用Hash索引砾层？
使用hash索引可能會出現(xiàn)hash沖突，且hash索引不支持范圍查找贱案；同時Hash無法被用來避免數(shù)據(jù)的排序操作肛炮，所以大多數(shù)情況下不滿足業(yè)務(wù)需求，只有在確定只用到精確查詢等情況下可以考慮使用Hash索引宝踪。
8侨糟、什么是最左前綴原則？什么是最左匹配原則瘩燥？
最左優(yōu)先秕重，在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求厉膀，where子句中使用最頻繁的一列放在最左邊溶耘。
最左前綴匹配原則二拐，非常重要的原則，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的順序可以任意調(diào)整杰妓。=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序碘勉，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式
最左前綴法則：如果索引了多列巷挥，要遵守最左前綴原則，指的是查詢從索引的最左前列開始并且不跳過索引中的列验靡。
如下圖所示倍宾，如果聯(lián)合索引的列是(id,job,time)則在查詢的時候要遵守最左側(cè)開始索引，即先按照第一行10002的字段比較胜嗓、然后按照第二行Staff字段比較高职，接著是第三行時間來比較；如果有其他條件跳過則索引效果失效辞州。
通俗理解口訣：
? ?全值匹配我最愛怔锌，最左前綴要遵守；
? ?帶頭大哥不能死变过，中間兄弟不能斷埃元；
? ?索引列上少計算，范圍之后全失效媚狰；
? ?LIKE百分寫最右岛杀，覆蓋索引不寫星；
? ?不等空值還有or哈雏，索引失效要少用楞件。
9、MySQL InnoDB一棵B+樹可以存放多少行數(shù)據(jù)裳瘪？
約2千萬
在計算機(jī)中磁盤存儲數(shù)據(jù)最小單元是扇區(qū)，一個扇區(qū)的大小是512字節(jié)罪针，而文件系統(tǒng)（例如XFS/EXT4）他的最小單元是塊彭羹，一個塊的大小是4k，而對于我們的InnoDB存儲引擎也有自己的最小儲存單元——頁（Page）泪酱，一個頁的大小是16K派殷。在mysql中新創(chuàng)建的一個表初始化大小默認(rèn)就是16k还最。innodb的所有數(shù)據(jù)文件（后綴為ibd的文件），他的大小始終都是16384（16k）的整數(shù)倍毡惜。
那么現(xiàn)在我們需要計算出非葉子節(jié)點(diǎn)能存放多少指針拓轻，其實(shí)這也很好算，我們假設(shè)主鍵ID為bigint類型经伙，長度為8字節(jié)扶叉，而指針大小在InnoDB源碼中設(shè)置為6字節(jié)，這樣一共14字節(jié)帕膜，我們一個頁中能存放多少這樣的單元枣氧，其實(shí)就代表有多少指針；上面已經(jīng)說了一頁16k垮刹，轉(zhuǎn)換成字節(jié)就是16*1024=16384达吞；那14字節(jié)可以存放的數(shù)據(jù)量即16384/14=1170。那么可以算出一棵高度為2的B+樹荒典，能存放1170*16=18720條這樣的數(shù)據(jù)記錄酪劫。
根據(jù)同樣的原理我們可以算出一個高度為3的B+樹可以存放：1170*1170*16=21902400 條這樣的記錄。所以在InnoDB中B+樹高度一般為1-3層寺董，它就能滿足千萬級的數(shù)據(jù)存儲覆糟。在查找數(shù)據(jù)時一次頁的查找代表一次IO，所以通過主鍵索引查詢通常只需要1-3次IO操作即可查找到數(shù)據(jù)螃征。
下圖創(chuàng)建好的表默認(rèn)都是16k
10搪桂、MySql中char、varchar和Text的區(qū)別
char：存儲定長數(shù)據(jù)很方便盯滚，CHAR字段上的索引效率級高踢械，必須在括號里定義長度，可以有默認(rèn)值魄藕，比如定義char(10)内列，那么不論你存儲的數(shù)據(jù)是否達(dá)到了10個字節(jié)，都要占去10個字節(jié)的空間（自動用空格填充）背率，且在檢索的時候后面的空格會隱藏掉话瞧，所以檢索出來的數(shù)據(jù)需要記得用什么trim之類的函數(shù)去過濾空格。
varchar：存儲變長數(shù)據(jù)寝姿，但存儲效率沒有CHAR高交排，必須在括號里定義長度，可以有默認(rèn)值饵筑。保存數(shù)據(jù)的時候埃篓，不進(jìn)行空格自動填充，而且如果數(shù)據(jù)存在空格時根资，當(dāng)值保存和檢索時尾部的空格仍會保留架专。另外同窘，varchar類型的實(shí)際長度是它的值的實(shí)際長度+1，這一個字節(jié)用于保存實(shí)際使用了多大的長度部脚。
text：存儲可變長度的非Unicode數(shù)據(jù)想邦，最大長度為2^31-1個字符。text列不能有默認(rèn)值委刘，存儲或檢索過程中丧没，不存在大小寫轉(zhuǎn)換，后面如果指定長度钱雷，不會報錯誤骂铁，但是這個長度是不起作用的，意思就是你插入數(shù)據(jù)的時候罩抗，超過你指定的長度還是可以正常插入拉庵。
char和varchar一句話總結(jié)就是：“一個是時間換空間、一個是空間換時間”
11套蒂、百萬級別或以上的數(shù)據(jù)如何刪除
關(guān)于索引：由于索引需要額外的維護(hù)成本钞支，因?yàn)樗饕募菃为?dú)存在的文件,所以當(dāng)我們對數(shù)據(jù)的增加,修改,刪除,都會產(chǎn)生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執(zhí)行效率。所以操刀，在我們刪除數(shù)據(jù)庫百萬級別數(shù)據(jù)的時候烁挟，查詢MySQL官方手冊得知刪除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。
所以我們想要刪除百萬數(shù)據(jù)的時候可以先刪除索引（此時大概耗時三分多鐘）然后刪除其中無用數(shù)據(jù)（此過程需要不到兩分鐘）刪除完成后重新創(chuàng)建索引(此時數(shù)據(jù)較少了)創(chuàng)建索引也非彻强樱快撼嗓，約十分鐘左右。與之前的直接刪除絕對是要快速很多欢唾，更別說萬一刪除中斷,一切刪除會回滾且警。那更是坑了。
12礁遣、超大分頁怎么處理斑芜？
超大的分頁一般從兩個方向上來解決.
數(shù)據(jù)庫層面,這也是我們主要集中關(guān)注的(雖然收效沒那么大),類似于
select * from table where age > 20 limit 1000000,10
這種查詢其實(shí)也是有可以優(yōu)化的余地的. 這條語句需要load1000000數(shù)據(jù)然后基本上全部丟棄,只取10條當(dāng)然比較慢. 當(dāng)時我們可以修改為
select * from table where id in (select id from table where age > 20 limit 1000000,10)
.這樣雖然也load了一百萬的數(shù)據(jù),但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會很快. 同時如果ID連續(xù)的好,我們還可以
select * from table where id > 1000000 limit 10
效率也是不錯的,優(yōu)化的可能性有許多種,但是核心思想都一樣,就是減少load的數(shù)據(jù).
從需求的角度減少這種請求…主要是不做類似的需求(直接跳轉(zhuǎn)到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預(yù)測,可緩存)以及防止ID泄漏且連續(xù)被人惡意攻擊.
