How does the composite index of MySQL take effect?

background

Recently, slow SQL statements have frequently caused system performance issues, so we decided to optimize the indexes. Some table structures already have many indexes. If you continue to add indexes, it will inevitably affect the performance of inserting data. So, is it possible to use a combined index to achieve this goal? Let’s find out in this article.

Understanding composite indexes

If multiple fields are used in the where condition and multiple fields need to be indexed, you can consider using a composite index. For example, when searching for an address, you need to enter the province and city. Then, creating an index on the province and city will significantly improve the query speed when the amount of data is large.

What are the advantages of combined indexes?

• Reduce query overhead: Creating a composite index (c1, c2, c3) is actually equivalent to creating three indexes: (c1), (c1, c2), and (c1, c2, c3). For large tables, this can greatly reduce overhead.
• Covering index: MySQL can directly obtain data by traversing the index without returning to the table, reducing a lot of random IO operations.
• High efficiency: The more index columns there are, the less data is filtered out through the index, thus improving query efficiency.

shortcoming:

• The more index fields you have, the more indexes you create, and each index will increase disk space overhead;
• The more indexes there are, the higher the query efficiency will be. However, this will affect the efficiency of add, delete, and modify operations that require updating the index.

Recommendations for using composite indexes: A single table should not have more than one composite index, and a single composite index should not have more than three fields. Once it is exceeded, you need to consider the necessity and whether there are other alternatives.

Leftmost matching principle

Composite indexes follow the leftmost matching principle. As the name implies, in a composite index, the leftmost field is matched first. Therefore, when creating a composite index, the most frequently used field in the where clause is placed on the leftmost side of the composite index.

The auxiliary index is implemented by B+ tree. Although multiple columns can be specified, the comparison priority of each column is different, and the one written in front has a higher priority. Once an omission occurs, the search cannot continue on the B+ tree (unless it is resolved by measures such as filling in the gaps), so the search is performed according to the leftmost continuous match. Since the search is on a B+ tree, the comparison of the conditions naturally requires an exact match (i.e. "=" and "IN").

Two fields c1 and c2 are used in the where clause. When creating an index, should the order of the two fields be (c1, c2) or (c2, c1)?

The correct approach is to put the one with the least repeated values ​​first. For example, if 95% of the values ​​are unique, you can consider putting them at the front.

Effect of field order

The composite index follows the leftmost matching principle, so do the fields in the where query conditions also need to be written in the order of the index?
For example, if the composite index is (c1, c2, c3), will the following two query conditions affect the index?

select * from t_user where c1 = 1 and c2 = 4;
select * from t_user where c2 = 4 and c1 = 1;

I saw an article suggesting that the first SQL statement is more efficient. Is it credible? The two query methods have the same conditions and the results should be the same. Normally, MySQL will let them use the same index.

By analyzing the above two statements through MySQL's query optimizer explain, we can find that the execution plans are exactly the same. In other words, the order of fields in the SQL statement does not need to be consistent with the order of the composite index fields, and the query optimizer will automatically adjust the order.

If there is an impact on efficiency, it is probably the impact of the query optimizer's correction order, which is almost negligible.

Can a single field trigger an index?

For a composite index of (c1,c2,c3), which is equivalent to three indexes (c1), (c1,c2), and (c1,c2,c3), if there is only c1 in the query condition, it is obvious that the index will be used.
But what if the where condition is as follows:

from t_user where c2 = 4;

Will the above statement go through the index? This can be explained in several situations.
Execute the SQL statement with explan query c1 as the condition:

explain select * from t_user where c1 = 1;

The index type of the above statement is: ref. The ref type means that MySQL will quickly find the index that meets the conditions based on a specific algorithm, without scanning and judging every piece of data in the index. In order to quickly find data, this type of index needs to meet certain data structures.
Execute the SQL statement with explan query c2 as the condition:

explain select c2 from t_user where c2 = 4;

The index type of the above statement is: index. The index type means that MySQL will scan the entire index. As long as it is an index or a part of an index, MySQL may scan it in the index type. Since this method searches for data one by one, the performance is not very high.

In this example, there are certain requirements for the queried fields. The condition in where is c2, and the field queried in select can only be c2, so the index type index will be used.

If c2 is replaced with * or other fields:

explain select * from t_user where c2 = 4;

The above statement will find that the index is no longer used, but the full table scan is performed. This also explains why MySQL follows the leftmost matching principle.
So the conclusion is: if a single field is the first field of a composite index, the index will be used normally; if a single field is another field of a composite index, and only this field appears after the select, the index type index will be used; in other cases, the full table scan will be used.

Can a composite index replace a single index?

Single index: (c1), composite index: (c1, c2).

When c1 is used as the query condition, the query speed of a single index is almost the same as that of a composite index, or even slightly faster than that of a composite index.
If only the non-starting column (c2) of the composite clustered index is used as the query condition, the composite index will not have any effect.
For a table, if there is a composite index (c1, c2), there is no need to create a single index (c1).
If a single index (c1) already exists, you can add a composite index (c1, c2) to improve efficiency if required by the query.

summary

This article summarizes some knowledge points that you need to pay attention to when using MySQL composite indexes. When using, you can use explain to check whether your SQL statement uses the index and which index it uses.
But you also need to understand that Mysql's execution plan and the actual execution process of the query do not completely match.
Don't ask me why I know this, because I have encountered it in practice. For the same SQL statement, depending on the query conditions, the index may or may not be used.

This is the end of this article about how MySQL composite index works. For more information about MySQL composite index, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!