1、你一般怎么建索引的？

去my.cnf里配置三個(gè)配置

監(jiān)控到慢SQL后，就馬上開始建索引?

NO,NO,NO….這種時(shí)候，應(yīng)該先考慮你的SQL能不能進(jìn)行SQL優(yōu)化。

例如，當(dāng)只要一行數(shù)據(jù)時(shí)使用 limit 1

查詢時(shí)如果已知會(huì)得到一條數(shù)據(jù)，這種情況下加上 limit 1 會(huì)增加性能。因?yàn)?mysql 數(shù)據(jù)庫引擎會(huì)在找到一條結(jié)果停止搜索，而不是繼續(xù)查詢下一條是否符合標(biāo)準(zhǔn)直到所有記錄查詢完畢。

然而大多數(shù)情況下，業(yè)務(wù)SQL十分復(fù)雜，沒法優(yōu)化。所以就要建立索引了。這個(gè)時(shí)候，參照如下規(guī)則建立索引

• (1)索引并非越多越好，大量的索引不僅占用磁盤空間，而且還會(huì)影響insert,delete,update等語句的性能

• (2)避免對(duì)經(jīng)常更新的表做更多的索引，并且索引中的列盡可能少；對(duì)經(jīng)常用于查詢的字段創(chuàng)建索引，避免添加不必要的索引

• (3)數(shù)據(jù)量少的表盡量不要使用索引，由于數(shù)據(jù)較少，查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果

• (4)在條件表達(dá)式中經(jīng)常用到不同值較多的列上創(chuàng)建索引，在不同值很少的列上不要建立索引。比如性別字段只有“男”“女”倆個(gè)值，就無需建立索引。如果建立了索引不但不會(huì)提升效率，反而嚴(yán)重減低數(shù)據(jù)的更新速度

• (5)在頻繁進(jìn)行排序或者分組的列上建立索引，如果排序的列有多個(gè)，可以在這些列上建立聯(lián)合索引。

2、講講索引的分類？你知道哪些？

從物理存儲(chǔ)角度:

聚簇索引和非聚簇索引

從數(shù)據(jù)結(jié)構(gòu)角度:

B+樹索引、hash索引、FULLTEXT索引、R-Tree索引

從邏輯角度:

• 主鍵索引：主鍵索引是一種特殊的唯一索引，不允許有空值

• 普通索引或者單列索引

• 多列索引（復(fù)合索引）：復(fù)合索引指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會(huì)被使用。使用復(fù)合索引時(shí)遵循最左前綴集合

• 唯一索引或者非唯一索引

• 空間索引：空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。

3、如何避免回表查詢?什么是索引覆蓋?

這個(gè)問題，如果要看詳細(xì)版，請(qǐng)參閱文章《Innodb中索引的原理》

這里簡單說一下。

當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要回表讀取行了。一個(gè)索引包含了（或覆蓋了）滿足查詢結(jié)果的數(shù)據(jù)就叫做索引覆蓋。

例如此時(shí)有一張表table1,有一個(gè)聯(lián)合索引(a,b)

執(zhí)行如下SQL

select a,b from table1

在索引上就能找到結(jié)果，就不用回表去查詢！

而你執(zhí)行的是

c列在索引上不存在，就需要回表查詢。

需要說明的是覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引不存儲(chǔ)索引列的值，所以mysql只能用B+ tree索引做覆蓋索引。

4、現(xiàn)在我有一個(gè)列，里頭的數(shù)據(jù)都是唯一的，需要建一個(gè)索引，選唯一索引還是普通索引？

答唯一索引！

首先，在孤盡出的《阿里巴巴JAVA開發(fā)規(guī)范》中有這么一段話

【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段，即使是多個(gè)字段的組合，也必須建成唯一索引

說明：不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。

那好，下一問出現(xiàn)了！

為什么唯一索引的插入速度比不上普通索引？為什么唯一索引的查找速度比普通索引快？

這個(gè)問題就要從Insert Buffer開始講起了，在進(jìn)行非聚簇索引的插入時(shí)，先判斷插入的索引頁是否在內(nèi)存中。如果在，則直接插入；如果不在，則先放入Insert Buffer 中，然后再以一定頻率和情況進(jìn)行Insert Buffer和原數(shù)據(jù)頁合并(merge)操作。

這么做的優(yōu)點(diǎn):能將多個(gè)插入合并到一個(gè)操作中，就大大提高了非聚簇索引的插入性能。

InnoDB 從 1.0.x 版本開始引入了 Change Buffer，可以算是對(duì) Insert Buffer 的升級(jí)。從這個(gè)版本開始，InnoDB 存儲(chǔ)引擎可以對(duì) insert、delete、update 都進(jìn)行緩存。

唯一速度的插入比普通索引慢的原因就是:

• 唯一索引無法利用Change Buffer

• 普通索引可以利用Change Buffer

于是乎下一問又來了！

為什么唯一索引的更新不使用 Change Buffer？

因?yàn)槲ㄒ凰饕秊榱吮ＷC唯一性，需要將數(shù)據(jù)頁加載進(jìn)內(nèi)存才能判斷是否違反唯一性約束。但是，既然數(shù)據(jù)頁都加載到內(nèi)存了，還不如直接更新內(nèi)存中的數(shù)據(jù)頁，沒有必要再使用Change Buffer。

