背景

使用mysql多了之后建立了不少索引比如经常出现的联合索引已经包含了单列索引。对于人工排查来说略有难度

使用pt-duplicate-key-checker是一个简便快捷的方法

用法

pt-duplicate-key-checker --help

pt-duplicate-key-checker examines MySQL tables for duplicate or redundant indexes and foreign keys.  Connection options are read from MySQL option files. For more details, please use the --help option, or try 'perldoc /usr/bin/pt-duplicate-key-checker' for complete documentation. Usage: pt-duplicate-key-checker [OPTIONS] [DSN] Options:   --all-structs         Compare indexes with different structs (BTREE, HASH,                         etc)   --ask-pass            Prompt for a password when connecting to MySQL   --charset=s       -A  Default character set   --[no]clustered       PK columns appended to secondary key is duplicate (                         default yes)   --config=A            Read this comma-separated list of config files; if                         specified, this must be the first option on the command                         line   --databases=h     -d  Check only this comma-separated list of databases   --defaults-file=s -F  Only read mysql options from the given file   --engines=h       -e  Check only tables whose storage engine is in this comma-                         separated list   --help                Show help and exit   --host=s          -h  Connect to host   --ignore-databases=H  Ignore this comma-separated list of databases   --ignore-engines=H    Ignore this comma-separated list of storage engines   --ignore-order        Ignore index order so KEY(a,b) duplicates KEY(b,a)   --ignore-tables=H     Ignore this comma-separated list of tables   --key-types=s         Check for duplicate f=foreign keys, k=keys or fk=both (                         default fk)   --password=s      -p  Password to use when connecting   --pid=s               Create the given PID file   --port=i          -P  Port number to use for connection   --set-vars=A          Set the MySQL variables in this comma-separated list of                         variable=value pairs   --socket=s        -S  Socket file to use for connection   --[no]sql             Print DROP KEY statement for each duplicate key (                         default yes)   --[no]summary         Print summary of indexes at end of output (default yes)   --tables=h        -t  Check only this comma-separated list of tables   --user=s          -u  User for login if not current user   --verbose         -v  Output all keys and/or foreign keys found, not just                         redundant ones   --version             Show version and exit   --[no]version-check   Check for the latest version of Percona Toolkit, MySQL,                         and other programs (default yes) Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time Rules:   This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details. DSN syntax is key=value[,key=value...]  Allowable DSN keys:   KEY  COPY  MEANING   ===  ====  =============================================   A    yes   Default character set   D    yes   Default database   F    yes   Only read default options from the given file   P    yes   Port number to use for connection   S    yes   Socket file to use for connection   h    yes   Connect to host   p    yes   Password to use when connecting   u    yes   User for login if not current user   If the DSN is a bareword, the word is treated as the 'h' key. Options and values after processing arguments:   --all-structs         FALSE   --ask-pass            FALSE   --charset             (No value)   --clustered           TRUE   --config              /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-duplicate-key-checker.conf,/root/.percona-toolkit.conf,/root/.pt-duplicate-key-checker.conf   --databases           (No value)   --defaults-file       (No value)   --engines             (No value)   --help                TRUE   --host                (No value)   --ignore-databases   --ignore-engines   --ignore-order        FALSE   --ignore-tables   --key-types           fk   --password            (No value)   --pid                 (No value)   --port                (No value)   --set-vars   --socket              (No value)   --sql                 TRUE   --summary             TRUE   --tables              (No value)   --user                (No value)   --verbose             FALSE   --version             FALSE   --version-check       TRUE

实战

pt-duplicate-key-checker --host 192.168.1.7 --user=root --password=root >test.sql

# ######################################################################## # f6db_1116_prod_backup.p_user # ######################################################################## # p_user_userid_idx is a duplicate of PRIMARY # Key definitions: #   KEY `p_user_userid_idx` (`USER_ID`) USING BTREE, #   PRIMARY KEY (`USER_ID`), # Column types: #         `user_id` int(13) not null auto_increment # To remove this duplicate index, execute: ALTER TABLE `f6db_1116_prod_backup`.`p_user` DROP INDEX `p_user_userid_idx`; # p_user_openid_idx is a left-prefix of p_user_uk_idx # Key definitions: #   KEY `p_user_openid_idx` (`WX_OPEN_ID`) USING BTREE #   UNIQUE KEY `p_user_uk_idx` (`WX_OPEN_ID`,`WX_APP_ID`), # Column types: #         `wx_open_id` varchar(28) default null #         `wx_app_id` varchar(20) default null # To remove this duplicate index, execute: ALTER TABLE `f6db_1116_prod_backup`.`p_user` DROP INDEX `p_user_openid_idx`;

比如这样就查找出来了重复索引

附：percona-toolkit的安装及简介