MySQL 冗餘和重複索引

                                       冗餘和重複索引

冗餘和重複索引的概念：

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

本文出自 “Focus on the database” 部落格，請務必保留此出處http://lgdvsehome.blog.51cto.com/3360656/1278740