30 SQL query optimization principles

In our ordinarySQLIn the Query Process, we actually have many principles that should be paid attention.SQLQueriedOptimization, This article will introduce you30Query Optimization Principles.

Principles that should be paid attention to first

1. optimize queries and avoid full table scans, first, you should consider creating columns involved in where and order by . index.

2. avoid null In the where clause. value judgment, otherwise, the engine will discard the index and perform a full table scan, for example: select ID from t where num is null On num set the default value 0 , make sure that the num column in the table does not have the null value, and then query it as follows: select ID from t where num = 0

3.AvoidWhereClause! =Or<>Operator. Otherwise, the engine will discard the index for full table scanning.

4.AvoidWhereClauseOrOtherwise, the engine will discard the index and perform a full table scan, for example:Select ID from t where num = 10 or num = 20You can query it as follows:Select ID from t where num = 10 Union all select ID from t where num = 20

5. InAndNot inUse it with caution. Otherwise, full table scan may occur, for example:Select ID from t where num in (1, 2, 3)Continuous values can be usedBetweenDo not useInNow: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 search.

7. if you use parameters in the where clause, full table scan is also performed. Because SQL parses local variables only at runtime, however, the optimization Program does not delay the selection of the access plan 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 performs a full table scan: select ID from t where num = @ num can be changed to force the query to use the index: select ID from T with (index ( index name )) where num = @ num

8. avoid performing expression operations on fields in the where clause, this will cause the engine to stop using the index 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. do not perform function operations on fields in the where clause, this will cause the engine to stop using the index for full table scanning. For example, select ID from t where substring (name, 1, 3) = 'abc' -- name ID select starting with ABC ID from t where datediff (day, createdate, '2017-11-30 ') = 0 -- the id generated by '2014-11-30 ' should be changed to : select ID from t where name like 'abc % 'select ID from t where createdate> = '2017-11-30' and createdate <'2017-12-1 '

10.Do notWhereClause"="Perform functions, arithmetic operations, or other expression operations on the left. 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 = 0This typeCodeIf no result set is returned but system resources are consumed, change it to the following:Create Table # T (...)

13. In is often replaced by exists . good choice: 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 optimizes queries based on table data, when a large amount of data is duplicated in the index column, the SQL query may not use the index, for example, the table has a field sex , male and female are almost half of each other, therefore, even if you create an index on sex , it does not play a role in query efficiency. 15. the more indexes, the better. indexes can improve the efficiency of select , however, it also reduces the efficiency of insert and Update , because insert or Update may re-create an index, therefore, you need to carefully consider how to create an index, depending on the actual situation. The number of indexes in a table cannot exceed 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 possibleClusteredIndex data columns becauseClusteredThe order of index data columns is the physical storage order of table records. Once the column value changes, the order of the records in the entire table is adjusted, which consumes considerable resources. If the application system needs to be updated frequentlyClusteredIf you want to create an indexClusteredIndex.

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/nvarcharReplaceChar/ncharBecause the storage space of variable-length fields is small, it can save storage space. Secondly, for queries, the search efficiency in a relatively small field is obviously higher.

19.Do not use it anywhereSelect * from t, Instead of using the 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 repeatedly reference a large table or a data set in a common table. However, it is best to use the export table for one-time events.

23.When creating a temporary table, you can useSelectReplaceCreate TableTo avoid causing a large numberLogTo speed up; if the data volume is small, in order to ease the system table resources, you should firstCreate Table, And thenInsert.

24.If a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure.Truncate table, And thenDrop tableIn this way, the system table can be locked for a long time.

25.Avoid using a cursor whenever possible because the cursor efficiency is poor. If the cursor operation data exceeds1We 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, a cursor is not unavailable. Small DatasetsFast_forwardA cursor is usually better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. Included in the result set"Total"Usually faster than using 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 at the endSet nocount off. You do not need to send the statement to the client after executing each stored procedure and trigger.Done_in_procMessage.

29.Avoid large transaction operations and improve system concurrency.

30.Avoid returning large data volumes to the client whenever possible. If the data volume is too large, consider whether the appropriate requirements are reasonable.

The aboveArticleI just raised some information about my learning.SQLQueryThe principle of optimization, we hope that the vast majority of the experts will not be able to give us some advice.