概念

• mysql index官方文檔
• 索引是數(shù)據(jù)庫(kù)管理系統(tǒng)中一種數(shù)據(jù)結(jié)構(gòu)檩禾，用以協(xié)助快速查詢(xún)數(shù)據(jù)庫(kù)表中數(shù)據(jù)，典型的索引結(jié)構(gòu)如B+ tree疤祭。

有什么用盼产？

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度勺馆。

打個(gè)比方戏售，如果合理的設(shè)計(jì)且使用索引的MySQL是一輛蘭博基尼的話(huà)，那么沒(méi)有設(shè)計(jì)和使用索引的MySQL就是一個(gè)人力三輪車(chē)谓传。

拿漢語(yǔ)字典的目錄頁(yè)（索引）打比方蜈项，我們可以按拼音、筆畫(huà)续挟、偏旁部首等排序的目錄（索引）快速查找到需要的字。

分類(lèi)

物理存儲(chǔ)順序

• MySQL的Innodb存儲(chǔ)引擎的索引分為聚集索引和非聚集索引(二級(jí)索引)侥衬。 聚集索引是Innodb引擎才有的概念诗祸。
• 為什么說(shuō)是按照物理存儲(chǔ)順序分類(lèi)呢？因?yàn)榫奂饕倪壿嬳樞蚓褪菙?shù)據(jù)記錄的物理順序轴总，而非聚集索引卻不是如此直颅。 舉個(gè)例子，聚集索引就像是漢語(yǔ)字典中的拼音索引怀樟，字典中的字就是按照這個(gè)順序存的功偿。非聚集索引就像是部首索引，相同偏旁的字在索引頁(yè)上是相鄰的往堡，但是真實(shí)的位置卻不一定是相鄰的械荷。類(lèi)似的例子還有電話(huà)簿，大家自行想象一下虑灰。
• 聚集索引怎么選定呢吨瞎？
  • 第一個(gè)唯一且不能為NULL的列就會(huì)被選為聚集索引，一般就是主鍵了穆咐。
  • 如果沒(méi)有符合條件的列颤诀，引擎會(huì)自動(dòng)生成一個(gè)6字節(jié)的ROWID字旭。
• 非聚集索引為什么被稱(chēng)為二級(jí)索引呢？ 因?yàn)槠渌饕罱K是指向聚集索引的key崖叫，一般來(lái)說(shuō)記錄還要通過(guò)聚集索引才能找到真實(shí)的記錄遗淳，這樣要經(jīng)過(guò)兩次索引查詢(xún)，所以被稱(chēng)為二級(jí)索引心傀。
• 非聚集索引可以有多個(gè)么屈暗？Innodb存儲(chǔ)引擎中行記錄就是按照聚集索引維度順序存儲(chǔ)的，Innodb的表也稱(chēng)為索引表剧包；因?yàn)樾杏涗浿荒馨凑找粋€(gè)維度進(jìn)行排序恐锦，所以一張表只能有一個(gè)聚集索引。

邏輯存儲(chǔ)

• 唯一索引：column不可重復(fù)的索引
• 主鍵索引：非NULL的唯一索引
• 普通索引：相比前兩者要求要更低疆液，可以重復(fù)一铅，可以為空，一般要求區(qū)分度比較大堕油，不然可能也不會(huì)走這個(gè)索引
• 聯(lián)合索引：多列組合的索引
• 覆蓋索引：官方定義是說(shuō)包含了一個(gè)查詢(xún)的所有列的索引潘飘。列索引(column index)，或者是聯(lián)合索引(composite index)就可以成為覆蓋索引掉缺。

數(shù)據(jù)類(lèi)型

• b+ tree索引: btree也就是平衡多路查找樹(shù)的升級(jí)版本
• hash索引：適合做精確的搜索卜录，不適合做范圍查詢(xún)
• fulltext索引：希望通過(guò)關(guān)鍵字的匹配來(lái)進(jìn)行查詢(xún)過(guò)濾，那么就需要基于相似度的查詢(xún)
  • 引擎支持
    • MySQL 5.6 以前的版本眶明，只有 MyISAM 存儲(chǔ)引擎支持全文索引艰毒；
    • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存儲(chǔ)引擎均支持全文索引;
    • 只有字段的數(shù)據(jù)類(lèi)型為 char搜囱、varchar丑瞧、text 及其系列才可以建全文索引。
• R-tree索引: 一種多維度的空間索引蜀肘，例如:地圖左邊绊汹，巨型，多邊形

b+ tree

• b+ tree官方文檔（注意官方文檔上一直寫(xiě)的是b-tree,但實(shí)際上大家為了區(qū)分普通的b-tree扮宠，都會(huì)叫b+ tree）
• b tree: 在計(jì)算機(jī)科學(xué)中西乖，B樹(shù)（B-tree）是一種樹(shù)狀數(shù)據(jù)結(jié)構(gòu)，它能夠存儲(chǔ)數(shù)據(jù)坛增、對(duì)其進(jìn)行排序并允許以O(shè)(log n)的時(shí)間復(fù)雜度運(yùn)行進(jìn)行查找获雕、順序讀取、插入和刪除的數(shù)據(jù)結(jié)構(gòu)轿偎。B樹(shù)典鸡，概括來(lái)說(shuō)是一個(gè)節(jié)點(diǎn)可以擁有多于2個(gè)子節(jié)點(diǎn)的二叉查找樹(shù)。與自平衡二叉查找樹(shù)不同坏晦，B-樹(shù)為系統(tǒng)最優(yōu)化大塊數(shù)據(jù)的讀和寫(xiě)操作萝玷。
• b+ tree
  • 一種廣泛用于各種數(shù)據(jù)庫(kù)索引的數(shù)據(jù)結(jié)構(gòu), 能始終保持索引是排序的狀態(tài)嫁乘，支持等于和范圍查詢(xún)（包含大于，小于球碉，between and）
  • 它并不是一顆二叉樹(shù)蜓斧，因?yàn)樗淖庸?jié)點(diǎn)個(gè)數(shù)不限于兩個(gè)
