Schema optimization and indexing

MySQL can create multiple indexes on one column; this does not alert and prevent errors. MySQL must maintain each duplicate index separately, and it will refer to these indexes when the statement optimizer optimizes the statement. This can have an impact on performance.

Duplicate indexes are the same set of indexes that have the same type, in the same order. You should avoid creating them and if you find them to be deleted as soon as possible.

Sometimes you can create a duplicate index without knowing it. For example, the following code

CREATE TABLE test (
　ID INT NOT NULL PRIMARY KEY,
　UNIQUE(ID),
　INDEX(ID)
);

An inexperienced user might think of making a column a primary key, plus a unique constraint, and adding an index. In fact, MySQL implements a unique constraint and primary key index. So in fact, three indexes have been created on a single column. There is no need to do this unless you want to create different types of indexes on the same columns to satisfy different types of queries.

Redundant indexes are different from duplicate indexes. If there is an index on (a,b), the other index on (A) is redundant. Because it is the prefix of the first index. The index on (a,b) can also be used separately as an index on (A). However, the index on one (B,A) is not redundant, and the index on (B) is not redundant because it is not the prefix of the (a,b) index. Further, different index types are not redundant for B-tree indexes, regardless of the columns they cover.

When people add indexes, redundant indexes are often present. For example, some people have added an index (A,B) to replace the extension of an existing (A) index.

In most cases, you don't want to have redundant indexes, and to avoid them, you should extend existing indexes instead of adding a new one. There are also times when you may need to use a redundant index for performance reasons. The main reason for using redundant indexes is that when you extend an existing index, this redundant index will make it larger.

For example, if you have an index on an integral column and you extend it to this integer column and an index on a varchar column, it slows down. If your query overwrites the index, or if the table is MyISAM and needs a range scan (because MyISAM uses prefix compression), the slow situation can occur.

Consider the UserInfo table. As mentioned in the previous tutorial. This table has 1,000,000 lines and each state_id has 20,000 records. There is an index on the state_id. This is useful for the following queries. The Q1 statement is as follows:

Mysql> SELECT Count (*) from userinfo WHERE state_id=5;

A simple benchmark shows an execution rate of 115 per second (QPS). We are looking at the relevant query Q2.

Mysql> SELECT state_id, City, address from UserInfo WHERE state_id=5;

For this query, the result is less than 10QPS. The simple way to improve performance is to extend the index (state_id, city, address). Therefore, the index overwrites the query.

mysql> ALTER TABLE userinfo DROP KEY state_id,
-> ADD KEY state_id_2 (state_id, city, address);

After modifying the index, the Q2 is faster and the Q1 is slow. If we want two queries to be quick, we should leave both indexes, even if that individual index is redundant. The following table shows the performance of both indexes in MyISAM and InnoDB. Note that when using the State_id2 index, the InnoDB Q1 does not degrade much because InnoDB does not have a key compression.

state_id only 　 state_id_2 only 　Both state_id and　state_id_2
MyISAM, Q1 　 　 114.96 　 　 　 　25.40 　 　 　 　 　 　 　 　 　112.19
MyISAM, Q2 　 　 9.97 　 　 　 　 　16.34 　 　 　 　 　 　 　 　 　16.37
InnoDB, Q1 　 　 108.55 　 　 　 　100.33 　 　 　 　 　 　 　 　 　107.97
InnoDB, Q2 　 　 12.12 　 　 　 　 28.04 　 　 　 　 　 　 　 　 　 28.06

The disadvantage of using two indexes at the same time is a large maintenance consumption. The following data is inserted 1 million rows of performance.

state_id only 　 　 　 　 Both state_id and state_id_2
InnoDB, enough memory for both　indexes 　 　80 seconds 　 　136 seconds
MyISAM, enough memory for only　one index 　 72 seconds 　 　 470 seconds

As you can see, the performance of inserting data is poor. In general, the following is true: Adding an index can have a significant impact on the performance of insert,update,delete operations, especially if the new index reaches memory limits.