Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about why MySQL occasionally chose the wrong index. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Before that, I have done a lot of ToC projects. In the application scenario of ToC, the business is generally relatively simple, basically there are not many complex queries (basically, as long as the user ID is established as the index, the query efficiency can be greatly improved.) In the past two years, I have gradually come into contact with some ToB business and found that the business of ToB is really more complicated than that of ToC. To take a simple example, the most painful thing in ToB applications is the organizational structure. Originally, querying a person's data may become querying the data of a group, a department, or even a branch.

Not only that, the query rights of employees of different ranks may be different. The query conditions are much more complex than in the ToC scenario, so sometimes a table creates many different indexes. Later, we will find that how to query inexplicably becomes very slow. According to reason, if we hit the index we want, it should be right soon.

So, we analyzed the Sql statement and found that Mysql uses another index, but in this business, using another index will get better results, why did Mysql choose the wrong index? Obviously, it is difficult for storage to understand the actual situation of the business, and Mysql also needs certain algorithms to evaluate the advantages and disadvantages of the index. Mysql scores in this way.

The first principle of Mysql's scoring of the index is that the difference of the index is the greatest. For example, if it is a primary school information query system, and we build the index based on the date of birth, then there will be about 365-7 different values. If we take the gender of the student as the index, then there will basically be only two different values. If a query condition includes both the date of birth and gender. Then Mysql must first choose a larger cardinality as the index, that is, the date of birth as the index.

But Mysql doesn't really understand what is the date of birth and what is gender. Which is the larger base they judge? It's very simple. Don't you know the result by scanning the index? We only need to scan the index tree to know how many different Key there are. However, if we have more and more data, it is not realistic to scan all the index trees every time. Based on the fact that most Internet applications read more and write less, Mysql records the score of the index for a period of time, but it still takes a lot of time each time it triggers a reevaluation.

Mysql adopts the way of sampling survey, randomly takes a certain number of pages from each index tree, and evaluates the index by counting these pages. Now back to our real development, I don't know if you have ever encountered such a problem, some abnormal states account for a very small number of total orders, for example, returned orders account for only a small number of total orders, but you hit this index when you use Mysql query. It is because when Mysql evaluates the score, most of the time it feels that the amount of different data on this index is very small, so it gets a low score.

This is why MySQL occasionally chooses the wrong index. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.