[Java] itoo project practices: optimized collection of millions of data queries and itoo data queries

[Java] itoo project practices: optimized collection of millions of data queries and itoo data queries

1. To optimize the query, you should consider creating an index on the columns involved in where and order.

 

2. Try to avoid null value determination on the field in the where clause, for example, select id from t wherenum is null.

You can set the default value 0 on num to make sure that the num column in the table does not have a null value, and then query it like this:

Select idfrom t where num = 0

 

3. Try to avoid using it in the where clause! = Or <> operator.

 

4. Try to avoid using or in the where clause to connect conditions, such:

Select idfrom t where num = 10 or num = 20

You can query it as follows:

Select idfrom t where num = 10

Unionall

Select idfrom t where num = 20

 

5. Use in and not in with caution, for example:

Select idfrom t where num in (1, 2, 3)

For continuous 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. Avoid performing expression operations on fields in the where clause as much as possible, such:

Select idfrom t where num/2 = 100

Should be changed:

Select idfrom t where num = 100*2

 

8. Avoid performing function operations on fields in the where clause whenever possible,

 

9. 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 correctly use the index.

 

10. 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 a 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.

 

The above 10 items can be summarized as follows: avoid full table scan and use indexes correctly

 

11. Do not write meaningless queries. If you need to generate an empty table structure:

Selectcol1, col2 into # t from t where1 = 0

This type of code will not return any result set, but will consume system resources, should be changed to this:

Create table # t (...)

 

12. in many cases, replacing in with exists is a good choice:

Select numfrom a where num in (select num fromb)

Replace the following statement:

Select numfrom a where exists (select 1 from B wherenum = a. num)

 

13. Not all indexes are valid for queries. SQL queries are optimized based on the data in the table. When a large amount of data is duplicated in the index column, SQL queries may not use indexes,

For example, if a table contains sex fields, male and female are almost half of each other, indexing sex does not play a role in query efficiency.

 

14. The more indexes, the better. indexes can improve the select efficiency, but also reduce the insert and update efficiency,

Because the index may be re-built during insert or update, You need to carefully consider how to create the index, depending on the actual situation.

It is recommended that the number of indexes in a table be no more than 6. If there are too many indexes, consider whether the indexes on some columns that are not frequently used are necessary.

 

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

 

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

Secondly, for queries, the search efficiency in a relatively small field is obviously higher.

 

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

 

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

 

19. When creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid a large number of logs,

To speed up; if the data volume is small, in order to ease the system table resources, you should first createtable and then insert.

 

20. Avoid using a cursor whenever possible, because the efficiency of the cursor is poor. If the cursor operation has more than 10 thousand rows of data, you should consider rewriting.

 

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

 

22. Avoid large transaction operations as much as possible to improve the system concurrency capability.

 

23. Avoid returning large amounts of data to the client as much as possible. If the data volume is too large, consider whether the corresponding requirements are reasonable.

By doing itoo from V1.0 to V3.0, and adding previous projects, we are actually doing SQL optimization, but starting from ordinary personal habits, we don't need to wait until there is a large amount of data, it is found that the query data is slow and slow, so we can try our best to optimize it. When the data volume is small, we start to consider what problems will occur when the data volume is large. This requires us to start from the beginning, we need to have a mind and a mind to consider the long-term issues.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.