pt-duplicate-key-checker使用

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

看一下我们的测试表：

mysql> desc new_orders;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| no_o_id | int() | NO | PRI | NULL | |

| no_d_id | tinyint() | NO | PRI | NULL | |

| no_w_id | smallint() | NO | PRI | NULL | |

+---------+-------------+------+-----+---------+-------+

 rows in set (0.00 sec)

mysql> create index idx1 on new_orders(no_o_id);

Query OK,  rows affected (0.92 sec)

Records:  Duplicates:  Warnings:

mysql> create index idx2 on new_orders(no_o_id);

Query OK,  rows affected,  warning (0.93 sec)

Records:  Duplicates:  Warnings:

mysql> create index idx3 on new_orders(no_o_id);

Query OK,  rows affected,  warning (0.87 sec)

Records:  Duplicates:  Warnings:

mysql> create index idx3 on new_orders(no_o_id,no_d_id);

ERROR  (): Duplicate key name 'idx3'

mysql> create index idx4 on new_orders(no_o_id,no_d_id);

Query OK,  rows affected (1.07 sec)

Records:  Duplicates:  Warnings:

mysql> create index idx5 on new_orders(no_o_id,no_d_id,no_w_id);

Query OK,  rows affected (1.04 sec)

Records:  Duplicates:  Warnings:

mysql> create index idx6 on new_orders(no_o_id,no_d_id,no_w_id);

Query OK,  rows affected,  warning (1.58 sec)

Records:  Duplicates:  Warnings:

下面开始进行检测：

[root@mxqmongodb2 bin]# ./pt-duplicate-key-checker --host=172.16.16.35 --port= --user=root --password= --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() not null

# `no_d_id` tinyint() not null

# `no_w_id` smallint() 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() not null

# `no_d_id` tinyint() not null

# `no_w_id` smallint() 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() not null

# `no_d_id` tinyint() not null

# `no_w_id` smallint() 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() not null

# `no_d_id` tinyint() not null

# `no_w_id` smallint() 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() not null

# `no_d_id` tinyint() not null

# `no_w_id` smallint() not null

# To remove this duplicate index, execute:

ALTER TABLE `test`.`new_orders` DROP INDEX `idx3`;

# ########################################################################

# Summary of indexes

# ########################################################################

# Size Duplicate Indexes

# Total Duplicate Indexes

# Total Indexes

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

巴特西

pt-duplicate-key-checker使用

最新文章

热门文章