The optimization of MySQL joint index and Where clause improves the efficiency of database operation
- 2020-05-12 06:21:57
- OfStack
Since the launch of the website system, the amount of data has reached 500M unconsciously, and nearly 8W has been recorded. Related to basic database operations are becomes slow, with people would think that is a fire ~ ~ and then stick it in group 1, including the machine configuration of what one says, soon a group of friends, and help me to determine the problem is not the machine configuration, "shenzhen - gunmen" warm-hearted man his machine 512 memory best W data also run fast, even with those few W block machine 1 sample (blow too much), ha ha ~ ~ ~
Under the guidance of the analysis of group friends, I tried to remove 1 by 1 to do the test, and found that the problem was in the sorting part. When the sorting was removed, the execution time changed from 48 seconds to 0.3x seconds, which is a change of what level.
So I put the fields involved in sorting into a joint index, alter table xx add index indexname(x1,x2,x3), and after 2 minutes of creating a new index, I executed the same SQL statement, wow, 0.28S... shuang
Therefore, some other commonly used SQL were optimized according to the same idea, and the effect was immediately effective
After 30 minutes, I checked the slow sql record file again, but it was not good. I found that a good SQL had become grey and slow. What happened?
After several analyses and tests, it turned out that the reason was the addition of the joint index, and there was an or in the SQL statement. When the or was changed to union, the problem was eliminated.
This time, I have a new idea: when writing SQL, don't leave it at 1, just write OK, it will bring very serious consequences.
Attached is a further paragraph on the order in which Where clauses are executed:
When I used MySQL to query the database, I connected many applications and found it was very slow. Such as:
SELECT... WHERE p. languages_id = 1 AND m. languages_id = 1 AND t. languages_id = 1 AND p. products_IN IN (472,474)
This query takes more than 20 seconds, although each field is indexed. Through the analysis of Explain SQL1, it was found that tens of thousands of pieces of data were returned in the first analysis:
WHERE p.languages_id = 1, and then, in turn, narrow it down according to the conditions.
After I changed the position of the WHERE field by 1, the speed was significantly improved:
WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND m.languages_id = 1 AND t.languages_id = 1
In this way, the first condition is p.products_id IN (472,474), which returns less than 10 results, and then it needs to filter according to other conditions, which naturally improves the speed greatly.
After practice, it is found that you should not think that the order of the fields in WHERE does not matter, but you can put them wherever you like. You should filter out most of the useless data as soon as possible the first time, and only return the minimum range of data.
I hope I can help my friends who are in the same situation.
Under the guidance of the analysis of group friends, I tried to remove 1 by 1 to do the test, and found that the problem was in the sorting part. When the sorting was removed, the execution time changed from 48 seconds to 0.3x seconds, which is a change of what level.
So I put the fields involved in sorting into a joint index, alter table xx add index indexname(x1,x2,x3), and after 2 minutes of creating a new index, I executed the same SQL statement, wow, 0.28S... shuang
Therefore, some other commonly used SQL were optimized according to the same idea, and the effect was immediately effective
After 30 minutes, I checked the slow sql record file again, but it was not good. I found that a good SQL had become grey and slow. What happened?
After several analyses and tests, it turned out that the reason was the addition of the joint index, and there was an or in the SQL statement. When the or was changed to union, the problem was eliminated.
This time, I have a new idea: when writing SQL, don't leave it at 1, just write OK, it will bring very serious consequences.
Attached is a further paragraph on the order in which Where clauses are executed:
When I used MySQL to query the database, I connected many applications and found it was very slow. Such as:
SELECT... WHERE p. languages_id = 1 AND m. languages_id = 1 AND t. languages_id = 1 AND p. products_IN IN (472,474)
This query takes more than 20 seconds, although each field is indexed. Through the analysis of Explain SQL1, it was found that tens of thousands of pieces of data were returned in the first analysis:
WHERE p.languages_id = 1, and then, in turn, narrow it down according to the conditions.
After I changed the position of the WHERE field by 1, the speed was significantly improved:
WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND m.languages_id = 1 AND t.languages_id = 1
In this way, the first condition is p.products_id IN (472,474), which returns less than 10 results, and then it needs to filter according to other conditions, which naturally improves the speed greatly.
After practice, it is found that you should not think that the order of the fields in WHERE does not matter, but you can put them wherever you like. You should filter out most of the useless data as soon as possible the first time, and only return the minimum range of data.
I hope I can help my friends who are in the same situation.