The optimization method of database

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 for quickly accessing 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 has cache modules   tables: Generate a large amount of data for each time period, you may consider using a certain strategy to store the data in multiple data tables.   Sub-Library: The system according to the characteristics of the module distribution to different data, in order to improve the overall load capacity of the system.  sql optimizations:  1.in and not are also used sparingly, because in causes the system to not use the index and can only search the data in the table directly. For example: Select ID from t where num in (three-in-one) is not used in between for consecutive values: Select ID from t where num between 1 and 32. When judging true or false is, if a nd or OR: (When a "where condition 1 and Condition 2" is present, the database executes the statement to the right first) and tries to put the false to the right (a false is false)  or try to put it to the right (one for true)  3. Avoid the WHERE clause as much as possible. , which causes the engine to discard the use of the index for full-table scanning. For example: SELECT * from T1 where f1/2=100  should be changed to:  select * from T1 where F1=100*2select * from RECORD where SUBSTRING (card_ no,1,4) = ' 5378 '   should be changed to:  select * from RECORD WHERE card_no like ' 5378% ' 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, It includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.  4. Many times using exists is a good option: Elect num from a where num in (select num from B) is replaced with the following statement: Select Num from a where exists (select 1 from B where Num=a.num) SELECT SUM (T1. C1) from the T1 where (  (SELECT COUNT (*) from T2 where T2. C2=t1. c2>0)  select SUM (T1. C1) from the T1where EXISTS ( select * from T2 WHERE t2.c2=t1.c2)   both produce the same result, but the latter is obviously more efficient than the former. Because the latter does not produce a large number of locked table scans or index scans. If you want to verify that there is a record in the table, do not use COUNT (*) as inefficient and waste server resources. Can be replaced with exists. such as:  if (select COUNT (*) from table_name WHERE column_name = ' xxx ')   can be written as:  if EXISTS (SELECT * FROM table_name WHERE column_name = ' xxx ') 5. Make full use of the join condition, in some cases, there may be more than one connection condition between the two tables, at which point the connection condition is fully written in the WHERE clause, which can greatly improve the query speed.   Example:  select sum (a.amount) from account a,card B WHERE a.card_no = b.card_no select sum (a.amount) from Accoun T a,card B WHERE a.card_no = b.card_no and a.account_no=b.account_no  The second sentence will be more than the first sentenceThe line was much faster. 6. Use the view to speed up queries   sort a subset of tables and create views, sometimes speeding up queries. It helps to avoid multiple sorting operations, and in other ways simplifies the work of the optimizer. Example: SELECT cust.name,rcvbles.balance,......other columns from Cust,rcvbles where cust.customer_id = Rcvlbes.customer_id and rcvblls.balance>0 and cust.postcode> "98000"  ORDER by Cust.name if the query is to be executed more than once, all unpaid customers can be found in a single view and sorted by the customer's name:  create View DBO. V_cust_rcvlbes as select Cust.name,rcvbles.balance,......other Columns from Cust,rcvbles where cust.customer_id = Rcvlbes.customer_id and Rcvblls.balance>0 order by cust.name  then query in the view in the following way:  select * from V_cust_rcvlbes where postcode> the rows in the "98000"   view are less than the rows in the primary table, and the physical order is the required order, reducing disk I/O, So the query workload can be greatly reduced. Mostly from http://blog.csdn.net/mis_lixiaoli/article/details/6331312.

How to optimize a database