Millions MySQL database SQL query statement optimization scheme

1. To optimize the query, you should try to avoid full table scan, first consider the where and the order by the columns involved in the establishment of the index.

2. The null value of the field in the WHERE clause should be avoided as far as possible, or it will cause the engine to discard the use of the index for a full table scan, such as:

Select ID from t where num is null

You can set the default value of 0 on NUM to ensure that the NUM column in the table does not have a null value and then query this way:

Select ID from t where num=0

3. The use of!= or <> operators in the WHERE clause should be avoided as far as possible, otherwise the engine discards the use of the index for a full table scan.

4. You should try to avoid using or to join conditions in the WHERE clause, or it will cause the engine to discard the use of the index for a full table scan, such as:

Select ID from t where num=10 or num=20

You can query this way:

Select ID from t where num=10

UNION ALL

Select ID from t where num=20

5.in and not in should also be used with caution, otherwise it will result in a full table scan, such as:

Select ID from t where num in (1,2,3)

For consecutive values, you can use between instead of in:

Select ID from t where num between 1 and 3

6. The following query will also result in a full table scan:

Select ID from t where name like '%abc% '

To improve efficiency, you can consider Full-text search.

7. The use of parameters in the WHERE clause also results in a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot access the scheduled

The choice is deferred to run time, and 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 cannot be indexed

The input item that is selected. The following statement will perform a full table scan:

Select ID from t where num= @num

You can use the index instead of forcing the query:

Select ID from T with (index name) where num= @num

8. You should try to avoid the expression of fields in the WHERE clause, which will cause the engine to discard the use of indexes for full table scans. Such as:

Select ID from t where num/2=100

should read:

Select ID from t where num=100*2

9. Avoid functional operations of fields in the WHERE clause, which causes the engine to discard the use of indexes for full table scans. Such as:

Select ID from t where substring (name,1,3) = ' abc ' –name an ID beginning with ABC

The ID generated by the Select ID from t where DATEDIFF (day,createdate, ' 2005-11-30 ') =0– ' 2005-11-30 '

should read:

Select ID from t where name like ' abc% '

Select ID from t where createdate>= ' 2005-11-30 ' and createdate< ' 2005-12-1 '

10. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, otherwise the system may not be able to 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 ensure that the system is used

The index, otherwise the index will not be used, and the order of the fields should be consistent with the index order as much as possible.

12. Do not write some meaningless queries, such as the need to generate an empty table structure:

Select Col1,col2 into #t the from T where 1=0

This type of code does not return any result sets, but consumes system resources and should be changed to this way:

CREATE TABLE #t (...)

13. It is a good choice to use exists instead of 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=a.num)

14. Not all indexes are valid for queries, SQL is optimized for queries based on table data, and SQL queries may not benefit when there is a lot of data duplication in the index column

Indexed, such as a table with fields Sex,male, female almost half, then the query effect even if the index is built on sex

Rate does not work.

15. Indexing is not as good as it is, indexing can increase the efficiency of the corresponding select, but it also reduces the efficiency of INSERT and update because insert

or update, you may be able to rebuild the index, so how to build the index requires careful consideration, depending on the circumstances. The number of indexes for one table is best not to exceed 6, if too many

You should consider whether indexes built on columns that are not used frequently are necessary.

16. Avoid updating clustered indexed data columns as much as possible, because the order of the clustered indexed data columns is the physical storage order of the table records, once the column value

Changes will result in an adjustment of the order of the entire table record, which will consume considerable resources. If the application system needs to frequently update the clustered index data columns, you need to consider

Whether the index should be built as a clustered index.

17. Use numeric fields as much as possible, if the fields containing only numeric information are not designed to be character type, this will reduce the performance of queries and connections, and increase storage overhead. This

This is because the engine compares each character of the string one at a time while processing queries and connections, but only one time for a numeric type is enough.

18. Use Varchar/nvarchar as much as possible instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, followed by the query to

says that search efficiency is clearly higher in a relatively small field.

19. Do not use SELECT * from t anywhere, use a specific field list instead of "*", and do not return any fields that are not used.

20. Use table variables as far as possible instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only primary key indexes).?????

21. 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 some routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table

。 However, for one-off events, it is best to use an export table.

23. In the new temporary table???? , you can use SELECT INTO instead of CREATE table to avoid creating a large number of logs if you insert a large amount of data at a time.

High speed, if the amount of data is small, in order to ease the resources of the system table, create a table first, and then insert.

24. If you use a temporary table, be sure to explicitly delete all temporary tables at the end of the stored procedure, first truncate the table, and then drop table, which

To avoid longer locking of system tables.

25. Avoid the use of cursor???, because the cursor is inefficient, if the cursor operation of more than 10,000 rows of data, then you should consider rewriting.

26. Before using a method based on a cursor or a temporary table method, you should first look for a set based solution to solve the problem, and a set based approach is usually more efficient.

27. As with temporary tables, cursors are not unusable. Using Fast_forward cursors for small datasets is generally superior to other row-by-line methods, especially when you must

You can refer to several tables to get the data you want. Routines that include "totals" in the result set are typically faster than those used with cursors. If development time allows, the base

The cursor method and the set based method can be tried to see which method works better.

28. Set the set NOCOUNT on at the beginning of all stored procedures and triggers, and set NOCOUNT off at the end. No need to execute stored procedures and triggers

Sends a DONE_IN_PROC message to the client after each statement.

29. Try to avoid large business operations, improve system concurrency capability.

30. Try to avoid the return of large data to the client, if the amount of data is too large, we should consider whether the corresponding demand is reasonable.