Summary of SQL statement efficiency issues

1. The principle of SQL optimization is:

Reduce the number of BLOCKs that need to be read in one operation to the minimum, that is, achieve the maximum data throughput in the shortest time.
Adjusting bad SQL can usually start from the following points:
Check the bad SQL and consider whether there is any optimization content in its writing method
Check the subquery Consider whether the SQL subquery can be rewritten using a simple connection method
Check the optimization index Use
Consider the optimizer of the database

2. Avoid SELECT * FROM table statements and make sure the fields you want to check are clear. ​​​​
​​​​
3. In a SQL statement, if a where condition filters more database records,

​​ and the more accurate the positioning, the more the where condition should be moved forward.​
​
4. Use index coverage as much as possible when querying. That is, a composite index is established on the SELECT field. In this way, only the index scan is performed during the query, and the data block is not read.

5. It is recommended not to use SELECT COUNT (*) and select top 1 statements when determining whether there are records that meet the conditions.​

​

6. Use the principle of inner qualification, when spelling out SQL statements, decompose and classify the query conditions,

​ and try to limit the innermost layer of the SQL statement to reduce the amount of data processing.​

​

7. You should absolutely avoid using expressions in order by clauses.​
​
8. If you need to read data from a related table, the number of related tables should generally not exceed 7.​
​
9. Use IN and OR carefully, and you need to pay attention to the amount of data in the In collection. It is recommended that the data in the collection does not exceed 200.

10. <> Replace with < , > , > with >=, < with <=, so that the index can be effectively used.​
​
11. When querying, try to reduce the reading of redundant data, including redundant columns and redundant rows.

12. Pay attention to the composite index. For example, when building the composite index, the order of the columns is F1, F2, F3,

. Then the order in which these fields appear in the where or order by clause must be the same as the order of the fields when building the index. Consistent,

and must contain the first column. It can only be F1 or F1, F2 or F1, F2, F3. Otherwise the index will not be used.

13. When performing multi-table related queries, the writing method must follow the following principles. This will help build indexes and improve query efficiency.

The format is as follows

select sum(table1.je) from table1 table1, table2 table2,

table3 where (equivalence condition of table1 (=)) and (table1’s Non-equivalent conditions)

and (table2 and association conditions of table1) and (equivalence conditions of table2) and (non-equivalence conditions of table2) and (association conditions of table3 and table2) and (equivalence conditions of table3) and (non-equivalence conditions of table3) .​

​ Note: The impact of the order of appearance of subsequent tables on efficiency during multi-table queries remains to be studied.​

​

14. Subquery problem. For functions that can be implemented using joins or views, do not use subqueries.

For example: select name from customer where

customer_id in ( select customer_id from order where money>1000).

should be replaced by the following statement: select name from customer

inner join order on customer.customer_id=order.customer_id

where order.money>100.​

​

15. In the WHERE clause, avoid the four arithmetic operations on columns,

Especially on the left side of the where condition, it is strictly prohibited to use operations and functions to process columns.

For example, in some places, substring can be replaced by like.​
​
16. If there is a not in (in) operation in the statement,

should be considered to rewrite it with not exists (exists). The best way is to use an outer connection to implement it.一 对17. The processing of a business process should make the shortage between the start and the end of the thing, the better. In principle, the reading operation of the database is completed. cross.

18. Please be careful not to use column functions and order by, group by, etc. on too many columns, and use disti software development with caution.​

​

19. Use union ​ all ​ instead of ​ union, the database performs a union operation,

​ First, execute the query on both ends of the union,

​ Put it in a temporary table, and then sort it to filter duplicate records.​

​ When the known business logic determines that there will be no duplicate records in query A and query B,

​ should use union ​ all instead of union to improve query efficiency.