什么是索引销部？索引的種類(lèi)眠饮？
索引是幫助數(shù)據(jù)庫(kù)高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)奥帘，是基于數(shù)據(jù)庫(kù)表創(chuàng)建的，包含一個(gè)表中某些列的值以及記錄對(duì)應(yīng)的地址仪召，并且把這些值存儲(chǔ)在一個(gè)數(shù)據(jù)結(jié)構(gòu)中寨蹋。最常見(jiàn)的就是使用哈希表松蒜、B+樹(shù)作為索引，項(xiàng)目中我們使用InnoDB引擎已旧，默認(rèn)的是B+樹(shù)秸苗。
什么情況下創(chuàng)建索引？
一般來(lái)說(shuō)运褪，在WHERE和JOIN中出現(xiàn)的列需要建立素引惊楼，因?yàn)镸ySQL只對(duì)＜，<=秸讹，>檀咙，＞=，BETWEEN璃诀， IN弧可，以及某些時(shí)候的LIKE才會(huì)使用索引（以通配符%和_開(kāi)頭作查詢時(shí)，MySQL不會(huì)使用索引）通常會(huì)根據(jù)慢查詢?nèi)罩緛?lái)優(yōu)化 SQL以及判斷是否建索引劣欢。
查詢更快棕诵、占用空間更小
- 適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中指定的列
- 基數(shù)較小的表凿将，索引效果較差校套，沒(méi)有必要在此列建立索引
- 使用短索引，如果對(duì)長(zhǎng)字符串列進(jìn)行索引牧抵，應(yīng)該指定一個(gè)前綴長(zhǎng)度搔确，這樣能夠節(jié)省大量索引空間，如果搜索詞超過(guò)索引前綴長(zhǎng)度灭忠，則使用索引排除不匹配的行膳算，然后檢查其余行是否可能匹配。
- 不要過(guò)度索引弛作。索引需要額外的磁盤(pán)空間涕蜂，并降低寫(xiě)操作的性能。在修改表內(nèi)容的時(shí)候映琳，索引會(huì)進(jìn)行更新甚至重構(gòu)机隙，索引列越多，這個(gè)時(shí)間就會(huì)越長(zhǎng)萨西。所以只保持需要的索引有利于查詢即可有鹿。
- 定義有外鍵的數(shù)據(jù)列一定要建立索引。
- 更新頻繁字段不適合創(chuàng)建索引
- 若是不能有效區(qū)分?jǐn)?shù)據(jù)的列不適合做索引列(如性別谎脯，男女未知葱跋，最多也就三種，區(qū)分度實(shí)在太低)
- 盡量的擴(kuò)展索引，不要新建索引娱俺。比如表中已經(jīng)有a的索引稍味，現(xiàn)在要加(a,b)的索引，那么只需要修改原來(lái)的索引即可荠卷。
- 對(duì)于那些查詢中很少涉及的列模庐，重復(fù)值比較多的列不要建立索引。
- 對(duì)于定義為text油宜、image和bit的數(shù)據(jù)類(lèi)型的列不要建立索引掂碱。
在創(chuàng)建索引時(shí)，怎樣考慮多個(gè)字段之間的順序的慎冤？
在創(chuàng)建多列索引時(shí)顶吮，我們根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列
放在最左邊粪薛，因?yàn)镸ySQ索引查詢會(huì)遵循最左前綴匹配的原則悴了，即最左
優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合素引的最左邊開(kāi)始匹配违寿。所以當(dāng)我們創(chuàng)建：
個(gè)聯(lián)合索引的時(shí)候湃交，如(key1,key2,key3)，相當(dāng)于創(chuàng)建了 (key1)
(key1,key2)和(key1,key2,key3)三個(gè)索引藤巢，這就是最左匹配原則搞莺。
關(guān)心過(guò)業(yè)務(wù)系統(tǒng)里面的sql耗時(shí)嗎？統(tǒng)計(jì)過(guò)慢查詢嗎掂咒？對(duì)慢查詢都怎么優(yōu)化過(guò)才沧？
在業(yè)務(wù)系統(tǒng)中，除了使用主鍵進(jìn)行的查詢绍刮，其他的都會(huì)在測(cè)試庫(kù)上測(cè)試其耗時(shí)温圆，慢查詢的統(tǒng)計(jì)主要由運(yùn)維在做，會(huì)定期將業(yè)務(wù)中的慢查詢反饋給我們孩革。
慢查詢的優(yōu)化首先要搞明白慢的原因是什么岁歉？是查詢條件沒(méi)有命中索引？是load了不需要的數(shù)據(jù)列膝蜈？還是數(shù)據(jù)量太大锅移？慢查詢參考
所以優(yōu)化也是針對(duì)這三個(gè)方向來(lái)的：
- 首先分析語(yǔ)句，看看是否load了額外的數(shù)據(jù)饱搏，可能是查詢了多余的行并且拋棄掉了非剃，可能是加載了許多結(jié)果中并不需要的列，對(duì)語(yǔ)句進(jìn)行分析以及重寫(xiě)推沸。
- 分析語(yǔ)句的執(zhí)行計(jì)劃备绽，然后獲得其使用索引的情況券坞，之后修改語(yǔ)句或者修改索引，使得語(yǔ)句可以盡可能的命中索引疯坤。
- 如果對(duì)語(yǔ)句的優(yōu)化已經(jīng)無(wú)法進(jìn)行，可以考慮表中的數(shù)據(jù)量是否太大深浮，如果是的話可以進(jìn)行橫向或者縱向的分表压怠。
為什么使用B+樹(shù)？
由于索引是存在于磁盤(pán)中飞苇，當(dāng)索引非常大的時(shí)候菌瘫，比如達(dá)到幾個(gè)G的時(shí)候，無(wú)法一次加載到內(nèi)存中布卡，所以數(shù)據(jù)庫(kù)中索引使用的是查找效率更高的樹(shù)形結(jié)構(gòu)雨让。B+樹(shù)是平衡多路查找樹(shù)，是為磁盤(pán)等外存儲(chǔ)設(shè)備設(shè)計(jì)的一種平衡查找樹(shù)忿等。
系統(tǒng)從磁盤(pán)讀取數(shù)據(jù)到內(nèi)存時(shí)是以磁盤(pán)塊(block)為基本單位的栖忠，位于同一個(gè)磁盤(pán)塊中的數(shù)據(jù)會(huì)被一次性讀取出來(lái)，InnoDB存儲(chǔ)引擎中有頁(yè) (Page) 的概念贸街，頁(yè)是其磁盤(pán)管理的最小單位庵寞。InnoDB存儲(chǔ)引擎中默認(rèn)每個(gè)頁(yè)的大小為16KB，可通過(guò)參數(shù)innodb_page_size設(shè)置頁(yè)的大小薛匪，InnoDB在把磁盤(pán)數(shù)據(jù)讀入到磁盤(pán)時(shí)會(huì)以頁(yè)為基本單位捐川，在查詢數(shù)據(jù)時(shí)如果一個(gè)頁(yè)中的每條數(shù)據(jù)都能有助于定位數(shù)據(jù)記錄的位置這將會(huì)減少磁盤(pán)IO次數(shù)，提高查詢效率逸尖。
B+樹(shù)使用有序數(shù)組鏈表+平衡多叉樹(shù)改良了B樹(shù)的有序數(shù)組＋平衡多叉樹(shù)古沥；B+樹(shù)的關(guān)鍵字全部存放在葉子節(jié)點(diǎn)中，非葉子節(jié)點(diǎn)用來(lái)做索引娇跟，而葉子節(jié)點(diǎn)中有一指針指向一下個(gè)葉子節(jié)點(diǎn)岩齿。做這個(gè)優(yōu)化的目的是為了提高區(qū)間訪問(wèn)的性能。
舉個(gè)例子苞俘？
數(shù)據(jù)庫(kù)索引采用B+樹(shù)的主要原因是B樹(shù)在提高了磁盤(pán)IO性能的同時(shí)并沒(méi)有解決元素遍歷效率低下的問(wèn)題纯衍。正是為了解決這個(gè)問(wèn)題，B+樹(shù)應(yīng)運(yùn)而生苗胀。B+樹(shù)只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹(shù)的遍歷襟诸。而且在數(shù)據(jù)庫(kù)中基于范圍的查詢是非常頻繁的，也是B+樹(shù)的優(yōu)勢(shì)所在基协。
例如：要查 5-10之間的歌亲，B+樹(shù)一把到5這個(gè)標(biāo)記，再一把到10澜驮，然后串起來(lái)就行了陷揪。而B(niǎo)樹(shù)在找到第一個(gè)符合條件的數(shù)字5后，訪問(wèn)完第一個(gè)關(guān)鍵字所在的塊后，得遍歷這個(gè)B樹(shù)悍缠，獲取下一個(gè)塊卦绣，直到遇到一個(gè)不符合條件的關(guān)鍵字。遍歷的過(guò)程是比較復(fù)雜的飞蚓。
什么是B+樹(shù)滤港？
B+樹(shù)的演變要從二叉樹(shù)開(kāi)始，可參考B+樹(shù)的演變趴拧。前面有提到溅漾，系統(tǒng)從磁盤(pán)讀取數(shù)據(jù)到內(nèi)存的時(shí)候是以磁盤(pán)塊為基本單位，將磁盤(pán)塊中的數(shù)據(jù)一次性的讀取出來(lái)的著榴。如果是二叉樹(shù)添履，如圖（二叉樹(shù)1）每一個(gè)節(jié)點(diǎn)只存儲(chǔ)一個(gè)鍵值對(duì)，如果是海量的數(shù)據(jù)的話脑又，那么就會(huì)有海量的節(jié)點(diǎn)暮胧，那么如果要檢索出需要的數(shù)據(jù)，可能就要進(jìn)行多次的IO问麸，是會(huì)導(dǎo)致效率低下的叔壤。
這時(shí)候就引入了B樹(shù)（BalanceTree），每一個(gè)節(jié)點(diǎn)成為頁(yè)（page）,mysql的數(shù)據(jù)讀取單位也就是頁(yè)口叙。相對(duì)于平衡二叉樹(shù)來(lái)說(shuō)炼绘，每一個(gè)節(jié)點(diǎn)存儲(chǔ)了更多的鍵值和數(shù)據(jù)，同時(shí)妄田，每一個(gè)節(jié)點(diǎn)擁有更多的子節(jié)點(diǎn)俺亮，稱為“階”，高度也就相對(duì)的比較低疟呐，所以脚曾，B樹(shù)讀取磁盤(pán)的IO次數(shù)也會(huì)大大的減小，數(shù)據(jù)查找效率也會(huì)高启具。
B+樹(shù)的非葉子節(jié)點(diǎn)上是不存儲(chǔ)數(shù)據(jù)的本讥，僅存儲(chǔ)鍵值，因?yàn)閿?shù)據(jù)庫(kù)中頁(yè)的大小是固定的（innodb每個(gè)頁(yè)的大小是16kb鲁冯，當(dāng)然可以通過(guò)參數(shù)進(jìn)行配置）不存儲(chǔ)數(shù)據(jù)拷沸，就可以存儲(chǔ)更多的鍵值，樹(shù)就會(huì)更矮薯演，更胖一些撞芍，B+樹(shù)的階數(shù)是等于鍵值的數(shù)量。如果B+樹(shù)的一個(gè)節(jié)點(diǎn)可以存放1000個(gè)鍵值的話跨扮，那么就可以存儲(chǔ)大約10億的數(shù)據(jù)序无，根節(jié)點(diǎn)是常駐于內(nèi)存之中的验毡，所以只需要兩次磁盤(pán)的IO就可以搞定。因?yàn)锽+樹(shù)的數(shù)據(jù)都是按照順序進(jìn)行排列的帝嗡，所以進(jìn)行范圍查找晶通，排序查找，分組查找以及去重都會(huì)很快哟玷。而B(niǎo)樹(shù)都是在各個(gè)節(jié)點(diǎn)上狮辽，要困難一些。
B+樹(shù)和hash索引比較起來(lái)有什么優(yōu)缺點(diǎn)碗降？
哈希索引適合等值查詢隘竭，但是無(wú)法進(jìn)行范圍查詢塘秦；哈希索引沒(méi)辦法利用索引完成排序以及l(fā)ike ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢讼渊，其實(shí)本質(zhì)上也是范圍查詢）；哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則尊剔；B+樹(shù)索引的關(guān)鍵字檢索效率比較平均爪幻，哈希索引如果有大量重復(fù)鍵值的情況下，哈希索引的效率會(huì)很低须误，可能存在哈希碰撞問(wèn)題挨稿。
B+樹(shù)葉子節(jié)點(diǎn)都可以存什么東西？
InnoDB的B+樹(shù)可能存儲(chǔ)的是整行數(shù)據(jù)京痢，也有可能是主鍵的值奶甘，索引B+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引祭椰。而索引B+樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵的值的是非主鍵索引臭家，也被稱之為非聚簇索引。
Innodb和myisam引擎及索引學(xué)習(xí)參考
聚簇索引和非聚簇索引在查詢時(shí)有什么區(qū)別方淤？
主鍵索引查詢只會(huì)查一次钉赁，而非主鍵索引需要回表查詢多次，通過(guò)覆蓋索引也可以只查詢一次携茂，覆蓋索引指一個(gè)查詢語(yǔ)句的執(zhí)行只用從索引中就能夠取得你踩，不必從數(shù)據(jù)表中讀取。
MySQL只需要通過(guò)索引就可以返回查詢所需要的數(shù)據(jù)讳苦，這樣避免了查到索引后再返回表操作带膜，減少I(mǎi)/O提高效率。
聚簇索引VS非聚簇索引
聚集索引（聚簇索引)：以InnoDB 作為存儲(chǔ)引擎的表鸳谜，表中的數(shù)據(jù)都會(huì)有一個(gè)主鍵钱慢，即使你不創(chuàng)建主鍵，系統(tǒng)也會(huì)幫你創(chuàng)建一個(gè)隱式的主鍵卿堂。
非聚集索引（非聚簇索引)：以主鍵以外的列值作為鍵值構(gòu)建的 B+ 樹(shù)索引束莫，我們稱之為非聚集索引懒棉。
緩存知識(shí)介紹
一次IO讀寫(xiě)，可以獲取到16K（需要看操作系統(tǒng)中的配置）大小的資源览绿，讀取到的數(shù)據(jù)區(qū)域?yàn)镻age（頁(yè)）策严。當(dāng)需要查詢某個(gè)索引的B+樹(shù)結(jié)構(gòu)的時(shí)候，某些頁(yè)被加載到內(nèi)存的緩存區(qū)域中饿敲，查詢操作會(huì)在內(nèi)存里操作妻导，而不用再次進(jìn)行IO操作了。當(dāng)要查詢的行數(shù)據(jù)不在緩存里怀各，才會(huì)觸發(fā)新的IO操作倔韭。
通過(guò)上面的緩存知識(shí)來(lái)看，如果數(shù)據(jù)存放的位置是相對(duì)連續(xù)的瓢对，則緩存命中率會(huì)很高寿酌。而聚簇索引正好就是在磁盤(pán)上連續(xù)存放的。因?yàn)镸yISAM的主索引并非聚簇索引硕蛹，那么他的數(shù)據(jù)的物理地址（硬盤(pán)數(shù)據(jù)區(qū)的編號(hào)）相對(duì)于聚簇索引是比較凌亂的醇疼，拿到這些物理地址，按照合適的算法進(jìn)行I/O讀取法焰，于是開(kāi)始不停的尋道秧荆，不停的旋轉(zhuǎn)，且存儲(chǔ)地址跨度過(guò)大埃仪，也容易導(dǎo)致緩存命中率低乙濒。
聚簇索引的優(yōu)缺點(diǎn)
優(yōu)點(diǎn)：
1.數(shù)據(jù)訪問(wèn)更快，因?yàn)榫鄞厮饕龑⑺饕蛿?shù)據(jù)保存在同一個(gè)B+樹(shù)中卵蛉，因此從聚簇索引中獲取數(shù)據(jù)比非聚簇索引更快颁股。聚簇索引對(duì)于主鍵的排序查找和范圍查找速度非常快
缺點(diǎn)：
1.插入速度嚴(yán)重依賴于插入順序毙玻，按照主鍵的順序插入是最快的方式豌蟋，否則將會(huì)出現(xiàn)頁(yè)分裂，嚴(yán)重影響性能桑滩。因此梧疲，對(duì)于InnoDB表，我們一般都會(huì)定義一個(gè)自增的ID列為主鍵运准。
2.更新主鍵的代價(jià)很高幌氮，因?yàn)閷?huì)導(dǎo)致被更新的行移動(dòng)。因此胁澳，對(duì)于InnoDB表该互，我們一般定義主鍵為不可更新。
3.二級(jí)索引訪問(wèn)需要兩次索引查找韭畸，第一次找到主鍵值宇智，第二次根據(jù)主鍵值找到行數(shù)據(jù)蔓搞。
以下參考自 鏈接
myisam的主索引和次索引都指向物理行(磁盤(pán)位置)。
innodb的主鍵下存儲(chǔ)該行的數(shù)據(jù)随橘，此索引指向?qū)χ麈I的引用喂分。
myisam的索引存儲(chǔ)圖如下，可以看出机蔗，無(wú)論是id還是cat_id蒲祈，下面都存儲(chǔ)有執(zhí)行物理地址的值。通過(guò)主鍵索引或者次索引來(lái)查詢數(shù)據(jù)的時(shí)候萝嘁，都是先查找到物理位置梆掸，然后再到物理位置上去尋找數(shù)據(jù)。
innodb的索引存儲(chǔ)圖如下牙言，我們會(huì)發(fā)現(xiàn)酸钦，主鍵索引下面直接存儲(chǔ)有數(shù)據(jù)，而次索引下嬉挡，存儲(chǔ)的是主鍵的id钝鸽。通過(guò)主鍵查找數(shù)據(jù)的時(shí)候汇恤，就會(huì)很快查找到數(shù)據(jù)庞钢，但是通過(guò)次索引查找數(shù)據(jù)的時(shí)候，需要先查找到對(duì)應(yīng)的主鍵id因谎，然后才能查找到對(duì)應(yīng)的數(shù)據(jù)基括。
