1.SQL語句中IN包含的值不應過多:
例如：select id from t where num in(1,2,3) 對于連續(xù)的數(shù)值，能用between就不要用in了； 實測速度差距不是很大.

image.png

2.SELECT語句務必指明字段名稱:
禁止用 * 來查詢 ,禁止用 * 來查詢 ,禁止用 * 來查詢 , 查找哪個字段,就寫具體的字段.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

image.png

3.只查詢一條數(shù)據(jù)的時候，使用limit 1
【這個很有用】

4.避免在where子句中對字段進行null值判斷:
【實測：null值的判斷依然走了索引】
explain select uid from user_test WHERE phone is null;

image.png

5.避免在where子句中對字段進行表達式操作:

select  uid from user_test  WHERE uid*10=40;
上面的sql對字段就行了算術運算，這會造成引擎放棄使用索引，建議改成：
select  uid from user_test  WHERE uid=40/10;
 

image.png

image.png

6.對于聯(lián)合索引來說，要遵守最左前綴法則:
例如組合索引(id,name,sex) 使用的時候,可以id 或者id,name . 禁止直接name,或者sex.會導致聯(lián)合索引失敗

注意: id, name,sex 這三個字段填寫順序不會有影響, mysql會自動優(yōu)化成最左匹配的順序.

前三條sql都能命中索引,中間兩條由于不符合最左匹配原則,索引失效.

最后一條sql 由于有最左索引id 所以索引部分成功,部分失效. id字段索引使用成功.

explain select * from  `user_test` where uid=10  ; 
explain select * from  `user_test` where uid=10 and name='張三'; 
explain select * from  `user_test` where uid=10 and name='張三' and phone='13527748096'; 
 
explain select * from  `user_test` where name='張三' and phone='13527748096'; 
explain select * from  `user_test` where name='張三'; 
 
explain select * from  `user_test` where uid=10 and phone='13527748096'; 

7.盡量使用inner join，避免left join：
如果連接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。
【實測：不是很準確，具體用explain測試】

8.注意范圍查詢語句:
對于聯(lián)合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成后面的索引字段失效。

解決辦法: 業(yè)務允許的情況下,使用 >= 或者<= 這樣不影響索引的使用.

explain select * from user_test where uid=10 and name='張三' and phone='13527748096';
explain select * from user_test where uid between( 1 and 10) and name ='張三' and phone='13527748096';

image.png

image.png

9.不建議使用%前綴模糊查詢:
例如 : LIKE“%name”或者LIKE“%name%”，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE “name%”。

explain select * from user_test where uid=10 and uid like "%1" ;
explain select * from user_test where uid=10 and uid like "1%" ;

image.png

image.png

10.在 where 子句中使用 or 來連接條件，如果or連接的條件有一方沒有索引,將導致引擎放棄使用索引而進行全表掃描
解決辦法: 將or連接的雙方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='張三';

image.png

11.應盡量避免在where子句中對字段進行函數(shù)操作，這將導致引擎放棄使用索引而進行全表掃描。(此處存在疑點,我本人測試的時候,發(fā)現(xiàn)索引還是能使用到)

explain select uid from  `user_test` where uid=1 and  substring(phone,1,3)='135'
 
explain select uid from  `user_test` where uid=1 and   abs(uid)=1;

image.png

12.字符串類型的字段 查詢的時候如果不加引號'' ,會導致自動進行隱式轉換,然后索引失效