SQL Server database optimization experience (1)

Database optimization considerations:

1. Create an index for the key fields.

2. Using Stored Procedures makes SQL more flexible and efficient.

3. Back up the database and clear junk data.

4. SQL statement syntax optimization. You can use Sybase SQL Expert. Unfortunately, I did not find the unexpired serial number)

5. Clear and delete logs.

Basic principles for SQL statement optimization:

1. Use indexes to traverse tables faster.

The index created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically stored on the data page randomly. Reasonable index design should be based on the analysis and prediction of various queries. Generally speaking: ①. you can create a cluster index for columns with a large number of duplicate values and frequent range queries such as between, >,<<=, <=), order by, and group by. ②. multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can create a composite index. composite indexes should try to overwrite key queries. The leading column must be the most frequently used column.

2. is null and IS NOT NULL

Null cannot be used as an index. Any column containing null values will not be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved. Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.

3. IN and EXISTS

EXISTS is far more efficient than IN. It is related to full table scan and range scan. Almost all IN operator subqueries are rewritten to subqueries using EXISTS.

4. Use as few formats as possible for massive queries.