您好,登錄后才能下訂單哦!
這篇文章主要講解了“怎么正確使用索引”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“怎么正確使用索引”吧!
本文將從以下幾個(gè)方便進(jìn)行講解:
1.索引失效常見原因:
2.索引失效常見誤區(qū):
3.索引設(shè)計(jì)的幾個(gè)建議:
查看當(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 )普通索引。
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 ,索引是使用充分的,索引使用效率最佳。
有些朋友可能會問:索引長度為什么是這樣計(jì)算的?
答:請參考《 explain | 索引優(yōu)化的這把絕世好劍,你真的會用嗎? 》,里面給出了非常詳細(xì)的講解。
where 條件后的字段包含了聯(lián)合索引的所有索引字段,順序是不按照: code 、 age 、name。
執(zhí)行sql如下:
explain select * from test1 where code='001' and name='張飛' and age=18;
結(jié)果:
從上圖中看出執(zhí)行結(jié)果跟第一種情況一樣。
注意:這種情況比較特殊,在查詢之前mysql會自動(dòng)優(yōu)化順序。
where 條件后的字段包含了聯(lián)合索引中的: code 字段
執(zhí)行sql如下:
explain select * from test1 where code='001';
結(jié)果:
從上圖看出也走了索引,但是索引長度有所變化,現(xiàn)在變成了 92 , 92 = 30*3 + 2 ,只用到了一個(gè)索引字段code,索引使用不充分。
where 條件后的字段包含了聯(lián)合索引中的: age 字段
執(zhí)行sql如下:
explain select * from test1 where age=18;
結(jié)果:
從上圖中看變成了全表掃描,所有的索引都失效了。
where 條件后的字段包含了聯(lián)合索引中的: name 字段
執(zhí)行sql如下:
explain select * from test1 where name='張飛';
結(jié)果:從上圖中看變成了全表掃描,所有的索引都失效了。
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,索引使用也不充分。
where 條件后的字段包含了聯(lián)合索引中的: code 和 name 字段
執(zhí)行sql如下:
explain select * from test1 where code='001' and name='張飛';
結(jié)果:
從上圖中看出走的索引長度跟第1種情況一樣,長度也是 92 。也就是說只用到了一個(gè)索引字段 code ,而 age 字段的索引失效了。
where 條件后的字段包含了聯(lián)合索引中的: age 和 name 字段
執(zhí)行sql如下:
explain select * from test1 where age=18 and name='張飛';
結(jié)果:
從上圖中看出變成了全表掃描,所有的索引都失效了。
code code、age code、age、name
code age name
如果中間出現(xiàn)斷層,如: code、name ,只會走第一個(gè)索引code,從斷層后的索引都會失效。
age name age,name
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 ?
范圍查詢放最后是指創(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條件中的范圍列沒放最后也能正常走到索引。
其實(shí)在《阿里巴巴開發(fā)手冊》中也明確說了,禁止使用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ù)代碼中是要杜絕這種寫法的。
執(zhí)行sql如下:
explain select * from test1 where height+1 =7;
結(jié)果:從上圖中可以看出變成全表掃描了,由此可見在索引列上有計(jì)算,索引會失效。
如果在索引列加某個(gè)函數(shù),具體sql如下:
explain select * from test1 where SUBSTR(height,1,1)=8;
結(jié)果:從上圖中可以看出變成全表掃描了,由此可見在索引列上加了函數(shù),索引也會失效。
廢話不多說直接上sql:
explain select * from test1 where name = 123;
結(jié)果:
從圖中看出走的全表掃描,索引失效了。
為什么索引會失效呢?
這里有些朋友可能會有點(diǎn)懵。
答: name 字段是 字符類型 ,而等于號右邊的是 數(shù)字類型 ,類型不匹配導(dǎo)致索引丟失。
所以在使用字符類型字段做判斷時(shí),一定要加上單引號。
類型不匹配導(dǎo)致索引丟失問題,是我們平時(shí)工作中非常容易忽視的問題,一定要引起足夠的重視
前面創(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 類型的索引。
如果字段不允許為空,則is null 和 is not null這兩種情況索引都會失效。
如果字段允許為空,則is null走 ref 類型的索引,而is not null走 range 類型的索引。
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 類型的全索引掃描,相對于全表掃描性能更好。
當(dāng)然,最佳實(shí)踐是在 sql 中要避免 like 語句 % 在左邊的情況,如果有這種業(yè)務(wù)場景可以使用es 代替 mysql 存儲數(shù)據(jù)。
小結(jié):
like '%a' 索引失效
like 'a%' 走range類型索引
like '%a%' 索引失效
用法如下:
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的索引會失效。
那么,我們在想使用 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)鍵字會讓索引失效,可以用union代替
用法如下:
explain select * from test1 where height not in (7,8);
結(jié)果:
從上圖中看出是走了 range 類型索引的,并沒失效。
需要特別說明的是mysql5.7和5.8不同的版本效果不一樣,5.7中這種情況sql執(zhí)行結(jié)果是全表掃描,而5.8中使用了 range 類型索引。
用法如下:
explain select * from test1 where height!=8;
結(jié)果:
從圖中看出走了 range 類型的索引。
需要特別說明的是mysql5.7和5.8不同的版本效果不一樣,5.7中這種情況sql執(zhí)行結(jié)果是全表掃描,而5.8中使用了 range 類型索引。5.7中如果想使用索引該怎么辦呢?答案:使用大于和小于代替不等于。
在這里溫馨的提醒一聲,不等于號不只是 != ,還包括 <> 。
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)整一致就不會出現(xiàn)這個(gè)問題。
優(yōu)先使用唯一索引,能夠快速定位
為常用查詢字段建索引
為排序、分組和聯(lián)合查詢字段建索引
一張表的索引數(shù)量不超過5個(gè)
表數(shù)據(jù)量少,可以不用建索引
盡量使用占用空間小的字段建索引
用idx_或unx_等前綴命名索引,方面查找
刪除沒用的索引,因?yàn)樗鼤家欢臻g
感謝各位的閱讀,以上就是“怎么正確使用索引”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對怎么正確使用索引這一問題有了更深刻的體會,具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。