冗餘和重複索引

冗餘和重複索引的概念：

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

重複索引：是指在相同的列上按照相同的順序建立的相同型別的索引。應該避免這樣建立重複索引，發現後也應該立即移除。

eg：有時會在不經意間建立了重複索引

CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY,
  a  INT NOT NULL,
  INDEX(ID)
)ENGINE=InnoDB;

一個經驗不足的使用者可能是想建立一個主鍵，然後再加上索引以供查詢使用。事實上主鍵也就是索引了。所以完全沒必要再新增INDEX(ID)了。

冗餘索引和重複索引有一些不同，如果建立了索引(A,B)，再建立索引(A)就是冗餘索引，因為這只是前一個索引的字首索引。因此索引(A,B)也可以當索引(A)來使用(這種冗餘只是對B-Tree索引來說)。冗餘索引通常發生在為表新增新索引的時候。例如，有人可能會增加一個新的索引(A,B)而不是擴充套件已有的索引(A)。還有一種情況是將一個索引擴充套件為(A,ID)，其中ID是主鍵，對於InnoDB來說主鍵列已經包含在二級索引中了，索引也是冗餘的。

大多數的情況下都不需要冗餘索引，應該儘量擴充套件已有的索引而不是建立新索引。但也有時候出於性能方面的考慮需要冗餘索引，因為擴充套件已有的索引會導致其變得太大，從而影響其它使用該索引的查詢的效能。

eg：如果在整數列上有一個索引，現在需要額外增加一個很長的VARCHAR列來擴充套件該索引，那效能可能會急劇下降。特別是有查詢把這個索引當作覆蓋索引，或者這是MyISAM表並且有很多範圍查詢的時候。

另外注意到：表中的索引越多插入速度會越慢。一般來說，增加新索引將會導致INSERT，UPDATE，DELETE等操作的速度變慢，特別是當新增索引後導致達到了記憶體瓶頸的時候。

解決冗餘索引和重複索引的方法：

解決冗餘索引和重複索引的方法很簡單，刪除這些索引就可以，但首先要做的是找出這樣的索引。

方法:

1:可以通過寫一些複雜的訪問INFORMATION_SCHEMA表的查詢來找。

2:通過common_schema中的一些檢視來定位

3:通過Percona Toolkit中的pt-duplicate-key-checker工具

eg: pt-duplicate-key-checker工具的使用

首先pt-duplicate-key-checker工具的安裝，參考相關官方手冊。

使用語法：

pt-duplicate-key-checker[OPTIONS][DSN]

主要引數的介紹：

-u                    :指定連線資料庫的使用者名稱

-p                    :指定連線資料庫的密碼

–charset         ：指定字符集

–database       :指定要檢查的資料庫名列表

例項如下：

pt-duplicate-key-checker -udbuser -pdbpaswd --charset=gbk 
--database=dbname

執行過後將會統計出有關dbname資料庫的重複和冗餘的索引，內容如下：

# ########################################################################
# dbname.test1                                              
# ########################################################################
# vkey is a left-prefix of keydesc_index
# Key definitions:
#   KEY `vkey` (`VehicleKey`),
#   KEY `keydesc_index` (`VehicleKey`,`Description`)
# Column types:
#         `vehiclekey` char(8) not null default ``
#         `description` char(255) not null default ``
# To remove this duplicate index, execute:
ALTER TABLE `dbname`.`test1` DROP INDEX `vkey`;
# ########################################################################
# dbname.test2                                               
# ########################################################################
# vkey is a duplicate of PRIMARY
# Key definitions:
#   KEY `vkey` (`VehicleKey`),
#   PRIMARY KEY (`VehicleKey`),
# Column types:
#         `vehiclekey` varchar(8) not null default `0`
# To remove this duplicate index, execute:
ALTER TABLE `dbname`.`test2` DROP INDEX `vkey`;

它會統計出所有出現的重複，冗餘的索引，還將要執行的SQL語句也提供了，是不是很方便。

想了解其工具所有引數或其用法的請參考：pt-duplicate-key-checker