pt-duplicate-key-checker工具可以检测表中重复的索引，对于一些业务量很大的表，而且开发不规范的情况下有用。基本用法：

看一下我们的测试表：

mysql> desc new_orders;+---------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| no_o_id | int(11) | NO | PRI | NULL | || no_d_id | tinyint(4) | NO | PRI | NULL | || no_w_id | smallint(6) | NO | PRI | NULL | |+---------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> create index idx1 on new_orders(no_o_id);Query OK, 0 rows affected (0.92 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx2 on new_orders(no_o_id);Query OK, 0 rows affected, 1 warning (0.93 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> create index idx3 on new_orders(no_o_id);Query OK, 0 rows affected, 1 warning (0.87 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> create index idx3 on new_orders(no_o_id,no_d_id);ERROR 1061 (42000): Duplicate key name 'idx3'mysql> create index idx4 on new_orders(no_o_id,no_d_id);Query OK, 0 rows affected (1.07 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx5 on new_orders(no_o_id,no_d_id,no_w_id);Query OK, 0 rows affected (1.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx6 on new_orders(no_o_id,no_d_id,no_w_id);Query OK, 0 rows affected, 1 warning (1.58 sec)Records: 0 Duplicates: 0 Warnings: 1

 

下面开始进行检测：

[root@mxqmongodb2 bin]# ./pt-duplicate-key-checker --host=172.16.16.35 --port=3306 --user=root --password=123456 --database=test --tables=new_orders;# ######################################################################### test.new_orders# ######################################################################## # idx6 is a duplicate of idx5# Key definitions:# KEY `idx6` (`no_o_id`,`no_d_id`,`no_w_id`)# KEY `idx5` (`no_o_id`,`no_d_id`,`no_w_id`),# Column types:# `no_o_id` int(11) not null# `no_d_id` tinyint(4) not null# `no_w_id` smallint(6) not null# To remove this duplicate index, execute:ALTER TABLE `test`.`new_orders` DROP INDEX `idx6`; # idx4 is a left-prefix of idx5# Key definitions:# KEY `idx4` (`no_o_id`,`no_d_id`),# KEY `idx5` (`no_o_id`,`no_d_id`,`no_w_id`),# Column types:# `no_o_id` int(11) not null# `no_d_id` tinyint(4) not null# `no_w_id` smallint(6) not null# To remove this duplicate index, execute:ALTER TABLE `test`.`new_orders` DROP INDEX `idx4`; # idx1 is a left-prefix of idx5# Key definitions:# KEY `idx1` (`no_o_id`),# KEY `idx5` (`no_o_id`,`no_d_id`,`no_w_id`),# Column types:# `no_o_id` int(11) not null# `no_d_id` tinyint(4) not null# `no_w_id` smallint(6) not null# To remove this duplicate index, execute:ALTER TABLE `test`.`new_orders` DROP INDEX `idx1`; # idx2 is a left-prefix of idx5# Key definitions:# KEY `idx2` (`no_o_id`),# KEY `idx5` (`no_o_id`,`no_d_id`,`no_w_id`),# Column types:# `no_o_id` int(11) not null# `no_d_id` tinyint(4) not null# `no_w_id` smallint(6) not null# To remove this duplicate index, execute:ALTER TABLE `test`.`new_orders` DROP INDEX `idx2`; # idx3 is a left-prefix of idx5# Key definitions:# KEY `idx3` (`no_o_id`),# KEY `idx5` (`no_o_id`,`no_d_id`,`no_w_id`),# Column types:# `no_o_id` int(11) not null# `no_d_id` tinyint(4) not null# `no_w_id` smallint(6) not null# To remove this duplicate index, execute:ALTER TABLE `test`.`new_orders` DROP INDEX `idx3`; # ######################################################################### Summary of indexes# ######################################################################## # Size Duplicate Indexes 196656# Total Duplicate Indexes 5# Total Indexes 7

 

我们看到，除了主键以外，其他的索引按说都是不成功的，但是pt-duplicate-key-checker只检查到了五个重复索引，这个重复不是我们理解的完全一样，而是包含索引。`idx5` (`no_o_id`,`no_d_id`,`no_w_id`),包含了刚才创建的1-4的索引，而且和6的索引是一样的。而主键的排序和idx5是不一样的所以说两者不同，也是满足了最左匹配的原则。