上一篇 <<<MySQL性能優(yōu)化之常用SQL語句優(yōu)化
下一篇 >>>MySQL性能優(yōu)化之分頁查詢優(yōu)化

索引失效場景或使用注意事項

a、索引無法存儲null值焙贷，所以建議都給默認值
b、如果條件中有or羹令，即使使用了索引條件也不起作用，所以盡量少用or
如果想使用or温算，又讓索引生效襟锐，只能將or的每個列上加上索引
c蒲列、對于多列索引，不是使用其中的第一部分争拐，則不會使用索引腋粥。
d、like查詢以%開頭(like '%XX'或者like '%XX%')
e陆错、如果列類型是字符串灯抛，那一定要在條件中將數(shù)據(jù)使用引號引用起來,否則不使用索引
f、如果mysql估計使用全表掃描要比使用索引快,則不使用索引
g音瓷、索引的字段類型與傳入?yún)?shù)不匹配对嚼，則索引失效

索引優(yōu)化樣例

1.使用索引時，關(guān)聯(lián)表的條件字段中绳慎，字段長度和編碼必須一致

a.fk_user_id = b.user_id 纵竖，fk_user_id 的編碼是utf8 而 user_id 的編碼方式是utf8mb4的漠烧，所以導(dǎo)致索引失效

2.盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少select *語句

 SELECT name,age FROM employees WHERE name= 'jarye' AND age = 23 AND position ='ceo';----直接走索引
 SELECT * FROM employees WHERE name= 'jarye' AND age = 23 AND position ='ceo';-----索引走了還會走二次查詢

3.列類型是字符串靡砌，則必須使用''引號已脓，否則不使用索引

SELECT * FROM employees WHERE name = 123;--索引會失效
SELECT * FROM employees WHERE name = '123';--索引生效

4.判斷是否為空用is null，使用=null則不啟用索引

select id from t where num is null;
注意： NULL 與任何值的直接比較都為 NULL通殃。
1 ） NULL<>NULL 的返回結(jié)果是 NULL 度液，而不是 false 。
2 ） NULL=NULL 的返回結(jié)果是 NULL 画舌，而不是 true 堕担。
3 ） NULL<>1 的返回結(jié)果是 NULL ，而不是 true 曲聂。

5.應(yīng)盡量避免在 where 子句中對”="左邊進行函數(shù)霹购、算數(shù)運算或表達式運算，這將導(dǎo)致引擎放棄使用索引而進行全表掃描朋腋。

select id from t where substring(name,1,3) = ’abc’ -–name 以 abc 開頭的 id 
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ –生成的 id
應(yīng)改為:
select id from t where name like ‘a(chǎn)bc%’ 
select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’ 

6.在where子句中盡量避免使用!=或<>操作符齐疙，引擎會放棄使用索引而進行全表掃描。

 SELECT * FROM employees WHERE name != 'jarye'

7.若中間索引列用到了范圍（>旭咽、<贞奋、like等），則后面的所以全失效

 SELECT * FROM employees WHERE name= 'jarye' AND age = 22 AND position ='ceo';----索引生效
 SELECT * FROM employees WHERE name= 'jarye' AND age > 22 AND position ='ceo';----索引position無效

8.like前面有%會失效穷绵，如果字段長的話忆矛，可以考慮使用全文檢索

Select * from dw_user where username like ‘%123%’——索引失效 
Select * from dw_user where username like ‘%123’——索引失效 
Select * from dw_user where username like ‘123%'——索引有效 

9.在 where 子句中使用 or 來連接條件，必須全部索引存在才有效

如果一個字段有索引请垛，一個字段沒有索引，將導(dǎo)致引擎放棄使用索引而進行全表掃描洽议，如： 
select id from t where num=10 or Name = ‘a(chǎn)dmin’
可以這樣查詢：
select id from t where num = 10 
union all 
select id from t where Name = ‘a(chǎn)dmin’ 

10.有 order by 的場景宗收，請注意利用索引的有序性【阿里巴巴JAVA開發(fā)手冊】，參考order by中的單路和雙路排序算法原理

order by 最后的字段是組合索引的一部分亚兄，并且放在索引組合順序的最后混稽，避免出現(xiàn) file _ sort 的情況，影響查詢性能审胚。 
正例： where a =?  and b =?  order by c; 索引： a _ b _ c
反例：索引中有范圍查找匈勋，那么索引有序性無法利用，如： WHERE a >10  ORDER BY b; 索引a _ b 無法排序膳叨。

11.聯(lián)合索引洽洁，第一條件必須使用，且盡可能按索引順序執(zhí)行

alter table dept add index my_ind (dname,loc);
select * from dept where dname=‘a(chǎn)aa’ and loc=‘a(chǎn)aa’———二個條件都使用了菲嘴，索引生效 
select * from dept where dname=‘a(chǎn)aa’——第一條件查詢饿自，使用索引 
select * from dept where loc=‘a(chǎn)aa’——沒有第一條件汰翠，不使用索引
建組合索引的時候，區(qū)分度最高的在最左邊昭雌。
正例：如果 where a =?  and b =? 复唤， a 列的幾乎接近于唯一值，那么只需要單建 idx _ a 索引即可烛卧。
說明：存在非等號和等號混合判斷條件時佛纫，在建索引時，請把等號條件的列前置总放。如： where a >? and b =? 那么即使 a 的區(qū)分度更高呈宇，也必須把 b 放在索引的最前列。

聯(lián)合索引為什么需要遵循左前綴原則间聊？
因為索引底層采用B+樹葉子節(jié)點順序排列攒盈，必須通過左前綴索引才能定位到具體的節(jié)點范圍。

12. 在where子句中使用參數(shù)哎榴，也會導(dǎo)致全表掃描,可以使用強制索引

因為SQL只有在運行時才會解析局部變量型豁，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇尚蝌。然而迎变，如果在編譯時建立訪問計劃，變量的值還是未知的飘言，因而無法作為索引選擇的輸入項衣形。如下面語句將進行全表掃描：
select id from t where num = @num
可以改為強制索引
select id from t with(index(索引名)) where num = @num

推薦閱讀：
<<<MySQL執(zhí)行計劃示例解讀
<<<MySQL性能優(yōu)化之慢查詢定位
<<<MySQL性能優(yōu)化之表設(shè)計優(yōu)化
<<<MySQL性能優(yōu)化之常用SQL語句優(yōu)化
<<<MySQL性能優(yōu)化之分頁查詢優(yōu)化
<<<MySQL性能優(yōu)化之關(guān)聯(lián)查詢優(yōu)化
<<<MySQL性能優(yōu)化之in、exists優(yōu)化
<<<order by中的單路和雙路排序算法原理
<<<MySQL如何性能優(yōu)化面試題完美解答