Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

What is a composite index

An index on two or more columns is called a joint index, also known as a composite index.

Index, for example, is the key index (a, b, c) can support a | a, b | a, b, c three combinations are looking for, but does not support b, c to look up. When used, the combination of A and C can also be used, but only the index of A is used

Create composite indexes

Plain composite indexes
create index index_name on table_name (column1, column2, ...)
The combination requires a unique index, which contains the keyword unique
create unique index index_name on table_name (column1, column2, ...)
Copy the code

Delete composite index

The usage - 1
drop index index_name on talbe_name
-- 用法 2
alter table table_name drop index index_name
-- Usage 3, the packaging of 2
alter table table_name drop primary key
Copy the code

Single-column index trap

As shown in the figure, we create two indexes. When we use and in where, we can see from the execution plan that only the index on the first condition is used. Subsequent indexes are ignored

Combined index trap

As shown, now create composite indexes A, B,c

Mysql automatically changes the order of a, B, and C to a, B, and C, and then uses the defined composite index

Above terms of query is b, a, in the same way, the mysql sequential adjustment conditions for a, b, composite index

The above query conditions: b, c, obviously, since there is no use a as conditions, lead to mysql didn’t use composite index

Differences from single-column indexes

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

A single index is one index at a time, that is, there are three single indexes, which condition query is the first to work, and the other does not work.

Composite index usage scenarios

  1. The WHERE condition occurs frequently, and the current number of tables is large.
  2. Where, where, whereandRather thanorFrom time to time.
  3. A joint index is more suitable than a single index, because the index occupies a certain amount of disk space, which means that there is a certain amount of overhead. If there are many single indexes, then the waste of many resources is also more. A joint index is equivalent to creating an index for multiple columns, and only once.