「Mysql索引原理（七）」覆蓋索引

? ?通常大家都會(huì)根據(jù)查詢的WHERE條件來創(chuàng)建合適的索引，不過這只是索引優(yōu)化的一個(gè)方面。設(shè)計(jì)優(yōu)秀的索引應(yīng)該考慮到整個(gè)查詢，而不單單是WHERE條件部分。索引確實(shí)是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來直接獲取列的數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要再回到表中查詢呢？ 如果一個(gè)索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計(jì)制作、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的武威網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

覆蓋索引是非常有用的工具，能夠極大地提高性能：

? ?在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。

? ?不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引都不存儲(chǔ)索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

? ?當(dāng)發(fā)起一個(gè)唄索引覆蓋的查詢是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

? ?索引覆蓋查詢還有很多陷阱可能會(huì)導(dǎo)致無法實(shí)現(xiàn)優(yōu)化。MySQL查詢優(yōu)化器會(huì)在執(zhí)行查詢前判斷是否有一個(gè)索引能進(jìn)行覆蓋。假設(shè)索引覆蓋了wehre條件中的字段，但不是整個(gè)查詢涉及的字段。mysql5.5和更早的版本也總是會(huì)回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會(huì)被過濾掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

這里索引無法覆蓋該查詢，有兩個(gè)原因：

這條語句只檢索1行，而之前的 like '%Kim%'要檢索3行。

也有辦法解決上面所說的兩個(gè)問題，需要重寫查詢并巧妙設(shè)計(jì)索引。

? ?這種方式叫做延遲關(guān)聯(lián)，因?yàn)檠舆t了對(duì)列的訪問。在查詢第一個(gè)階段MySQL可以使用覆蓋索引，因?yàn)樗饕酥麈Iid的值，不需要做二次查找。

? ?在FROM子句的子查詢中找到匹配的id，然后根據(jù)這些id值在外層查詢匹配獲取需要的所有列值。雖然無法使用索引覆蓋整個(gè)查詢，但總算比完全無法利用索引覆蓋的好吧。

數(shù)據(jù)量大了怎么辦？

? ?這樣優(yōu)化的效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個(gè)people表有100萬行，我們看一下上面兩個(gè)查詢?cè)谌齻€(gè)不同的數(shù)據(jù)集上的表現(xiàn)，每個(gè)數(shù)據(jù)集都包含100萬行。

實(shí)例1中 ，查詢返回了一個(gè)很大的結(jié)果集，因此看不到優(yōu)化的效果。大部分時(shí)間都花在讀取和發(fā)送數(shù)據(jù)上了。

實(shí)例2中 ，經(jīng)過索引過濾，尤其是第二個(gè)條件過濾后只返回了很少的結(jié)果集，優(yōu)化的效果非常明顯：在這個(gè)數(shù)據(jù)及上性能提高了很多，優(yōu)化后的查詢效率主要得益于只需讀取40行完整數(shù)據(jù)行，而不是原查詢中需要的30000行。

實(shí)例3中 ，子查詢效率反而下降。因?yàn)樗饕^濾時(shí)符合第一個(gè)條件的結(jié)果集已經(jīng)很小了，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

? ?在大多數(shù)存儲(chǔ)引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以更進(jìn)一步優(yōu)化InnoDB。回想一下，InnoDB的二級(jí)索引的葉子節(jié)點(diǎn)都包含了主鍵的值，這意味著InnoDB的二級(jí)索引可以有效地利用這些額外的主鍵列來覆蓋查詢。

? ?例如，people表中l(wèi)ast_name字段有一個(gè)二級(jí)索引，雖然該索引的列不包括主鍵id，但也能夠用于對(duì)id做覆蓋查詢：

select id,last_name from people where last_name='hua'

Mysql建立索引經(jīng)驗(yàn)

