MySQL是我們經(jīng)常使用到的數(shù)據(jù)庫(kù)，因?yàn)楹芏鄷r(shí)候是免費(fèi)的，所以用的比較多，我們?cè)谠O(shè)計(jì)表的時(shí)候應(yīng)該會(huì)使用到索引，所以我們一起來(lái)聊下索引應(yīng)該怎么去設(shè)置
1、索引的定義
什么叫索引 ，索引是幫助Mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)，在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿(mǎn)足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引，如圖所示
索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在磁盤(pán)上索引是數(shù)據(jù)庫(kù)中用來(lái)提高性能的最常用工具。
索引的優(yōu)勢(shì)：1）類(lèi)似于書(shū)籍中的目錄索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)中的IO成本 2）通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗
索引的劣勢(shì)：1）實(shí)際上索引也是一張表，該表中保存了主鍵和索引字段，并指向?qū)嶓w類(lèi)的記錄，所以索引列也是要占用空間的。
2）雖然索引大大提高了查詢(xún)效率，同時(shí)卻也降低更新表的速度，如對(duì)表進(jìn)行insert、update、delete。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息
2、存儲(chǔ)引擎對(duì)各種索引類(lèi)型的支持
平常所說(shuō)的索引，若沒(méi)有特別指明，都是指B+數(shù)(多路搜索樹(shù)，并不一定是二又的)結(jié)構(gòu)組織的所有。其中聚集索引、非聚集索引( 普通索引，前綴索引、唯一索引，全文索引)默認(rèn)都是使用B+tree樹(shù)索引，統(tǒng)稱(chēng)為索引。
3、Btree結(jié)構(gòu)
Btree又叫多路平衡搜索樹(shù)，一顆m又的Btree特性如下樹(shù)中每個(gè)節(jié)點(diǎn)最多包含M個(gè)孩子除根節(jié)點(diǎn)和葉子節(jié)點(diǎn)外，每個(gè)節(jié)點(diǎn)至少有ceil(m/2)個(gè)孩子若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有兩個(gè)孩子。所有的葉子節(jié)點(diǎn)都在同一層。
每個(gè)非葉子節(jié)點(diǎn)有n個(gè)key與n+1個(gè)指針組成，其中[ceil(m/2)-1]<=n<=m-1。
以5叉Btree為例，key的數(shù)量:工時(shí)推導(dǎo)[ceil(m/2)-1]<n<=m-1。所以2<=n<n<=4。當(dāng)n>4時(shí)，
中間節(jié)點(diǎn)分裂到父子節(jié)點(diǎn)，兩邊節(jié)點(diǎn)分裂。
插入CNGAHEKOMFWLTZDPRXYS數(shù)據(jù)為例。
到此，該Btree樹(shù)就已經(jīng)構(gòu)建完成了，Btree樹(shù)和二叉樹(shù)相比，查詢(xún)數(shù)據(jù)的效率更高，因?yàn)橄嗤臄?shù)據(jù)量來(lái)說(shuō)，Btree的層級(jí)結(jié)構(gòu)比二叉樹(shù)小，因此搜索速度快
B+tree為Btree的變種，B+tree與Btree的區(qū)別為:
1）n又B+tree最多包含n個(gè)key，而B(niǎo)tree最多包含n-1個(gè)key
2）B+tree的葉子節(jié)點(diǎn)保存所有的key信息，依key大小順序排列
3）所有的非葉子節(jié)點(diǎn)都可以看做是key的索引部分。
由于B+tree只有葉子節(jié)點(diǎn)保存key信息，查詢(xún)?nèi)魏蝛ey都要從root走到葉子，所以B+tree的查詢(xún)效率更加穩(wěn)定
Mysql索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+bree進(jìn)行了優(yōu)化，在原B+Tree的基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針，就形成了帶有順序指針的B+bree，提高區(qū)間訪(fǎng)問(wèn)性能。
4、索引的種類(lèi)
- 聚集索引
- 非聚集索引(輔助索引、二級(jí)索)
CREATE TABLEmerchandiseidint(11)NOT NULLserial no varchar(20) DEFAULT NULLname varchar(255)DEFAULT NULL.unit_price decimal(10, 2) DEFAULT NULLPRIMARY KEY Cid)USING BTREECHARACTER SET = utf8 COLLATE =utf8_general_ci ROW_FORMAT = Dynamic;
非聚集索引
MyISAM 使用的是輔助索引，索引中每一個(gè)葉子節(jié)點(diǎn)僅僅記錄的是每行數(shù)據(jù)的物理地址，即行指針
聚集索引
Inndb主鍵索引是聚簇索引，其葉子節(jié)點(diǎn)則記錄了主鍵值事務(wù)id、用于事務(wù)和MVCC的回流指針以及所有的剩余列，其他非主鍵索引是非聚集索引
5、MySQL的索引-設(shè)計(jì)原則
- 對(duì)查詢(xún)頻次較高，且數(shù)據(jù)量比較大的表建立索引。
- 索引字段的選擇，最佳候選列應(yīng)當(dāng)從where子句的條件中提取，如果where子句的組合比較多，那么應(yīng)當(dāng)選擇最常用、過(guò)濾效果最好的列的組合
- 使用唯一索引，區(qū)分越高，使用索引的效率越高。
- 索引可以有效提升查詢(xún)數(shù)據(jù)庫(kù)的效率，但是索引數(shù)量不是多多益善，索引越多，維護(hù)索引的代，價(jià)自然也就水漲船高。對(duì)于插入、更新、刪除等DML操作比較頻繁的表來(lái)說(shuō)，索引過(guò)多，會(huì)映入相當(dāng)高的維護(hù)代價(jià)，境地DML操作的效率，增加相應(yīng)操作的時(shí)間消耗。另外索引過(guò)多的話(huà)MySQL也會(huì)犯選擇困難病，雖然最終仍然會(huì)找到一個(gè)可用的索引，但無(wú)疑提交了選擇的代價(jià)
- 使用短索引，索引創(chuàng)建之后也是使用硬盤(pán)來(lái)存儲(chǔ)，因此提升索引訪(fǎng)問(wèn)的I/0效率，也可以提升總體的訪(fǎng)問(wèn)效率。假如構(gòu)成索引的字段總長(zhǎng)度比較短，那么在給定大小的存儲(chǔ)塊內(nèi)可以以存儲(chǔ)更多的索引值，相對(duì)應(yīng)的可以有效的提升MySOL訪(fǎng)問(wèn)索引的I/O效率
- 利用最左前綴，N個(gè)列組合而成的組合索引，那么相當(dāng)于是創(chuàng)建了N個(gè)索引，如果查詢(xún)時(shí)where子句中使用了組成該索引的前幾個(gè)字段，那么這條查詢(xún)SOL可以利用組合索引來(lái)提高查詢(xún)效率
