Reference: https://mp.weixin.qq.com/s/NJW8_Rjd-dCSmGAZ-vMGZQ
The database used is mysql5.6, the following is a brief introduction to the scenario
The curriculum:
Article 100 the data
The student table:
Article 70000 the data
Student Transcript SC:
Article 70 w
Query Purpose:
Find candidates with 100 points in Chinese test
Query statement:
Execution time: 30248.271s
Why so slow? Let’s first look at the query plan:
Select * from ‘where’; select * from ‘where’; select * from ‘where’;
Select * from sc where c_id = score
Execute the above query statement again in 1.054 seconds
Fast 3W times, greatly shorten the query time, it seems that the index can greatly improve the query efficiency, it seems that it is necessary to build the index, many times forget to build the index, when the data volume is small, there is no feeling at all, this optimization feeling is cool.
But 1s is still too long, can we still optimize it? Take a closer look at the execution plan:
View the optimized SQL:
How to view the optimized statement
The method is as follows:
Execute in the command window
A type = all
In my previous thinking, the order of execution of this SQL would be to execute the subqueries first
Time: 0.001 s
The results are as follows:
And then execute
Time: 0.001 s
Instead of executing the inner query first, Mysql optimizes the SQL into an EXISTS clause and produces EPENDENT SUBQUERY.
Mysql > execute outer layer query first, then inner layer query, so that the loop 70007*11=770077.
What about joining queries instead?
In order to re-analyze the connection query, delete the indexes sc_C_ID_INDEX and sc_score_index temporarily
The execution time is 0.057 seconds
Efficiency has improved. Look at the execution plan:
Select * from sc where s_id = 1
CREATE index sc_s_id_index on SC(s_id);
show index from SC
A join query is being executed
Time: 1.076s, unexpectedly the time also became longer, what is the reason? View the execution plan:
The optimized query statement is as follows:
The join query is performed first and then the WHERE filter is performed
Back to the previous execution plan:
SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL
Normally, join is followed by WHERE filtering, but in our case, if join is first, 70W data will be sent to join for operation, so where is executed first
Filtering is a wise solution, now in order to eliminate mysql query optimization, I write an optimized SQL
That is, filter sc tables first and then join sc tables. The execution time is 0.054 seconds
That’s about the same time as before there was no s_ID index
View the execution plan:
Extracting sc first and then connecting tables is much more efficient. The problem now is that scanning tables appear when extracting SC, so it is clear that relevant indexes need to be established
Then execute the query:
Execution time: 0.001s, this time is quite reliable, 50 times faster
Execution Plan:
We’ll see that the index is used to extract the SC and then the table.
So let’s do the SQL again
Execution time: 0.001s
Execution Plan:
SQL > select * from ‘where’; SQL > select * from ‘where’; SQL > select * from ‘where’;
1, mysql nested subquery efficiency is indeed low
2. It can be optimized into join queries
3. Build appropriate indexes
4, learn to analyze the SQL execution plan, mysql will optimize SQL, so it is important to analyze the execution plan