在實(shí)際開發(fā)中使用數(shù)據(jù)庫時(shí)，難免會(huì)遇到一些大表數(shù)據(jù)，對(duì)這些數(shù)據(jù)進(jìn)行查詢時(shí)，有時(shí)候SQL會(huì)查詢得特別慢，這時(shí)候，有經(jīng)驗(yàn)的老師傅會(huì)告訴你，你看一下哪幾個(gè)字段查的多，加一個(gè)索引就好了。

那么，怎么合理地建立索引呢？這里分享一下我的一些經(jīng)驗(yàn)，如有不妥之處，歡迎批評(píng)指正。

1、不要盲目建立索引 ， 先分析再創(chuàng)建

索引雖然能大幅度提升我們的查詢性能，但也要知道，在你進(jìn)行增刪改時(shí)，索引樹也要同樣地進(jìn)行維護(hù)。所以，索引不是越多越好，而是按需建立。最好是在一整塊模塊開發(fā)完成后，分析一下，去針對(duì)大多數(shù)的查詢，建立聯(lián)合索引。

2、使用聯(lián)合索引盡量覆蓋多的條件

這是說在一個(gè)慢sql里假如有五個(gè)where ，一個(gè) order by ，那么我們的聯(lián)合索引盡量覆蓋到這五個(gè)查詢條件，如果有必要，order by 也覆蓋上 。

3、小基數(shù)字段不需要索引

這個(gè)意思是，如果一張表里某個(gè)字段的值只有那么幾個(gè)，那么你針對(duì)這個(gè)字段建立的索引其實(shí)沒什么意義，比如說，一個(gè)性別字段就兩種結(jié)果，你建了索引，排序也沒什么意思（也就是索引里把男女給分開了）

所以說，索引盡量選擇基數(shù)大的數(shù)據(jù)去建立，能最大化地利用索引

4、長字符串可以使用前綴索引

我們建立索引的字段盡量選擇字段類型較小的，比如一個(gè)varchar(20)和varchar(256)的，我們?cè)?0的上面建立的索引和在256上就有明顯的差距（字符串那么長排序也不好排呀，唉）。

當(dāng)然，如果一定是要對(duì)varchar(256)建立索引,我們可以選擇里面的前20個(gè)字符放在索引樹里（這里的20不絕對(duì)，選擇能盡量分辨數(shù)據(jù)的最小字符字段設(shè)計(jì)），類似這樣KEY index(name(20),age,job) ，索引只會(huì)對(duì)name的前20個(gè)字符進(jìn)行搜索，但前綴索引無法適用于order by 和 group by。

5、對(duì)排序字段設(shè)計(jì)索引的優(yōu)先級(jí)低

如果一個(gè)SQL里我們出現(xiàn)了范圍查找，后邊又跟著一個(gè)排序字段，那么我們優(yōu)先給范圍查找的字段設(shè)置索引，而不是優(yōu)先排序。

6、如果出現(xiàn)慢SQL，可以設(shè)計(jì)一個(gè)只針對(duì)該條SQL的聯(lián)合索引。

不過慢SQL的優(yōu)化，需要一步步去進(jìn)行分析，可以先用explain查看SQL語句的分析結(jié)果，再針對(duì)結(jié)果去做相應(yīng)的改進(jìn)。explain的東西我們下次再講。

PS：在 select 語句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息，而不是 執(zhí)行這條SQL。

mysql---索引優(yōu)化

索引就是為特定的mysql字段進(jìn)行一些特定的算法排序，比如二叉樹的算法和哈希算法，哈希算法是通過建立特征值，然后根據(jù)特征值來快速查找。

1.普通索引:(index)最基本的索引，沒有任何限制? 目的:加快數(shù)據(jù)的查詢速度

2.唯一索引:(unique)? 與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。

3.主鍵索引(primary key) 它 是一種特殊的唯一索引，不允許有空值。

4.復(fù)合索引:index(a,b,c)? 為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。

