Get an in-depth understanding of combined indexes in MySQL and see the differences from single-column indexes

This article will take you to understand the combined index in MySQL, introduce the method of creating and deleting the combined index, talk about the pitfalls of the combined index, the difference between the combined index and the single column index, and the usage scenarios of the combined index. I hope it will be useful to everyone. help!

What is a composite index

An index on two or more columns is called a joint index, and a joint index is It's called a composite index. [Related recommendations: mysql video tutorial]

For example, the index is key index (a,b,c) which can support a | a,b| a ,b,c 3 combinations are used for search, but b,c is not supported for search. When used, the combination of a, c can also be used, but in fact only the index

is used Create a composite index

-- 普通的组合索引
create index index_name on table_name (column1, column2, ...)
-- 组合要求唯一的索引，相比与上一种，多出了 【unique】关键字
create unique index index_name on table_name (column1, column2, ...)

Delete a composite index

-- 用法 1
drop index index_name on talbe_name
-- 用法 2
alter table table_name drop index index_name
-- 用法 3，对 2 的包装
alter table table_name drop primary key

Single column index trap

As shown in the figure, we created 2 indexes and used

and

in where to query, from execution According to the plan, only the index on the first condition is used, and subsequent indexes are directly ignored.

Combined index trap

As shown in the figure, now create a combined index a ,b,c

The conditional query in the above figure is

b,a,c

, mysql will automatically process the condition sequence into a,b, c , and then use the defined combined index

The query conditions in the above figure are

b,a

. Similarly, mysql adjusts the condition order as a,b, and then use the combined index

The query condition in the above picture is

b,c

, obviously, because # is not used ##a As a conditional column, mysql does not use a composite index The difference between a single column index and a single column index

The order of columns in a joint index is very important, from the left principle .

Single indexes work one by one, that is to say, there are three single indexes. Which condition query works first, and the others do not work.

Combined index usage scenarios

where conditions will often appear, and the number of current tables is relatively large.

• where When
and
• is used in the condition instead of

  or.

  Joint index is more suitable than a single index, because the index occupies a certain amount of disk space, which means there is a certain amount of overhead. If there are more than one single index, the waste of resources will be greater. Many, a joint index is equivalent to building an index on multiple columns, and it is only built once, which is very suitable under and conditions.

• For more programming-related knowledge, please visit:
Programming Video

! !

The above is the detailed content of Get an in-depth understanding of combined indexes in MySQL and see the differences from single-column indexes. For more information, please follow other related articles on the PHP Chinese website!