Leftmost prefix matching principle ： stay MySQL The leftmost prefix matching principle will be followed when establishing a federated index , I.e. top left priority , Match from the far left of the union index when retrieving data .
To understand the leftmost matching principle of union index , Let's first understand the underlying principles of indexing . At the bottom of the index is a B+ tree , So the bottom layer of the union index is a B+ tree , It's just a joint index B+ The key value is stored in the tree node . As a result of building a B+ The tree can only determine the index relationship based on one value , So the database relies on the leftmost field of the union index to build .
give an example ： Create a （a,b） Union index of , So its index tree looks like the figure below .
You can see a The values of are sequential ,1,1,2,2,3,3, and b There is no order for the values of 1,2,1,4,1,2. But we can find out a In the case of equivalence ,b Values are arranged in order , But the order is relative . that is because MySQL The rule for creating a union index is that the first left most field of the union index will be sorted first , Based on the sorting of the first field , Then sort the second field . therefore b=2 There is no way for this query condition to take advantage of the index .
Because the whole process is based on explain Result analysis , So let's get to know explain In type Fields and key_lef field .
1.type： Connection type . Various connection types are shown below , Sort by best to worst :（ Focus ref,rang,index）
system： Table has only one row of records （ Equal to system table ）, This is const Special case of type , Not usually , Negligible
const： Indicates that it is found once through index ,const For comparison primary key perhaps unique Indexes . Because only one row of data needs to be matched , All very fast . If the primary key is placed in where In the list ,mysql You can convert the query to a const
eq_ref： Unique index scan , For each index key , Only one record in the table matches it . Common in primary key or Unique index scan .
be careful ：ALL The tables scanned by the whole table record the least, such as t1 surface
： Non unique index scan , Returns all rows matching a single value . In essence, it is also an index access , It returns all rows that match a single value , However, he may find multiple qualified lines , So it should be a mixture of search and scan .
： Retrieve only rows in the given range , Use an index to select rows .key Column display uses that index . It's usually in where Statement bettween,<,>,in Etc . The range scan on this index column is better than the full index scan . Just start at a point , End at another point , Do not scan all indexes .
：Full Index Scan,index And ALL The difference is index Type traverses index tree only . This is usually ALL block , Expected index file is usually smaller than data file .（Index And ALL Even though they read the whole table , but index Is read from index , and ALL Read from hard disk ）
ALL：Full Table Scan, Traverse the entire table to find matching rows
display MySQL The length of the index actually used . If the index is NULL, Then the length is NULL. If not NULL, Is the length of the index used . So from this field, we can infer which index is used .
Calculation rules ：
1. Fixed length field ,int occupy 4 Bytes ,date occupy 3 Bytes ,char(n) occupy n Characters .
2. Variable length field varchar(n), Occupied n Characters + Two bytes .
3. Different character sets , The number of bytes a character takes is different .Latin1 Coded , One character takes one byte ,gdk Coded , One character takes two bytes ,utf-8 Coded , One character takes three bytes .
（ Because my database uses Latin1 Encoding format , So in the later calculation , One character is counted by one byte ）
4. For all index fields , If set to NULL, Then we need to 1 Bytes .
Let's get to the point !!!
Create a table first
In this table id column .name column .age Column establishes a union index
id_name_age_index, It's actually three indexes （id）（id_name）（id_name_age）.
Here are a few situations in which this index may be used ：
1. Full value matching query
It can be seen from the above results ,where Later query criteria , Whether used （id,age,name）（name,id,age） still （age,name,id） order , Federated indexes are used in queries , Some students may be confused , Why the following two search criteria do not match from left to right according to the joint index , But it also uses union index ?
that is because MySQL Query optimizer in explain
, therefore sql The order of the fields in the statement does not need to be the same as that defined by the union index , The query optimizer will determine to correct this SQL In what order statements are executed efficiently , Finally, the real execution plan can be generated , So federated indexes can be used in any order . In addition, by observing the key_len field , You can also specify the （id_name_age） Indexes , because id by int type , allow null, So the 5 Bytes ,name by char(10), allow null, What we use again is latin1 code , So the 11 Bytes ,age by int Type permission null, So it also takes up 5 Bytes , So the index length is 21（5+11+5）, And up there key_len The value of is exactly 21, Demonstrable （id_name_age） Indexes .
2. When matching the leftmost column
The search follows the leftmost matching principle , adopt key Fields are also known , Union index used during search , And it uses the （id） Indexes , because key_len The field value is 5, and id The length of the index is exactly 5（ because id by int type , allow null, So the 5 Bytes ）.
because id reach name From left to right , The values in both fields are ordered , So we also follow the leftmost matching principle , adopt key Field known , Federated indexes are also used in the search process , But it uses the （id_name） Indexes , because key_len The field value is 16, and (id_name) The length of the index is exactly 16（ because id by int type , allow null, So the 5 Bytes ,name by char(10), allow null, What we use again is latin1 code , So the 11 Bytes ）.
Because the above three searches are all from the far left id Start matching right , So it's all used id_name_age_index Joint index .
Well, if it doesn't match in turn ?
adopt key Field known , Federated indexes are also used in the search process , But it uses the （id） Indexes , from key_len Fields are also known . Because the federated index tree is based on id Created by field , but age be relative to id It's out of order , only id Only sequential , So he can only use the id Indexes .
Through observation, we found that key Field discovery is also used in search id_name_age_index Indexes , Many students may wonder that it doesn't follow the principle of leftmost matching , The index will fail by reason , Why the federated index is also used ? Because I didn't id Start matching , And name It's out of order on its own , So it does not follow the leftmost matching principle , However, from type Field known , Although it uses union index , But it scans the entire index tree , Exactly match to the index , Nothing to do with the leftmost matching principle , Generally, as long as it is part of a joint index , But it doesn't follow the leftmost matching principle , Will probably adopt index Scan by type , But its efficiency is far less than that of the most matching principle ,index The scan method of type type is to search one field by one from the first field of index , Until a matching index is found , And all The difference is ,index Scan all index trees , and all It is a full table scan of the data of the entire disk .
These two results are the same as the above , Because they don't match from the far left , So there's no union index , All of them are index Full index scan .
3. Match column prefix
If id It's character , Then prefix matching uses index , The middle drop and suffix are full table scans .
select * from staffs where id like 'A%';// Prefixes are all ordered , Using federated indexes select * from
staffswhere id like '%A%';// Full table query select * from staffs where id like '%A';// Full table query
4. Match range value
Encountered during match <>= Number , Will stop matching , but id It's orderly , So through possible_keys Fields and key_len
Field known , In this search process, the id Indexes （ because id by int type , allow null, So the 5 Bytes ）, And what's going on is rang Range query .
Because the leftmost matching principle is not followed , And id<4 In the scope of ,age It's out of order , So what we use is index Full index scan .
Do not follow the leftmost matching principle , But in the database id<2 There's only one （id）, So in id<2 In the scope of ,age It's orderly , So what we use is rang Range query .
Do not follow the leftmost matching principle , and age It's disordered again , So the full index scan .
5. Match the first column exactly and range to another column
Because there are id=1, So in id In scope age It's out of order , So only the id Indexes .