5.全文索引:fulltext? 僅可用于 MyISAM 表，針對(duì)較大的數(shù)據(jù)，生成全文索引很耗時(shí)耗空間。

第一類是myisam存儲(chǔ)引擎使用的叫做b-tree結(jié)構(gòu)，

第二類是innodb存儲(chǔ)引擎使用的叫做聚簇結(jié)構(gòu)（也是一種 b-tree）。 如下圖：

注意：

1.myisam不需要回行處理?

2.innodb不需要回行處理,直接可以獲取數(shù)據(jù),因?yàn)閕nnodb的儲(chǔ)存引擎是包含了數(shù)據(jù)和索引文件的,其主鍵索引包含了數(shù)據(jù),(唯一索引及普通索是沒有直接包含數(shù)據(jù)的)

1、索引列不能參與計(jì)算

有索引列參與計(jì)算的查詢條件對(duì)索引不友好（甚至無法使用索引），如from_unixtime(create_time) = '2014-05-29'。

原因很簡單，如何在節(jié)點(diǎn)中查找到對(duì)應(yīng)key？如果線性掃描，則每次都需要重新計(jì)算，成本太高；如果二分查找，則需要針對(duì)from_unixtime方法確定大小關(guān)系。

因此，索引列不能參與計(jì)算。上述from_unixtime(create_time) = '2014-05-29'語句應(yīng)該寫成create_time = unix_timestamp('2014-05-29')。

2、最左前綴匹配

如有索引(a, b, c, d)，查詢條件a = 1 and b = 2 and c 3 and d = 4，則會(huì)在每個(gè)節(jié)點(diǎn)依次命中a、b、c，無法命中d。也就是最左前綴匹配原則。

3、冗余和重復(fù)索引

冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應(yīng)當(dāng)盡量避免這種索引，發(fā)現(xiàn)后立即刪除。比如有一個(gè)索引(A,B)，再創(chuàng)建索引(A)就是冗余索引。冗余索引經(jīng)常發(fā)生在為表添加新索引時(shí)，比如有人新建了索引(A,B)，但這個(gè)索引不是擴(kuò)展已有的索引(A)

4、避免多個(gè)范圍條件

? ? select user.* from user where login_time '2017-04-01' and age between 18 and 30;

比如想查詢某個(gè)時(shí)間段內(nèi)登錄過的用戶：它有兩個(gè)范圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時(shí)使用它們 .

5、覆蓋索引 (能擴(kuò)展就不新建)

如果一個(gè)索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因?yàn)椴樵冎恍枰獟呙杷饕龝?huì)帶來許多好處：

1.索引條目遠(yuǎn)小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪問量2.索引是有按照列值順序存儲(chǔ)的，對(duì)于I/O密集型的范圍查詢要比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO要少的多

6、選擇區(qū)分度高的列作索引

如，用性別作索引，那么索引僅能將1000w行數(shù)據(jù)劃分為兩部分（如500w男，500w女），索引幾乎無效。

區(qū)分度的公式是count(distinct ) / count(*)，表示字段不重復(fù)的比例，比例越大區(qū)分度越好。唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前的區(qū)分度趨近于0。

7、刪除長期未使用的索引

場景一(覆蓋索引 5)

索引應(yīng)該建在選擇性高的字段上（鍵值唯一的記錄數(shù)/總記錄條數(shù)），選擇性越高索引的效果越好、價(jià)值越大，唯一索引的選擇性最高；

組合索引中字段的順序，選擇性越高的字段排在最前面；

where條件中包含兩個(gè)選擇性高的字段時(shí)，可以考慮分別創(chuàng)建索引，引擎會(huì)同時(shí)使用兩個(gè)索引（在OR條件下，應(yīng)該說必須分開建索引）；

不要重復(fù)創(chuàng)建彼此有包含關(guān)系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

組合索引的字段不要過多，如果超過4個(gè)字段，一般需要考慮拆分成多個(gè)單列索引或更為簡單的組合索引；

