Shulou(Shulou.com)06/01 Report--

The following is about the difference and function of redundant and repeated indexes in mysql. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the difference and function of redundant and repeated indexes in mysql.

Mysql allows multiple indexes to be created on the same column, intentionally or unintentionally, mysql needs to maintain duplicate indexes separately, and the optimizer needs to consider them one by one when optimizing the query, which can affect performance.

Duplicate indexes are indexes of the same type created in the same order on the same column, which should be avoided and deleted as soon as they are found. 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, An INT NOT NULL, B INT NOT NULL, UNIQUE (ID), INDEX (ID),) ENGINE=InnoDB

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

There are some differences between a redundant index and a duplicate index. If you create an index (aforce b), then create an index (a) is a redundant index, because this is only the prefix index of the previous index, so (aforce b) can also be used as (a), but (bforce a) is not a redundant index, and index (b) is not, because b is not the leftmost prefix column of the index. Other different types of indexes created on the same column (such as hash index and full-text index) will not be redundant indexes of the B-Tree index, regardless of the index column being overwritten.

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

In most cases, redundant indexes are not required, and existing indexes should be extended as much as possible rather than creating new ones, but sometimes redundant indexes are needed for performance reasons, because extending existing indexes can cause them to become too large, thus affecting the performance of other queries that use the index. For example, if you have an index on an integer column and now need to add a very long varchar column to extend the index, the nature may decline sharply, especially if a query treats the index as an overlay index, or when this is an myisam table and has a lot of range queries (due to the prefix compression of myisam)

For example, there is a userinfo table. This table has 1000000 pieces of data and about 20000 records for each state_ id value. There is an 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 changed query is about 115times per second (QPS)

There is also a SQL, which we call Q2.

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

The QPS of this query is 10, and the easiest way to improve the performance of the index is to state_id,city,address the index 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 rather than a duplicate index.)

How to find redundant indexes and duplicate indexes?

1. You can use some of the attempts in Shlomi Noach's common_schema to locate. Common_schema is a series of common storage and attempts that can be installed on a cloud server.

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

Is there anything you don't understand about the difference and function of redundant and duplicate indexes in the above mysql? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.