一、索引創建的原則

1、針對數據量較大，且查詢比較頻繁的表建立索引。

單表超過10萬數據，即可增加索引

2、使用經常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引

3、盡量選擇區分度高的字段作為索引，盡量建立唯一索引，區分度越高，使用的索引越高效。

例如下圖中的address，區分度太小就不適合作為索引

4、盡量使用聯合索引（多個字段），減少單列索引，查詢的時候，聯合索引很多時候可以用到“覆蓋索引”，節省存儲空間，避免“回表查詢”，提高查詢效率

5、索引也不是越多越好，盡量控制索引的數量

索引越多，維護索引結構的代價越大，在增刪改的時候效率也越低

6、如果要建立索引的列不能存儲null值，請在創建表時，使用not null約束，可以增加查詢效率

二、什么情況下索引會失效

首先講一個我碰到的真實案例：

我們公司系統有一張大表，有80多個字段（按照規范其實不應該有這么多字段，歷史遺留問題，后面在數據庫國產化的時候，準備優化），每天500萬-2000萬之間的訂單數據；原來是使用日期作為分區鍵，再走主鍵索引查詢的；

結果有一次代碼改動，這張表又加了幾個字段，而且在其中一條SQL的select 后加上了幾個字段，oracle的索引就既不走分區鍵，也不走主鍵索引了，通過查看執行計劃發現，是執行到了另外幾個字段的普通聯合唯一索引上，而我們除了select后加了字段以外，where條件沒有改動。

出現的現象：應用更新上去后，立馬開始告警，數據庫連接超時，告警數據庫連接池滿。新進來的請求無法下單，大面積異常。決定立馬執行回退操作；

回退完成（業務正常）后，開始分析原因：除了加了幾個字段以外，select 后加了幾個字段要查詢出來以外，也沒有增加改動索引，也沒有where條件的任何改動；后面分析執行計劃發現索引走偏了，走到另一個普通索引上了

原因分析為：

Oracle優化器會根據查詢的代價（Cost）來選擇執行計劃。當查詢中涉及的字段或數據量發生變化時，優化器可能會重新評估使用不同索引的代價。例如：

• 新添加的字段可能使得查詢結果集的大小或數據分布發生了變化，導致優化器認為使用其他索引更高效。

• 如果查詢涉及的字段在新索引中能夠覆蓋查詢所需的所有列（即覆蓋索引），優化器可能會優先選擇該索引

當然，在我碰到的這次案例中，oracle優化器，顯然是幫了倒忙，選擇了錯誤的索引，導致查詢時間超長，占用連接池

解決方案：聯系DBA人員，強行綁定執行的索引，使用分區鍵+主鍵，問題解決

后續又碰到一次類似的問題，但是我們預料到可能出現這樣的情況，提前聯系了DBA人員支撐，一出現這個問題，就立馬綁定索引；

索引失效的條件：

1、違反最左前綴原則

即：當使用一個聯合索引（索引了多列）時，查詢條件中，，要從索引的最左前列開始，并且不跳過索引中的列，匹配最左前綴法則，走索引

例如：tb_seller表有一個聯合索引，是name、status、address三個字段的聯合索引；

上述兩條SQL執行，沒有帶上name字段，所以我們可以看到執行計劃中，key和key_len都沒有表現出這條SQL走了索引，索引失效

2、范圍查詢右邊的列，不能使用索引

即，如果一個聯合索引，某個字段使用了范圍查詢，那么這個字段后面的索引都不能生效

例如：還是上面這個表，第一條SQL使用了正確的聯合索引，執行計劃的key顯示使用了索引tb_seller_index，索引長度key_len顯示612，表示用到了name、status、address三個字段的索引，這三個字段的長度，加起來一共是612

而第二條SQL的條件中，status約束了查詢范圍“>1”，導致address這個字段的索引沒有走到，key_len的長度就只有309了，只使用到了name和status

3、不要在索引列上進行運算操作，索引會失效

即：如果對有索引的列，使用運算操作，那么索引可能會失效

如圖，還是這張表，組合索引是（name、status、address）

圖中的SQL的執行計劃中，key為null，說明沒有走到索引，導致索引失效，這樣查詢會很慢

4、字符串不加單引號，造成索引失效

在查詢時，如果沒有對字符串加單引號，MySQL的查詢優化器，會自動的進行類型轉換，造成索引失效

上圖中的SQL，第一條正確的SQL的執行計劃，能夠看到key_len是309，是使用了name、status兩個字段的索引；

而第二條SQL的執行計劃中，key_len是303，僅僅使用了name這個字段上的索引，造成了status上的索引失效；

原因是：第二條SQL條件中字符串類型的字段status后面的=跟的是0，沒有帶字符串，MySQL就認為是數字類型，而要與status相匹配，則要轉換成字符串類型 ；而進行的類型轉換，導致了索引失效

5、以%開頭的like模糊查詢，索引失效。

但是如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

如圖中，第一條，第二條SQL中，組合索引統統失效，第三條中，%在末尾，并沒有導致索引失效