Applications are as slow as cattle , There are many reasons , It may be the network , It may be due to the system architecture , It may also be the database .
So how to improve the database SQL What about the execution speed of statements ? Some people would say that performance tuning is a database administrator （DBA） About , However, performance tuning also has a lot to do with programmers .
Lines embedded in a program SQL sentence , If some optimization tips are used , It will achieve twice the result with half the effort .
skill 1 Comparison operators can be used “=” No “<>”
“=” Increases the probability of using the index .
skill 2 Knowing that there is only one query result , Then please use “LIMIT 1”
“LIMIT 1” Full table scanning can be avoided , If you find the corresponding result, you won't continue scanning .
skill 3 Select the appropriate data type for the column
Can use TINYINT No SMALLINT, Can use SMALLINT No INT, You know the truth , The smaller the disk and memory consumption, the better .
skill 4 Will be big DELETE,UPDATE or INSERT The query becomes multiple small queries
Can write dozens of lines , Hundreds of lines SQL Does the sentence seem to be highly qualified ? however , In order to achieve better performance and better data control , You can turn them into multiple small queries .
skill 5 use UNION ALL replace UNION, If the result set allows duplication
because UNION ALL No weight removal , Higher efficiency UNION.
skill 6 Multiple executions to get the same result set , Please keep SQL Consistent statement
The purpose of this is to make full use of the query buffer .
For example, according to region and product id Query product price , Used for the first time ：
So the second time the same query , Please keep the consistency of the above statements , For example, don't where In the statement id and region Position exchange sequence .
skill 7 Try to avoid using “SELECT *”
If you do not query all the columns in the table , Try to avoid using SELECT
*, Because it does a full table scan , Index cannot be used effectively , It increases the burden of database server , And the network between it and the application client IO expenses .
skill 8 WHERE The columns in the clause should be indexed as much as possible
just “ as far as possible ” oh , Not all columns . suit one's measures to local conditions , Adjust according to the actual situation , Because sometimes too many indexes will also reduce performance .
skill 9 JOIN The columns in the clause should be indexed as much as possible
Also just “ as far as possible ” oh , Not all columns .
skill 10 ORDER BY The columns are indexed as much as possible
ORDER BY If the column is indexed , The performance will also be better .
skill 11 use LIMIT Implement paging logic
Not only improves performance , At the same time, unnecessary network transmission between database and application is reduced .
skill 12 use EXPLAIN Keyword to view the execution plan
EXPLAIN You can check index usage and scanned rows .
other
SQL There are many tuning methods , There are many different query methods for the same query result .
In fact, the best way is to test in the development environment with the closest real data set and hardware environment , Then publish to the production environment .
Technology
Daily Recommendation