不要濫用索引。因?yàn)檫^多的索引不僅僅會(huì)增加物理存儲(chǔ)的開銷，對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開銷，而且會(huì)增加優(yōu)化器在選擇索引時(shí)的計(jì)算代價(jià)。

因此太多的索引與不充分、不正確的索引對(duì)性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。

mysql如何創(chuàng)建索引覆蓋

你的意思是通過建立足夠多的索引，而不必去讀行數(shù)據(jù)嗎？ ?那就要把你的查詢where 的每一個(gè)字段都要添加一個(gè)索引，是單獨(dú)的字段，不要組合的字段索引。

組合索引是兩個(gè)及以上的字段組合到一起，這樣的話，只有where ?前兩個(gè)判斷都到用到這個(gè)組合字段才有用，其他情況是用不到索引的。比如索引 a,b ?只有where ?a=xx and b=xx的時(shí)候有用。

你的意思可能就是 ?where ? a=xx ?AND b=xx AND c=XX, ?那么，要把這三個(gè)字段都要添加索引才行

索引也是需要空間的，建議最常用的查詢字段建立索引就行了

淺聊 MySQL索引覆蓋

盡量使用覆蓋索引，減少select *。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并 且需要返回的列，在該索引中已經(jīng)全部能夠找到 。

現(xiàn)在有一張用戶表tb_user;

索引情況：

接下來，我們來看一組SQL的執(zhí)行計(jì)劃，看看執(zhí)行計(jì)劃的差別，然后再來具體做一個(gè)解析。

Using where; Using Index：查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需 要回表查詢數(shù)據(jù)

Using index condition：查找使用了索引，但是需要回表查詢數(shù)據(jù)

因?yàn)椋趖b_user表中有一個(gè)聯(lián)合索引 idx_user_pro_age_sta，該索引關(guān)聯(lián)了三個(gè)字段 profession、age、status，而這個(gè)索引也是一個(gè)二級(jí)索引，所以葉子節(jié)點(diǎn)下面掛的是這一行的主 鍵id。 所以當(dāng)我們查詢返回的數(shù)據(jù)在 id、profession、age、status 之中，則直接走二級(jí)索引 直接返回?cái)?shù)據(jù)了。 如果超出這個(gè)范圍，就需要拿到主鍵id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個(gè)過程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會(huì)造成回表 查詢（除非是根據(jù)主鍵查詢，此時(shí)只會(huì)掃描聚集索引）。

為了大家更清楚的理解，什么是覆蓋索引，什么是回表查詢，我們一起再來看下面的這組SQL的執(zhí)行過 程。

id是主鍵，是一個(gè)聚集索引。 name字段建立了普通索引，是一個(gè)二級(jí)索引（輔助索引）。

B. 執(zhí)行SQL : select * from tb_user where id = 2;

根據(jù)id查詢，直接走聚集索引查詢，一次索引掃描，直接返回?cái)?shù)據(jù)，性能高。

C. 執(zhí)行SQL：selet id,name from tb_user where name = 'Arm';

雖然是根據(jù)name字段查詢，查詢二級(jí)索引，但是由于查詢返回在字段為 id，name，在name的二級(jí)索 引中，這兩個(gè)值都是可以直接獲取到的，因?yàn)楦采w索引，所以不需要回表查詢，性能高。

D. 執(zhí)行SQL：selet id,name,gender from tb_user where name = 'Arm';

由于在name的二級(jí)索引中，不包含gender，所以，需要兩次索引掃描，也就是需要回表查詢，性能相 對(duì)較差一點(diǎn)。

文章題目：mysql覆蓋索引怎么建,mysql覆蓋索引詳解
分享路徑：http://chinadenli.net/article44/dsggiee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供營銷型網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站改版、網(wǎng)站維護(hù)、用戶體驗(yàn)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)