pt-duplicate-key-checker檢查資料庫的重複索引

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這個組合索引呢？pt-因為現在的索引設計儘可能的設計成組合索引，而不要單獨的列進行索引，所以pt-duplicate-key-checker在判斷的時候儘可能的讓組合索引保持下來。下面還記錄了重複索引佔用了多少大小，重複索引的個數以及總共的索引數目。

    pt-duplicate-key-checker中還有很多其它的選項與引數，可以透過/usr/local/bin/pt-duplicate-key-checker --help檢視