• b+ tree與 b tree的區(qū)別是什么？參考文章淺談算法和數(shù)據(jù)結(jié)構(gòu): 十 平衡查找樹(shù)之B樹(shù)
  • b+ tree的非葉子節(jié)點(diǎn)不包含數(shù)據(jù)信息睁冬，只包含導(dǎo)航信息
  • 樹(shù)的所有葉結(jié)點(diǎn)構(gòu)成一個(gè)有序鏈表挎春，可以按照關(guān)鍵碼排序的次序遍歷全部記錄。
  • b+ tree有n棵子樹(shù)的結(jié)點(diǎn)中含有n個(gè)關(guān)鍵字豆拨； (而b tree是n棵子樹(shù)有n-1個(gè)關(guān)鍵字)
• B+ tree的優(yōu)點(diǎn)在于：
  • 由于B+樹(shù)在內(nèi)部節(jié)點(diǎn)上不好含數(shù)據(jù)信息直奋，因此在內(nèi)存頁(yè)中能夠存放更多的key。 數(shù)據(jù)存放的更加緊密施禾，具有更好的空間局部性脚线。因此訪(fǎng)問(wèn)葉子幾點(diǎn)上關(guān)聯(lián)的數(shù)據(jù)也具有更好的緩存命中率。
  • B+樹(shù)的葉子結(jié)點(diǎn)都是相鏈的弥搞，因此對(duì)整棵樹(shù)的便利只需要一次線(xiàn)性遍歷葉子結(jié)點(diǎn)即可邮绿。而且由于數(shù)據(jù)順序排列并且相連，所以便于區(qū)間查找和搜索攀例。而B(niǎo)樹(shù)則需要進(jìn)行每一層的遞歸遍歷船逮。相鄰的元素可能在內(nèi)存中不相鄰，所以緩存命中性沒(méi)有B+樹(shù)好粤铭。

索引列

• 單列索引
• 多列索引-組合索引/聯(lián)合索引/復(fù)合索引

存儲(chǔ)引擎支持

Innodb

• B+ tree索引
• (5.6及以后的版本)全文索引
• （5.7及以后的版本）地理空間索引（Geospatial indexing）
• 自適應(yīng)哈希索引：注意這個(gè)只是個(gè)優(yōu)化項(xiàng)目挖胃，不能自己在DDL中選定。在Mysql5.6及以上可用梆惯，這個(gè)索引是Innodb針對(duì)于Using 和 in 這兩種操作的一種優(yōu)化手段冠骄，只存在于內(nèi)存中，是基于已有B+ tree索引建立的加袋。hash的key是任意長(zhǎng)度的b+ tree索引的前綴，這個(gè)長(zhǎng)度是根據(jù)索引被搜索的模式來(lái)決定的抱既。

Myisam

• B+ tree索引
• 全文索引
• 地理空間索引(Geospatial indexing)

使用技巧

建索引

• 對(duì)常用排序职烧、分組，CURD條件字段應(yīng)當(dāng)建立索引
• 索引的數(shù)量不宜過(guò)多防泵，避免冗余索引蚀之，DBA的建議是不超過(guò)5個(gè)。聯(lián)合索引的鍵不超過(guò)3個(gè)捷泞，太都了之后更新效率必然受到影響足删，而且似乎也沒(méi)有必要，極有可能造成索引的冗余
• 主鍵盡量是數(shù)字锁右，避免使用字符串失受，因?yàn)槭褂脭?shù)字的判斷速度快
• 索引的區(qū)分度盡量的大
• 索引(邏輯)類(lèi)型選擇讶泰，按照邏輯順序
  • 每張表必須有一個(gè)主鍵索引，因?yàn)橹麈I索引速度快
  • 對(duì)于常用的字段拂到，如果唯一可以建立唯一索引痪署，如果不唯一可考慮建立普通索引
  • 組合使用列，可以使用聯(lián)合索引

用索引

• 能用主鍵索引的地方一定要用上兄旬，速度快
• 避免在索引字段上使用函數(shù)
• 聯(lián)合索引的最左前綴原則

優(yōu)化分析

• 5.7版本explain關(guān)鍵字狼犯，限于篇幅，這里只是簡(jiǎn)單說(shuō)一下
  • 作用：獲取SQL的執(zhí)行計(jì)劃信息
  • 信息來(lái)源：SQL語(yǔ)句優(yōu)化器-optimizer领铐。這些信息包含：優(yōu)化器是怎么處理語(yǔ)句的, 包括表連接的順序悯森。具體的信息見(jiàn)explain輸出格式
  • 作用范圍： SELECT, DELETE, INSERT, REPLACE以及UPDATE
• 5.7版本慢查詢(xún)?nèi)罩?/a>

• 淺談算法和數(shù)據(jù)結(jié)構(gòu): 十 平衡查找樹(shù)之B樹(shù)
• mysql開(kāi)發(fā)者手冊(cè)