mysql允許在相同列上創(chuàng)建多個(gè)索引,無論是有意還是無意,mysql需要單獨(dú)維護(hù)重復(fù)的索引,并且優(yōu)化器在優(yōu)化查詢的時(shí)候也需要逐個(gè)地進(jìn)行考慮,這會(huì)影響性能。
重復(fù)索引是指的在相同的列上按照相同的順序創(chuàng)建的相同類型的索引,應(yīng)該避免這樣創(chuàng)建重復(fù)索引,發(fā)現(xiàn)以后也應(yīng)該立即刪除。但,在相同的列上創(chuàng)建不同類型的索引來滿足不同的查詢需求是可以的。
CREATE TABLE test(
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID),
) ENGINE=InnoDB;
這段SQL創(chuàng)建了3個(gè)重復(fù)索引。通常并沒有理由這么做。
冗余索引和重復(fù)索引有一些不同,如果創(chuàng)建了索引(a,b),再創(chuàng)建索引(a)就是冗余索引,因?yàn)檫@只是前面一個(gè)索引的前綴索引,因此(a,b)也可以當(dāng)作(a)來使用,但是(b,a)就不是冗余索引,索引(b)也不是,因?yàn)閎不是索引(a,b)的最左前綴列,另外,其他不同類型的索引在相同列上創(chuàng)建(如哈希索引和全文索引)不會(huì)是B-Tree索引的冗余索引,而無論覆蓋的索引列是什么。
冗余索引通常發(fā)生再為表添加新索引的時(shí)候。例如,有人可能會(huì)增加一個(gè)新的索引(A,B)而不是擴(kuò)展以后的索引(A)。還有一種情況是將一個(gè)索引擴(kuò)展為(A,ID),其中ID是主鍵,對(duì)于InnoDB來說主鍵已經(jīng)包含在二級(jí)索引中了,所以這也是冗余的。
大多數(shù)情況下都不需要冗余索引,應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引,但也有時(shí)候處于性能方面的考慮需要冗余索引,因?yàn)閿U(kuò)展已有的索引會(huì)導(dǎo)致其變得太大,從而影響其他使用該索引的查詢性能。如:如果在整數(shù)列上有一個(gè)索引,現(xiàn)在需要額外增加一個(gè)很長(zhǎng)的varchar列來擴(kuò)展該索引,那么性可能會(huì)急劇下降,特別是有查詢把這個(gè)索引當(dāng)作覆蓋索引,或者這是myisam表并且有很多范圍查詢的時(shí)候(由于myisam的前綴壓縮)
比如,有一張userinfo表。這個(gè)表有1000000條數(shù)據(jù),對(duì)每個(gè)state_id值大概有20000條記錄。在state_id有一個(gè)索引,那么下面的SQL我們稱之為Q1
SELECT count(*) FROM userinfo WHERE state_id=5; --Q1
改查詢的執(zhí)行速度大概是每秒115次(QPS)
還有一個(gè)SQL,我們稱之為Q2
SELECT state_id,city,address FROM userinfo WHERE state_id=5; --Q2
這個(gè)查詢的QPS是10,提升該索引性能最簡(jiǎn)單的辦法就是狂戰(zhàn)索引為(state_id,city,address),讓索引能覆蓋查詢:
ALERT TABLE userinfo ADD KEY state_id_2(state_id,city,address);
注:state_id已經(jīng)有索引了,根據(jù)前面的概念,這是一個(gè)冗余索引而不是重復(fù)索引)
怎么找出冗余索引和重復(fù)索引呢?
1.可以使用Shlomi Noach的common_schema中的一些試圖來定位,common_schema是一系列可以安裝到服務(wù)器上的常用的存儲(chǔ)和試圖。
2.可以使用Percona Toolkit中的pt_duplicate-key-checker,該工具通過分析表結(jié)構(gòu)來找出冗余和重復(fù)的索引。
總結(jié)
以上就是本文關(guān)于詳解mysql中的冗余和重復(fù)索引的全部?jī)?nèi)容,希望對(duì)大家有所幫助。感興趣的朋友可以參閱:幾個(gè)比較重要的MySQL變量、簡(jiǎn)述Redis和MySQL的區(qū)別、MYSQL子查詢和嵌套查詢優(yōu)化實(shí)例解析等,如有不足之處,歡迎留言指出,小編會(huì)及時(shí)回復(fù)大家并進(jìn)行修改,感謝朋友們對(duì)本站的支持!
您可能感興趣的文章:- MySQL查詢?nèi)哂嗨饕臀词褂眠^的索引操作
- mysql重復(fù)索引與冗余索引實(shí)例分析
- MySQL中冗余和重復(fù)索引的區(qū)別說明