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

Today, Xiaobian to share with you the relevant knowledge points in mysql, detailed content, clear logic, I believe most people still know too much about this knowledge, so share this article for everyone's reference, I hope you have gained something after reading this article, let's learn about it together.

Use of index in mysql: 1. If the index can be used normally under the premise of conforming to the leftmost principle during in query;2. If there is a range query in front of the query, then the joint index is invalid and the index will not be used;3. When the type of query value is int and the type of column is vachar, the index will be invalid.

Operating environment of this tutorial: Windows 10 system, mysql version 5.7.17, Dell G3 computer.

Does MySQL use indexes?

1. In mysql version 5.7.17,in queries can use indexes

+-----------+| version() |+-----------+| 5.7.17 |+-----------+

2. The value type of the query is int, and the type of the column is vachar, which will cause the index to fail.

3, the amount of data is too large (200w) and in many conditions, this should be Mysql optimizer to choose their own judgment, when the amount of data is large, in the query index may not be the best. (PS: In this part, we should consider the judgment of mysql's built-in optimizer. The execution principle of mysql's optimizer is quite complicated. In short, it is to judge various conditions and select the optimal solution that mysql thinks.)

So will in queries work for fields that join the union index?

First of all: for the joint index, we want to clarify a concept, for the joint index, the execution order is matched from left to right, which is equivalent to order by id,name, etc. The first field must be ordered. If you use the index of the second field, you must use the first field first, and you must ensure that the second field is ordered.

Second: the leftmost prefix matching principle, a very important principle, mysql will always match to the right until it encounters a range query (>,"3" and d = "4". If the index is established in the order of (a,b,c,d), d is not indexed, because the c field has a range query, and the joint index is invalid. If the index of (a,b,d,c) is established, it can be used, and the order of a,b,d can be adjusted arbitrarily.

The result is the same as the concept above, if the in query is in accordance with the leftmost principle, it is a normal index. However, if there is a range query in front of the in query, then the joint index fails, and naturally our in query does not need the index.

The above is all the content of this article, thank you for reading! I believe everyone has a great harvest after reading this article. Xiaobian will update different knowledge for everyone every day. If you want to learn more knowledge, please pay attention to 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.