mysql中關於冗餘和重複索引

　　mysql允許在相同列上建立多個索引，無論是有意還是無意，mysql需要單獨維護重複的索引，並且優化器在優化查詢的時候也需要逐個考慮，這會影響效能。

　　重複索引是指的在相同的列上按照相同的順序建立的相同類型的索引，應該避免這樣建立重複索引，發現以後也應該立即刪除。但，在相同的列上建立不同類型的索引來滿足不同的查詢需求是可以的。

CREATE TABLE test(
  ID INT NOT NULL PRIMARY KEY,
  A INT NOT NULL,
  B INT NOT NULL,  UNIQUE(ID),  INDEX(ID),
) ENGINE=InnoDB;

　　這段SQL建立了3個重複索引。通常並沒有理由這麼做。

　　冗餘索引和重複索引有一些不同，如果創建了索引（a,b），再創建索引（a）就是冗餘索引，因為這只是前面一個索引的前綴索引，因此（a ,b）也可以當作(a)來使用，但是（b,a）就不是冗餘索引，索引(b)也不是，因為b不是索引（a,b）的最左前綴列，另外，其他不同類型的索引在相同欄位上建立（如雜湊索引和全文索引）不會是B-Tree索引的冗餘索引，而無論覆蓋的索引列是什麼。

　　冗餘索引通常發生再為表格新增索引的時候。例如，有人可能會增加一個新的索引(A,B)而不是擴展以後的索引(A)。還有一種情況是將一個索引擴展為(A,ID),其中ID是主鍵，對於InnoDB來說主鍵已經包含在二級索引中了，所以這也是冗餘的。

　　大多數情況下都不需要冗餘索引，應該盡量擴展已有的索引而不是創建新索引，但也有時候處於性能方面的考慮需要冗餘索引，因為擴展已有的索引會導致其變得太大，從而影響其他使用該索引的查詢效能。如：如果在整數列上有一個索引，現在需要額外增加一個很長的varchar列來擴展該索引，那麼性可能會急劇下降，特別是有查詢把這個索引當作覆蓋索引，或者這是myisam表而且有很多範圍查詢的時候(由於myisam的前綴壓縮)

　　比如，有一張userinfo表。這個表有1000000筆數據，對每個state_id值大概有20000筆記錄。在state_id有一個索引，那麼下面的SQL我們稱為Q1

SELECT count(*) FROM userinfo WHERE state_id=5; --Q1

　　改查詢的執行速度大概是每秒115次(QPS)

　　還有一個SQL，我們稱為Q2

SELECT state_id,city,address FROM userinfo WHERE state_id=5; --Q2

　　這個查詢的QPS是10，提升該索引性能最簡單的辦法就是狂戰索引為(state_id, city,address)，讓索引可以覆寫查詢：

ALERT TABLE userinfo ADD KEY state_id_2(state_id,city,address);

　　（註：state_id已經有索引了，根據前面的概念，這是一個冗餘索引而不是重複索引）

怎麼找出冗餘索引和重複索引呢？

1.可以使用Shlomi Noach的common_schema中的一些試圖來定位，common_schema是一系列可以安裝到伺服器上的常用的儲存和試圖。

2.可以使用Percona Toolkit中的pt_duplicate-key-checker,該工具透過分析表結構來找出冗餘和重複的索引。

以上是mysql中關於冗餘和重複索引的詳細內容。更多資訊請關注PHP中文網其他相關文章！