MySQL duplicate index, redundant index, definition and lookup of unused indexes

1. 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 so that they should be deleted as soon as they are found. However, it is possible to create different types of indexes on the same columns to meet different query requirements.

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) 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) are not redundant indexes on the Btree index.

Also: For a Level two index (a,id), theID is the primary key, and for InnoDB, the primary key column is already contained in the two-level index, so this is also a redundant index. 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 (because MyISAM prefix compression ).

such as: Table userinfo,MyISAM engine, there are 100W Row Records, each state_id value of about 2W rows, in the state_id column has an index to the following query is useful: Select COUNT (*) from userinfo where state_id=5; test QPS 115 times per second

For the following query , the index of this state_id column is not very good, and the QPS is 10 times per second.

Select State_id,city,address from UserInfo where state_id=5;

If the state_id index is extended to (state_id,city,address), then the second query has a faster performance, but the first query slows down, and if you want two queries to be fast, then you have to put a state_ The ID column index is redundant. However, if it is a innodb table, the non-redundant state_id column index has no significant effect on the first query because InnoDB does not use index compression,MyISAM and innmodb tables use different index policies The QPS test results for SELECT queries (the following test data is for reference only):

Only the state_id column index has a state_id_2 index of two indexes at the same time

MyISAM, first query 114.96 25.40 112.19

MyISAM, second query 9.97 16.34 16.37

InnoDB, first query 108.55 100.33 107.97

InnoDB, second query 12.12 28.04 28.06

As you can see, the disadvantage of the two indexes is that the cost is higher, the following is the speed of inserting InnoDB and myisam table 100W rows of data in different indexing strategies (the following test data is for reference only):

Only the STATE_ID column index has two indexes at a time

InnoDB, 80 seconds and 136 seconds for enough content for two indexes

MyISAM, only one index has enough content at the time of 72 seconds 470 seconds

As you can see, no matter what engine, the more indexes, the slower the insertion speed, especially when the new index leads to a memory bottleneck. The method of resolving redundant indexes and repeating indexes is simple, deleting these indexes is fine, but the first thing to do is to find such indexes, which can be found through some complicated queries to access the INFORMATION_SCHEMA table, but there are two simpler ways to use:Shlomi Noach Some views in the Common_schema, or you can use the pt-dupulicate-key-checker tool in Percona Toolkit , The tool analyzes the table structure to find redundant and duplicate indexes, and for large servers, using external tools is more appropriate, if there is a large amount of data on the server or a large number of tables, querying the information_schema table may cause performance problems. It is recommended to use the pt-dupulicate-key-checker tool.

Be very careful when deleting an index:

If a query such as the where a=5 order by ID is available on the InnoDB engine table, then index (a) is useful, and the index (a, b) is actually (A,b,id) the index, which isfor a query such as the where a=5 order by ID cannot be sorted using an index, but only by using a file sort. Therefore, it is recommended to use the Pt-upgrade tool in the Percona Toolbox to carefully examine the index changes in the plan.

2. Unused indexes

In addition to redundant indexes and duplicate indexes, there may be indexes that are never used by the server, such indexes are completely cumbersome, it is recommended to consider deletion, there are two tools to help locate unused indexes:

A: Open the Userstat=on server variable in Percona server or MARIADB, turn it off by default, then let the server run for a period of time before querying Information_ Schema.index_statistics will be able to find out how often each index is used.

B: Using the pt-index-usage tool in Percona Toolkit , the tool can read the query log and explain each query in the log, then print a report of the Guan Yu Index and query, This tool can not only find out which indexes are unused, but also understand the execution plan of the query, such as: In some cases, some similar queries do not perform the same way, which can help to locate the occasional server poor quality of the query, the tool can also write the results to the MySQL table, convenient query results.

MySQL duplicate index, redundant index, definition and lookup of unused indexes