索引是一種數(shù)據(jù)結(jié)構(gòu),為了應(yīng)對(duì)不同的場(chǎng)景會(huì)有多種實(shí)現(xiàn),下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫索引以及失效場(chǎng)景的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

3.1 全值匹配
3.2 最佳左前綴
3.3 索引計(jì)算
3.4 索引范圍：索引列上不能有范圍查詢
3.5 索引覆蓋：盡量使用覆蓋索引
3.6 不等：使用不等于（!= 或者 <>）的時(shí)候
3.7 null：字段的is not null 與is null
3.8 like：like的前后模糊匹配
3.9 or：減少使用or

總結(jié)

1. MySQL索引概述

1.1 索引的概念

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

1.2 索引的特點(diǎn)

索引的優(yōu)點(diǎn)

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

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

索引的缺點(diǎn)

1.雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、 UPDATE和DELETE。因?yàn)楦卤頃r(shí)， MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息。

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

1.3 索引的分類

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

簡(jiǎn)單說下以下幾個(gè)常用索引。

單值索引

概念：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

唯一索引

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

主鍵索引

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

復(fù)合索引

概念：即一個(gè)索引包含多個(gè)列

1.4 索引的使用場(chǎng)景

適合創(chuàng)建索引的情況

1.主鍵自動(dòng)建立唯一索引；

2.頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引

3.查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引

4.單鍵/組合索引的選擇問題，組合索引性價(jià)比更高

5.查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度

6.查詢中統(tǒng)計(jì)或者分組字段

不適合創(chuàng)建索引的情況

1.表記錄太少

2.經(jīng)常增刪改的表或者字段

3.Where 條件里用不到的字段不創(chuàng)建索引

4.過濾性不好的不適合建索引

2. 索引失效場(chǎng)景

2.1 索引失效9種場(chǎng)景

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

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

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

3. 索引失效驗(yàn)證

索引測(cè)試環(huán)境

1.mysql版本：5.7.27-log，查詢語句：select VERSION();

2.建表語句及數(shù)據(jù)：mysql批量插入數(shù)據(jù)

3.1 全值匹配

-- 全值匹配
-- 查看sql執(zhí)行計(jì)劃
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)合索引的第一個(gè)字段
explain select sql_no_cache * from emp where deptId = 4 and name = 'abcd';
-- 聯(lián)合索引的第一二個(gè)字段，缺少最后一個(gè)字段
explain select sql_no_cache * from emp where age = 30 and deptId = 4;
-- 聯(lián)合索引的第一三各字段，缺少第二個(gè)字段
explain select sql_no_cache * from emp where age = 30 and name = 'abcd' ;

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

3.3 索引計(jì)算

不要在索引上做任何計(jì)算！

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

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

-- 索引不帶計(jì)算
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
-- 索引字段計(jì)算
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);
-- 字符串加單引號(hào)情況
explain select sql_no_cache * from emp where name='30000';
-- 字符串不加單引號(hào)， 則會(huì)在 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';