Analysis of MySQL joint index function and usage examples

This article uses examples to illustrate the functions and usage of MySQL joint indexes. Share with you for your reference, the details are as follows:

A joint index is also called a composite index. For composite indexes: MySQL uses the fields in the index from left to right. A query can use only part of the index, but only the leftmost part. For example, the index is key index (a,b,c). It can support the search of three combinations of a | a,b | a,b,c, but does not support the search of b,c. When the leftmost field is a constant reference, the index is very effective.

An index on two or more columns is called a composite index.

The additional columns in the index allow you to narrow the scope of your search, but using one index with two columns is different from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name consists of a last name and a first name. The phone book is sorted first by last name pairs, and then by first name pairs for people with the same last name. A phone book is very useful if you know the last name, even more useful if you know both the first and last name, but useless if you know only the first name.

So when creating a composite index, you should carefully consider the order of the columns. Composite indexes are useful when you perform searches on all columns in the index or on just the first few columns; they are not useful when you perform searches on just any of the later columns.

For example: create a composite index of name, age, and gender.

create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);

 
Optimum: select * from test where a=10 and b>50
Bad: select * from test where a>50

Optimistic: select * from test order by a
Bad: select * from test order by b
Bad: select * from test order by c

Optimum: select * from test where a=10 order by a
Optimum: select * from test where a=10 order by b
Bad: select * from test where a=10 order by c

Optimum: select * from test where a>10 order by a
Bad: select * from test where a>10 order by b
Bad: select * from test where a>10 order by c

Optimum: select * from test where a=10 and b=10 order by a
Optimum: select * from test where a=10 and b=10 order by b
Optimum: select * from test where a=10 and b=10 order by c

Optimum: select * from test where a=10 and b=10 order by a
Optimum: select * from test where a=10 and b>10 order by b
Bad: select * from test where a=10 and b>10 order by c

 

Indexing principles

1. The fewer indexes, the better

Reason: Mainly when modifying data, every index needs to be updated, which reduces the writing speed.

2. The narrowest field is placed on the left side of the key

3. Avoid file sort, temporary tables and table scans.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.