Percona-Toolkit系列之pt-duplicate-key-checker冗余索引/外键检测利器

1.pt-duplicate-key-checker

pt-duplicate-key-checker是一个用perl语言写的冗余索引和冗余外键检测的工具，这可是个好东西啊，一次性将整个库都可以检查一遍。某次听一朋友讲他们生产环境一张表20个列，有15个索引，对该表做更新操作巨慢，搞得他们束手无策,建议他们删掉一些不用的索引后，秒变好了。建索引这个事情一般来讲都是开发DBA决定的，难免也有犯糊涂的时候。

1.1冗余索引列

如某表有三列c1,c2,c3,添加了如下索引，这就算冗余索引

CREATE INDEX index_name1 ON table_name(c2,c3,c4);
CREATE INDEX index_name2 ON table_name(c2,c3,c4);

假如列的顺序改变了就不不算冗余了，索引列的顺序不同，这是为了满足不同查询场景:

CREATE INDEX index_name1 ON table_name(c2,c3,c4);
CREATE INDEX index_name2 ON table_name(c3,c2,c4);

又比如如下两个索引:

CREATE INDEX index_name1 ON table_name(c2,c3,c4);
CREATE INDEX index_name2 ON table_name(c2,c3);

按照最左原则，能使用index_name2的索引，肯定能使用index_name1，那么维护两个索引，就没有必要，具体保留哪一个索引，就看具体需求了。

1.2.还有一种情况就是二级组合索引的情况，请看下面例子:

PRIMARY KEY (‘a‘)
KEY ‘b‘ (‘b‘,‘a‘)

我们有如下查询

SELECT … WHERE b=1 ORDER BY a;

本意上我们想利用符合索引来完成排序，没错这个想法非常好，这个地方我们就要研究研究innodb二级索引的结构了。

这时我们就不要忘记了，innodb的二级索引项本来就指向主键索引，如下图所示，索引key b(b,c)就是冗余索引了，只需定义Key(b)就可以了，其实问题也不大，包含了主键，无非多占点存储空间，更新操作时多费点计算罢了。

1.3冗余外检

还有就是外键冗余，同一个外键约束定了了两次，相信这个概率还是比较小的。

2.pt-duplicate-key-checker使用方法

首先我们创建一些测表:

create database if not exists  duplicate_key_test;
use duplicate_key_test;
create table duplicate_key_check(
c1 bigint ,
c2 varchar(32),
c3 varchar(32),
c4 varchar(32),
c5 varchar(32),
c6 varchar(32),
c7 varchar(32),
c8 varchar(32),
c9 varchar(32),
primary key(c1),
index index_a(c2,c3,c4),
index index_b(c2,c3,c4),
index index_c(c2,c3)
)engine=innodb charset=utf8;


create table parent(
c1 bigint ,
c2 varchar(32),
c3 varchar(32),
primary key(c1),
index index_a(c2)
)engine=innodb charset=utf8;

create table child(
c1 bigint ,
c2 varchar(32),
c3 varchar(32),
primary key(c1),
foreign key (c2) references parent(c2) on delete cascade on update cascade,
foreign key (c2) references parent(c2) on delete cascade on update cascade
)engine=innodb charset=utf8;

很显然，duplicate_key_check表中的index_b和index_c都是冗余索引，parent表和child表的外检约束也重复定义了。好了我们使用 pt-duplicate-key-checker来验证一把。

首先我们配置一个配置文件:

[mysql@hpc02 ~]$ cat duplicate_key.cnf 
host=192.168.56.103
user=root
password=111111
port=3306
databases=duplicate_key_test/*需要检查的数据库名，不指定就是整个数据库实例上的所有database都会检查*/
clustered                   /*将二级索引包含主键的情况也打印出来*/
charset=utf8
all-structs                 /*同一个的不同种类索引也打印出来，比如某列c1,有fulltext索引个BTREE索引，这样的情况也会检查*/

下面就一谈究竟:

[mysql@hpc02 ~]$ pt-duplicate-key-checker --config duplicate_key.cnf 
# ########################################################################
# duplicate_key_test.child                                                
# ########################################################################

# FOREIGN KEY child_ibfk_2 (`c2`) REFERENCES `duplicate_key_test`.`parent` (`c2`) is a duplicate of FOREIGN KEY child_ibfk_1 (`c2`) REFERENCES `duplicate_key_test`.`parent` (`c2`)
# Key definitions:
#   CONSTRAINT `child_ibfk_2` FOREIGN KEY (`c2`) REFERENCES `parent` (`c2`)
#   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `parent` (`c2`)
# Column types:
#         `c2` varchar(32) default null
# To remove this duplicate foreign key, execute:
ALTER TABLE `duplicate_key_test`.`child` DROP FOREIGN KEY `child_ibfk_2`;

# MySQL uses the c2 index for this foreign key constraint

# ########################################################################
# duplicate_key_test.duplicate_key_check                                  
# ########################################################################

# index_b is a duplicate of index_a
# Key definitions:
#   KEY `index_b` (`c2`,`c3`,`c4`),
#   KEY `index_a` (`c2`,`c3`,`c4`),
# Column types:
#         `c2` varchar(32) default null
#         `c3` varchar(32) default null
#         `c4` varchar(32) default null
# To remove this duplicate index, execute:
ALTER TABLE `duplicate_key_test`.`duplicate_key_check` DROP INDEX `index_b`;

# index_c is a left-prefix of index_a
# Key definitions:
#   KEY `index_c` (`c2`,`c3`)
#   KEY `index_a` (`c2`,`c3`,`c4`),
# Column types:
#         `c2` varchar(32) default null
#         `c3` varchar(32) default null
#         `c4` varchar(32) default null
# To remove this duplicate index, execute:
ALTER TABLE `duplicate_key_test`.`duplicate_key_check` DROP INDEX `index_c`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   495
# Total Duplicate Indexes  3
# Total Indexes            10

打印出非常详尽的信息，还给出如何修复冗余索引的SQL语句。是不是超级赞！