"Java" Itoo project million data query optimization collection and practice

1. To optimize the query, consider establishing an index on the columns involved in the Where and order by.

2. Try to avoid null values in the WHERE clause, such as: Select ID from t wherenum is null

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 Idfrom t where num=0

3. You should try to avoid using the! = or <> operator in the WHERE clause.

4. You should try to avoid using or in the WHERE clause to join conditions, such as:

Select Idfrom t where num=10 or num=20

You can query this:

Select Idfrom t where num=10

UnionAll

Select Idfrom t where num=20

5.in and not in should also be used with caution, such as:

Select Idfrom t where num in (All-in)

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

Select Idfrom t where num between 1 and3

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

Select Idfrom t where name like '%abc% '

7. You should try to avoid expression operations on fields in the WHERE clause, such as:

Select Idfrom t where num/2=100

should read:

Select Idfrom t where num=100*2

8. You should try to avoid function operations on the fields in the WHERE clause,

9. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the index may not be used correctly by the system.

10. 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 will use the index.

Otherwise, the index will not be used, and the field order should be consistent with the index order as much as possible.

above Ten The article can be summed up as: Avoid full table scan, use index correctly

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

Selectcol1,col2 to #t from T where1=0

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

Create Table#t (...)

12. It is a good choice to replace in with exists in many cases:

Select Numfrom a Where num in (select num fromb)

Replace with the following statement:

Select Numfrom a where exists (select 1 from b wherenum=a.num)

13. Not all indexes are valid for queries, and SQL is optimized for queries based on the data in the table, and when there is a large number of data duplication in the index columns, the SQL query may not take advantage of the index.

As there are fields Sex,male and female almost half of the table, even indexing on sex does not work for query efficiency.

14. 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 indexing may be rebuilt at insert or update, it is necessary to consider how to build the index, 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.

15. Use numeric fields as much as possible, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of queries and connections 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.

16. Use varchar instead of char as much as possible, because the first variable-length field has a small storage space and can save storage space.

Second, for queries, the search efficiency is clearly higher in a relatively small field.

17. Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.

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

19. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a lot of log

To improve the speed, if the amount of data is not large, in order to mitigate the resources of the system table, createtable first, then insert.

20. 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.

21. Before 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.

22. Try to avoid large transaction operation and improve the system concurrency ability.

23. Try 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.

By doing Itoo from V1.0 To V3.0, plus the previous project, in fact, when doing SQL optimization, but from the usual personal habits began, do not have to wait until the amount of data, found that the query data is slow and slow, just to brain to optimize, in the small amount of data when it began to consider the large amount of data when the problem will arise, this need we start from a bit We need to have a mind and a bosom to consider the long-term problem.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"Java" Itoo project million data query optimization collection and practice