pt-duplicate-key-checker使用

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: 0
mysql> create index idx2 on new_orders(no_o_id);
Query OK, 0 rows affected, 1 warning (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> create index idx3 on new_orders(no_o_id);
Query OK, 0 rows affected, 1 warning (0.87 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> 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: 0
mysql> 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: 0
mysql> 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是不一样的所以说两者不同，也是满足了最左匹配的原则。