Large Data Volume query optimization-database design, SQL statement, Java encoding

Database Design Aspects:

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

2, should try to avoid the null value of the field in the Where clause to judge, otherwise it will cause the engine to abandon 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, make sure that the NUM column in the table does not have a null value, and then query: Select ID from t where num=0

3, not all indexes are valid for the query, SQL is based on the data in the table to query optimization, when the index column has a large number of data duplication, the query may not use the index, such as the table has a field sex,male, female almost half,

So even if you build an index on sex, it doesn't work for query efficiency.

4, 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 indexes need to be cautious

Consideration, depending on the circumstances. 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.

5, as far as possible to avoid updating the index data columns, because the order of the Index data column is the physical storage order of table records, once the column value changes will result in the order of the entire table records adjustment, it will cost considerable resources. If the application system requires a frequency

Update the index data column, you need to consider whether the index should be built as an index.

6, try to use numeric fields, if only the value of the field is not designed as a character type, which will reduce the performance of query and connection, and increase storage overhead. This is because when the engine processes queries and connections, it compares each of the strings

One character, and for a digital type it only needs to be compared once.

7, as far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field search efficiency is obviously higher.

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

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

10, when the new temporary table, if the amount of disposable data is large, then you can use SELECT INTO instead of CREATE table, to avoid causing a large number of logs to improve speed, if the amount of data is small, in order to mitigate the resources of the system table, you should first Crea Te table, and then insert.

11. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

In terms of SQL statements:

1. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

2, should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon the use of the index for full table scan
such as: Select ID from t where num=10 or num=20
This can be queried: Select ID from t where num=10 union ALL select ID from t where num=20

3, in and not in also to use caution, otherwise it will result in a full table scan.
Example: Select ID from t where num in
For consecutive values, you can use between to not use in: Select ID from t where num between 1 and 3

4. The following query will also cause a full table scan: Select ID from t where name like '%abc% '

5, if the use of parameters in the WHERE clause, 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 cannot be used as an input for the index selection.
              The following statement will perform a full table scan:  select id from t where [email protected] 
             You can force the query to use the index instead:  select id from t with (index name))  where [email protected ]

6. 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.
Example: Select ID from t where num/2=100
Should read: Select ID from t where num=100*2

7, should try to avoid in the WHERE clause function operations on the field, which will cause the engine to abandon the use of the index for a full table scan.
For example: Select ID from t where substring (name,1,3) = ' abc '--name ID starting with ABC
Select ID from t where DATEDIFF (day,createdate, ' 2005-11-30′) =0--' 2005-11-30 ' generated ID
should read:
Select ID from t where name like ' abc% '
Select ID from t where createdate>= ' 2005-11-30′and createdate< ' 2005-12-1′

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

9, do not write some meaningless queries, such as the need to generate an empty table structure: Select Col1,col2 into #t from T where 1=0 such code will not return any result set, but will consume system resources,

Should be changed to this: Create TABLE #t (")

10, a lot of times with exists instead of in is a good choice: 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)

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

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

13, 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.

14, try to avoid large transaction operation, improve the system concurrency ability.

Java aspect:

1, as far as possible to create less objects.

2, reasonable pendulum system design position. A large number of data operations, and a small number of data operations must be separate. A lot of data manipulation, certainly not the ORM framework is fixed.

3. Use the JDBC link database to manipulate the data.

4, control the memory, let the data flow up, not all read to the memory and processing, but the edge read edge processing.

5, reasonable use of memory, some data to be cached

Large Data Volume query optimization-database design, SQL statement, Java encoding