background

Recently, the frequent occurrence of slow SQL was causing performance problems, so it was decided to do some optimization for indexes. Some table structures already have a number of indexes, and adding more indexes will inevitably affect the performance of the inserted data. So, can you use composite indexes to do this? Let’s find out in this article.

Understanding composite index

If more than one field is used in a WHERE condition and you need to index more than one field, compound indexes (composite indexes) can be considered. For example, you need to enter the province and city to query the address. Therefore, building indexes in the province and city can significantly improve the query speed when the data volume is large.

What are the advantages of composite indexes?

  • Reduce query overhead: creating a composite index (C1, C2,c3) is equivalent to creating three indexes (C1), (C1,c2), (C1, C2,c3). For large tables, this can greatly reduce overhead.
  • Overwrite index: MySQL can retrieve data directly by traversing the index without returning to the table, reducing the number of random I/O operations.
  • High efficiency: The more index columns there are, the less data is filtered through the index, improving the query efficiency.

Disadvantages:

  • The more index fields, the more indexes are created, and each index increases the disk space overhead.
  • The more indexes there are, the higher the query efficiency will be, but the efficiency will be affected by the operation of adding, deleting and modifying indexes that need to update.

Recommendations for using composite indexes: A single table should contain no more than one composite index, and a single composite index should contain no more than three fields. Once exceeded, necessity and alternatives need to be considered.

Leftmost matching principle

Composite indexes follow the left-most matching principle, and as the name implies, in composite indexes, the left-most field is matched first. Therefore, when creating a composite index, the most frequently used field in the WHERE clause is placed at the far left of the composite index.

Secondary indexes are implemented in B+ trees, and although multiple columns can be specified, each column has a different priority, with the first column having higher priority. Once an omission occurs, there is no further search in the B+ tree (except for those solved by completion, etc.), so it is based on the left-most continuous matching. Since you are searching IN a B+ tree, the natural comparison for conditions is to require an exact match (i.e., “=” and “IN”).

C1 and c2 are used in the WHERE clause. Should the two fields be in the order (c1, c2) or (c2, c1) when creating the index?

The correct way to do this is to put the one with the fewest repetitions first. For example, if 95% of the values are not repeated, consider putting them first.

Effect of field order

Compound indexes follow the leftmost matching rule. Do fields in a WHERE query condition also need to be written in index order?

For example, if the composite index is (c1,c2,c3), do 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;
Copy the code

See the article proposed that the first SQL statement is more efficient, is it credible? The two query methods should have the same conditions and the same results, and normally Mysql will let them go through the same index.

Parsing the above two statements through Mysql’s query optimizer explain shows that the execution plan is identical. That is: the order of the fields in the SQL statement does not need to be the same as the order of the fields in the composite index; the query optimizer adjusts the order automatically.

The effect of efficiency, if any, on the order in which the query optimizer corrects, is negligible.

Can a single field trigger an index?

Select * from (c1,c2,c3); select * from (c1,c2,c3); select * from (c1,c2,c3);

But what if the WHERE condition is as follows:

from t_user where c2 = 4;
Copy the code

Does the above statement go through the index? This can be illustrated in several cases.

Execute explan query c1 conditional SQL statement:

explain select * from t_user where c1 = 1;
Copy the code

The index type of the above statement is ref. The ref type indicates that Mysql will quickly find the index that meets the condition based on a specific algorithm, instead of scanning every data in the index. This type of index needs to satisfy certain data structures in order to find out data quickly.

Execute explan query C2 conditional SQL statement:

explain select c2 from t_user where c2 = 4;
Copy the code

The index type of the preceding statement is index. The index type indicates that Mysql scans the entire index. If an index or part of an index is scanned, Mysql may use the index type. Because this method is one data one data search, the performance is not high.

In this example, the query field must be c2 in WHERE and C2 in SELECT. The index type is used only when c2 is queried.

If you replace c2 with * or some other field:

explain select * from t_user where c2 = 4;
Copy the code

The above statement will find that instead of going through index, it will go through the full table scan. This also explains from the side of Mysql to talk about the leftmost matching principle.

So the conclusion is: if a single field is the first field in a composite index, the index will move normally; If a single field is another field in a composite index, and only that field appears after select, the index type is used. In other cases, the full table scan is performed.

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 the single index and the composite index is almost the same, or even slightly faster than the composite index.

A composite index is useless if only the non-starting column (C2) of the composite clustered index is used as the query condition.

For a table, if there are compound indexes (C1, c2), there is no need to create a single index (c1).

If there is a single index (C1), you can add multiple indexes (C1, c2) to improve efficiency.

summary

In this article, you can use explain to check whether your SQL statement is indexed and what index is used.

But it’s also important to understand that Mysql’s execution plan doesn’t exactly match the actual execution of the query.

Don’t ask me how I know, because I’ve seen it in practice. The same SQL statement may or may not use the index because of different query conditions.

About the blogger: Author of the technology book SpringBoot Inside Technology, loves to delve into technology and writes technical articles.

Public account: “program new vision”, the blogger’s public account, welcome to follow ~

Technical exchange: Please contact the weibo user at Zhuan2quan

\