MySQL中重复索引和重复外键清理

妖狐艹你老母 2022-08-09 14:40 168阅读 0赞

MySQL允许在相同列上创建重复的索引，但这样做对数据库却是有害而无利的，需要定期检查此类重复索引以改善数据库性能。

可减少磁盘空间占用、减少磁盘IO、减少优化器优化查询时需要比较的索引个数、减少数据库维护冗余索引的各类开销、提高数据库性能（插入、更新、删除）

**重复索引检测**  
pt-duplicate-key-checker：通过SHOW CREATE TABLE输出的表定义检测MySQL表中重复或者冗余的索引或外键

可以检测到的冗余/重复索引类型：若某个索引和另外某个索引以同样的顺序包含同样的列，或者该索引包含的列是另外某个索引的最左前缀列，则被认为是重复/冗余的索引。默认情况下只在同类型的索引间（如BTREE索引）进行比较，不同类型的索引即使符合上述描述也不会被认为是重复/冗余,但这一行为可以通过参数改变。除此之外，还可检测重复的外键，即引用的表和列均相同的外键。对于聚簇索引的表，在辅助索引后添加主键列的索引也被认为是冗余的，因为这种情况下，辅助索引末尾本身就包含有主键信息。

基本用法以及样例输出如下  
\[root@VM\_8\_180\_centos packages\]\# pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass

样例输出：

# ########################################################################
    # dcf.privilege 
    # ########################################################################
    
    # Uniqueness of UQI_IDX_1 ignored because PRIMARY is a duplicate constraint
    # UQI_IDX_1 is a duplicate of PRIMARY
    # Key definitions:
    # UNIQUE KEY `UQI_IDX_1` (`privilege_id`),
    # PRIMARY KEY (`privilege_id`),
    # Column types:
    # `privilege_id` varchar(50) collate utf8_bin not null comment '权限id'
    # To remove this duplicate index, execute:
    ALTER TABLE `dcf`.`privilege` DROP INDEX `UQI_IDX_1`;
    
    # ########################################################################
    # dcf.t_game_config 
    # ########################################################################
    
    # Uniqueness of pkey ignored because PRIMARY is a duplicate constraint
    # pkey is a duplicate of PRIMARY
    # Key definitions:
    # UNIQUE KEY `pkey` (`pkey`)
    # PRIMARY KEY (`pkey`),
    # Column types:
    # `pkey` bigint(20) not null auto_increment
    # To remove this duplicate index, execute:
    ALTER TABLE `dcf`.`t_game_config` DROP INDEX `pkey`;
    
    # ########################################################################
    # dcf.t_project_institution 
    # ########################################################################
    
    # index_1 is a left-prefix of index_2
    # Key definitions:
    # KEY `index_1` (`project_id`),
    # KEY `index_2` (`project_id`,`institution_id`,`delete_flag`)
    # Column types:
    # `project_id` bigint(20) not null comment '项目id'
    # `institution_id` varchar(20) not null comment '机构id'
    # `delete_flag` tinyint(4) not null
    # To remove this duplicate index, execute:
    ALTER TABLE `dcf`.`t_project_institution` DROP INDEX `index_1`;
    
    # ########################################################################
    # dcf_commons.bank_cnaps 
    # ########################################################################
    
    # idx is a duplicate of PRIMARY
    # Key definitions:
    # KEY `idx` (`cnaps`)
    # PRIMARY KEY (`cnaps`),
    # Column types:
    # `cnaps` varchar(255) not null comment '电子联行号'
    # To remove this duplicate index, execute:
    ALTER TABLE `dcf_commons`.`bank_cnaps` DROP INDEX `idx`;
    
    # ########################################################################
    # dcf_contract.customer_bank_account 
    # ########################################################################
    
    # IDX_CUSTOMER_ID is a left-prefix of UQI_IDX_1
    # Key definitions:
    # KEY `IDX_CUSTOMER_ID` (`customer_id`)
    # UNIQUE KEY `UQI_IDX_1` (`customer_id`,`account_no`,`branch_bank`,`account_type`,`account_name`) USING BTREE,
    # Column types:
    # `customer_id` varchar(20) collate utf8_bin not null comment '客户id'
    # `account_no` varchar(40) collate utf8_bin default null comment '银行账号'
    # `branch_bank` varchar(100) collate utf8_bin default null comment '开户支行'
    # `account_type` tinyint(4) default null comment '账户类型：比如收款账户，还款账户等\n0-收款账户\n1-还款账户'
    # `account_name` varchar(100) collate utf8_bin default null comment '银行账户户名'
    # To remove this duplicate index, execute:
    ALTER TABLE `dcf_contract`.`customer_bank_account` DROP INDEX `IDX_CUSTOMER_ID`;
    
    # ########################################################################
    # dcf_contract.t_contract_account 
    # ########################################################################
    
    # IDX_CONTRACT_ID is a left-prefix of t_contract_account_uq1
    # Key definitions:
    # KEY `IDX_CONTRACT_ID` (`contract_id`)
    # UNIQUE KEY `t_contract_account_uq1` (`contract_id`,`account_type`),
    # Column types:
    # `contract_id` bigint(20) not null comment '合同id'
    # `account_type` tinyint(4) not null comment '账户类 型：globalconstant.bankaccounttypec常数 \n0-收款账户\n1-还款账户 等'
    # To remove this duplicate index, execute:
    ALTER TABLE `dcf_contract`.`t_contract_account` DROP INDEX `IDX_CONTRACT_ID`;
    
    ......
    ......
    
    # ########################################################################
    # Summary of indexes 
    # ########################################################################
    
    # Size Duplicate Indexes 173317386
    # Total Duplicate Indexes 18
    # Total Indexes 562

会给出重复/冗余类型、索引/外键定义、索引包含的列类型、移除重复/冗余索引/外键的SQL、最后会给出有关索引的统计信息。

**重复索引删除**  
直接执行工具输出结果中的ALTER TABLE语句即可，但是执行前一定要仔细评估可能造成的影响。比如，表非常非常大的情况下可能造成主从复制延迟，又比如SQL中若包含索引提示的话直接删除索引可能导致报SQL语法错误，最好事先查一下是不是包含此类SQL（可通过general log或者tcpdump工具获取SQL并加以分析）