The leftmost matching principle of MySQL database index

1. Joint index description

Create a joint index of three fields

The joint index (a,b,c) is equivalent to creating the index: (a), (a,b), (a,b,c)

2. Can ac use indexes?

先給出結論：a可以命中聯合索引（a,b,c），c無法命中，所以ac組合無法命中聯合索引。

1. Create an abc joint index (province, city, district)

ac index query

SELECT * FROM user_address WHERE province = 'Guangdong' 
AND district = 'Nanxiong City'

Display query range is ALL

2. Create a joint index for the two fields ac directly

SELECT * FROM user_address WHERE province = 'Guangdong' 
AND district = 'Nanxiong City'

The query result is that ref uses the index, and the number of rows scanned has also changed from 21 to 13.

3.ab index query

The query scope is ref

in conclusion

abc joint index, c in ac cannot hit the joint index of these three fields, but a can hit it, so the possible_keys column will show that the joint index is used.

3. Thinking

abc index, will acb follow the index?

• According to the leftmost prefix matching principle, MySQL will keep matching to the right until it encounters a range query (>, <, between, like) and stops matching.
• For example, if a=3 and b=4 and c>5 and d=6, if you create an index in the order of (a, b, c, d), d will not be used. If you create an index in the order of (a, b, d, c), all of them can be used, and the order of a, b, d can be adjusted arbitrarily.
• = and in can be in any order, for example, a=1 and b=2 and c=3. The (a, b, c) index can be created in any order. The MySQL query optimizer will help you optimize it into a form that the index can recognize.
  

4. The cause of the leftmost matching principle

• Because the joint index, such as abc, can be understood as ordered and the basis of its formation is built on a, from a to build b, and from b to build c, so it must be in order
• It can be simply understood as: first sort a and create an index, then sort b based on a, and then sort c.
• Therefore, when a joint index encounters a range query, the subsequent indexes will become invalid.
  

This is the end of this article about the leftmost matching principle of MySQL database index. For more relevant MySQL index leftmost matching content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!