How to use pt-duplicate-key-checker to find redundant and duplicate indexes in MySQL
MySQL how to use pt-duplicate-key-checker to find redundant, duplicate index, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.
Unzip pt software
# tar xfz percona-toolkit-3.0.2_x86_64.tar.gz
Install the corresponding perl dependency package
# yum install perl-DBI
# yum install perl-DBD-MySQL
Execute pt-duplicate-key-checker command
# ./ bin/pt-duplicate-key-checker -uroot -pcnstrong#123454321 -S /data/mysql01/mysql.sock
# ########################################################################
# beike.ls_lesson
# ########################################################################
# index_lesson_classSubjId is a left-prefix of index_lesson_classSubjId_startTime
# Key definitions:
# KEY `index_lesson_classSubjId` (`classSubjId`),
# KEY `index_lesson_classSubjId_startTime` (`classSubjId`,`startTime`)
# Column types:
# `classsubjid` bigint(20) default null comment '???? id'
# `starttime` timestamp null default null comment '???? '
# To remove this duplicate index, execute:
ALTER TABLE `beike`.` ls_lesson` DROP INDEX `index_lesson_classSubjId`;
# ########################################################################
# course.QRTZ_BLOB_TRIGGERS
# ########################################################################
# SCHED_NAME is a duplicate of PRIMARY
# Key definitions:
# KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
# PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
# Column types:
# `sched_name` varchar(120) not null
# `trigger_name` varchar(200) not null
# `trigger_group` varchar(200) not null
# To remove this duplicate index, execute:
ALTER TABLE `course`.` QRTZ_BLOB_TRIGGERS` DROP INDEX `SCHED_NAME`;
# ########################################################################
# course.QRTZ_FIRED_TRIGGERS
# ########################################################################
# IDX_QRTZ_FT_TRIG_INST_NAME is a left-prefix of IDX_QRTZ_FT_INST_JOB_REQ_RCVRY
# Key definitions:
# KEY `IDX_QRTZ_FT_TRIG_INST_NAME` (`SCHED_NAME`,`INSTANCE_NAME`),
# KEY `IDX_QRTZ_FT_INST_JOB_REQ_RCVRY` (`SCHED_NAME`,`INSTANCE_NAME`,`REQUESTS_RECOVERY`),
# Column types:
# `sched_name` varchar(120) not null
# `instance_name` varchar(200) not null
# `requests_recovery` varchar(1) default null
# To remove this duplicate index, execute:
ALTER TABLE `course`.` QRTZ_FIRED_TRIGGERS` DROP INDEX `IDX_QRTZ_FT_TRIG_INST_NAME`;
After reading the above, do you know how to use pt-duplicate-key-checker to find redundant and duplicate indexes in MySQL? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!
