MySQL進(jìn)階學(xué)習(xí)：詳解創(chuàng)建高效且合適索引的方法

本篇文章是mysql的進(jìn)階學(xué)習(xí)，帶大家詳細(xì)了解一下創(chuàng)建更合適索引的方法，希望對大家有所幫助！

不要當(dāng)庫里的數(shù)據(jù)較多的時(shí)候才能知道索引的重要性，更不要當(dāng)庫里的數(shù)據(jù)更多的時(shí)候才能知道合適的索引重要性。本文介紹下怎么創(chuàng)建高效且合適的索引。【相關(guān)推薦：mysql視頻教程】

1. 當(dāng)使用索引列進(jìn)行查詢的時(shí)候盡量不要使用表達(dá)式，把計(jì)算放到業(yè)務(wù)層而不是數(shù)據(jù)庫層

如下圖 兩個sql的結(jié)果是一樣的，但是兩個sql的執(zhí)行計(jì)劃是不一樣，在type中index的效率遠(yuǎn)不如const where條件中 actor_id+4 表達(dá)式影響了執(zhí)行計(jì)劃,對于type表示的含義請參考 mysql視頻教程

2. 盡量使用主鍵查詢，而不是其他索引，主鍵查詢不會出現(xiàn)回表查詢。

我們所有的表基本都會有主鍵的，所以平時(shí)開發(fā)中能用索引就用索引，能用主鍵索引就用主鍵索引。

3. 使用前綴索引

很多時(shí)候我們的索引其實(shí)都是字符串，不可避免會出現(xiàn)長字符串，就會導(dǎo)致索引占用過大，降低其效率。尤其是對于blob,text, varchar這樣的長列。這時(shí)候處理方式就是不使用字段的全值作為索引，而是只取其前半部分即可（選擇的這部分前綴索引的選擇性接近于整個列）。這樣可以大大減少索引空間，從而提高效率，壞處就是降低了索引的選擇性。

索引選擇性：不重復(fù)的索引值和數(shù)據(jù)表記錄總數(shù)的比值(#T),范圍從1/#T到1之間。索引的選擇性越高查詢效率也高，因?yàn)閿?shù)據(jù)的區(qū)分度很高，可以過濾掉更多的行。唯一性索引的選擇性是1，其性能也最好。

例如公司的員工表中郵箱字段，一個公司的郵箱后綴都是一樣的如xxxx@qq.com, 其實(shí)用郵箱作為索引有效的就xxxx部分，因?yàn)锧qq.com都是一樣的，對索引是無意義的，明顯只用xxxx作為索引，其選擇性和整個值的是一樣的，但是xxxx作為索引明顯就會減少索引空間。

下面我們已employee表為例子(表結(jié)構(gòu)和數(shù)據(jù)看文末)

我們以email字段建立索引為例：

這個數(shù)據(jù)的郵箱其實(shí)是手機(jī)號+@qq.com為例的，其實(shí)前11位后面都是相同的。我用下面的sql來看看這些數(shù)據(jù)的選擇性（分別取前10，11，12）位計(jì)算。

--?當(dāng)是11個前綴的時(shí)候選擇性是1，在增加字段長度，選擇性也不會變化 select?count(distinct?left(email,10))/count(*)?as?e10,?count(distinct?left(email,11))/count(*)?as?e11,??????count(distinctleft(email,12))/count(*)?as?e12?from?employee;

從上圖我們可以看出前10，前11，前12的選擇性分別是0.14，1.0，1.0 ,在第11位的時(shí)候索引選擇性是最高的1，就沒必要使用全部作為索引，增加了索引的空間。

--?創(chuàng)建前綴索引 alter?table?employee?add?key(email(11));

我們也可以使用count計(jì)算頻率來統(tǒng)計(jì)（出現(xiàn)的次數(shù)越少，說明重復(fù)率越低，選擇性越大）

--?查找前綴出現(xiàn)的頻率 select?count(*)?as?cnt,left(email,11)?as?pref?from?employee?group?by?pref?order?by?cnt?desc?limit?10;

4.使用索引掃描來排序

我們經(jīng)常會有排序的需求，使用order by 但是order by是比較影響性能的，它是通過把數(shù)據(jù)加載到內(nèi)存去排序的，如果數(shù)據(jù)量很大內(nèi)存放不下，只能分多次處理。但是索引本身就是有序的，直接通過索引完成排序更省事。

掃描索引本身是很快的，因?yàn)橹恍枰獜囊粭l索引記錄移動到緊接著的下一條記錄，但如果索引不能覆蓋查詢所需的所有列時(shí)，就不得不每掃描一條索引記錄就回表查詢一次對應(yīng)行，這基本都是隨機(jī)IO。因此按索引順序讀取數(shù)據(jù)的速度通常比順序的全表掃描慢。

mysql可以使用同一個索引即滿足排序，又用于查找行。如果可以的話請考慮建立這種索引。

