Good programmer big data parsing SQL optimization program refined solution ten, SQL optimization is an important topic to improve the efficiency of database operation, so first straight to the topic, share with you a few commonly used, but easy to ignore a few SQL optimization program is as follows:
Avoid null judgments
Avoid using null values for columns in the WHERE clause. Otherwise, the engine will abandon the use of indexes and perform a full table scan. It is best NOT to leave null values in the database, but to populate the database with NOT NULL values whenever possible.
Remarks, descriptions, comments, etc., can be set to NULL, and it is best not to use NULL. Do not make the mistake of assuming that NULL does not require space, as char(100) does, since space is fixed when the field is created. Whether or not a value is inserted (NULL is included), it takes up 100 characters of space. For varied-length fields such as VARCHar, NULL takes up no space. You can set the default value 0 on num to ensure that num is not null in the table.
Do not use select *
Using select * will increase the parsing time and will also query the data that is not needed at the same time, thus extending the data transmission time and consuming energy. For example, a field of the text type is usually used to store some complicated things. If you use select *, this field will also be queried.
Three, cautious use of fuzzy query
When a fuzzy match begins with %, the column index is invalidated. If it does not start with %, the column index is valid.
Do not use column numbers
Using column numbers adds unnecessary parsing time.
5. Use UNION ALL instead of UNION
Because a UNION compares records from a subset of queries, it is usually much slower than a UNION ALL. In general, use UNION ALL if it satisfies the requirements. Another case is if the business can ensure that no duplicate records will occur.
Avoid evaluating index fields in WHERE or ORDER by statements
After an operation is performed on an index column, the index is invalidated. The correct thing to do is to compute the value and pass it in.
7, Use not exist instead of not in
Select * from ‘not in’; select * from ‘not in’; The not extsts subquery can still be used for indexes on the table.
The difference between exist and in
In hashes an external table to an inner table, while exists loops an external table each time
The internal table is then queried. Therefore, in uses the outer index and EXISTS uses the inner table index. There is little difference between in and EXISTS if the two tables queried are of the same size. If one of the two tables is smaller and the other is larger, exists is used for the larger subtable and in is used for the smaller subtable.
Avoid the following operations on index columns
1. Avoid using IS NULL and IS NOT NULL on index columns.
2. Avoid casts on index columns. (For example, a field is a String and the parameter is an int.) If the preceding operation is performed on an index column, the index will be invalid, causing a full table scan.
Ten, complex operation can consider appropriate disassembly into a few steps
Sometimes there are examples of complex operations implemented by a SINGLE SQL statement, in which multiple levels of subqueries are nested. SQL performance problems occur. In this case, consider splitting the SQL into multiple SQL statements, or giving the program some of the work it can do.
Avoid null judgments
Avoid using null values for columns in the WHERE clause. Otherwise, the engine will abandon the use of indexes and perform a full table scan. It is best NOT to leave null values in the database, but to populate the database with NOT NULL values whenever possible.
Remarks, descriptions, comments, etc., can be set to NULL, and it is best not to use NULL. Do not make the mistake of assuming that NULL does not require space, as char(100) does, since space is fixed when the field is created. Whether or not a value is inserted (NULL is included), it takes up 100 characters of space. For varied-length fields such as VARCHar, NULL takes up no space. You can set the default value 0 on num to ensure that num is not null in the table.
Do not use select *
Using select * will increase the parsing time and will also query the data that is not needed at the same time, thus extending the data transmission time and consuming energy. For example, a field of the text type is usually used to store some complicated things. If you use select *, this field will also be queried.
Three, cautious use of fuzzy query
When a fuzzy match begins with %, the column index is invalidated. If it does not start with %, the column index is valid.
Do not use column numbers
Using column numbers adds unnecessary parsing time.
5. Use UNION ALL instead of UNION
Because a UNION compares records from a subset of queries, it is usually much slower than a UNION ALL. In general, use UNION ALL if it satisfies the requirements. Another case is if the business can ensure that no duplicate records will occur.
Avoid evaluating index fields in WHERE or ORDER by statements
After an operation is performed on an index column, the index is invalidated. The correct thing to do is to compute the value and pass it in.
7, Use not exist instead of not in
Select * from ‘not in’; select * from ‘not in’; The not extsts subquery can still be used for indexes on the table.
The difference between exist and in
In hashes an external table to an inner table, while exists loops an external table each time
The internal table is then queried. Therefore, in uses the outer index and EXISTS uses the inner table index. There is little difference between in and EXISTS if the two tables queried are of the same size. If one of the two tables is smaller and the other is larger, exists is used for the larger subtable and in is used for the smaller subtable.
Avoid the following operations on index columns
1. Avoid using IS NULL and IS NOT NULL on index columns.
2. Avoid casts on index columns. (For example, a field is a String and the parameter is an int.) If the preceding operation is performed on an index column, the index will be invalid, causing a full table scan.
Ten, complex operation can consider appropriate disassembly into a few steps
Sometimes there are examples of complex operations implemented by a SINGLE SQL statement, in which multiple levels of subqueries are nested. SQL performance problems occur. In this case, consider splitting the SQL into multiple SQL statements, or giving the program some of the work it can do.