Detailed Explanation of Redundant and Duplicate Indexes in mysql

mysql allows multiple indexes to be created on the same column, intentionally or unintentionally, while mysql needs to maintain duplicate indexes separately, and the optimizer needs to consider them individually when optimizing queries, which affects performance.

Duplicate indexes are indexes of the same type that are created on the same columns in the same order. This should be avoided and removed immediately after discovery. However, it is possible to create different types of indexes on the same column to meet different query requirements.

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

This section of SQL creates three duplicate indexes. There is usually no reason to do so.

There are one difference between redundant indexes and duplicate indexes, If indexes are created (a, b), The re-created index (a) is the redundant index, Because this is only the prefix index of the previous index, Thus (a, b) can also be used as (a), but (b, a) is not a redundant index, nor is the index (b), because b is not the leftmost prefix column of the index (a, b), and other different types of indexes created on the same column (such as hash index and full-text index) will not be redundant indexes of B-Tree indexes, regardless of the overwritten index columns.

Redundant indexes usually occur when new indexes are added to tables. For example, someone might add a new index (A, B) instead of extending a later index (A). In another case, an index is expanded to (A, ID), where ID is the primary key, and for InnoDB, the primary key is already included in the level 2 index, so it is also redundant.

In most cases, redundant indexes are not required, and existing indexes should be extended as much as possible instead of creating new indexes. However, sometimes redundant indexes are required for performance reasons, because expanding existing indexes will cause them to become too large, which will affect the performance of other queries using the index. For example, if you have an index on an integer column and now need to add an extra long varchar column to extend the index, then performance may drop dramatically, especially if there are queries that treat this index as an override index, or if this is an myisam table and there are many range queries (due to the prefix compression of myisam)

For example, there is an userinfo table. This table has 1,000,000 pieces of data and approximately 20,000 records for each state_id value. There is one index in state_id, so the following SQL is called Q1

SELECT count(*) FROM userinfo WHERE state_id=5; --Q1

The execution speed of the modified query is about 115 times per second (QPS)

There is also an SQL, which we call Q2

SELECT state_id,city,address FROM userinfo WHERE state_id=5; --Q2

The QPS of this query is 10. The easiest way to improve the performance of this index is to fight the index (state_id, city, address) so that the index can cover the query:

ALERT TABLE userinfo ADD KEY state_id_2(state_id,city,address);

Note: state_id already has an index. According to the previous concept, this is a redundant index instead of a duplicate index.)

How to find redundant indexes and duplicate indexes?

1. You can use one of the attempts in common_schema of Shlomi Noach to locate, and common_schema is a series of commonly used stores and attempts that can be installed on a server.

2. You can use pt_duplicate-key-checker in Percona Toolkit, which analyzes the table structure to find redundant and duplicate indexes.

Summarize

The above is this article about the detailed explanation of mysql redundancy and duplicate index of all the content, I hope to help you. Interested friends can refer to: several more important MySQL variables, briefly Redis and MySQL differences, MYSQL sub-query and nested query optimization instance analysis, etc., if there are deficiencies, welcome to leave a message that this site will reply to everyone in time and modify, thank friends for their support to this site!