典型案例分析

小猿正在開發一個網上商城系統,現在有一個需求需要查詢訂單以及關聯的商品信息,因此小猿立刻寫出了如下語句：

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

先解釋上圖幾個關鍵字段的含義：

type：all 全表掃描

possible_key 可能用到的索引

key：真正用到的索引

rows：掃描的行數

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

接下來小猿分析了下現有的建表語句：

發現相關字段都已經加上了索引可是為何實際查詢并沒有找索引而是采用了全表掃描?

細心觀察會發現訂單表主鍵是 order_id + org_id ,訂單明細表則是 org_id+order_id ,兩邊建立的索引順序相反導致通過order_id來關聯是無法啟用索引進行查詢的.

解決的方法有兩種：1、重建索引 2、為語句加上org_id的關聯條件,讓現有索引生效.

修改后的語句如下：

耗時只要2毫秒,使用索引后速度提升相當驚人!

使用explain解析執行語句可以發現,已經沒有之前的全表掃描了

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

通過上圖的分析可以看出org_id的的區分度是非常低的,建立索引的意義并不大,在實際應用中,Cardinality/n_row_in_table應盡可能的接近1,如果非常小,那用戶需要考慮是否還有必要創建這個索引.

索引使用的注意事項：

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

2、like查詢時把%寫在前面,索引無法進行比對.好比：like ‘%xxx%’.

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

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

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

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

庫表設計原則：

1、采用InnoDB存儲引擎,禁用Memory引擎.

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

3、有小數點的價格用DECIMAL.

4、主鍵字段盡量用數值型.

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

索引設計原則：

1、選擇區分度高的字段上創建.

2、過濾的字段上創建.

3、連接的字段上創建.

4、主鍵盡量用數值型.

5、必定要設置主鍵.