Shulou(Shulou.com)06/02 Report--

This article mainly shows you the "what is the leftmost matching principle of the MySQL database index", the content is simple and clear, and I hope it can help you solve your doubts. Let the editor lead you to study and learn what is the leftmost matching principle of the MySQL database index.

one。 Joint index description

Establish a joint index of three fields

A joint index (a) is equivalent to establishing an index: (a), (b), (b)

two。 Can the index be used in ac?

First, it is concluded that a can hit the federated index, but c cannot, so the ac combination cannot hit the federated index.

1. Build abc federated index (province,city,district)

Ac index query

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

Shows that the query scope is ALL

two。 Directly set up the joint index of the two fields ac.

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

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

3.ab index query

The query scope is ref

Conclusion

Abc federated index, c in ac cannot hit the federated index of these three fields, a can, so the possible_keys column will show that the federated index is used.

three。 Thinking

Abc index, will acb go to the index?

According to the leftmost prefix matching principle, mysql will match to the right until it encounters a range query (>, 5 and daily6). If you build an index in the order of (aforce bforce c), d does not need an index, but if you build an index (a force bforce d d), you can use it all, and the order of a line bdepartment d can be adjusted at will.

= and in can be out of order, for example, indexing can be in any order, and mysql's query optimizer will help you optimize it into a form that the index can recognize.

four。 The cause of formation of the leftmost matching principle

Because a federated index, such as abc, can be understood as ordered and based on a, an is built on b and b is built on c, so it must be done in order.

It can be simply understood as: first, index a, then sort b on the basis of a, and then c

So when the federated index encounters a range query, the subsequent index will fail.

The above is all the content of the article "what is the leftmost matching principle of MySQL database index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.