Database Optimization method

Common Database Optimization methods: Index (database), cache, sub-table, sub-Library, SQL optimization.

Index: Creating an index typically has the following two purposes: maintain the uniqueness of the indexed columns and provide a strategy to quickly access the data in the table. 95% of database problems can be solved by indexing technology. Indexes can help improve retrieval performance, but too many or improper indexes can cause system inefficiencies. Because the user adds an index to the table, the database will do more work. Too many indexes can even cause index fragmentation.

Cache: Hibernate,spring3 with cache module

Sub-table: to generate a large amount of data for each time period, consider using a certain strategy to store the data in multiple data tables.

Sub-Library: It is the system according to the characteristics of the module distribution to different data, to improve the overall load capacity of the system.

SQL Optimizations:

1.in and not in are also used sparingly, because in makes the system unusable with indexes and can only search the data in the table directly. Such as:

The select ID from the Where Num in (three-to-three) value is not used in the Between:

Select ID from t where num between 1 and 3

2. When judging whether true or false is, if with and OR OR:

(when "Where Condition 1 and condition 2" exists, the database executes the statement to the right first)

And try to put the fake to the right (one false is false) or try to put it to the right (one is true)

3. You should try to avoid expression operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. Such as:

SELECT * from T1 WHERE f1/2=100

should read:

SELECT * from T1 WHERE f1=100*2

SELECT * from RECORD WHERE SUBSTRING (card_no,1,4) = ' 5378 '

should read:

SELECT * from RECORD WHERE card_no like ' 5,378% '

SELECT Member_number, first_name, last_name from members

WHERE DATEDIFF (Yy,datofbirth,getdate ()) > 21

should read:

SELECT Member_number, first_name, last_name from members

WHERE dateOfBirth < DATEADD (Yy,-21,getdate ())

That is, any action on a column causes a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

Database Optimization method