Several methods of SQL optimization

in SQL query in order to improve query efficiency, we often take some measures to SQL optimization of query statements, the following summarizes some of the methods, there is a need to reference. 1To optimize queries, avoid full table scans, and first considerwhereAndOrder  byan index is built on the column involved. 2. should try to avoidwhereclause to make a field in theNULLvalue, otherwise it will cause the engine to discard full table scans using the index, such as:SelectId fromTwhereNum is NULLyou can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:SelectId fromTwhereNum=0        3. should try to avoidwhereclause is used in the!=Or<>operator, otherwise the engine discards the full table scan using the index. 4. should try to avoidwhereclause is used in theorto connect the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as:SelectId fromTwhereNum=Ten orNum= -You can query this:SelectId fromTwhereNum=Ten    Union  All    SelectId fromTwhereNum= -        5.inchAnd not inchalso use caution, otherwise it will result in a full table scan, such as:SelectId fromTwhereNuminch(1,2,3) for continuous values, you can use thebetweenDon't use it.inchthe following:SelectId fromTwhereNumbetween 1  and 3        6The following query will also cause a full table scan:SelectId fromTwhereName like '%abc%'        7. should try to avoidwhereclause, which causes the engine to discard the use of the index for full-table scanning. such as:SelectId fromTwhereNum/2= -should read:SelectId fromTwhereNum= -*2        8you should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. such as:SelectId fromTwhere substring(Name,1,3)='ABC'--name the ID that starts with ABCshould read:SelectId fromTwhereName like 'abc%'        9. Not inwhereclause, in the="On the left to perform functions, arithmetic operations, or other expression operations, or the system may not use the index correctly. Ten. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee 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.  Onedo not write some meaningless queries, such as the need to generate an empty table structure:SelectCol1,col2 into#t fromTwhere 1=0This type of code does not return any result sets, but consumes system resources and should be changed to this:Create Table#t (...)  A. Many times useexistsReplaceinchis a good choice:SelectNum fromAwhereNuminch(SelectNum fromb) Replace with the following statement:SelectNum fromAwhere exists(Select 1  fromBwhereNum=a.num) -Not all indexes are valid for queries, SQL is query-optimized based on the data in the table, and when there is a large number of data duplicates in the index columns, the SQL query may not take advantage of the index, as there are fields in the table Sex,male, female almost half,        So even if you build an index on sex, it doesn't work for query efficiency.  -Index is not the more the better, although the index can improve the correspondingSelectEfficiency, but it also reducesInsertAndUpdatethe efficiency, becauseInsertOrUpdateThe index may be rebuilt, so how to build the index requires careful consideration, depending on the situation.        The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.  theUse a numeric field as much as possible, and if a field that contains only numeric information is not designed as a character type, this can degrade query and connection performance and increase storage overhead.        This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.  -. Use as much as possiblevarcharReplaceChar, because the first variable long field storage space is small, can save storage space, second for the query, in a relatively small field search efficiency is obviously higher.  -. Do not use anywhereSelect *  fromT, use a specific field list instead of "*", do not return any fields that are not available.  -avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.  +temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table.        However, for one-time events, it is best to use an export table.  -When you create a new temporary table, if you insert a large amount of data at once, you can use theSelect  intoReplaceCreate Table, avoid causing a lot ofLogTo improve the speed, if the amount of data is small, in order to mitigate the resources of the system table, createTable, and then insert.  +If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, firsttruncate TableAnd thenDrop Table, which avoids long-time locking of system tables.  Aavoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.  atbefore using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient.  -. As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data. Routines that include "totals" in the result set are typically faster than using cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better.  -Try to avoid large transaction operations and improve system concurrency. -Try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the appropriate requirements are reasonable.

Several methods of SQL optimization