Normally what are the treatment
CodePudding user response:
1. Where the back field and index2. Select the specified field name, and as far as possible
3. The queries as far as possible
4. The associative table as far as possible
CodePudding user response:
Data redundancy; To minimize the associated query; Add index,CodePudding user response:
1. Optimize the query, should try to avoid a full table scan, the first consideration should be given at the where and order by column index,
2. Should avoid as far as possible in the where clause to use!=or & lt;> Operator, otherwise will full table scan engine abandon the use of index,
3. Should avoid as far as possible in the where clause to null value judgment of field, otherwise will lead to a full table scan engine abandon the use of index, such as:
Select the id from t where num is null
Can set the default value of 0 in the num, make sure num column is not null values in the table, then query like this:
Select the id from t where num=0
4. Avoid using or in the where clause to join condition, otherwise will lead to a full table scan engine abandon the use of index, such as:
Select id from t where num=10 or num=20
Can query like this:
Select id from t where num=10
Union all
Select id from t where num=20
5. The following query will also lead to a full table scan:
Select the id from t where name like '% ABC %'
If you want to improve efficiency, can consider to full text search,
6. And in the not in should also be careful, otherwise it will lead to a full table scan, such as:
Select the id from t where num in (1, 2, 3)
For the value of continuous, it can be used between don't use in:
Select the id from t where num is between 1 and 3
7. If used in the where clause parameter, can also lead to a full table scan, because only at run time will parse SQL local variable, but the optimizer cannot access plan selection will be put off to the runtime; It must make a selection at compile time, however, if establish access plan at compile time, the value of a variable or unknown, and therefore cannot be used as inputs of index selection, such as the following statement will be a full table scan:
Select the id from t where num=@ num
Can be used instead of mandatory query index:
Select the id from t with (index (index)) where num=@ num
8. Should avoid as far as possible in the where clause expression was carried out on the field operation, this will lead to a full table scan engine abandon the use of index, such as:
Select the id from t where num/2=100
Should be changed to:
Select id from t where num=100 * 2
9. Should avoid as far as possible in the where clause function was carried out on the field operation, this will lead to a full table scan engine abandon the use of index, such as:
Select id from t the where the substring (name, 1, 3)='ABC' - the name begin with ABC the id of the
Select id from t where datediff (day, createdate, '2005-11-30')=0 - '2005-11-30 s' generation id
Should be changed to:
Select the id from t the where the name like '% ABC'
Select the id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10. Don't in the where clause of the "=" to the left to carry on the function, the arithmetic expression or other operations, otherwise will may not be able to correct use of the index system,
11. When using the index field as a condition, if the index is a composite index, then you have to use the first field in the index as a condition to guarantee the system using the index, the index will not be used, and should as far as possible let field is consistent with the indexed sequential order,
12. Don't write some meaningless query, such as the need to generate an empty table structure:
Select col1, col2 into the from # t t where 1=0
This type of code doesn't return any result sets, but consumes system resources, should be changed to this:
The create table # t (... )
13. Substitute exists in a lot of time is a good choice:
The select num from where a num in (select num from b)
Replace with the following statement:
Select num from a where the exists (select 1 from b where num=a.n um)
14. Not all index is effective to query, SQL is the query optimization according to the data in the table, when the index columns have a large amount of data repeat, SQL queries may not to take advantage of the index, like in the table of field sex, male, female almost half each, so even if the index is built on the sex has no effect on the query efficiency,
15. The index is not the more the better, the index is can improve the efficiency of the corresponding select, but also reduce the efficiency of the insert and update at the same time, because the insert or update from time to tome could rebuild index, so how to build index need to be considered, depending on the specific situation, indexes of a table had better not more than six, if too much consideration should be given some rarely used to built on the column of the index if necessary,
16. Should be updated as much as possible to avoid clustered index data, because the order of the clustered index data column is the physical storage sequence table record, once the column value changes will lead to the entire table records the order of the adjustment, will spend considerable resources, if the application system need frequent updates clustered index data column, so you need to consider whether the index should be built for the clustered index,
17. Use digital type field as far as possible, if only the numerical information fields as far as possible don't design for character, this will degrade the performance of the query and the connection, and can increase the storage costs, this is because the engine in the treatment of the query and the connection will then compare each character in the string, and for the digital type need comparison only once is enough,
18. As far as possible using varchar/nvarchar instead of char/nchar, because first of all, variable-length field small storage space, can save the storage space, the second for the query, within a relatively small field had higher search efficiency obviously,
19. Do not use the select * from t anywhere, with specific field list instead of "*", don't return any fields, with less than
20. Try to use table variables instead of a temporary table, if the table variable contains a large amount of data, please note that the index is very limited (only the primary key index),
21. Avoid frequently to create and delete temporary table, table to reduce the system resource consumption,
22. A temporary table is not is use, use them appropriately can make some routine more effective, for example, when you need to repeat quoted a large table or table is commonly used in a data set, however, for a one-off event, it is best to use export table,
23. In the new temporary table, if one-time insert the data quantity is very big, so you can use the select into instead of the create table, avoid to cause a large number of log, in order to improve the speed; If the data volume is not large, in order to reduce resource system tables, should create table first, and then insert,
24. If you use the temporary table, at the end of the stored procedure must be explicit delete all the temporary table, truncate table first, and then drop the table, so that you can avoid system table lock for a long time,
25. Avoid using a cursor, because of poor efficiency of the cursor, if the cursor operation data of more than 10000 lines, so you should consider rewriting,
26. Using the method based on the cursor or temporary table method before, should be based on a set of solution to solve the problem, based on a set of methods are usually more effective,
27. Like a temporary table, the cursor is not is use, for small data sets using FAST_FORWARD cursor generally superior to other processing method, line by line especially in must refer to several tables to obtain data that are needed in the result set includes "combined" routines usually faster than using the cursor, development time permitting, based on the method and the method based on set cursor can give it a try and see which method is better,
28. In all of the stored procedures and triggers the start of the SET SET NOCOUNT ON, at the end of the SET SET NOCOUNT OFF, no stored procedures and triggers in the implementation of the DONE_IN_PROC messages sent to the client after each statement,
29. Try to avoid large amount of data to the client, if the data volume is too big, should consider the corresponding demand is reasonable,
30. Try to avoid big affairs operation, improve the system of concurrent ability,