当前位置：首页 > news >正文

pt-duplicate-key-checker检查数据库的重复索引

news 来源：原创 2024/5/3 23:44:29

pt-duplicate-key-checker这款工具也是percona-toolkit中一款非常适用的工具，它可以帮助你检测表中重复的索引或者主键。我们知道索引会更查询带来好处，但是过量的索引反而可能会使数据库的性能降低，这款工具可以帮助我们找到重复的索引并且还会给你删除重复索引的建议语句，非常好用。

例子：

mysql> create table curs (id int ,name varchar(30),age char(2));
Query OK, 0 rows affected (0.03 sec)

mysql> create index name on curs(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index age on curs(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index name_age on curs(name,age);
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index age_name on curs(age,name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index age_1 on curs(age);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> create index name_1 on curs(name);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show indexes from curs;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| curs | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | name_age | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | name_age | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | age_name | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | age_name | 2 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | age_1 | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | name_1 | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

使用pt-duplicate-key-checker工具检查表中使用重复的索引

[root@zhuku ~]# pt-duplicate-key-checker -S /tmp/mysql.sock -uroot -p123456 --databases=test --tables=curs >q.txt

# ########################################################################
# test.curs
# ########################################################################

# name is a left-prefix of name_age
# Key definitions:
# KEY `name` (`name`),
# KEY `name_age` (`name`,`age`),
# Column types:
# `name` varchar(30) default null
# `age` char(2) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`curs` DROP INDEX `name`;

# name_1 is a left-prefix of name_age
# Key definitions:
# KEY `name_1` (`name`)
# KEY `name_age` (`name`,`age`),
# Column types:
# `name` varchar(30) default null
# `age` char(2) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`curs` DROP INDEX `name_1`;

# age is a left-prefix of age_name
# Key definitions:
# KEY `age` (`age`),
# KEY `age_name` (`age`,`name`),
# Column types:
# `age` char(2) default null
# `name` varchar(30) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`curs` DROP INDEX `age`;

# age_1 is a left-prefix of age_name
# Key definitions:
# KEY `age_1` (`age`),
# KEY `age_name` (`age`,`name`),
# Column types:
# `age` char(2) default null
# `name` varchar(30) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`curs` DROP INDEX `age_1`;

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

# Size Duplicate Indexes 200
# Total Duplicate Indexes 4
# Total Indexes 6

执行建议删除的索引

mysql> source q.txt

mysql> show indexes from curs;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| curs | 1 | name_age | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | name_age | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | age_name | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| curs | 1 | age_name | 2 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

这里面有一处很有意思的地方是，为什么删除name，name_age与name_1这3个重复索引的时候没有删除name_age这个组合索引呢？<a style="color: rgb(0, 0, 0); font-family:; text-decoration-line: none;" href="http://blog.csdn.net/z1547840014/article/details/10198237" target="_blank" font-size:20px;line-height:30px;"="">pt-因为现在的索引设计尽可能的设计成组合索引，而不要单独的列进行索引，所以pt-duplicate-key-checker在判断的时候尽可能的让组合索引保持下来。下面还记录了重复索引占用了多少大小，重复索引的个数以及总共的索引数目。