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

This article will explain in detail the four SQL writing methods about the same logic of MYSQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

When it comes to complex queries, MYSQL headache journey begins, of course, the optimization method and other data monitoring is not different, MYSQL sentence optimization belongs to divergent thinking, as long as you can use the method can, but not limited to the database itself statement optimization. So the optimization of MYSQL seems to be an endless story.

Let's take a look at the writing of different statements that achieve the same result at the same time, and what is the difference in performance results.

Now there are two tables, an department table and an association table dept_emp between employees and departments.

Now, due to the abolition of the department, it is necessary to count which departments still have employees, and the departments with employees will be shown.

Of course, not to mention that the structure of the table and the performance comparison of the number of rows belong to hooliganism.

Here are two ways to write it

Select em.dept_name

From (select distinct dept_no from dept_emp) as de

Inner join departments as em on em.dept_no = de.dept_no

Select distinct em.dept_name

From dept_emp as de

Inner join departments as em on em.dept_no = de.dept_no

From the analysis of the above picture,

Select em.dept_name

From (select distinct dept_no from dept_emp) as de

Inner join departments as em on em.dept_no = de.dept_no

The way of writing is better than

Select distinct em.dept_name

From dept_emp as de

Inner join departments as em on em.dept_no = de.dept_no

With the blessing of related indexes, after the duplicate data is deduplicated in the query, the method of association is obviously better than the method of association first.

Then it's over. Is there any other way to write it? here's another way to write it.

Select em.dept_name

From departments as em

Inner join (

Select de.dept_no_d from (select distinct dept_no as dept_no_d from dept_emp) as de where de.dept_no_d in (select dept_no from departments)) as tm on em.dept_no = tm.dept_no_d

Can also achieve the same result, seemingly complex way of writing, in fact, is not slow

Do we have any other ways to write it, or to make the query faster in the way we just did?

Select distinct de.dept_name from departments as de where exists (select 1 from dept_emp em where de.dept_no = em.dept_no)

Finally, let's go through all the four writing methods, and compare the speed and consumption of the four methods through profile.

From the above analysis, the most important thing is to use in to query, and the best way to query is to use exists, the method of using JOIN is regular.

However, in the analysis of these four query methods, as well as the different effects, we can see

Select distinct de.dept_name from departments as de where exists (select 1 from dept_emp em where de.dept_no = em.dept_no)

Vs.

Select distinct em.dept_name

-> from dept_emp as de

-> inner join departments as em on em.dept_no = de.dept_no

The two methods are similar in the selected index and execution plan, so why is it faster to use exists's subquery here than to use join?

You can see that although the execution plan of the statement is the same, the difference is that the slow one uses Using temporary, that is, the search results are processed twice, and a deduplicated work is done, while the fast exists does not.

Then the question comes, isn't it that the subquery is slow, how the subquery is queried, but in fact why it is not slow in this example.

The MySQL subquery evaluates the query from outside to inside. That is, it first gets the value of the outer expression, then runs the subquery and captures the rows it generates. A useful optimization for a subquery is the "notify" subquery, where only those rows whose conditions of the internal expression are equal to those of the external expression can be optimized, pushing an appropriate equation down to the WHERE clause of the subquery.

It is written as follows

EXISTS (SELECT 1 FROM... WHERE external condition = internal condition)

The fast one in our example is exactly the same as this one. After the conversion, MySQL can use the push-down equation to limit the number of rows it must check to calculate the subquery. Remember that I wrote an article about ICP before, so I won't talk about push-down here.

Here to achieve ICP there is also a condition is that there can be no NULL value, that is, null, so this is also DBA painstaking communication with developers, saying that your field as far as possible do not have NULL had better have DEFAULT default value of a reason, because you do not know when your field because of the initial design of the NULL value, resulting in painstaking optimization halfway.

If there is a NULL value, the result is

EXISTS (SELECT 1 FROM... WHERE external condition = internal condition or internal condition is NUll)

Of course, this is nothing, MYSQL encountered NULL do not go to the index, I have also written an article, refuted the rumor.

The problem is that in the operation of or, you need to perform another table operation, and in addition, you cannot push it in ICP. The main reason is that NULL is not a FALSE but an unknown state in the database. ICP push down must be properly calculated and must be able to check whether SELECT has generated any rows, so that internal conditions = external conditions cannot be pushed down to the subquery.

So this is also why the family query is not slow, a factor of your slowness, do not think that the query is written the same, the result is the same, a variety of early attention, can cheat you.

On the same logic of MYSQL four SQL writing analysis is shared 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.