【Percona-Toolkit】系列之pt-duplicate-key-checker索引check神器

工具简介

pt-duplicate-key-checker
Usage：
1.检查数据库表中重复/多余索引或者外键

常用参数

--key-types=s 		校验的类型，f=foreign keys, k=keys or fk=前面两种都check，默认两种都check
--ignore-order 		忽略索引的顺序， KEY(a,b) 不同于 KEY(b,a)
--all-structs 		btree hash索引都进行比较
--verboses			打印所有索引

使用示例

使用表结构如下：

>show  create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` varchar(128) DEFAULT NULL,
  `num` int DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY `k1` (`content`,`num`),
  KEY `k2` (`num`,`content`),
  KEY `k3` (`num`),
  KEY `k4` (`content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.检查表中重复索引

$ pt-duplicate-key-checker h=localhost,u=root,p=pass,S=/data/mysql/mysql.sock --all-structs --key-types=k
# ########################################################################
# test.t2
# ########################################################################

# k3 is a left-prefix of k2
# Key definitions:
#   KEY `k3` (`num`),
#   KEY `k2` (`num`,`content`),
# Column types:
#	  `num` int default null
#	  `content` varchar(128) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t2` DROP INDEX `k3`;

# k4 is a left-prefix of k1
# Key definitions:
#   KEY `k4` (`content`),
#   KEY `k1` (`content`,`num`),
# Column types:
#	  `content` varchar(128) default null
#	  `num` int default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t2` DROP INDEX `k4`;

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

# Size Duplicate Indexes   520
# Total Duplicate Indexes  2
# Total Indexes            49

可以看到，总共扫描了49个索引，然后找出来了多余的`K3`和`K4`索引，还给我们提供了sql语句。

2.忽略顺序

众所周知(a,b)与（b,a）是不同的索引，但是有时候开发会建立错索引，所以像这种索引我们也要去检测
pt-duplicate-key-checker h=localhost,u=root,p=pass,S=/data/mysql/mysql.sock --all-structs --key-types=k --ignore-order
# ########################################################################
# test.t2
# ########################################################################

# k2 is a duplicate of k1
# Key definitions:
#   KEY `k2` (`num`,`content`),
#   KEY `k1` (`content`,`num`),
# Column types:
#	  `num` int default null
#	  `content` varchar(128) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t2` DROP INDEX `k2`;

# k4 is a left-prefix of k1
# Key definitions:
#   KEY `k4` (`content`),
#   KEY `k1` (`content`,`num`),
# Column types:
#	  `content` varchar(128) default null
#	  `num` int default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t2` DROP INDEX `k4`;

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

# Size Duplicate Indexes   1039
# Total Duplicate Indexes  3
# Total Indexes            49

总结

对于我们DBA来说，如果新接到一个业务的话，那么我们可以使用这个工具可以对数据库整体做一个扫描，查看一下哪些索引有问题，因为其实大部分我们工作来看，都是开发用错索引导致问题，对于我们DBA来说也是一个快速扫描优化的工具。