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 -- \　　
--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