The difference between redundant and duplicate indexes in MySQL

MySQL allows you to create multiple indexes on a single column. Whether intentional or unintentional, MySQL needs to maintain these duplicate indexes separately, and the optimizer also needs to consider them one by one when optimizing queries, which will affect MySQL performance.

Concept Explanation

Duplicate indexes: Indexes of the same type created on the same columns in the same order. Creating such duplicate indexes should be avoided and removed immediately upon discovery.

Redundant indexes: Two indexes cover the same columns in the same order.

Reason for creation

Generally speaking, we sometimes create duplicate indexes inadvertently, such as the following example:

CREATE TABLE test(
 ID INT NOT NULL PRIMARY KEY,
 A INT NOT NULL,
 B INT NOT NULL,
 UNIQUE(ID),
 INDEX(ID)
)ENGINE=InnoDB;

Because MySQL's unique restrictions and primary key restrictions are implemented through indexes, in fact, the table created using the above code will actually create three indexes on the ID column. There is usually no reason to do this, except to create different types of indexes on the same column to satisfy different query requirements. For example, there are two indexes: KEY(col) and FULLTEXT KEY(col).

Redundant indexes are somewhat different from duplicate indexes. If you create index (A, B), then creating index (A) is a redundant index because it is only a prefix index of the previous index. However, if index (B,A) is created, it is not a redundant index, and index (B) is not a redundant index because it is not the leftmost prefix column of index (A,B). In addition, indexes of different types will not be redundant with B-tree indexes, regardless of the index columns covered.

In most cases, redundant indexes are not necessary, and existing indexes should be expanded as much as possible instead of creating new indexes. But sometimes, for performance reasons, such as expanding an existing index, it will become too large, thus affecting the performance of other queries that use the index.

Influence

Creating redundant indexes as covering indexes can improve our QPS for some queries, but having two indexes also has the disadvantage of higher index costs.

As more and more indexes are added to a table, insertion speed into the table will slow down. Generally speaking, adding new indexes will slow down INSERT, UPDATE, DELETE and other operations, especially when the new indexes cause memory bottlenecks.

The solution to redundant and duplicate indexes is simple: just delete them. However, the first thing to do is to find such indexes.

Supplement: Cleaning up duplicate indexes and duplicate foreign keys in MySQL

MySQL allows you to create duplicate indexes on the same column, but doing so is harmful rather than beneficial to the database. You need to check such duplicate indexes regularly to improve database performance.

It can reduce disk space usage, reduce disk IO, reduce the number of indexes that need to be compared when the optimizer optimizes queries, reduce various overheads of maintaining redundant indexes in the database, and improve database performance (insert, update, delete)

Duplicate index detection

pt-duplicate-key-checker: Detect duplicate or redundant indexes or foreign keys in MySQL tables using table definitions output by SHOW CREATE TABLE

Redundant/duplicate index types that can be detected: If an index contains the same columns in the same order as another index, or if the columns contained in the index are the leftmost prefix columns of another index, it is considered a duplicate/redundant index.

By default, only indexes of the same type (such as BTREE indexes) are compared. Indexes of different types will not be considered duplicated/redundant even if they meet the above description, but this behavior can be changed through parameters.

In addition, duplicate foreign keys can be detected, that is, foreign keys that reference the same table and columns. For a table with a clustered index, adding an index of the primary key column after the secondary index is also considered redundant because in this case, the end of the secondary index itself contains the primary key information.

Basic usage and sample output are as follows

[root@VM_8_180_centos packages]# pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass

Sample output:

# ########################################################################
# 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 'Privilege 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 'Project id'
# `institution_id` varchar(20) not null comment 'Institution 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 'Electronic bank number'
# 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 'Customer ID'
# `account_no` varchar(40) collate utf8_bin default null comment 'Bank account number'
# `branch_bank` varchar(100) collate utf8_bin default null comment 'Opening branch'
# `account_type` tinyint(4) default null comment 'Account type: such as receiving account, repayment account, etc.\n0-receiving account\n1-repayment account'
# `account_name` varchar(100) collate utf8_bin default null comment 'Bank account name'
# 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 'Contract id'
# `account_type` tinyint(4) not null comment 'Account type: globalconstant.bankaccounttypec constant\n0-receiving account\n1-repayment account, etc.'
# 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

It will give the duplicate/redundant type, index/foreign key definition, column types included in the index, SQL to remove duplicate/redundant index/foreign key, and finally statistics about the index.

Duplicate index removal

You can directly execute the ALTER TABLE statement in the tool output results, but be sure to carefully evaluate the possible impact before execution. For example, if the table is very large, it may cause master-slave replication delay. If the SQL contains index hints, directly deleting the index may result in SQL syntax errors. It is best to check in advance whether such SQL is included (you can obtain the SQL through general log or tcpdump tools and analyze it)

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.