Teach you a trick: the application of MSSQL database index

First, the concept of the index

Indexing is the way to speed up the retrieval of data in a table. The index of the database is similar to the index of the book. In books, the index allows users to quickly find the information they need without having to read through the entire book. In the database, the index also allows the database program to quickly locate the data in the table without having to scan the entire database.

Second, the characteristics of the index

1. Index can speed up retrieval of database

2. Indexing reduces the speed of database inserts, modifications, deletions, and other maintenance tasks

3. Indexing is created on a table and cannot be created on a view

4. Indexes can be created either directly or indirectly

5. You can use the index in the optimization hide

6. Use the query processor to execute the SQL statement, on a table, you can use only one index at a time

7. Other

Third, the advantages of the index

1. Create a unique index to ensure uniqueness of each row of data in a database table

2. Greatly speed up the retrieval of data, which is the main reason to create indexes

3. The acceleration of the connection between tables and tables, especially with regard to the realization of the referential integrity of the data, is particularly relevant.

4. When you use grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in a query.

5. By using the index, you can use the optimization hidden in the query process to improve the performance of the system.

Iv. Disadvantages of the index

1. It takes time to create indexes and maintain indexes, which increase as the amount of data increases

2. The index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies certain physical space, if wants to establish the clustered index, then needs the space to be bigger

3. When the data in the table to add, delete and modify the time, the index will also be dynamic maintenance, reduce the data maintenance speed

V. Classification of indexes

1. Creating indexes directly and indirectly creating indexes

Creating index directly: Create INDEX mycolumn_index on MyTable (myclumn)

Indirectly create an index: Define a PRIMARY KEY constraint or a Uniqueness key constraint, and you can create an index indirectly

2. General index and Uniqueness Index

Normal index:

CREATE INDEX Mycolumn_index on MyTable (myclumn)

Uniqueness Index: guarantees that all data in an indexed column is unique and can be used for both clustered and nonclustered indexes

CREATE UNIQUE coustered INDEX myclumn_cindex on MyTable (MyColumn)

3. Single index and composite index

Single index: Non-composite index

Composite index: Also called combined index, contains more than one field name in the index establishment statement, up to 16 fields

CREATE INDEX Name_index on username (firstname,lastname)

4. Clustered index and nonclustered index (clustered index, cluster index)

Clustered index: Physical index, the same as the physical order of the base table, and the order of data values is always sorted in order

CREATE CLUSTERED INDEX mycolumn_cindex on MyTable (MyColumn) with

Allow_dup_row (clustered index with duplicate records allowed)

Non-clustered index:

CREATE unclustered INDEX mycolumn_cindex on MyTable (MyColumn)

Vi. Use of indexes

1. When the field data Update frequency is low, query usage is high and there is a large number of duplicate values is recommended to use clustered index

2. Multiple columns are frequently accessed at the same time, and each column contains duplicate values to consider establishing a composite index

3. The leading columns of the composite index must be well controlled, otherwise the effect of indexing cannot be played. If the leading column is not in the query condition, the composite index is not used. The leading column must be the most frequently used column

4. The query optimizer lists several sets of possible connectivity scenarios and finds the best solution for the least cost of the system, based on the connection conditions, before the multiple table operations are actually executed. The join condition takes into account the table with the index, the table with many rows, the selection of the inner and outer table can be determined by the formula: the number of matching rows in the outer table determines the count of each lookup in the inner table, and the product minimum is the best scheme.

Any operation result of a column in a 5.where clause is computed by column in the SQL runtime, so it has to do a table search instead of using the index above the column, and if the results are available at query compilation, you can optimize the SQL optimizer by using indexes to avoid table searches.

Cases:

The following are the referenced contents: SELECT * FROM record where substring (card_no,1,4) = ' 5378 ' && select * from record where card_no like ' 5,378% '

Any action on the column will result in a table scan, including database functions, evaluation expressions, and so on, to move the action to the right of the equals sign whenever possible

The ' in ' of the 6.where condition is logically equivalent to ' or ', so the parser converts in (' 0 ', ' 1 ') to column= ' 0 ' or column= ' 1 ' to execute. We expect it to be looked up according to each or clause separately, add the result so that you can take advantage of the index on column, but in fact it takes an "or policy," that is, the row that satisfies each or clause, the worksheet in the temp database, and the unique index to remove the duplicate rows. Finally, the result is computed from this temporary table. Therefore, the actual process does not use the index on column, and the finish time is also affected by the performance of the tempdb database. The IN, or clauses often use worksheets to invalidate indexes; If you do not produce a large number of duplicate values, you can consider the opening of the sentence; an open clause should contain an index

7. To be good at using stored procedures, it makes SQL more flexible and efficient