本篇內(nèi)容主要講解“索引失效的原因是什么”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“索引失效的原因是什么”吧!
江北網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián),江北網(wǎng)站設(shè)計(jì)制作,有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為江北1000多家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站建設(shè)要多少錢,請找那個(gè)售后服務(wù)好的江北做網(wǎng)站的公司定做!
MySQL數(shù)據(jù)是如何存儲(chǔ)的?
聚集索引
我們先建如下的一張表
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號(hào)', `name` varchar(10) NOT NULL COMMENT '學(xué)生姓名', `age` int(11) NOT NULL COMMENT '學(xué)生年齡', PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB;
插入如下sql
insert into student (`name`, `age`) value('a', 10); insert into student (`name`, `age`) value('c', 12); insert into student (`name`, `age`) value('b', 9); insert into student (`name`, `age`) value('d', 15); insert into student (`name`, `age`) value('h', 17); insert into student (`name`, `age`) value('l', 13); insert into student (`name`, `age`) value('k', 12); insert into student (`name`, `age`) value('x', 9);
數(shù)據(jù)如下
圖片mysql是按照頁來存儲(chǔ)數(shù)據(jù)的,每個(gè)頁的大小為16k。
在MySQL中可以通過執(zhí)行如下語句,看到一個(gè)頁的大小
show global status like 'innodb_page_size'
結(jié)果為16384,即16kb
在InnoDB存儲(chǔ)引擎中,是以主鍵為索引來組織數(shù)據(jù)的。記錄在頁中按照主鍵從小到大的順序以單鏈表的形式連接在一起。
可能有小伙伴會(huì)問,如果建表的時(shí)候,沒有指定主鍵呢?
如果在創(chuàng)建表時(shí)沒有顯示的定義主鍵,則InnoDB存儲(chǔ)引擎會(huì)按如下方式選擇或創(chuàng)建主鍵。
首先判斷表中是否有非空的唯一索引,如果有,則該列即為主鍵。如果有多個(gè)非空唯一索引時(shí),InnoDB存儲(chǔ)引擎將選擇建表時(shí)第一個(gè)定義的非空唯一索引作為主鍵
如果不符合上述條件,InnoDB存儲(chǔ)引擎自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針作為索引
頁和頁之間以雙鏈表的形式連接在一起。并且下一個(gè)數(shù)據(jù)頁中用戶記錄的主鍵值必須大于上一個(gè)數(shù)據(jù)頁中用戶記錄的主鍵值
假設(shè)一個(gè)頁只能存放3條數(shù)據(jù),則數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)如下。
可以看到我們想查詢一個(gè)數(shù)據(jù)或者插入一條數(shù)據(jù)的時(shí)候,需要從最開始的頁開始,依次遍歷每個(gè)頁的鏈表,效率并不高。
我們可以給這頁做一個(gè)目錄,保存主鍵和頁號(hào)的映射關(guān)系,根據(jù)二分法就能快速找到數(shù)據(jù)所在的頁。但這樣做的前提是這個(gè)映射關(guān)系需要保存到連續(xù)的空間,如數(shù)組。如果這樣做會(huì)有如下幾個(gè)問題
隨著數(shù)據(jù)的增多,目錄所需要的連續(xù)空間越來越大,并不現(xiàn)實(shí)
當(dāng)有一個(gè)頁的數(shù)據(jù)全被刪除了,則相應(yīng)的目錄項(xiàng)也要?jiǎng)h除,它后面的目錄項(xiàng)都要向前移動(dòng),成本太高
我們可以把目錄數(shù)據(jù)放在和用戶數(shù)據(jù)類似的結(jié)構(gòu)中,如下所示。目錄項(xiàng)有2個(gè)列,主鍵和頁號(hào)。
數(shù)據(jù)很多時(shí),一個(gè)目錄項(xiàng)肯定很多,畢竟一個(gè)頁的大小為16k,我們可以對(duì)數(shù)據(jù)建立多個(gè)目錄項(xiàng)目,在目錄項(xiàng)的基礎(chǔ)上再建目錄項(xiàng),如下圖所示
這其實(shí)就是一顆B+樹,也是一個(gè)聚集索引,即數(shù)據(jù)和索引在一塊。葉子節(jié)點(diǎn)保存所有的列值
以 InnoDB 的一個(gè)整數(shù)字段索引為例,這個(gè) N 差不多是 1200。這棵樹高是 4 的時(shí)候,就可以存 1200 的 3 次方個(gè)值,這已經(jīng)17 億了??紤]到樹根的數(shù)據(jù)塊總是在內(nèi)存中的,一個(gè) 10 億行的表上一個(gè)整數(shù)字段的索引,查找一個(gè)值最多只需要訪問 3次磁盤。其實(shí),樹的第二層也有很大概率在內(nèi)存中,那么訪問磁盤的平均次數(shù)就更少了。
非聚集索引
聚集索引和非聚集索引非常類似,區(qū)別如下
聚集索引葉子節(jié)點(diǎn)的值為所有的列值非聚集索引葉子節(jié)點(diǎn)的值為索引列+主鍵
當(dāng)我們查詢name為h的用戶信息時(shí)(學(xué)號(hào),姓名,年齡),因?yàn)閚ame上建了索引,先從name非聚集索引上,找到對(duì)應(yīng)的主鍵id,然后根據(jù)主鍵id從聚集索引上找到對(duì)應(yīng)的記錄。
從非聚集索引上找到對(duì)應(yīng)的主鍵值然后到聚集索引上查找對(duì)應(yīng)記錄的過程為回表
聯(lián)合索引/索引覆蓋
假設(shè)teacher表定義如下,在name和age列上建立聯(lián)合索引
CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '教師編號(hào)', `name` varchar(10) NOT NULL COMMENT '教師姓名', `age` int(11) NOT NULL COMMENT '教師年齡', `ismale` tinyint(3) NOT NULL COMMENT '是否男性', PRIMARY KEY (`id`), KEY `idx_name_age` (`name`, `age`) ) ENGINE=InnoDB;
插入如下sql
insert into teacher (`name`, `age`, `ismale`) value('aa', 10, 1); insert into teacher (`name`, `age`, `ismale`) value('dd', 12, 0); insert into teacher (`name`, `age`, `ismale`) value('cb', 9, 1); insert into teacher (`name`, `age`, `ismale`) value('cb', 15, 1); insert into teacher (`name`, `age`, `ismale`) value('bc', 17, 0); insert into teacher (`name`, `age`, `ismale`) value('bb', 15, 1); insert into teacher (`name`, `age`, `ismale`) value('dd', 15, 1); insert into teacher (`name`, `age`, `ismale`) value('dd', 12, 0);
對(duì)name和age列建立聯(lián)合索引
目錄頁由name列,age列,頁號(hào)這三部分組成。目錄會(huì)先按照name列進(jìn)行排序,當(dāng)name列相同的時(shí)候才對(duì)age列進(jìn)行排序。
數(shù)據(jù)頁由name列,age列,主鍵值這三部分組成。同樣的,數(shù)據(jù)頁會(huì)先按照name列進(jìn)行排序,當(dāng)name列相同的時(shí)候才對(duì)age列進(jìn)行排序。
當(dāng)執(zhí)行如下語句的時(shí)候,會(huì)有回表的過程
select * from student where name = 'aa';
當(dāng)執(zhí)行如下語句的時(shí)候,沒有回表的過程
select name, age from student where name = 'aa';
為什么不需要回表呢?
因?yàn)閕dx_name_age索引的葉子節(jié)點(diǎn)存的值為主鍵值,name值和age值,所以從idx_name_age索引上就能獲取到所需要的列值,不需要回表,即索引覆蓋
仔細(xì)看一下聯(lián)合索引這個(gè)圖,你就基本上能明白為什么不滿足最左前綴原則的索引會(huì)失效?
索引下推
當(dāng)執(zhí)行如下語句的時(shí)候
select * from student where name like '張%' and age = 10 and ismale = 1;
在5.6版本之前的執(zhí)行過程如下,先從idx_name_age索引上找到對(duì)應(yīng)的主鍵值,然后回表找到對(duì)應(yīng)的行,判斷其他字段的值是否滿足條件
在5.6引入了索引下推優(yōu)化,可以在遍歷索引的過程中,對(duì)索引中包含的字段做判斷,直接過濾掉不滿足條件的數(shù)據(jù),減少回表次數(shù),如下圖
最左前綴原則
加速查詢
主要針對(duì)組合索引,滿足如下2個(gè)條件即可滿足左前綴原則
需要查詢的列和組合索引的列順序一致
查詢不要跨列
構(gòu)造數(shù)據(jù)如下,其中在name,address,country上建了聯(lián)合索引
CREATE TABLE `people` ( `name` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, `country` varchar(50) NOT NULL, KEY `idx_name_addr_country` (`name`,`address`,`country`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
舉幾個(gè)例子,下面涉及到一些explain相關(guān)的知識(shí),后面單開一篇長文來做介紹
例子一
explain select * from people where name = "jack" and address = "beijing" and country = "china"
type為ref,key_len為456=(50*3+2)*3,聯(lián)合索引的所有列都使用了
例子二
explain select * from people where name = "jack"
type為ref,key_len為152=50*3+2,聯(lián)合索引只使用了name列
例子三
explain select * from people where address = "beijing"
type為index,表明查詢的時(shí)候?qū)φ麄€(gè)索引進(jìn)行了掃描,并沒有加速查找。
假設(shè)有如下的聯(lián)合索引 key idx_a_b_c(a,b,c)
sql | 是否使用索引 |
---|---|
where a = x and b = x and c = x | 是 |
where a = x and b = x | 是,部分索引 |
where a = x | 是,部分索引 |
where b = x | 否,不包含最左列name |
where b = x and c = x | 否,不包含最左列name |
如果你仔細(xì)看了前面聯(lián)合索引是如何存儲(chǔ)的,那你一定能看懂是否使用索引的介紹
目錄頁是按照 a b c 列的順序依次遞增排序的。先按照a列排序,如果a列相同,再按照b列排序,如果b列相同,才按照c列排序
所以查詢列值a b c,則這個(gè)排序規(guī)則能用到,即會(huì)走索引。如果只查列值b,并不能用到這個(gè)排序規(guī)則,所以得遍歷所有的記錄
加速排序
最左前綴原則不僅用在查詢中,還能用在排序中。MySQL中,有兩種方式生成有序結(jié)果集:
通過有序索引順序掃描直接返回有序數(shù)據(jù)
Filesort排序,對(duì)返回的數(shù)據(jù)進(jìn)行排序
因?yàn)樗饕慕Y(jié)構(gòu)是B+樹,索引中的數(shù)據(jù)是按照一定順序進(jìn)行排列的,所以在排序查詢中如果能利用索引,就能避免額外的排序操作。EXPLAIN分析查詢時(shí),Extra顯示為Using index。
所有不是通過索引直接返回排序結(jié)果的操作都是Filesort排序,也就是說進(jìn)行了額外的排序操作。EXPLAIN分析查詢時(shí),Extra顯示為Using filesort,當(dāng)出現(xiàn)Using filesort時(shí)對(duì)性能損耗較大,所以要盡量避免Using filesort
還是先舉2個(gè)例子,然后總結(jié)
explain select * from people order by name
Extra列只有Using index,即根據(jù)索引順序進(jìn)行掃描
explain select * from people order by address
在這里插入圖片描述
Extra列有Using filesort
總結(jié):假如說有如下聯(lián)合索引,key idx_a_b_c(a,b,c)
order by 能使用索引排序
order by a order by a,b order by a,b,c order by a desc, b desc, c desc where a = const order by b,c where a = const and b = const order by c where a = const and b > const order by b,c
order by 不能使用索引進(jìn)行排序
order by b order by c order by b, c order by a asc, b desc, c desc //排序不一致 where g = const order by b,c //丟失a索引 where a = const order by c //丟失b索引 where a = const order by a,d //d不是索引的一部分 where a in (...) order by b,c //范圍查詢
這個(gè)原因就不用我解釋了把,相信你一定看懂了
聯(lián)合索引的好處
索引覆蓋,減少了很多回表的操作,提高了查詢的效率
索引下推,索引列越多,通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表,有如下sql:select * from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù),如果只有單值索引,那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù),然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù);如果是聯(lián)合索引,通過索引篩選出1000w*10%*10% *10%=1w,效率提升可想而知!
索引為什么會(huì)失效?
當(dāng)別人問我索引在什么條件下會(huì)失效時(shí),我能背出一大堆規(guī)則
不要在索引列上進(jìn)行運(yùn)算或使用函數(shù)
前導(dǎo)模糊查詢不會(huì)使用索引,例如 like %李
負(fù)向條件索引不會(huì)使用索引,建議用in。負(fù)向條件有:!=、<>、not in、not exists、not like 等
索引是按照一定規(guī)則排好序的,如果對(duì)索引列使用函數(shù),或者 like % 李,具體的值都不知道,它怎么在B+樹上加速查詢?
到此,相信大家對(duì)“索引失效的原因是什么”有了更深的了解,不妨來實(shí)際操作一番吧!這里是創(chuàng)新互聯(lián)網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
網(wǎng)頁題目:索引失效的原因是什么
網(wǎng)站地址:http://bm7419.com/article2/goceoc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站制作、搜索引擎優(yōu)化、網(wǎng)站營銷、云服務(wù)器、動(dòng)態(tài)網(wǎng)站、App開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)