Percona-Tookit工具包之pt-duplicate-key-checker

 

Preface

 

    I suppose that we have a requirement of checking out how many duplicated indexes on a certain table or even on several specific databases.what will you do then?

 

Introduce

 

    pt-duplicate-key-checker is the very tool which can help us to check out the replicated keys and foreign keys in our databases.

 

Procedure

 

Usage

1 pt-duplicate-key-checker [OPTIONS] [DSN]

 

Common parameter

1 --clustered //Treats the columns of pripary key to be redundent like secondary key does.(default "yes")
2 --key-types //Specify the type of index to check.(default "fk" means both of f&k,f=foreign keys,k=ordinary keys)
3 --database //Specify the database you want to check.
4 --tables //Specify the tables you want to check.
5 --ignore-databases //Specify the ignoring database.
6 --ignore-tables //Specify the ignoring tables.
7 --ignore-order //Treats the key(a,b) to be duplicated when it is compared with the key(b,a)
8 --sql //Generate dropping index sql statment for those duplicated indexes.

 

Example

 

Check the indexes on a "sbtest4".

 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>show create table sbtest4\G
13 *************************** 1. row ***************************
14        Table: sbtest4
15 Create Table: CREATE TABLE `sbtest4` (
16   `id` int(11) NOT NULL AUTO_INCREMENT,
17   `k` int(11) NOT NULL DEFAULT '0',
18   `c` char(120) NOT NULL DEFAULT '',
19   `pad` char(60) NOT NULL DEFAULT '',
20   PRIMARY KEY (`id`),
21   KEY `k_4` (`k`)
22 ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8
23 1 row in set (0.00 sec)
24 
25 (zlm@192.168.1.101 3306)[sysbench]>show index from sbtest4;
26 +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
27 | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
28 +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
29 | sbtest4 |          0 | PRIMARY  |            1 | id          | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
30 | sbtest4 |          1 | k_4      |            1 | k           | A         |        1752 |     NULL | NULL   |      | BTREE      |         |               |
31 +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
32 2 rows in set (0.00 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>

 

Add an index on column id and k in table "sbtest4".

 1 (zlm@192.168.1.101 3306)[sysbench]>alter table sbtest4 add index idx_id_k1(id,k);
 2 Query OK, 0 rows affected (0.11 sec)
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 (zlm@192.168.1.101 3306)[sysbench]>show index from sbtest4;
 6 +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 7 | Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 8 +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 9 | sbtest4 |          0 | PRIMARY   |            1 | id          | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
10 | sbtest4 |          1 | k_4       |            1 | k           | A         |        1752 |     NULL | NULL   |      | BTREE      |         |               |
11 | sbtest4 |          1 | idx_id_k1 |            1 | id          | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
12 | sbtest4 |          1 | idx_id_k1 |            2 | k           | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
13 +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 

Check whether there's a duplicated index in table "sbtest4".

 1 [root@zlm2 03:31:52 ~]
 2 #pt-duplicate-key-checker -d=sysbench -t=sbtest4
 3 # ########################################################################
 4 # Summary of indexes                                                      
 5 # ########################################################################
 6 
 7 # Total Indexes  0 //No duplicated indexes were found.
 8 
 9 [root@zlm2 03:31:54 ~]
10 #pt-duplicate-key-checker -dsysbench -tsbtest4
11 # ########################################################################
12 # Summary of indexes                                                      
13 # ########################################################################
14 
15 # Total Indexes  3

 

Check the conclusion with option "--no-clustered".

 1 [root@zlm2 03:32:24 ~]
 2 #pt-duplicate-key-checker -dsysbench -tsbtest4 -h192.168.1.101 -P3306 -uzlm -pzlmzlm --no-clustered
 3 # ########################################################################
 4 # sysbench.sbtest4                                                        
 5 # ########################################################################
 6 
 7 # k_4 is a left-prefix of idx_id_k2
 8 # Key definitions:
 9 #   KEY `k_4` (`k`),
10 #   KEY `idx_id_k2` (`k`,`id`) //The index "idx_id_k2" contains the column in index "k_4" and in the same order.Therefore,"k_4" is indicated redundant.
11 # Column types:
12 #      `k` int(11) not null default '0'
13 #      `id` int(11) not null auto_increment
14 # To remove this duplicate index, execute:
15 ALTER TABLE `sysbench`.`sbtest4` DROP INDEX `k_4`;
16 
17 # ########################################################################
18 # Summary of indexes                                                      
19 # ########################################################################
20 
21 # Size Duplicate Indexes   38720
22 # Total Duplicate Indexes  1
23 # Total Indexes            4

 

Check the conclusion with option "--clustered".(Its default is "true" what means we can also omit it)

 1 [root@zlm2 03:33:11 ~]
 2 #pt-duplicate-key-checker -dsysbench -tsbtest4 -h192.168.1.101 -P3306 -uzlm -pzlmzlm
 3 # ########################################################################
 4 # sysbench.sbtest4                                                        
 5 # ########################################################################
 6 
 7 # k_4 is a left-prefix of idx_id_k2
 8 # Key definitions:
 9 #   KEY `k_4` (`k`),
10 #   KEY `idx_id_k2` (`k`,`id`)
11 # Column types:
12 #      `k` int(11) not null default '0'
13 #      `id` int(11) not null auto_increment
14 # To remove this duplicate index, execute:
15 ALTER TABLE `sysbench`.`sbtest4` DROP INDEX `k_4`;
16 
17 # Key idx_id_k2 ends with a prefix of the clustered index
18 # Key definitions:
19 #   KEY `idx_id_k2` (`k`,`id`)
20 #   PRIMARY KEY (`id`),
21 # Column types:
22 #      `k` int(11) not null default '0'
23 #      `id` int(11) not null auto_increment
24 # To shorten this duplicate clustered index, execute:
25 ALTER TABLE `sysbench`.`sbtest4` DROP INDEX `idx_id_k2`, ADD INDEX `idx_id_k2` (`k`); //The tool suggests to change the union index to a single column key.
26 
27 # ########################################################################
28 # Summary of indexes                                                      
29 # ########################################################################
30 
31 # Size Duplicate Indexes   116160
32 # Total Duplicate Indexes  2
33 # Total Indexes            4

 

Add another index on column id and k in table "sbtest4".(reverse the order this time)

 1 (zlm@192.168.1.101 3306)[sysbench]>alter table sbtest4 add index idx_id_k2(k,id);
 2 Query OK, 0 rows affected (0.04 sec)
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 (zlm@192.168.1.101 3306)[sysbench]>show index from sbtest4;
 6 +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 7 | Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 8 +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 9 | sbtest4 |          0 | PRIMARY   |            1 | id          | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
10 | sbtest4 |          1 | k_4       |            1 | k           | A         |        1752 |     NULL | NULL   |      | BTREE      |         |               |
11 | sbtest4 |          1 | idx_id_k1 |            1 | id          | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
12 | sbtest4 |          1 | idx_id_k1 |            2 | k           | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
13 | sbtest4 |          1 | idx_id_k2 |            1 | k           | A         |        1752 |     NULL | NULL   |      | BTREE      |         |               |
14 | sbtest4 |          1 | idx_id_k2 |            2 | id          | A         |        9680 |     NULL | NULL   |      | BTREE      |         |               |
15 +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
16 6 rows in set (0.00 sec)

 

Check the conclusion with default option again.

 1 [root@zlm2 03:35:57 ~]
 2 #pt-duplicate-key-checker -dsysbench -tsbtest4 -h192.168.1.101 -P3306 -uzlm -pzlmzlm
 3 # ########################################################################
 4 # sysbench.sbtest4                                                        
 5 # ########################################################################
 6 
 7 # k_4 is a left-prefix of idx_id_k2
 8 # Key definitions:
 9 #   KEY `k_4` (`k`),
10 #   KEY `idx_id_k2` (`k`,`id`)
11 # Column types:
12 #      `k` int(11) not null default '0'
13 #      `id` int(11) not null auto_increment
14 # To remove this duplicate index, execute:
15 ALTER TABLE `sysbench`.`sbtest4` DROP INDEX `k_4`;
16 
17 # Key idx_id_k2 ends with a prefix of the clustered index
18 # Key definitions:
19 #   KEY `idx_id_k2` (`k`,`id`)
20 #   PRIMARY KEY (`id`),
21 # Column types:
22 #      `k` int(11) not null default '0'
23 #      `id` int(11) not null auto_increment
24 # To shorten this duplicate clustered index, execute:
25 ALTER TABLE `sysbench`.`sbtest4` DROP INDEX `idx_id_k2`, ADD INDEX `idx_id_k2` (`k`);
26 
27 # ########################################################################
28 # Summary of indexes                                                      
29 # ########################################################################
30 
31 # Size Duplicate Indexes   116160
32 # Total Duplicate Indexes  2
33 # Total Indexes            4

 

 Check the conclusion another time with option "--ignore-order".

 1 [root@zlm2 03:43:16 ~]
 2 #pt-duplicate-key-checker --databases=sysbench --tables=sbtest4 --ignore-order -h192.168.1.101 -P3306 -uzlm -pzlmzlm
 3 # ########################################################################
 4 # sysbench.sbtest4                                                        
 5 # ########################################################################
 6 
 7 # idx_id_k2 is a duplicate of idx_id_k1
 8 # Key definitions:
 9 #   KEY `idx_id_k2` (`k`,`id`)
10 #   KEY `idx_id_k1` (`id`,`k`),
11 # Column types:
12 #      `k` int(11) not null default '0'
13 #      `id` int(11) not null auto_increment
14 # To remove this duplicate index, execute:
15 ALTER TABLE `sysbench`.`sbtest4` DROP INDEX `idx_id_k2`; //Why no dropping "idx_id_k1"?Beacause "idx_id_k2" is also a duplicate of "k_4" as we can see above.
16 
17 # ########################################################################
18 # Summary of indexes                                                      
19 # ########################################################################
20 
21 # Size Duplicate Indexes   77440
22 # Total Duplicate Indexes  1
23 # Total Indexes            4

 

Summary

• Notice the difference between long option and short option,do not mix them up.
• pt-duplicate-key-checker is quit convenient to generate a summay report of rudundant indexes in specific databases.
• We can execute it with script in a certain interval of time and collect the information in a flat logfile for future analysis.
• It also provides us the pertinent SQL statement to adjust the indexes in target tables.
• Further more,we'd better compare the result of the tool with the data in sys.schema_redundant_indexes(5.7 or above).

 

转载于:https://www.cnblogs.com/aaron8219/p/9357509.html