典型案例分析

小猿正在開(kāi)發(fā)一個(gè)網(wǎng)上商城系統(tǒng),現(xiàn)在有一個(gè)需求需要查詢(xún)訂單以及關(guān)聯(lián)的商品信息,因此小猿立刻寫(xiě)出了如下語(yǔ)句：

語(yǔ)句看著十分簡(jiǎn)單,可執(zhí)行的時(shí)候卻讓小猿懵了,結(jié)果執(zhí)行了1000多秒還是沒(méi)有出結(jié)果,效率實(shí)在太低,為了找尋原因,小猿通過(guò)EXPLAIN來(lái)解析查詢(xún)過(guò)程：

先解釋上圖幾個(gè)關(guān)鍵字段的含義：

type：all 全表掃描

possible_key 可能用到的索引

key：真正用到的索引

rows：掃描的行數(shù)

從上圖可以看出,這條語(yǔ)句執(zhí)行采用的是全表掃描,沒(méi)有走任何索引因此效率十分低.

接下來(lái)小猿分析了下現(xiàn)有的建表語(yǔ)句：

發(fā)現(xiàn)相關(guān)字段都已經(jīng)加上了索引可是為何實(shí)際查詢(xún)并沒(méi)有找索引而是采用了全表掃描?

細(xì)心觀察會(huì)發(fā)現(xiàn)訂單表主鍵是 order_id + org_id ,訂單明細(xì)表則是 org_id+order_id ,兩邊建立的索引順序相反導(dǎo)致通過(guò)order_id來(lái)關(guān)聯(lián)是無(wú)法啟用索引進(jìn)行查詢(xún)的.

解決的方法有兩種：1、重建索引 2、為語(yǔ)句加上org_id的關(guān)聯(lián)條件,讓現(xiàn)有索引生效.

修改后的語(yǔ)句如下：

耗時(shí)只要2毫秒,使用索引后速度提升相當(dāng)驚人!

使用explain解析執(zhí)行語(yǔ)句可以發(fā)現(xiàn),已經(jīng)沒(méi)有之前的全表掃描了

其實(shí)上述的建表語(yǔ)句自己就存在著問(wèn)題,我們可以通過(guò)SHOW INDEX命令來(lái)查看Cardinality字段值的高低來(lái)判斷是否需要建立索引.

通過(guò)上圖的分析可以看出org_id的的區(qū)分度是非常低的,建立索引的意義并不大,在實(shí)際應(yīng)用中,Cardinality/n_row_in_table應(yīng)盡可能的接近1,如果非常小,那用戶需要考慮是否還有必要?jiǎng)?chuàng)建這個(gè)索引.

索引使用的注意事項(xiàng)：

1、創(chuàng)建多列索引,查詢(xún)條件沒(méi)有從索引的第一列開(kāi)始比對(duì)無(wú)法使用索引,也就是文章開(kāi)頭出現(xiàn)的問(wèn)題.

2、like查詢(xún)時(shí)把%寫(xiě)在前面,索引無(wú)法進(jìn)行比對(duì).好比：like ‘%xxx%’.

3、列類(lèi)型是字符串,WHERE條件中需要字符串用引號(hào)引起來(lái).

4、不能再列上面進(jìn)行計(jì)算.好比WHERE YEAR(dt) = 2017.

5、OR語(yǔ)句中的前后兩個(gè)條件中,都要滿足索引的使用情況語(yǔ)句才會(huì)用上索引.

6、否定的寫(xiě)法無(wú)法使用索引.如：<>、not in、not exists.

庫(kù)表設(shè)計(jì)原則：

1、采用InnoDB存儲(chǔ)引擎,禁用Memory引擎.

2、夠用原則,能用TINYINT就不用INT.

3、有小數(shù)點(diǎn)的價(jià)格用DECIMAL.

4、主鍵字段盡量用數(shù)值型.

5、分庫(kù)分表的垂直拆分與水平拆分.

索引設(shè)計(jì)原則：

1、選擇區(qū)分度高的字段上創(chuàng)建.

2、過(guò)濾的字段上創(chuàng)建.

3、連接的字段上創(chuàng)建.

4、主鍵盡量用數(shù)值型.

5、必定要設(shè)置主鍵.