30 SQL query optimization principles

From: http://www.cnblogs.com/cpcpc/archive/2011/02/28/2123047.html

 

 In our ordinarySQLIn the query, we actually have a lot of principles that should be paid attention to, since the implementation of SQL queryOptimizationThis article will introduce 30 query optimization principles.

Principles that should be paid attention to first

1. To optimize the query, try to avoid full table scanning.Create an index on the columns involved in where and order.

2. AvoidThe where clause determines the null value of the field. Otherwise, the engine will discard the index and perform a full table scan. For example, select ID from t where num is null. The default value 0 can be set on num, make sure that the num column in the table does not have a null value, and then query: Select ID from t where num = 0

3. AvoidUse in the WHERE clause! = Or <> operator. Otherwise, the engine will discard the index for full table scanning.

4. AvoidUse or in the WHERE clause to connect conditions. Otherwise, the engine will discard the index and perform full table scanning. For example, select ID from t where num = 10 or num = 20 can be queried as follows: select ID from t where num = 10 Union all select ID from t where num = 20

5. In andNot in should also be used with caution, otherwise it will cause a full table scan, such as: Select ID from t where num in (, 3) for continuous values, you can use between to do not use in: select ID from t where num between 1 and 3

6. The following query will also cause a full table scan:Select ID from t where name like '% ABC %' to improve efficiency, you can consider full-text retrieval.

7. IfUsing parameters in the WHERE clause also results in full table scanning. Because SQL only parses local variables at run time, but optimizesProgramThe access plan cannot be postponed to runtime; it must be selected during compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection. For example, the following statement will scan the entire table: Select ID from t where num = @ num can be changed to force query using index: Select ID from T with (index name )) where num = @ num

8. AvoidThe where clause performs expression operations on fields, which causes the engine to stop using indexes for full table scanning. For example, select ID from t where num/2 = 100 should be changed to: Select ID from t where num = 100*2

9. AvoidThe where clause performs function operations on fields, which causes the engine to stop using indexes for full table scanning. For example, select ID from t where substring (name, 1, 3) = 'abc' -- name ID starting with ABC select ID from t where datediff (day, createdate, '2017-11-30 ') = 0 -- '2017-11-30' the generated ID should be changed: select ID from t where name like 'abc % 'select ID from t where createdate> = '2017-11-30' and createdate <'2017-12-1'

10. Do notFunction, arithmetic operation, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

Principles for Indexing

11. when using an index field as a condition, if the index is a composite index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used, and the field order should be consistent with the index order as much as possible.

12. Do not write meaningless queries. If you need to generate an empty table structure:Select col1, col2 into # T from t where 1 = 0CodeIf no result set is returned, but system resources are consumed, change it to create table # T (...)

13. Many timesExists is a good choice to replace in: Select num from a where num in (select num from B) Replace with the following statement: select num from a where exists (select 1 from B where num =. num)

14. not all indexes are valid for queries. SQL queries are optimized based on table data, when there is a large amount of data in the index column, the SQL query may not use the index. For example, the table contains almost half of the sex field, male and female fields, then, even if an index is created on sex, the query efficiency will not be affected. 15. the more indexes, the better. Although the index can improve the efficiency of the SELECT statement, it also reduces the efficiency of insert and update, because the insert or update statements may recreate the index, therefore, you need to carefully consider how to create an index, depending on the actual situation. It is recommended that the number of indexes in a table be no more than 6. If there are too many indexes, consider whether the indexes on some columns that are not frequently used are necessary.

16. update should be avoided as much as possibleClustered index data column, because the order of the clustered index data column is the physical storage order of the table records. Once the column value changes, the order of the entire table record will be adjusted, it will consume a considerable amount of resources. If the application system needs to frequently update the clustered index data column, consider whether to create the index as a clustered index.

17. use numeric fields whenever possible. If fields containing only numerical information are not designed as numeric fields, this will reduce query and connection performance and increase storage overhead. This is because the engine compares each character in the string one by one during query and connection processing, and only one comparison is required for the number type.

18. Try to useVarchar/nvarchar replaces Char/nchar, because the storage space of the variable-length field is small, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

19. Do not use it anywhereSelect * from T, replace "*" with a specific field list. Do not return any fields that are not used.

Considerations for temporary tables and cursors

20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index ).

21. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

22. Temporary tables are not unavailable. Using them appropriately can make some routines more effective. For example, when you need to reference large tables or a data set in common tables repeatedly. However, it is best to use the export table for one-time events.

23. When creating a temporary table, you can useSelect into replaces create table to avoid creating a large number of logs to increase the speed. If the data volume is not large, create table first and then insert to ease the system table resources.

24. If temporary tables are used, you must explicitly delete all temporary tables at the end of the stored procedure.Truncate table, and then drop table, so that the system table can be locked for a long time.

25. Avoid using a cursor whenever possible, because the efficiency of the cursor is poor, if the cursor operation data exceedsIf there are 10 thousand rows, you should consider rewriting.

26. before using the cursor-based or temporary table method, you should first find a set-based solution to solve the problem. The set-based method is generally more effective.

27. Like a temporary table, the cursor is not unavailable. Small DatasetsFast_forward cursors are generally better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. A routine that includes "sum" in the result set is usually faster than a cursor. If the development time permits, you can try both the cursor-based method and the set-based method to see which method works better.

28. set at the beginning of all stored procedures and triggersSet nocount on. Set nocount off at the end. You do not need to send the done_in_proc message to the client after executing each statement of the stored procedure and trigger.

29. Avoid large transaction operations as much as possible to improve the system concurrency capability.

30. Avoid returning a large amount of data to the client as much as possible. If the data volume is too large, consider whether the corresponding requirements are reasonable.

the above Article only mentions some of the things I 've learned in learning SQL query optimization principle. I hope that the majority of the experts will not be able to give me some advice.