1. MySQL索引概述

1.1 索引的概念

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

1.2 索引的特點(diǎn)

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

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

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

索引的缺點(diǎn)

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

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

1.3 索引的分類(lèi)

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

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

單值索引

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

唯一索引

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

主鍵索引

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

復(fù)合索引

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

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

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

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

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

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

4.單鍵/組合索引的選擇問(wèn)題， 組合索引性?xún)r(jià)比更高

5.查詢(xún)中排序的字段， 排序字段若通過(guò)索引去訪問(wèn)將大大提高排序速度

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

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

1.表記錄太少

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

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

4.過(guò)濾性不好的不適合建索引

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

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

1.全值匹配：查詢(xún)條件的列與索引列的字段，順序完全相同。
2. 最佳左前綴：查詢(xún)條件的列與索引列的字段相同，順序不同，從不同順序列開(kāi)始后邊都不走索引。
3. 索引計(jì)算：不要在索引上做任何計(jì)算
4. 索引范圍：索引列上不能有范圍查詢(xún)，比如大于，小于，大于等于，小于等于。
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模后不模前，見(jiàn)or就走union all

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

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

1.mysql版本：5.7.27-log，查詢(xún)語(yǔ)句：select VERSION();

2.建表語(yǔ)句及數(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' ;

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

3.3 索引計(jì)算

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

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

1.在查詢(xún)列上使用函數(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. 在查詢(xún)列上做了轉(zhuǎn)換

-- 創(chuàng)建單值索引，字符串類(lèi)型 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 索引范圍：索引列上不能有范圍查詢(xún)

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';

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

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 不等： 使用不等于（!= 或者 <>）的時(shí)候

mysql 在使用不等于(!= 或者<>)時(shí)， 有時(shí)會(huì)無(wú)法使用索引會(huì)導(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 來(lái)替代：

 總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)索引以及失效場(chǎng)景的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫(kù)索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論