mysql-Redundant and duplicate indexes

MySQL allows multiple indexes to be created on the same column, either intentionally or unintentionally, andMySQL needs to maintain duplicate indexes separately, and the optimizer needs to consider each one individually when optimizing queries, which can affect performance.

Duplicate indexes are indexes of the same type that are created in the same order on the same column, and should be avoided by creating duplicate indexes and discovering that they should be deleted in the future. However, it is possible to create different types of indexes on the same columns to meet different query requirements.

CREATE TABLE Test (  intnotNULLPRIMARYKEY,  int not null,  INTis notnull,   UNIQUE(id),  INDEX(id), ENGINE=InnoDB;

This SQL creates 3 duplicate indexes. There is usually no reason to do so.

Redundant indexes and duplicate indexes are somewhat different, and if an index (A, b) is created, then the index (a) is redundant, because this is only the prefix index of the previous index, so (A, a)can also be used as (a), but (B, A) It is not a redundant index, nor is index (b), because B is not the leftmost prefix column of the index (A, a), and other different types of indexes created on the same column (such as hash index and full-text index) will not beredundant indexes of the B-T ree index. Regardless of what index column is overwritten.

Redundant indexes typically occur when new indexes are added to the table. For example, someone might add a new index (a, b) instead of extending a later index (A). Another case is to extend an index to (A,id), where the ID is the primary key, and for InnoDB the primary key is already contained in the level two index, so this is also redundant.

Redundant indexes are not required in most cases, and you should try to extend an existing index rather than create a new one, but there are times when performance considerations require redundant indexing, because extending an existing index can cause it to become too large to affect other query performance using that index. For example, if you have an index on an integer column, and now you need to add an extra long varchar column to extend the index, the sex may drop sharply, especially if the query treats the index as an overwrite index, or if it is a MyISAM table and has many range queries ( Due to MyISAM prefix compression )

For example, there is a userinfo table. This table has 1 million data, about 20,000 records for each state_id value. In state_id there is an index, then the following SQL we call Q1

SELECT Count (*fromWHERE state_id=5;--q1

Query execution speed is about 115 times per second (QPS)

There is also a SQL that we call Q2

SELECT  from WHERE state_id=5; --q2

The QPS for this query is 10, and the easiest way to improve the performance of this index is to state_id,city,address the index to overwrite the query:

TABLE 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 repeating index)

How to find redundant indexes and duplicate indexes?

1. Some of the Common_schema in Shlomi Noach can be used to locate, Common_schema is a series of common storage and attempts that can be installed on the server.

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

Reference documents:

[1] Baron Schwartz, Ninghai Yuanhao and other translations; "High-performance MySQL" (3rd edition); electronics Industry Press, 2013

mysql-Redundant and duplicate indexes