SQL Performance Optimization suggestions

Experience shows that SQL ServerThe greatest performance improvement benefits from the logicDatabase Design,Index DesignAndQuery Design. Conversely, the biggest performance problem is often caused by these deficiencies in the same aspect. SQLThe essence of optimization is to use the statements that can be recognized by the optimizer on the premise that the results are correct to fully utilize the index to reduce the I/O of table scans.Times to avoid table search

1.Database Design:

1.1If the number is used as the primary key, the auto-increment field provided by the system is used.

1.2All databases are set to provide default values rather than null (avoid data detection at the data acquisition layer to reduce the possibility of throwing exceptions)

1.3Use redundancy as appropriate

1.4Use indexes as appropriate

1.5Some fields with fixed length can be usedChar (N)Use lessVarchar (N)

1.6If you use a view properly, you can consider the index view.

1.7Logs and official data files are stored on different hard disks.

 

2.Data Query

2.1Only query required fields

2.2Make sure that the same item does not appear in the query or the same item does not have a big relationship.Union allNoUnion

2.3IfInOrOrWhen the query is not indexed, use the display statement to specify the index:Select * From personmember (Index = ix_title) Where processid in ('Male','Female')

SelectField table

FromTable Name

With (index (Index name))

WhereQuery Conditions

2.4Multi-PurposeBetweenSubstitutionIn

2.5Unnecessary transactions

2.6Exercise cautionOr,Will invalidate the index

2.7Do not useDistinctAndOrder

2.8Do not use in thingsSelectCreate temporary table

2.9Use less temporary tables and try to use result setsTableClass variable to replace it, TableType variables are better than temporary tables

2.10 avoid where ! = or operator, otherwise, the engine will not use the index for full table scanning

2.11WhereClause describes the query conditions and directly determines the query performance. Therefore, in the wherePay more attention to the writing and Application of clauses. Write whereAvoid using incompatible data types and avoidThe condition parameters in the clause use other mathematical operators to convert the operation to the left of the formula sub as much as possible, so that the existing index technology can be effectively used. ForMultiple selection conditions in the statement. to select a small result set, execute

 

3.Index creation

3.1Index created by common query Fields

3.2The fields used to create an index must be highly dispersed.

3.3Properly control the queried fields. It is best to select only the index fields.

3.4For composite indexes, pay attention to the order of the composite indexes.

3.5Index needs to be updated regularly

3.6 ① Clustered index ( Clustered Index ): Data pages of a cluster index are stored physically and orderly, with a small footprint. The selection policy is used Where Clause column: including range query, fuzzy query, or highly repeated columns (continuous disk Scan Is used for connection. Join Operation column; used Order And Group Clause column. The clustered index is not conducive to insertion. * In addition, there is no need to use the primary key to create a clustered index.
② Non-clustered index ( Nonclustered Index ): Compared with clustered indexes, it occupies a large amount of space and is less efficient. The selection policy is used Where Clause columns: including range query, fuzzy query (when the clustered index is not available), primary key or external key Key column, point (pointer class), or small scope (the returned result field is smaller than the whole table data 20% ) Query; used for connection Join * Used for columns and primary key columns (range query ). Order And Group Clause columns; columns to be overwritten. It is advantageous for creating multiple non-clustered indexes for read-only tables. Indexing also has its drawbacks. One is that it takes time to create an index, the other is that the index occupies a large amount of disk space, and the third is to increase the maintenance generation. Price (the index slows down the modification speed when you modify a data column with an index ). In which case do you not create an index? For small tables (Data smaller 5 Page), small to medium table (do not directly access a single row of data or the result set does not need Sorting), single value field (intensive return values), index column value too long (greater 20 bitys ), Columns that are easy to change, highly repeated columns, Null Value column, the pair is not used Where Substatements and Join No index can be created for the queried columns. In addition, for data entry, try to create as few indexes as possible. Of course, it is also necessary to prevent the establishment of invalid indexes. When Where More 5 Index Maintenance The overhead is greater than the efficiency of the index. In this case, it is more effective to create a temporary table to store data.

3.7UnavailableNullFor indexing, any includeNullThe value columns are not included in the index.