SQL statement Efficiency

Post: blog. csdn. netyaowenbin528archive200910264728713. The principle of aspx1. SQL optimization is to minimize the number of blocks to be read by an operation, that is, to reach the maximum data throughput in the shortest time. To adjust a poor SQL statement, you can check for poor SQL statements and check whether the statements are correct.

Http://blog.csdn.net/yaowenbin528/archive/2009/10/26/4728713.aspx 1. The principle of SQL optimization is to minimize the number of blocks that an operation needs to read, that is, to reach the maximum data throughput in the shortest time. To adjust a poor SQL statement, you can check for poor SQL statements and check whether the statements are correct.

Post: http://blog.csdn.net/yaowenbin528/archive/2009/10/26/4728713.aspx

1. The principle of SQL optimization is:

Minimize the number of blocks to be read by an operation, that is, the maximum data throughput is reached in the shortest time.
You can adjust poor SQL statements from the following points:
Check for poor SQL statements and check whether there is any optimizable content in the statement.
Check whether the subquery considers whether the SQL subquery can be re-written by simple connection.
Check to optimize index usage
Database Optimizer

2. Avoid the SELECT * FROM table statement, and specify the fields.
3. In an SQL statement, if a where condition filters more database records, the query only performs index scanning without reading data blocks.
4. Try to overwrite the index when querying. Create a composite index for the SELECT field,

In this way, only index scanning is performed, and data blocks are not read.

5. We recommend that you do not use the select count (*) and select top 1 statements when determining whether there are any matching records.
6. Use the internal Limitation Principle to break down and classify query conditions when spelling SQL statements, and try to limit them at the innermost layer of SQL statements to reduce the amount of data processed.

7. Avoid using expressions in the order by clause.

8. If you want to read data from an associated table, the number of associated tables should not exceed 7.

9. Be careful when using IN and OR. Pay attention to the data volume In the IN set. We recommend that you set no more than 200 data entries.

10. <> Replace with <,>,> Replace with> =, <用<=代替，这样可以有效的利用索引。

11. When querying, minimize the amount of extra data read, including extra columns and extra rows.

12. Pay attention to composite indexes. For example, when a composite index is created, the column sequence is F1, F2, F3,

The order of these fields in the where or order by clause must be the same as that when the index is created,

And must contain the first column. It can only be F1, F1, F2, F1, F2, F3. Otherwise, the index is not used.

13. For multi-table join queries, you must follow the following principles. This helps you to create indexes and improve query efficiency.

The format is as follows:

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

Table3 table3 where (equivalent condition of table1 (=) and (non-equivalent condition of table1)

And (Association condition between table2 and table1) and (equivalent condition of table2) and (non-equivalent condition of table2)

And (Association conditions of table3 and table2) and (equivalent conditions of table3) and (non-equivalent parts of table3 ).
Note: The effect of the sequence of the tables following the from Statement on the efficiency of Multi-table queries remains to be studied.

14. subquery problems. Do not use subqueries for functions that can be implemented in connection or view mode.

Example: select name from customer where

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

Use the following statement instead: select name from customer

Inner join order on customer. customer_id = order. customer_id

Where order. money> 100.

15. Avoid the four arithmetic operations on columns in the WHERE clause,

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

For example, in some cases, substring can be replaced by like.

16. If there is a not in (in) operation in the statement,

We should consider using not exists (exists) for rewriting. The best way is to use external connections.

17. The shorter the time interval between the start and end of a business process, the better,

In principle, the read operations of the database are completed before the database write operations are completed at the end to avoid crossover.

18. Be careful not to use column functions, order by, group by, etc. for too many columns, and use disti software development with caution.

19. Replace union with union all. The database executes the union operation,

First, execute the query at both ends of union,

Put it in a temporary table and sort it to filter duplicate records.
When the known business logic determines that no duplicate records exist in query A and query B,

Union all should be used instead of union to improve query efficiency.