只有當(dāng)索引列順序和order by子句的順序完全一致，并且所有列的排序方向(倒敘或者正序)都是一樣的，mysql才能使用索引對結(jié)果做排序。如果查詢需要關(guān)聯(lián)多張表，只有當(dāng)order by子句的字段全是第一張表時(shí)才能使用索引排序。order by 查詢同時(shí)也需要滿足組合索引的最左前綴，否則也不能使用索引排序。

其實(shí)在開發(fā)中主要注意兩點(diǎn)：

• where條件中的字段和order by中的字段能夠是組合索引而且滿足最左前綴。
• order by中的字段的順序需要一致，不能存在desc,又存在asc。

5. union all ,in,or都能夠使用索引，但是推薦使用in

如上union all 會有兩次執(zhí)行，而in 和or只有一次。同時(shí)看出or和in的執(zhí)行計(jì)劃是一樣的,

但是我們在看一下他們的執(zhí)行時(shí)間。如下圖使用set profiling=1可以看到詳細(xì)時(shí)間，使用show profiles 查看具體時(shí)間。下圖看出or的時(shí)間0.00612000，in的時(shí)間0.00022800，差距還是很大的(測試的表數(shù)據(jù)只有200行)

union all: 查詢分為了兩階段，其實(shí)還有一個union，在平時(shí)開發(fā)中必須使用到union的時(shí)候推薦使用union all，因?yàn)閡nion中多出了distinct去重的步驟。所以盡量用union all。

6. 范圍列可以用到索引

范圍的條件：>,>=,

范圍列可以用到索引，但是范圍列后面的列就無法用到索引了(索引最多用于一個范圍列)

比如一個組合索引age+name 如果查詢條件是where age>18 and name=”紀(jì)”后面的name是用不到的索引的。

曾經(jīng)面試被問到不等于是否能夠走某個索引，平時(shí)沒有注意過也沒有回答成功，這次親自做個實(shí)驗(yàn)，關(guān)于結(jié)論請看文末。

7. 強(qiáng)制類型轉(zhuǎn)換會全表掃描

我在employee表中定義了mobile字段是varchar類型且建立索引，我分別用數(shù)字和字符串查詢.

看看結(jié)果: 兩者type是不一樣的，而且只有字符串才用到索引。

如果條件的值的類型和表中定義的不一致，那么mysql會強(qiáng)制進(jìn)行類型轉(zhuǎn)換，但是結(jié)果是不會走索引，索引在開發(fā)中我們需要根據(jù)自己定義的類型輸入對應(yīng)的類型值。

8. 數(shù)據(jù)區(qū)分度不高，更新頻繁的字段不宜建立索引

• 索引列更新會變更B+樹的，頻繁更新的會大大降低數(shù)據(jù)庫性能。
• 類似于性別這類(只有男女，或者未知)，不能有效過濾數(shù)據(jù)。
• 一般區(qū)分度在80%以上就可以建立索引，區(qū)分度可以使用count(distinct(列名))/count(*)

9. 創(chuàng)建索引的列不允許為NULL，可能會得到不符合預(yù)期的結(jié)果

也就是建立索引的字段盡量不要為空，可能會有些意想不到的問題，但是實(shí)際工作中也不太可能不為空，所以根據(jù)實(shí)際業(yè)務(wù)來處理吧，盡量避免這種情況。

10. 當(dāng)需要進(jìn)行表連接的時(shí)候，最好不要超過三張表

表連接其實(shí)就是多張表循環(huán)嵌套匹配，是比較影響性能的， 而且需要join的字段數(shù)據(jù)類型必須一致，提高查詢效率。關(guān)于表連接原理后面專門寫一篇吧。

11. 能使用limit的時(shí)候盡量使用limit。

limit的作用不是僅僅用了分頁，本質(zhì)作用是限制輸出。

limit其實(shí)是挨個遍歷查詢數(shù)據(jù)，如果只需要一條數(shù)據(jù)添加 limit 1的限制，那么索引指針找到符合條件的數(shù)據(jù)之后就停止了，不會繼續(xù)向下判斷了，直接返回。如果沒有l(wèi)imit，就會繼續(xù)判斷。

但是如果分頁取1萬條后的5條limit 10000,10005 就需要慎重了，他會遍歷1萬條之后取出5條，效率很低的。小技巧：如果主鍵是順序的，可以直接通過主鍵獲取分頁數(shù)據(jù)。

12. 單表索引盡量控制在5個內(nèi)

建立/維護(hù)索引也是需要代價(jià)的，也需要占用空間的。索引并不是越多越好，要合理使用索引。

13. 單個組合索引的字段個數(shù)不宜超過5個

字段越多，索引就會越大，占用的存儲空間就越多。

索引并不是越多越好，而且索引并不需要在開始建表的時(shí)候就全部設(shè)計(jì)出來，過早優(yōu)化反而不會是高效索引，需要在了解業(yè)務(wù)，根據(jù)相關(guān)業(yè)務(wù)sql做個統(tǒng)計(jì)權(quán)衡之后再去構(gòu)建相關(guān)索引，這樣考慮的更周全，建立的索引更有效和高效。