Shulou(Shulou.com)06/01 Report--

1. Try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to abandon the use of the index and perform a full table scan, such as:

Select id from t where num is null

You can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num=0

two。 Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans, such as:

Select id from t where num=10 or num=20

You can query it like this:

Select id from t where num=10

Union all

Select id from t where num=20

3 the following query will also cause a full table scan:

Select id from t where name like'% abc%'

To improve efficiency, consider full-text retrieval.

4.in and not in should also be used with caution, otherwise it will lead to full table scanning, such as:

Select id from t where num in (1, 2, 3)

For consecutive values, use between instead of in:

Select id from t where num between 1 and 3

5. Expression manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to abandon the use of indexes and perform full table scans. Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

6. In many cases, using exists instead of in is a good choice:

Select num from a where num in (select num from b)

7. Replace it with the following statement:

Select num from a where exists (select 1 from b where num=a.num)

8. Index is not the more the better, the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update, because insert or update may rebuild the index, so how to build the index needs to be carefully considered, depending on the specific situation. It is best to have no more than 6 indexes in a table, and if there are too many, consider whether it is necessary to build indexes on some infrequently used columns.

9. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, the storage space of longer fields is small, which can save storage space, and secondly, for queries, searching in a relatively small field is obviously more efficient.

10. Don't use select * from t anywhere, replace "*" with a specific list of fields, and don't return any fields that you don't need.

11. Avoid creating and deleting temporary tables frequently to reduce the consumption of system table resources.

twelve。 Temporary tables are not unavailable, and using them appropriately can make some routines more efficient, for example, when you need to re-reference a dataset in a large or common table. However, for one-time events, it is best to use an export table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.