Mysql-redundancy and duplicate indexes, mysql-redundant Indexes

Mysql-redundancy and duplicate indexes, mysql-redundant Indexes

Mysql allows you to create multiple indexes on the same column, whether intentionally or unintentionally. mysql needs to maintain duplicate indexes separately, and the optimizer also needs to consider the query optimization individually, this affects performance.

Duplicate indexes refer to the same types of indexes created in the same order on the same column. You should avoid creating duplicate indexes in this way and delete them immediately. However, you can 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 SQL statement creates three duplicate indexes. There is usually no reason to do so.

There are some differences between redundant indexes and duplicate indexes. If an index (a, B) is created and then an index (a) is created, it is redundant indexes, because this is only the prefix index of the previous index, therefore, (a, B) can also be used as (a), but (B, a) is not a redundant index, and (B) is not, because B is not an index (, b). In addition, other types of indexes created on the same column (such as hash indexes and full-text indexes) are not redundant indexes of B-Tree indexes, no matter what index columns are covered.

Redundant indexes usually occur when a new index is added to a table. For example, someone may add A new index (A, B) instead of an extended index (). Another case is to extend an index to (A, ID), where ID is the primary key. For InnoDB, the primary key is already included in the secondary index, so this is also redundant.

In most cases, no redundant indexes are required. You should try to expand existing indexes instead of creating new indexes. However, sometimes you need redundant indexes for performance considerations, because expansion of existing indexes will lead to a too large index, which affects the performance of other queries using this index. For example, if you have an index on an integer column, you need to add an extra long varchar column to expand the index. This may cause a sharp decrease, in particular, when a query regards this index as a covered index, or this is a myisam table and there are many range queries (due to myisam prefix compression)

For example, there is a userinfo table. This table has 1000000 data records, and each state_id value has about 20000 records. If state_id has an index, the following SQL statement is called Q1.

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

The query speed is about 115 times per second (QPS)

There is also an SQL statement called Q2.

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

The QPS of this query is 10. The simplest way to improve the index performance is to bid the index (state_id, city, address) so that the index can overwrite 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 can we find out redundant indexes and duplicate indexes?

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

2. The pt_duplicate-key-checker in Percona Toolkit can be used to identify redundant and duplicate indexes through the analysis table structure.

 

References:

[1] Baron Schwartz; translated by Ninghai yuan; high-performance MySQL (version 3rd); Electronic Industry Press, 2013