一葉知秋丿
程序員應該都知道,為了提高數據庫的查詢速度,我們可以對錶上的一個字段或者多個字段建立索引,但是有些 SQL 錯誤的寫法,可能會導致索引失效。
01. 查看執行計劃
如何判斷 SQL 的執行是做了全表掃描還是走了索引,不是憑感覺判斷 SQL 執行的快慢,而是要看 SQL 的執行計劃;很多工具都提供了查看執行計劃的功能,不過最原始的方法,還是通過 explain 進行查看;下面的 SQL,是否使用的索引,一目瞭然。
1. 沒有索引
explain select * from user where gender = 'M';
2. 有索引
explain select * from user where name = 'Tom';
02. 索引失效
1. 使用 like 時,% 在前面不走索引(在後面可以走索引);
explain select * from user where name like '%om';2. 數據類型出現隱式轉化,比如我們這裡手機號 mobile 字段設置的是 varchar 類型,但是查詢的時候用的是數字,那麼就【可能】不走索引。
explain select * from user where mobile = 13800000000;3. 在索引字段上使用 not,<>,!= ;
explain select * from user where mobile <> '13800000000';explain select * from user where mobile != '13800000000';
4. 對索引字段上使用函數;
explain select * from user where length(mobile) < 105. 聯合索引,如果查詢條件不滿足最左匹配原則,則不會走索引;
6. or 會使索引失效,儘管 or 左右的條件都有索引;
explain select * from user where name = 'Tom' or mobile = '13800000000';
總之,MySQL 的索引優化和索引失效還是挺複雜的,主要體現在 MySQL 隨著版本升級,有一些我們熟知的技巧可能會不再正確,我們現在認為一定會索引失效的 SQL 寫法,可能會變成走索引,所以這也是為什麼我在上文中,多次用到【可能】會造成索引失效的原因。
我將持續分享Java開發、架構設計、程序員職業發展等方面的見解,希望能得到你的關注。
會點代碼的大叔
以 Mysql 為例,其中索引 BTree 類型 。以下幾種SQL設計會導致雖然使用了索引,但是索引不會生效,即引擎放棄使用索引而進行全表掃描:
- WHERE 子句中使用 != 或 <> 操作符。
- WHERE 子句中對索引列使用 %前綴模糊查詢。
- WHERE 子句中對索引列使用 OR 來連接條件。
- WHERE 子句中對索引列使用 NOT IN。
- WHERE 子句中對索引列使用計算、函數、類型轉換等操作。
- WHERE 子句中對索引列使用參數。
軟件測試開發技術棧
以下情況,MySQL的索引"失效"不可用
1、通過索引掃描的記錄超過20%~30%,可能會變成全表掃描
2、聯合索引中,查詢條件不符合左側前導要求
3、查詢條件列最左以通配符%開始
4、查詢條件發生數據類型隱式轉換,或者字符集不匹配
5、HEAP表使用HASH索引時,使用範圍檢索或者ORDER BY
6、多表關聯時,排序字段不屬於驅動表,無法利用索引完成排序
7、JOIN查詢時,關聯列數據類型(字符集)不一致也會導致索引不可用
8、不可見索引,即便force index也不可用9、違反索引排序規則