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

This article introduces how to treat the MYSQL index, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

Let's look at a watch first.

Select * from employees where first_name = 'Kyoichi' and last_name =' Maliniak'

According to the general idea, we decide whether to build an index according to the amount of data, and in view of this situation, which index is better.

Let's do an experiment and set up three indexes for this query.

They are first_name last_name first_name_lat_name

What you can see is that the query executor chose a federated index like first_name_last_name

Why? we go on with questions.

I will delete this federated index here.

We can see what the result is, and the result is an index called intersect.

We continue to look at the questions (don't worry, your questions will be summarized and revealed at the bottom)

We delete the index IX_LAST_NAME and then continue with the query

OK, at this point, I think it's time to summarize.

Question 1, which index is better?

Question 2 whether it is better to index the penal intersect or to combine the index.

Question 3, why did you delete LAST_NAME instead of FIRST_NAME

With these three questions, we continue our journey.

When it is better to mention that kind of index, we should know exactly what effect these indexes have on the query.

The following numbers reflect the cost value of the query

0.00081800 | select * from employees where first_name = 'Kyoichi' and last_name =' Maliniak' (composite index)

0.00145100 | select * from employees where first_name = 'Kyoichi' and last_name =' Maliniak' (intersect index)

0.00250350 | select * from employees where first_name = 'Kyoichi' and last_name =' Maliniak' (using a separate first_name index)

From the above values, we can see very clearly

After careful examination of Sending data, the specific cost mainly occurs at this stage of sending data. Although the consumption of another location can be different at this level of subtlety, it is true that system lock this thing, by comparison, composite index and intersect are not comparable.

So in this comparison, it can be said that the composite index wins.

So that's the end of the story? Nein Nein Nein (I like Tomas very much, you know this stem)

Let's go ahead and build an index IX_first_name_last_name_hire_date.

Because we're going to use ORDER BY

We can see clearly from the diagram that our query does not go to filesort but directly to the index. This efficiency is naturally much better than that of filesort, especially in the case of a large amount of data.

But, to say the important thing three times, this query is actually the same as above, but the difference is that your query condition is changed to LIKE, although you also go to the index, but in the end, you don't take our using index condition only, you still bring filesort.

WHY

Let's analyze that the type in using index condition only is ref, while the one in filesort is range. Let's take a look at profiling. The first one is range followed by an equivalent query. We can clearly see that if we take range, we need one more step, creating sort index, and the consumption is not low. It can be said to account for 90% of the total query consumption, so that sentence can be sorted without sorting, which is still applicable to MYSQL 5.7.

In addition, the illusion to be exposed this time is that even if you create an index and consider adding the fields applicable to order by to the index, in some queries, he also wants to filesort when ASC, instead of saying to some people on the Internet, if you add the fields of ORDER BY to the index, you can not be in FILESORT (ASC). This is not true.

On how to view the MYSQL index to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.