MySQL leftmost matching principle is a principle that all brothers in the road should know. You must ask in an interview

select * from table where c = ‘1’;

select * from table where b =‘1’ and c =‘2’;

The following three situations are indexed :

select * from table where a = ‘1’;

select * from table where a = ‘1’ and b = ‘2’;

select * from table where a = ‘1’ and b = ‘2’ and c=‘3’;

From the above two examples, you can see something ？

Yes , Indexes abc_index:(a,b,c), Only in where The condition contains (a)、(a,b)、(a,b,c) Three types of queries using . In fact, there is a little ambiguity here , In fact, when where The only condition is (a,c) I'll go when I go , But just go a Field index , Not going c Field .

So why ？ Let's see how it works .

One 、 The principle of the leftmost matching principle

==========================================================================

MySQL Create multi column indexes （ Joint index ） There is a left most matching principle , That is, the leftmost priority ：

If there is a 2 Column index (a, b), It's already true (a)、(a, b) Indexed on ;

If there is a 3 Column index (a, b, c), It's already true (a)、(a, b)、(a, b, c) Indexed on ;

Hypothetical data surface LOL (id,sex,price,name) The physical location of （ Unordered data in a table ） as follows ：

（ notes ： The following data is used to test a small amount of data , Just to make it easy for you to see . In practice , According to the frequency of use 、 Data differentiation to comprehensively set the index order ~）

Primary key id sex(a) price(b) name

(1) 1 1350 AAA Anne

(2) 2 6300 MMM Blind monk

(3) 1 3150 NNN Nedley

(4) 2 6300 CCC Hammer stone

(5) 1 6300 LLL Dragon lady

(6) 2 3150 EEE Mr. Riel

(7) 2 6300 III Ike

(8) 1 6300 BBB Rogue Lori

(9) 1 4800 FFF Wind up demon

(10) 2 3150 KKK Card master

(11) 1 450 HHH Cold shooter

(12) 2 450 GGG galen

(13) 2 3150 OOO Little Timo

(14) 2 3150 DDD The shadow of the blade

(15) 2 6300 JJJ The strong wind sword

(16) 2 450 JJJ Blademaster

When you are in LOL Table creates a federated index abc_index:(sex,price,name) when , Generated The index file is logically equivalent to the contents of the following table （ Rank and order ）：

sex(a) price(b) name Primary key id

1 450 HHH Cold shooter (11)

1 1350 AAA Anne (1)

1 3150 NNN Nedley (3)

1 4800 FFF Wind up demon (9)

1 6300 BBB Rogue Lori (8)

1 6300 LLL Dragon lady (5)

2 450 GGG galen (12)

2 450 JJJ Blademaster (16)

2 3150 DDD The shadow of the blade (14)

2 3150 EEE Mr. Riel (6)

2 3150 KKK Card master (10)

2 3150 OOO Little Timo (13)

2 6300 CCC Hammer stone (4)

2 6300 III Ike (7)

2 6300 JJJ The strong wind sword (15)

2 6300 MMM Blind monk (2)

Did your friends find out B+ The law of tree union index ？ If you feel a little fuzzy , Let's take a look at the structure chart of index storage data , Maybe it's clearer .

This is a picture from Si mo , The sense of hierarchy is very clear , You can see , about B+ The union index in the tree , Each level of index is ordered . Joint index bcd_index:(b,c,d) , It looks like this in the index tree , In the process of comparison , First judge b To determine c And then there was d .

As can be seen from the figure above ,B+ The data items of the tree are composite data structures , Again , For our joint index of this table (sex,price,name) Come on ,B+ The search tree is also built from left to right , When SQL As follows ：

select sex,price,name from LOL where sex = 2 and price = 6300 and name = ‘JJJ The strong wind sword ’;

B+ Trees take precedence sex To determine the direction of the next pointer search , If sex Compare the same again price and name, Finally, the retrieved data is obtained ;

Two 、 Failure of an index due to violation of the leftmost principle

================================================================================

（ Here's a joint index abc_index:(a,b,c) To explain , Easy to understand ）

1、 In the query conditions , Missing highest priority index “a”

When where b = 6300 and c = 'JJJ The strong wind sword ' This doesn't mean a When searching for conditions ;B+ The tree doesn't know which node to look up in the first step , So we need to scan the whole table （ I don't want to go through the index ）. Because when you build the search tree a That's the first comparison factor , It has to be based on a To search for , And then we can continue to query in the future b and c, We can see this through the above storage structure diagram .

2、 In the query conditions , Missing index with middle priority “b”

When where a =1 and c =“JJJ The strong wind sword ” When such data is retrieved ;B+ A tree can be used a To specify the first search direction , But because of the next field b The lack of , So we can only put a = 1 Data primary key of ID All found , Through the primary key found ID Return to the table to query the relevant rows , Then match c = ‘JJJ The strong wind sword ’ The data of the , Of course , This, at least, will a = 1 We've filtered out the data of , It's better than a full table scan .

This is it. MySQL Very important principle , The left most matching principle of index .

3、 ... and 、 What did the query optimizer do secretly

==============================================================================

When all columns in the index pass through "=" or “IN” When making an exact match , Indexes can be used .

1、 If the index order is (a, b). And the query statement is where b = 1 AND a = ‘ Chen ha ha ’; Why can we still use the index ？

In theory, index is sensitive to order , But because of MySQL The query optimizer for will automatically adjust where Clause to use the appropriate index , therefore MySQL non-existent where The order of clauses leads to index invalidation . Yes, of course ,SQL Keep the good habit of writing , It also helps other colleagues understand your SQL.

2、 There is also a special case , Here's the type of SQL, a And b Can walk the index ,c Not going .

select * from LOL where a = 2 and b > 1000 and c=‘JJJ The strong wind sword ’;

For the above type of sql sentence ;mysql It will keep matching to the right until it encounters a range query (>、<、between、like) Just stop matching （ Include like ' Chen %' such ）. stay a、b After going through the index

《 A big factory Java Analysis of interview questions + Back end development learning notes + The latest architecture explanation video + Practical project source code handout 》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 Full content open source sharing

,c It's out of order , therefore c You can't go to the index , The optimizer thinks it's not as good as a full table scan c Fields come fast . So it's only used (a,b) Two indexes , It affects the efficiency of execution .

Actually , This scenario can be accessed through Modify the index order to abc_index:(a,c,b), You can use the index for all three index fields , It is suggested that the partners should not think about adding new indexes when they have problems , Wasting resources also increases server stress .

Sum up , If by adjusting the order , You can solve the problem or maintain one less index , So this order is often us DBA People need to give priority to the use of .

Four 、 Need you mark Knowledge points of

============================================================================

1、 How to sort through an ordered index , Avoid redundant execution order by

---

order by Use in select In the sentence , With sorting function . Such as ：

SELECT sex, price, name FROM LOL ORDER BY sex;

Is the table LOL Data in press “sex” A column sort .

And only when order by And where Sentences appear at the same time ,order by The sorting function of is invalid . let me put it another way ,order by When the fields in the execution plan take advantage of the index , No sorting operations . as follows SQL when , Can't press sex A column sort , because sex It's already in order .

SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;

therefore , Only order by Field appears in where When the condition is medium , To use the index of the field and avoid sorting .

For the above statement , The processing order of the database is ：