面試又給我問(wèn)到MySQL索引【索引的使用策略及優(yōu)化】
精選文章
2020最新版MySQL數(shù)據(jù)庫(kù)面試題（一）
2020最新版MySQL數(shù)據(jù)庫(kù)面試題（二）
MySQL的查詢需要遍歷幾次B+樹(shù)，理論上需要幾次磁盤(pán)I/O？
五、索引的使用策略
什么時(shí)候要使用索引？
主鍵自動(dòng)建立唯一索引；
經(jīng)常作為查詢條件在WHERE或者ORDER BY 語(yǔ)句中出現(xiàn)的列要建立索引；
作為排序的列要建立索引；
查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
高并發(fā)條件下傾向組合索引；
用于聚合函數(shù)的列可以建立索引，例如使用了max(column_1)或者count(column_1)時(shí)的column_1就需要建立索引
什么時(shí)候不要使用索引？
經(jīng)常增刪改的列不要建立索引；
有大量重復(fù)的列不建立索引；
表記錄太少不要建立索引。只有當(dāng)數(shù)據(jù)庫(kù)里已經(jīng)有了足夠多的測(cè)試數(shù)據(jù)時(shí)，它的性能測(cè)試結(jié)果才有實(shí)際參考價(jià)值。如果在測(cè)試數(shù)據(jù)庫(kù)里只有幾百條數(shù)據(jù)記錄，它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內(nèi)存里，這將使后續(xù)的查詢命令都執(zhí)行得非常快--不管有沒(méi)有使用索引。只有當(dāng)數(shù)據(jù)庫(kù)里的記錄超過(guò)了1000條、數(shù)據(jù)總量也超過(guò)了MySQL服務(wù)器上的內(nèi)存總量時(shí)，數(shù)據(jù)庫(kù)的性能測(cè)試結(jié)果才有意義。
索引失效的情況：
在組合索引中不能有列的值為NULL，如果有，那么這一列對(duì)組合索引就是無(wú)效的。
在一個(gè)SELECT語(yǔ)句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
LIKE操作中，'%aaa%'不會(huì)使用索引，也就是索引會(huì)失效，但是‘a(chǎn)aa%’可以使用索引。
在索引的列上使用表達(dá)式或者函數(shù)會(huì)使索引失效，例如：select * from users where YEAR(adddate)<2007，將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′。其它通配符同樣，也就是說(shuō)，在查詢條件中使用正則表達(dá)式時(shí)，只有在搜索模板的第一個(gè)字符不是通配符的情況下才能使用索引。
在查詢條件中使用不等于，包括<符號(hào)、>符號(hào)和！=會(huì)導(dǎo)致索引失效。特別的是如果對(duì)主鍵索引使用！=則不會(huì)使索引失效，如果對(duì)主鍵索引或者整數(shù)類(lèi)型的索引使用<符號(hào)或者>符號(hào)不會(huì)使索引失效。（經(jīng)erwkjrfhjwkdb同學(xué)提醒，不等于，包括<符號(hào)、>符號(hào)和！，如果占總記錄的比例很小的話，也不會(huì)失效）
在查詢條件中使用IS NULL或者IS NOT NULL會(huì)導(dǎo)致索引失效。
字符串不加單引號(hào)會(huì)導(dǎo)致索引失效。更準(zhǔn)確的說(shuō)是類(lèi)型不一致會(huì)導(dǎo)致失效，比如字段email是字符串類(lèi)型的，使用WHERE email=99999 則會(huì)導(dǎo)致失敗，應(yīng)該改為WHERE email='99999'。
在查詢條件中使用OR連接多個(gè)條件會(huì)導(dǎo)致索引失效，除非OR鏈接的每個(gè)條件都加上索引，這時(shí)應(yīng)該改為兩次查詢，然后用UNION ALL連接起來(lái)。
如果排序的字段使用了索引，那么select的字段也要是索引字段，否則索引失效。特別的是如果排序的是主鍵索引則select * 也不會(huì)導(dǎo)致索引失效。
盡量不要包括多列排序，如果一定要，最好為這隊(duì)列構(gòu)建組合索引；
六、索引的優(yōu)化
1、最左前綴
索引的最左前綴和和B+Tree中的“最左前綴原理”有關(guān)，舉例來(lái)說(shuō)就是如果設(shè)置了組合索引
根據(jù)最左前綴原則，我們一般把排序分組頻率最高的列放在最左邊，以此類(lèi)推。
2、帶索引的模糊查詢優(yōu)化
在上面已經(jīng)提到，使用LIKE進(jìn)行模糊查詢的時(shí)候，'%aaa%'不會(huì)使用索引，也就是索引會(huì)失效。如果是這種情況，只能使用全文索引來(lái)進(jìn)行優(yōu)化（上文有講到）。
3、為檢索的條件構(gòu)建全文索引，然后使用
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
4、使用短索引
對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如，如果有一個(gè)CHAR(255)的 列，如果在前10 個(gè)或20 個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤(pán)空間和I/O操作。
（完結(jié)）