年輕人不講武德，亂用索引，你到底走了多少彎路？

2020-12-21 20:07 作者:蘇三說技術(shù) 0人讀過 | 我要投稿

前言

繼上一篇《explain | 索引優(yōu)化的這把絕世好劍，你真的會(huì)用嗎？》之后，本篇是索引相關(guān)的第2篇文章。上一篇重點(diǎn)介紹的是使用explain執(zhí)行計(jì)劃查看索引執(zhí)行情況，以便于快速定位哪張表有索引使用問題。

本篇主要介紹的是索引失效的常見原因和如何用好索引，跟上一篇正好承上啟下，給有需要的朋友一個(gè)參考。

本文將從以下幾個(gè)方便進(jìn)行講解：

1.索引失效常見原因：

2.索引失效常見誤區(qū)：

3.索引設(shè)計(jì)的幾個(gè)建議：

準(zhǔn)備工作

查看當(dāng)前mysql的版本：

 ?select  VERSION();

查出當(dāng)前版本為：8.0.21

創(chuàng)建一張表test1

 CREATE TABLE `test1` (
 ?`id` bigint NOT NULL,
 ?`code` varchar(30) NOT NULL,
 ?`age` int NOT NULL,
 ?`name` varchar(30) NOT NULL,
 ?`height` int NOT NULL,
 ?PRIMARY KEY (`id`),
 ?KEY `idx_code_age_name` (`code`,`age`,`name`) USING BTREE,
 ?KEY `idx_height` (`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入兩條數(shù)據(jù)：

INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (1, '001', '張飛', 18,'7');
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (2, '002', '關(guān)羽', 19,'8');

查詢一下：

select * from test1;

結(jié)果：

此外建立了兩個(gè)索引：idx_code_age_name(code, age, name)聯(lián)合索引 ?和 idx_height(height)普通索引。

一. 索引失效常見原因

1.不滿足最左前綴原則

第1種情況：

where條件后的字段包含了聯(lián)合索引的所有索引字段，并且順序是按照：code、age、name。

執(zhí)行sql如下：

 ? ?explain  select * from test1 
 ? ?where code='001' and age=18  and  name='張飛' ;

結(jié)果：

從圖中標(biāo)紅的地方可以看出已經(jīng)走了聯(lián)合索引idx_code_name_age，并且索引的長度是188， 188 = 30 * 3 + 2 + 30 * 3 + 2 + 4，索引是使用充分的，索引使用效率最佳。

有些朋友可能會(huì)問：索引長度為什么是這樣計(jì)算的？

答：請(qǐng)參考《explain | 索引優(yōu)化的這把絕世好劍，你真的會(huì)用嗎？》，里面給出了非常詳細(xì)的講解。

第2種情況：

where條件后的字段包含了聯(lián)合索引的所有索引字段，順序是不按照：code、age、name。

執(zhí)行sql如下：

 ? ?explain  select * from test1 
 ? ?where code='001' and  name='張飛' and age=18;

結(jié)果：

從上圖中看出執(zhí)行結(jié)果跟第一種情況一樣。

★
注意：這種情況比較特殊，在查詢之前mysql會(huì)自動(dòng)優(yōu)化順序。
”

第3種情況：

where條件后的字段包含了聯(lián)合索引中的：code字段

執(zhí)行sql如下：

 ? ?explain  select * from test1 
 ? ?where code='001';

結(jié)果：

從上圖看出也走了索引，但是索引長度有所變化，現(xiàn)在變成了92，92 = 30*3 + 2，只用到了一個(gè)索引字段code，索引使用不充分。

第4種情況：

where條件后的字段包含了聯(lián)合索引中的：age字段

執(zhí)行sql如下：

 ? ?explain  select * from test1 
 ? ?where age=18;

結(jié)果：

從上圖中看變成了全表掃描，所有的索引都失效了。

第5種情況：

where條件后的字段包含了聯(lián)合索引中的：name字段

執(zhí)行sql如下：

 ? ?explain  select * from test1 
 ? ?where  name='張飛';

結(jié)果：

從上圖中看變成了全表掃描，所有的索引都失效了。

第6種情況：

where條件后的字段包含了聯(lián)合索引中的：code和age字段

執(zhí)行sql如下：

 ? explain  select * from test1 
 ? where code='001' and age=18;

結(jié)果：

從上圖中看出還是走了索引，但是索引長度變成了：96，96 = 30*3 + 2 + 4，只用到了兩個(gè)索引字段code和age，索引使用也不充分。

第7種情況：

where條件后的字段包含了聯(lián)合索引中的：code和name字段

執(zhí)行sql如下：

 ? explain  select * from test1 
 ? where code='001' and  name='張飛';

結(jié)果：

從上圖中看出走的索引長度跟第1種情況一樣，長度也是92。也就是說只用到了一個(gè)索引字段code，而age字段的索引失效了。

第8種情況：

where條件后的字段包含了聯(lián)合索引中的：age和name字段

執(zhí)行sql如下：

 ? explain  select * from test1 
 ? where age=18  and  name='張飛';

結(jié)果：

從上圖中看出變成了全表掃描，所有的索引都失效了。

小結(jié)：

滿足最左前綴的場景：code，code、age，code、age、name。
包含：code、age、name全索引字段的情況比較特殊，這三個(gè)字段作為查詢條件的任意一種排序都OK，最終mysql會(huì)優(yōu)化成最左前綴的順序。
如果中間出現(xiàn)斷層，如：code、name，只會(huì)走第一個(gè)索引code，從斷層后的索引都會(huì)失效。
其他的場景：age，name，age,name索引都會(huì)失效。

2.范圍索引列沒有放在最后

where條件后的字段age用了大于等于，具體sql如下：

EXPLAIN  select * from test1 
where ?code='001' and age>18  and  name='張飛' ;

結(jié)果：

從上圖中看出索引長度變成：96，96 = 30*3 + 2 + 4，只用到了兩個(gè)索引字段code和age，而name字段的索引失效了。

如果范圍查詢的語句放到最后：

EXPLAIN  select * from test1 
where code='001' and  name='張飛' and age>18 ;

結(jié)果：

什么鬼？怎么索引長度還是：96？

這是一個(gè)非常經(jīng)典的錯(cuò)誤

范圍查詢放最后是指創(chuàng)建聯(lián)合索引的字段順序，現(xiàn)在的順序是：

調(diào)整一下把索引字段name和age的順序調(diào)整一下：

再執(zhí)行上面的sql，結(jié)果：

從上圖中看出索引長度變成：188，索引使用充分了。

回過頭再執(zhí)行剛開始的那條sql：

EXPLAIN  select * from test1 
where ?code='001' and age>18  and  name='張飛';

結(jié)果：

什么？

索引長度也是：188。

★
注意：范圍查詢放最后，指的是聯(lián)合索引中的范圍列放在最后，不是指where條件中的范圍列放最后。如果聯(lián)合索引中的范圍列放在最后了，即使where條件中的范圍列沒放最后也能正常走到索引。
”

3.使用了select *

其實(shí)在《阿里巴巴開發(fā)手冊(cè)》中也明確說了，禁止使用select * ，這是為什么呢？

 ?EXPLAIN  select * from test1

結(jié)果：

從上圖中看出走了全表掃描。

那么如果查詢的是索引列：

EXPLAIN  select code,age,name  from test1

結(jié)果：

從圖中可以看出這種情況走了全索引掃描，比全表掃描效率更高。

其實(shí)這里用到了：覆蓋索引。

如果select的列都是索引列，則被稱為覆蓋索引。

如果select的列不只包含索引列，則需要回表，即回到表中再查詢出其他列，效率相當(dāng)更低一些。select * 大概率需要查詢非索引列，需要回表，因此要少用。

當(dāng)然，本文中很多示例都使用了select * ，主要是我表中只有兩條數(shù)據(jù)，為了方便演示，正常業(yè)務(wù)代碼中是要杜絕這種寫法的。

4.索引列上有計(jì)算

執(zhí)行sql如下：

explain  select * from test1 
where ?height+1 =7;

結(jié)果：

從上圖中可以看出變成全表掃描了，由此可見在索引列上有計(jì)算，索引會(huì)失效。

5.索引列上使用了函數(shù)

如果在索引列加某個(gè)函數(shù)，具體sql如下：

explain  select * from test1 
where  ?SUBSTR(height,1,1)=8;

結(jié)果：

從上圖中可以看出變成全表掃描了，由此可見在索引列上加了函數(shù)，索引也會(huì)失效。

6.字符類型沒加引號(hào)

廢話不多說直接上sql：

explain  select * from test1 
where  name = 123;

結(jié)果：

從圖中看出走的全表掃描，索引失效了。

為什么索引會(huì)失效呢？

這里有些朋友可能會(huì)有點(diǎn)懵。

答：name字段是字符類型，而等于號(hào)右邊的是數(shù)字類型，類型不匹配導(dǎo)致索引丟失。

所以在使用字符類型字段做判斷時(shí)，一定要加上單引號(hào)。

★
類型不匹配導(dǎo)致索引丟失問題，是我們平時(shí)工作中非常容易忽視的問題，一定要引起足夠的重視
”

7.用is null和is not null沒注意字段是否允許為空

前面創(chuàng)建的test1表中height字段是非空的。

查詢sql如下：

explain  select * from test1 
where ?height is  null;

explain  select * from test1 
where ?height is  not  null;

結(jié)果都是：

從上圖中看出都是全表掃描，索引都失效了。

如果height字段改成允許為空的呢？

上面第一條sql執(zhí)行結(jié)果：

從上圖中看出走了ref類型的索引。

上面第二條sql執(zhí)行結(jié)果：

從上圖中看出走了range類型的索引。

小結(jié)

如果字段不允許為空，則is null 和 is not null這兩種情況索引都會(huì)失效。
如果字段允許為空，則is null走ref類型的索引，而is not null走range類型的索引。

8.like查詢左邊有%

like查詢主要有三種情況：

like '%a'
like 'a%'
like '%a%'

先看看第一種情況：

explain  select * from test1 
where ?code like '%001';

結(jié)果：

從上圖看出走的全表掃描，索引失效了。

再看看第二種情況：

explain  select * from test1 
where ?code like  '001%';

結(jié)果：

從上圖看出走的range類型的索引。

最后看看第三種情況：

explain  select * from test1 
where ?code like  '%001%';

結(jié)果：

從上圖看出走的全表掃描，索引也失效了。

從這三種結(jié)果看出like語句只有%在右邊才能走索引。

如果有些場景就是要使用like語句%在左邊該怎么辦呢？

答案：使用覆蓋索引

具體sql如下：

explain  select code,age,name  from test1 
where ?code like  '%001%';

結(jié)果：

從上圖看出走的index類型的全索引掃描，相對(duì)于全表掃描性能更好。

當(dāng)然，最佳實(shí)踐是在sql中要避免like語句%在左邊的情況，如果有這種業(yè)務(wù)場景可以使用es代替mysql存儲(chǔ)數(shù)據(jù)。

小結(jié)：

like '%a' 索引失效
like 'a%' 走range類型索引
like '%a%' 索引失效

9.使用or關(guān)鍵字時(shí)沒有注意

用法如下：

explain  select * from test1 
where ?height = 8  or height = 9;

結(jié)果：

從上圖中看出走了range類型的索引，不是沒問題嗎？

再把sql改一下：

explain  select * from test1 
where ?code = '001' or height = 8;

結(jié)果：

從上圖中可以看出變成了全表掃描，索引失效了。

我們不妨單獨(dú)查詢一下：

explain  select * from test1 
where ?code = '001';

結(jié)果：

和

explain  select * from test1 
where ?height = 8;

結(jié)果：

兩種單獨(dú)查詢的情況都走了ref類型的索引，但是使用or關(guān)鍵字后sql的索引會(huì)失效。

那么，我們?cè)谙胧褂?code>or的場景，又想讓索引有效，該怎么辦呢？

explain (select * from test1 where ? code = '001') 
union (select * from test1 where ?height = 8);

沒錯(cuò)，使用union關(guān)鍵字，但是跟or關(guān)鍵字的語法稍微有點(diǎn)區(qū)別，不過查詢的數(shù)據(jù)結(jié)果是一樣的。

上面sql執(zhí)行結(jié)果如下：

我們看到走了ref類型索引。

★
or關(guān)鍵字會(huì)讓索引失效，可以用union代替
”

二. 索引失效的常見誤區(qū)

1.使用not in會(huì)導(dǎo)致索引失效

用法如下：

explain  select * from test1 
where ?height not  in (7,8);

結(jié)果：

從上圖中看出是走了range類型索引的，并沒失效。

★
需要特別說明的是mysql5.7和5.8不同的版本效果不一樣，5.7中這種情況sql執(zhí)行結(jié)果是全表掃描，而5.8中使用了range類型索引。
”

2.使用不等于號(hào)會(huì)導(dǎo)致索引失效

用法如下：

explain  select * from test1 
where height!=8;

結(jié)果：

從圖中看出走了range類型的索引。

★
需要特別說明的是mysql5.7和5.8不同的版本效果不一樣，5.7中這種情況sql執(zhí)行結(jié)果是全表掃描，而5.8中使用了range類型索引。5.7中如果想使用索引該怎么辦呢？答案：使用大于和小于代替不等于。
”

在這里溫馨的提醒一聲，不等于號(hào)不只是 != ，還包括 <>。

3.order by索引字段順序不當(dāng)導(dǎo)致索引失效

sql中除了where后面的字段能走索引之外，order by后面的字段也能走索引。

EXPLAIN  select * from test1 
where ?code='001' order  by age,name;

結(jié)果：

從上圖中看出走了ref類型的索引，索引長度是92，并且沒有額外信息。

但是如果把order by 后面的條件改成如下兩種排序：

 ? EXPLAIN  select * from test1 
	where ?code='001' order  by  name;

 ? EXPLAIN  select * from test1 
	where ?code='001' order  by  name,age;

結(jié)果：

從上圖中看出還是走了ref類型的索引，索引長度是92，但是額外信息中提示：Using filesort，即按文件重排序。

上面兩個(gè)例子能夠看出有沒有使用索引跟where后面的條件有關(guān)，而跟order by 后面的字段沒關(guān)系。

而需不需要按文件重排序，則跟order by 后面的字段有直接關(guān)系。

問題來了，額外信息中提示：Using filesort這種該如何優(yōu)化？

答：這種情況一般是聯(lián)合索引中索引字段的順序，跟sql中where條件及order by 不一致導(dǎo)致的，只要順序調(diào)整一致就不會(huì)出現(xiàn)這個(gè)問題。

三. 索引設(shè)計(jì)的幾個(gè)建議：

優(yōu)先使用唯一索引，能夠快速定位
為常用查詢字段建索引
為排序、分組和聯(lián)合查詢字段建索引
一張表的索引數(shù)量不超過5個(gè)
表數(shù)據(jù)量少，可以不用建索引
盡量使用占用空間小的字段建索引
用idx_或unx_等前綴命名索引，方面查找
刪除沒用的索引，因?yàn)樗鼤?huì)占一定空間

四. 彩蛋：

特別說明：索引失效除了上述的常見問題之外，mysql通過索引掃描的行記錄數(shù)超過全表的10%~30%左右,優(yōu)化器也可能不會(huì)走索引，自動(dòng)變成全表掃描。

送給大家一個(gè)避坑口訣：

全職匹配我最愛，最左前綴要遵守
帶頭大哥不能死，中間兄弟不能斷
索引列上少計(jì)算，范圍列后全失效
like百分寫最右，覆蓋索引不寫*
不等空值還有or，索引影響要注意；
字符字段引號(hào)不能丟，sql優(yōu)化有訣竅。

最后說一句(求關(guān)注，別白嫖我)

如果這篇文章對(duì)您有所幫助，或者有所啟發(fā)的話，幫忙掃描下發(fā)二維碼關(guān)注一下，您的支持是我堅(jiān)持寫作最大的動(dòng)力。

求一鍵三連：點(diǎn)贊、轉(zhuǎn)發(fā)、在看。

在公眾號(hào)中回復(fù)：面試、代碼神器、開發(fā)手冊(cè)、時(shí)間管理有超贊的粉絲福利，另外回復(fù)：加群，可以跟很多BAT大廠的前輩交流和學(xué)習(xí)。

參考：https://blog.csdn.net/sggtgfs/article/details/94439305

最美情侣中文字幕电影,在线麻豆精品传媒,在线网站高清黄,久久黄色视频

年輕人不講武德，亂用索引，你到底走了多少彎路？

前言

準(zhǔn)備工作

一. 索引失效常見原因

1.不滿足最左前綴原則

2.范圍索引列沒有放在最后

3.使用了select *

4.索引列上有計(jì)算

5.索引列上使用了函數(shù)

6.字符類型沒加引號(hào)

7.用is null和is not null沒注意字段是否允許為空

8.like查詢左邊有%

9.使用or關(guān)鍵字時(shí)沒有注意

二. 索引失效的常見誤區(qū)

1.使用not in會(huì)導(dǎo)致索引失效

2.使用不等于號(hào)會(huì)導(dǎo)致索引失效

3.order by索引字段順序不當(dāng)導(dǎo)致索引失效

三. 索引設(shè)計(jì)的幾個(gè)建議：

四. 彩蛋：

最后說一句(求關(guān)注，別白嫖我)