Advantages and disadvantages of indexes in a database

Transferred from: http://blog.sina.com.cn/s/blog_5a8b8eb80100sg84.html

First, the concept of the index

An index is a 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 scroll 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. Indexes can speed up the retrieval of databases

2. Indexing reduces the speed of maintenance tasks such as database insertions, modifications, and deletions

3. The index is created on the table and cannot be created on the view

4. The index can be created either directly or indirectly

5. You can use the index in the optimization hide

6. Execute SQL statements using the query processor, on one table, with only one index at a time

7. Other

Third, the advantages of the index

1. Create a unique index that guarantees the uniqueness of each row of data in a database table

2. Greatly accelerate the retrieval speed of data, which is the main reason for creating indexes

3. Accelerating the connection between tables and tables, particularly with regard to the realization of the referential integrity of data.

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

5. By using an index, you can improve the performance of your system by using an optimized hidden device in the process of querying.

Iv. Disadvantages of the index

1. It takes time to create indexes and maintain indexes, and this time increases with the amount of data

2. The index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space required will be greater

3. When the data in the table is added, deleted and modified, the index should be maintained dynamically, reducing the maintenance speed of the data.

V. Classification of indexes

1. Creating indexes directly and indirectly creating indexes

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

Indirectly create indexes: Define primary KEY constraints or uniqueness key constraints, you can create indexes indirectly

2. General index and Uniqueness Index

Normal index:

CREATE INDEX Mycolumn_index on MyTable (myclumn)

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

CREATE UNIQUE coustered INDEX myclumn_cindex on MyTable (MyColumn)

3. Single index and composite index

Single index: That is, non-composite index

Composite index: Also called a composite index, contains multiple field names in the index statement, up to 16 fields

CREATE INDEX Name_index on username (firstname,lastname)

4. Clustered and non-clustered indexes (cluster index, clustered index)

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

CREATE CLUSTERED INDEX mycolumn_cindex on MyTable (MyColumn) with

Allow_dup_row (allows clustered index with duplicate records)

Non-clustered index:

CREATE unclustered INDEX mycolumn_cindex on MyTable (MyColumn)

Vi. Use of indexes

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

2. Multiple columns are frequently accessed simultaneously, and each column contains duplicate values to consider a composite index

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

4. Multi-table operations before they are actually executed, the query optimizer lists a number of possible connection scenarios based on the connection criteria and finds the best solution with the lowest system overhead. Join conditions to fully consider the table with the index, the number of rows of tables; The selection of the internal and external tables can be determined by the formula: number of matching rows in the outer table * the number of times each lookup in the inner-level table, the product is minimized as the best scheme

Any action on a column in a 5.where clause results in a column-wise calculation at the SQL runtime, so it has to do a table search without using the index above the column, and if the results are available at query compile time, it can be optimized by the SQL optimizer, using the index, and avoiding table searches.

Cases:

SELECT * FROM record where substring (card_no,1,4) = ' 5378 ' && select * from record where card_no like ' 5,378% '

Any action on a column causes a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

The ' in ' is logically equivalent to ' or ' in the 6.where condition, so the parser converts in (' 0 ', ' 1 ') to column= ' 0 ' or column= ' 1 ' to execute. We expect it to look for each or clause separately, and then add the result so that it 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 is first taken out of the worksheet in the staging database, and a unique index is created to remove the duplicate rows. Finally, the result is calculated from this temporary table. Therefore, the actual process does not take advantage of the column index, and the completion time is affected by the tempdb database performance. The IN, or clauses often use worksheets to invalidate the index, and if you do not produce a large number of duplicate values, consider taking the sentence apart; the disassembled clause should contain an index

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

Advantages and disadvantages of indexes in a database