A very interesting SQL optimization experience: from 30248.271s to 0.001s

Time:2019-10-20

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Source: toutiao.com/i66682753333034148356

Author | Java technology architecture

Background introduction

My database is mysql5.6. The following is a brief introduction to the following scenario:

Class Schedule Card:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Data 100

Student list:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

70000 pieces of data

Student transcript SC:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Data 70W

Query purpose:

Find candidates with 100 points in Chinese test

Query statement:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Execution time: 30248.271s

Why is it so slow? First, let’s view the following query plan:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

A very interesting SQL optimization experience: from 30248.271s to 0.001s

It is found that no index is used, and the type is all, so the first thought is to build an index. The fields to be indexed are of course the fields under the where condition.

First, index the C? ID and score of the SC table

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Execute the above query statement again, time: 1.054s

It’s more than 3W faster, which greatly shortens the query time. It seems that the index can greatly improve the query efficiency. It seems that it’s necessary to build an index. Many times, I forget to build an index. When the data volume is small, I don’t feel at all, which makes the optimization feel good.

But the time of 1s is too long. Can we optimize it? Look at the execution plan carefully:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

View the optimized SQL:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Add: here is a netizen asking how to view the optimized sentence

The method is as follows:

Execute in command window

A very interesting SQL optimization experience: from 30248.271s to 0.001s

A very interesting SQL optimization experience: from 30248.271s to 0.001s

There are type=all

According to my previous idea, the execution order of this SQL should be to execute the subquery first

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Time: 0.001s

The results are as follows:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

And then

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Time: 0.001s

This is quite fast. MySQL does not execute the inner layer query first, but optimizes the SQL to the exists clause, and presents the intent subquery.

MySQL is to execute the outer query first, and then the inner query, so it needs to loop 700007 * 11 = 770077 times.

What about using join queries instead?

A very interesting SQL optimization experience: from 30248.271s to 0.001s

In order to re analyze the connection query, first temporarily delete the index SC ﹣ C ﹣ ID ﹣ index, SC ﹣ score ﹣ index.

Execution time: 0.057s

Efficiency has improved. Look at the implementation plan:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

There is a situation of linked tables here. I wonder if I want to establish an index for the s Ou ID of the SC table.

CREATE index sc_s_id_index on SC(s_id);

show index from SC

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Executing connection query

Time: 1.076s. It’s even longer. What’s the reason? To view the execution plan:

![clipboard.png](/img/bVb
A very interesting SQL optimization experience: from 30248.271s to 0.001s
The query statement is:

! [picture uploading…]

It seems that the connection query is done first, and then the W
A very interesting SQL optimization experience: from 30248.271s to 0.001s
Execution plan:

! [picture uploading…]

This is where we did it first.
A very interesting SQL optimization experience: from 30248.271s to 0.001s
It is fixed. Let’s first look at the standard SQL execution order:

! [picture uploading…]

It’s very important to know the execution order. It’s suggested to get familiar with this table. At last, don’t memorize them, but understand them.

Under normal circumstances, join first and then where filter, but in our case, if join first, 70W pieces of data will be sent to join for operation, so it’s a wise scheme to execute where filter first. Now, in order to exclude the query optimization of MySQL, I write an optimized SQL myself.

A very interesting SQL optimization experience: from 30248.271s to 0.001s

That is to say, filter SC table first, and then connect tables. The execution time is 0.054s.

It’s about the same time as before when the s’u ID index was not built.

To view the execution plan:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

It is much more efficient to extract SC first and then connect tables. Now the problem is that there is a scan table when extracting SC, so it is clear that relevant indexes need to be established.

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Execute the query again:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Execution time: 0.001s, which is quite reliable, 50 times faster

Execution plan:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

We will see that the index is used to extract SC first and then connect tables.

Then run SQL

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Execution time 0.001s

Execution plan:

A very interesting SQL optimization experience: from 30248.271s to 0.001s

Here, MySQL optimizes the query statements, performs where filtering first, then connection operations, and uses indexes.

summary

1. The efficiency of MySQL nested subquery is really low.

2. It can be optimized into connection query

3. Establish appropriate index

4. Learn to analyze the SQL execution plan, MySQL will optimize the SQL, so it is very important to analyze the execution plan.

Recommended Today

Single and multiple buttons are styled with pictures

I’ve always seen people asking how to style and enlarge the buttons of radio buttons and multi buttons? Let’s share an example I did. 1. First make the button into a picture  2.html page Copy code The code is as follows: <!DOCTYPE HTML> <html> <head> <meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ /> <script type=”text/javascript” src=”jquery-1.10.2.min.js”></script> <script type=”text/javascript”> […]