文章目錄

• 1. MySQL索引概述
• • 1.1 索引的概念
  • 1.2 索引的特點
  • 1.3 索引的分類
  • 1.4 索引的使用場景
• 2. 索引失效場景
• • 2.1 索引失效9種場景
  • 2.2 索引失效場景總結(jié)
• 3. 索引失效驗證
• • 3.1 全值匹配
  • 3.2 最佳左前綴
  • 3.3 索引計算
  • 3.4 索引范圍：索引列上不能有范圍查詢
  • 3.5 索引覆蓋：盡量使用覆蓋索引
  • 3.6 不等： 使用不等于（!= 或者 <>）的時候
  • 3.7 null：字段的is not null 與is null
  • 3.8 like：like的前后模糊匹配
  • 3.9 or：減少使用or

1. MySQL索引概述

1.1 索引的概念

什么是索引，索引就是排好序的快速查找數(shù)據(jù)結(jié)構(gòu)。

1.2 索引的特點

索引的優(yōu)點
1.提高數(shù)據(jù)檢索的效率， 降低數(shù)據(jù)庫的IO成本。

2.通過索引列對數(shù)據(jù)進行排序， 降低數(shù)據(jù)排序的成本， 降低了CPU的消耗。

索引的缺點
1.雖然索引大大提高了查詢速度， 同時卻會降低更新表的速度， 如對表進行INSERT、 UPDATE和DELETE。 因為更新表時， MySQL不僅要保存數(shù)據(jù)， 還要保存一下索引文件每次更新添加了索引列的字段， 都會調(diào)整因為更新所帶來的鍵值變化后的索引信息。

2.實際上索引也是一張表， 該表保存了主鍵與索引字段， 并指向?qū)嶓w表的記錄， 所以索引列也是要占用空間的。

1.3 索引的分類

MySQL 使用的是 Btree 索引。另外還有B+tree 索引，B-tree 索引，具體原理不在細說，原理詳情參考官網(wǎng)。
簡單說下以下幾個常用索引。

單值索引
概念：即一個索引只包含單個列， 一個表可以有多個單列索引

唯一索引
概念： 索引列的值必須唯一， 但允許有空值

主鍵索引
概念： 設(shè)定為主鍵后數(shù)據(jù)庫會自動建立索引， innodb為聚簇索引。

復(fù)合索引
概念： 即一個索引包含多個列

1.4 索引的使用場景

適合創(chuàng)建索引的情況
1.主鍵自動建立唯一索引；
2.頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
3.查詢中與其它表關(guān)聯(lián)的字段， 外鍵關(guān)系建立索引
4.單鍵/組合索引的選擇問題， 組合索引性價比更高
5.查詢中排序的字段， 排序字段若通過索引去訪問將大大提高排序速度
6.查詢中統(tǒng)計或者分組字段

不適合創(chuàng)建索引的情況
1.表記錄太少
2.經(jīng)常增刪改的表或者字段
3.Where 條件里用不到的字段不創(chuàng)建索引
4.過濾性不好的不適合建索引

2. 索引失效場景

2.1 索引失效9種場景

1.全值匹配：查詢條件的列與索引列的字段，順序完全相同。 2. 最佳左前綴：查詢條件的列與索引列的字段相同，順序不同，從不同順序列開始后邊都不走索引。 3. 索引計算：不要在索引上做任何計算 4. 索引范圍：索引列上不能有范圍查詢，比如大于，小于，大于等于，小于等于。 5. 索引覆蓋：盡量使用覆蓋索引 6. 不等： 使用不等于（!= 或者 <>）的時候 7. null：字段的is not null 與is null 8. like：like的前后模糊匹配 9. or：減少使用or

2.2 索引失效場景總結(jié)

全值匹配，左前綴。 索引計算范圍要覆蓋。 不等于（!= 或者 <>）掃全表，null走索引，not不走。 like模后不模前，見or就走union all

3. 索引失效驗證

索引測試環(huán)境
1.mysql版本：5.7.27-log，查詢語句：select VERSION();
2.建表語句及數(shù)據(jù)：mysql批量插入數(shù)據(jù)

3.1 全值匹配

-- 全值匹配 -- 查看sql執(zhí)行計劃 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30; EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4; EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd';-- 創(chuàng)建聯(lián)合索引 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

創(chuàng)建索引前

創(chuàng)建索引后

3.2 最佳左前綴

-- 創(chuàng)建索引 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME); -- 缺少聯(lián)合索引的第一個字段 explain select sql_no_cache * from emp where deptId = 4 and name = 'abcd'; -- 聯(lián)合索引的第一二個字段，缺少最后一個字段 explain select sql_no_cache * from emp where age = 30 and deptId = 4; -- 聯(lián)合索引的第一三各字段，缺少第二個字段 explain select sql_no_cache * from emp where age = 30 and name = 'abcd' ;

查詢字段與索引字段順序的不同會導(dǎo)致， 索引無法充分使用， 甚至索引失效！
原因： 使用復(fù)合索引， 需要遵循最佳左前綴法則， 即如果索引了多列， 要遵守最左前綴法則。 指的是查詢從索引的最左前列開始并且不跳過索引中的列。
結(jié)論： 過濾條件要使用索引必須按照索引建立時的順序， 依次滿足， 一旦跳過某個字段， 索引后面的字段都無法被使用

3.3 索引計算

不要在索引上做任何計算！
不在索引列上做任何操作（計算、 函數(shù)、 (自動 or 手動)類型轉(zhuǎn)換）， 會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。

1.在查詢列上使用函數(shù)

-- 索引不帶計算 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30; -- 索引字段計算 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;

\2. 在查詢列上做了轉(zhuǎn)換

-- 創(chuàng)建單值索引，字符串類型 name create index idx_name on emp(name); -- 字符串加單引號情況 explain select sql_no_cache * from emp where name='30000'; -- 字符串不加單引號， 則會在 name 列上做一次轉(zhuǎn)換！ explain select sql_no_cache * from emp where name=30000;

3.4 索引范圍：索引列上不能有范圍查詢

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';

建議： 將可能做范圍查詢的字段的索引順序放在最后

3.5 索引覆蓋：盡量使用覆蓋索引

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt'; explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';

3.6 不等： 使用不等于（!= 或者 <>）的時候

mysql 在使用不等于(!= 或者<>)時， 有時會無法使用索引會導(dǎo)致全表掃描。

3.7 null：字段的is not null 與is null

當(dāng)字段允許為 Null 的條件下：
is not null 用不到索引， is null 可以用到索引。

3.8 like：like的前后模糊匹配

前綴不能出現(xiàn)模糊匹配！

3.9 or：減少使用or

使用 union all 或者 union 來替代：

總結(jié)

以上是生活随笔為你收集整理的MySQL数据库索引及失效场景的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。