概念

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

有什么用？

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

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

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

分類

物理存儲(chǔ)順序

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

邏輯存儲(chǔ)

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

數(shù)據(jù)類型

• b+ tree索引: btree也就是平衡多路查找樹(shù)的升級(jí)版本
• hash索引：適合做精確的搜索，不適合做范圍查詢
• fulltext索引：希望通過(guò)關(guān)鍵字的匹配來(lái)進(jìn)行查詢過(guò)濾，那么就需要基于相似度的查詢
  • 引擎支持
    • MySQL 5.6 以前的版本，只有 MyISAM 存儲(chǔ)引擎支持全文索引；
    • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存儲(chǔ)引擎均支持全文索引;
    • 只有字段的數(shù)據(jù)類型為 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)，支持等于和范圍查詢（包含大于，小于，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ù)存放的更加緊密，具有更好的空間局部性。因此訪問(wèn)葉子幾點(diǎn)上關(guān)聯(lián)的數(shù)據(jù)也具有更好的緩存命中率。
  • B+樹(shù)的葉子結(jié)點(diǎn)都是相鏈的，因此對(duì)整棵樹(shù)的便利只需要一次線性遍歷葉子結(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ū)分度盡量的大
• 索引(邏輯)類型選擇，按照邏輯順序
  • 每張表必須有一個(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版本慢查詢?nèi)罩?/a>

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