本文源自 極客網(wǎng)站 的某知名大佬講mysql，整理一下心得和重點(diǎn)。
第10節(jié) mysql為什么會(huì)選錯(cuò)索引
mysql索引選擇策略
首先，我們必須了解到，mysql在選擇索引時(shí)，優(yōu)化器會(huì)從以下角度來(lái)考慮是否選擇索引：
- 使用force index(a-name)會(huì)強(qiáng)制使用a-name的索引（強(qiáng)制）；
- 根據(jù)統(tǒng)計(jì)條件對(duì)應(yīng)的行數(shù)（行數(shù)越少越好，占用的CPU資源就少）；
- 根據(jù)是否排序，及其排序要花費(fèi)的時(shí)間；
- 回表[0]的代價(jià)。
下面來(lái)逐條講解一下每條需要注意的情況：
除了第一條是強(qiáng)制執(zhí)行某索引，其他條都是綜合考慮，通過(guò)估值來(lái)確定是否選擇索引以及選擇哪個(gè)索引的問(wèn)題。
第二條，mysql在選擇行數(shù)時(shí)，會(huì)采樣統(tǒng)計(jì)[1]，根據(jù)統(tǒng)計(jì)信息估算在查詢(xún)條件的范圍內(nèi)大概有多少條記錄。而這個(gè)時(shí)候就需要依靠于索引的區(qū)分度，一個(gè)索引上不同的值越多，其區(qū)分度就越好。
所以在建立索引（或者前綴索引）時(shí)，既要考慮索引的占用頁(yè)數(shù)大小，也要考慮索引的使用效能，即區(qū)分度。
第三條，因?yàn)樗饕际桥藕眯虻?，所以有索引的可以直接忽略掉這部分花費(fèi)的時(shí)間的考慮。如果一個(gè)查詢(xún)語(yǔ)句中，order by和where中都有的字段，會(huì)更偏向于該字段的索引，盡管此時(shí)where中有別的索引有更優(yōu)的效果。
ps：
[0] 回表：從二級(jí)索引上取到值，到主索引查出整行數(shù)據(jù)，來(lái)看它是否滿(mǎn)足條件。而前綴索引的回表率是非常恐怖的，所以這個(gè)索引需要考慮諸多因素。一般使用distinct某字段的前幾個(gè)字符，最后算一下統(tǒng)計(jì)幾個(gè)字符可以達(dá)到要求（一般覆蓋率為95%就滿(mǎn)足大多數(shù)要求）。
[1] 采樣統(tǒng)計(jì)：因?yàn)檎斫y(tǒng)計(jì)代價(jià)太大，所以選擇采樣統(tǒng)計(jì)。而采樣統(tǒng)計(jì)會(huì)默認(rèn)選擇N個(gè)數(shù)據(jù)頁(yè)，統(tǒng)計(jì)這些頁(yè)上不同值，得到一個(gè)均值，再乘以這個(gè)索引的頁(yè)數(shù)，就得到了這個(gè)索引的基數(shù)。而當(dāng)數(shù)據(jù)庫(kù)變更數(shù)據(jù)超過(guò)1/M時(shí)，會(huì)自動(dòng)重新做一次索引統(tǒng)計(jì)。參數(shù)為innodb_stats_persistent，為on表示統(tǒng)計(jì)信息會(huì)持久化，此時(shí)默認(rèn)N=20，M=10；為off時(shí)表示統(tǒng)計(jì)信息只存儲(chǔ)于內(nèi)存中，此時(shí)默認(rèn)N=8，M=16。
[2] 慢查詢(xún)：指查詢(xún)超過(guò)指定參數(shù)long_query_time對(duì)應(yīng)的時(shí)間的查詢(xún)。該值在my.cnf中有，也可以手動(dòng)設(shè)值，set long_query_time=0，表示超過(guò)0秒的查詢(xún)記為慢查詢(xún)。
