MySQL Common 30 kinds of SQL query statement optimization method

1. You should try to avoid using! = or <> in the WHERE clauseoperator, otherwise the engine discards the full table scan using the index. 2, optimize the query, avoid full-table scanning, and first consider indexing on the columns involved in where and order by. 3, you should try to avoid null values for the field in the Where clause, or it will cause the engine to abandon using the index for a full table scan. such as: SELECT ID fromt where num isNULL You 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: SELECT ID fromt where num=04. Try to avoid using in the WHERE clauseorto join the condition, otherwise it will cause the engine to discard the full table scan using the index, for example: SELECT ID fromt where num=10orNum=20You can query this: SELECT ID fromt where num=10Union allselect ID fromt where num=205, the following query will also cause a full table scan: (cannot have a preceding percent) select ID fromt where name like '%c%' walk down the index select ID fromt where name like ' c%To improve efficiency, full-text search can be considered. 6.inchAnd not inchalso use caution, otherwise it will result in a full table scan, such as: Select ID fromt where numinch(Afor consecutive values, you can use between to not useinchthe: Select ID fromt where num between 1 and37, if you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan: Select ID fromt where num=@num can force queries to use the index instead: SELECT ID fromT with (index name) where num=@num8, you should try to avoid expression operations on the field in the Where clause, which causes the engine to discard full table scans using the index. such as: SELECT ID fromt where num/2=100should read: Select ID fromt where num=100*29, you should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to abandon using the index for a full table scan. such as: SELECT ID fromt where substring (name,1,3) =' abc ' –name idselect ID starting with ABC fromt where DateDiff (day,createdate, ' 2005-11-30′) =0– ' 2005-11-30the ID of the generated should be changed to: SELECT ID fromt where name like ' abc%' SELECT ID fromt where createdate>= ' 2005-11-30′ andcreatedate< ' 2005-12-1 -10. Do not "=" in the WHERE clause"On the left to perform functions, arithmetic operations, or other expression operations, or the system may not use the index correctly. 11, 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. 12, do not write meaningless queries, such as the need to generate an empty table structure: select Col1,col2 into#t from T where 1=0This type of code does not return any result sets, but consumes system resources and should be changed to this: CREATE TABLE#T (...)13, many times use exists insteadinchis a good choice: Select num fromA Where numinch(Select num fromb) Replace with the following statement: Select Num fromA Where exists (select 1 fromb where num=a.num)14, 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. 15, the index is not the more the better , although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many should consider whether some of the unused columns on the index is necessary. 16You should avoid updating the clustered index data columns as much as possible, because the order of the clustered index data columns is the physical storage order of the table records, which can cost considerable resources once the column values change to the order in which the entire table is recorded. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index. 17, use numeric fields as much as possible, and if fields with numeric information are not designed as character types, 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. 18, as far as possible to use Varchar/nvarchar instead of char/nchar, 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. 19. Do not use SELECT * Anywhere fromT, use a specific field list instead of "*", do not return any fields that are not available. 20, and try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index). 21st, avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. 22, 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 better to use the export table. 23, when you create a new temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid creating a large number of logs to improve speed, and if the amount of data is small, create a table to mitigate the resources of the system tables. Then insert. 24, if a temporary table is used, make sure that all temporary tables are explicitly deleted at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables. 25, avoid 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. 26, you should look for a set-based solution to solve the problem before using a cursor-based method or a temporary table method, which is often more efficient than a set-based approach. 27, 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. 28, set NOCOUNT on at the beginning of all stored procedures and triggers, set NOCOUNT OFF at the end. You do not need to send a DONEINPROC message to the client after each statement that executes the stored procedure and trigger. 29, as far as possible to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable. 30, try to avoid large transaction operation, improve the system concurrency ability.

MySQL Common 30 kinds of SQL query statement optimization method