最后回答一下，唯一索引的搜索速度比普通索引快的原因就是:

• 普通索引在找到滿足條件的第一條記錄后，還需要判斷下一條記錄，直到第一個(gè)不滿足條件的記錄出現(xiàn)。

• 唯一索引在找到滿足條件的第一條記錄后，直接返回，不用判斷下一條記錄了。

5、mysql索引是什么結(jié)構(gòu)的？用紅黑樹可以么?

這個(gè)妥妥答最常見的B+ Tree。

AVL樹和紅黑樹基本都是存儲(chǔ)在內(nèi)存中才會(huì)使用的數(shù)據(jù)結(jié)構(gòu)。在大規(guī)模數(shù)據(jù)數(shù)據(jù)存儲(chǔ)的時(shí)候，顯然不能將全部數(shù)據(jù)全部加載進(jìn)內(nèi)存，因此如果采用紅黑樹，就會(huì)造成頻繁IO，效率低下。

那為啥不用B Tree,而選擇B+ tree呢？

這就需要貼一下經(jīng)典的兩張圖。B tree是長下面這樣的

注意一下B tree的兩個(gè)明顯特點(diǎn)

• 樹內(nèi)存儲(chǔ)數(shù)據(jù)

• 葉子節(jié)點(diǎn)上無鏈表

而B+ tree長下面這樣的

注意一下B+ tree的兩個(gè)明顯特點(diǎn)

• 數(shù)據(jù)只出現(xiàn)在葉子節(jié)點(diǎn)

• 所有葉子節(jié)點(diǎn)增加了一個(gè)鏈指針

接下來就可以開始編了～～比如數(shù)據(jù)庫索引采用B+ tree的主要原因是B Tree在提高了磁盤IO性能的同時(shí)并沒有解決元素遍歷的效率低下的問題。正是為了解決這個(gè)問題，B+ tree應(yīng)運(yùn)而生。B+ tree只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的，如果使用B Tree，則需要做局部的中序遍歷，可能要跨層訪問，效率太慢。

提示，我下一問就是:

你剛才說了這么多B tree不行，那你知道為啥Mongodb用B Tree當(dāng)索引，而不用B+ Tree么？

(從關(guān)系數(shù)據(jù)庫和非關(guān)系數(shù)據(jù)庫的區(qū)別角度去答，不拓展了！仔細(xì)想想，在Mongodb里表示二者的關(guān)系，你會(huì)怎么處理!)

6、mysql某表建了多個(gè)單索引，查詢多個(gè)條件時(shí)如何走索引的？

其實(shí)，我看到這題的時(shí)候，內(nèi)心一抖。這題讓后端開發(fā)來答，真的很拼功底！

這里希望大家先看看我的另一篇文章《我是一條DQL》。此題在考優(yōu)化器的知識(shí)！此題是在考察優(yōu)化器如何抉擇索引的！優(yōu)化器會(huì)評(píng)估出走哪個(gè)索引最優(yōu)，然后執(zhí)行。

Mysql在優(yōu)化器中有一個(gè)優(yōu)化器稱為Range 優(yōu)化器，負(fù)責(zé)進(jìn)行范圍查詢的優(yōu)化！

那么該優(yōu)化器計(jì)算執(zhí)行成本有兩種方式index dive與index statistics。

它們是MySQL優(yōu)化器對(duì)開銷代價(jià)的估算方法，前者統(tǒng)計(jì)速度慢但是能得到精準(zhǔn)的值，后者統(tǒng)計(jì)速度快但是數(shù)據(jù)未必精準(zhǔn)。

坦白說寫到這里，我內(nèi)心痛哭流涕，要把index dive和index statistics寫明白，真不是一件容易的事，這里只能稍微扯扯。

對(duì)于index dive:

計(jì)算成本的方式為

COST = CPU COST + IO COST

其中CPU COST指的是處理返回記錄所花的開銷。而IO COST指的是讀取頁面的開銷。

mysql會(huì)對(duì)每種索引的執(zhí)行情況，進(jìn)行上述成本計(jì)算，最后以成本小的方式進(jìn)行執(zhí)行。

但是呢，在某些情況下mysql執(zhí)行index dive的成本太大。因此優(yōu)化器會(huì)選擇以index statistics方式進(jìn)行估算成本。

具體如下：

此時(shí)出來的結(jié)果中，有一列名為Cardinality，該值表示索引列中不重復(fù)值的個(gè)數(shù)。

簡單來說就是，索引列的唯一值的個(gè)數(shù)，如果是復(fù)合索引就是唯一組合的個(gè)數(shù)。

這個(gè)數(shù)值將會(huì)作為mysql優(yōu)化器對(duì)語句執(zhí)行計(jì)劃進(jìn)行判定時(shí)依據(jù)。如果唯一性太小，那么優(yōu)化器會(huì)認(rèn)為，這個(gè)索引對(duì)語句沒有太大幫助，而不使用索引。

Cardinality值越大，就意味著，使用索引能排除越多的數(shù)據(jù)，執(zhí)行也更為高效。