Three MySQL joint index interview questions to eliminate 80% of the interviewees, how many can you answer correctly?

It is well known that the MySQL joint index follows the leftmost prefix matching principle, and it will not follow it in a few cases (if you are interested, you can read the previous article).

When creating a joint index , it is recommended to put the highly differentiated fields in the first column.

As for how to count the discrimination, it can be done in the following way.

Create a test table to test:

 CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='测试表';

Count the discrimination of each field:

 select 
    count(distinct a)/count(*), 
    count(distinct b)/count(*),
    count(distinct c)/count(*)
from test;

The larger the value, the higher the degree of discrimination, and the priority is placed in the first column.

Many people do not know how the joint index is stored in the B+ tree? I just draw it.

For example, create a joint index on the field ( a , b ), and the storage structure is similar to the following:

The leaf nodes store all data and are connected by sequential pointers. The data are first sorted by the field a a b values of the fields are equal.

a The value of the field is globally ordered, 1,1,1,2,2,2 respectively.

b The value of the field is globally unordered, there are 1,3,5,1,3,5, respectively, only when the value of the a field is equal, the local order is shown .

Therefore, when performing SQL query, if there is no a field in the where condition, only the b field cannot be used for indexing, as follows:

 select * from test where b=1;

As mentioned in some articles, creating a joint index on two fields ( a , b ) will create two indexes, respectively ( a ) and ( a , b ), which is actually an inappropriate statement, although the result is correct.

Let's do a few classic interview questions about joint indexing. How about you try?

first question:

The following SQL, how to create a joint index ?

 SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;

你以为的答案是（ a , b , c ），其实答案是6个，abc三个的排列组合，（ a , b , c ）、（ a , c , b ）、（ b , a , c ）、（ b , c , a ）、（ c , a , b ）、（ c , b , a ）。

The MySQL optimizer adjusts the order of conditions to fit the index.

To add another sentence to the interviewer, the fields with a high degree of discrimination are placed at the top, which will greatly increase the points.

Second question:

The following SQL, how to create a joint index ?

 SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;

The knowledge point examined is: the joint index will stop when it encounters range matching, and will not match the following index fields.

所以答案应该是：（ a , c , b ）和（ c , a , b ).

当创建（ a , c , b ）和（ c , a , b ) When indexing, the query will use the index of 3 fields, which is more efficient.

How to judge that the index of 3 fields is used instead of the index of only the first two fields?

There is a very simple way to look at the index length of the execution plan.

Since the int type field occupies 4 bytes, the length of the 3 fields is exactly 12 bytes.

Third question:

The following SQL, how to create a joint index ?

 SELECT * FROM test WHERE a in (1,2,3) and b > 1;

The answer is ( a , b ). The in conditional query will be converted into an equivalent query, which can be verified:

You can see that an index of two fields is used.

Therefore, we usually do development and try our best to find a way to convert range queries into in-condition queries, which is more efficient.

The article is continuously updated, and WeChat searches for "One Light Architecture" to read more technical dry goods as soon as possible.