典型案例分析

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

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

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

type：all 全表掃描

possible_key 可能用到的索引

key：真正用到的索引

rows：掃描的行數(shù)

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

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

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

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

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

修改后的語句如下：

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

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

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

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

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

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

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

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

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

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

6、否定的寫法無法使用索引.如：<>、not in、not exists.

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

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

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

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

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

5、分庫分表的垂直拆分與水平拆分.

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

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

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

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

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

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