Advantages and disadvantages of indexes in Databases

I. Index concept index is a method to accelerate the retrieval of table data. A database index is similar to a book index. In books, indexes allow users to quickly find the desired information without having to read the complete book. In the database, the index also allows the database program to quickly find the data in the table without scanning the entire database. Ii. Index features 1. Cable

I. Index concept index is a method to accelerate the retrieval of table data. A database index is similar to a book index. In books, indexes allow users to quickly find the desired information without having to read the complete book. In the database, the index also allows the database program to quickly find the data in the table without scanning the entire database. Ii. Index features 1. Cable

I. Index concept

Index is the method to accelerate the retrieval of table data. A database index is similar to a book index. In books, indexes allow users to quickly find the desired information without having to read the complete book. In the database, the index also allows the database program to quickly find the data in the table without scanning the entire database.

Ii. Index features

1. indexing can speed up Database Retrieval

2. Indexes reduce the speed of maintenance tasks such as database insertion, modification, and deletion.

3. indexes are created on tables and cannot be created on views.

4. indexes can be created directly or indirectly.

5. You can use indexes in optimization hiding.

6. Use the query processor to execute SQL statements. Only one index can be used at a time on a table.

Iii. Advantages of Indexes

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

2. The data retrieval speed is greatly accelerated, which is also the main reason for index creation.

3. Accelerate the connection between tables, especially for Data Reference integrity.

4. When you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

5. By using indexes, you can use the optimizer during the query process to improve system performance.

Iv. Index disadvantages

1. It takes time to create and maintain indexes. This time increases with the increase of data volume.

2. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space will be larger.

3. When adding, deleting, and modifying table data, the index must be maintained dynamically, reducing the Data Maintenance speed.

V. Index classification

1. directly create indexes and indirectly create indexes

Directly CREATE an INDEX: create index mycolumn_index ON mytable (myclumn)

Indirect index creation: defines the primary key constraint or unique key constraint. indexes can be created indirectly.

2. Common and unique Indexes

Common Index:

Create index mycolumn_index ON mytable (myclumn)

Unique index: ensure that all data in the index column is unique and can be used for clustered indexes and non-clustered 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 composite index. The index creation statement contains multiple field names, up to 16 fields.

Create index name_index ON username (firstname, lastname)

4. Clustered index and non-clustered index (clustered index and clustered index)

Clustered index: physical index. The physical order is the same as that of the base table. The data values are always sorted in order.

Create clustered index mycolumn_cindex ON mytable (mycolumn)

ALLOW_DUP_ROW (clustering indexes with repeated records are 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, the query frequency is high and there are a large number of duplicate values. We recommend that you use clustered indexes.

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

3. The pre-column of the composite index must be properly controlled; otherwise, the index will not work. If the leading column is not in the query condition during the query, the composite index will not be used. The leading column must be the most frequently used column.

4. Before a multi-table operation is executed, the query optimizer will list several possible connection solutions based on the connection conditions and find the best solution with the minimum system overhead. The join conditions must fully consider the tables with indexes and tables with multiple rows. The selection of the internal and external tables can be determined by the formula: Number of matched rows in the outer table * Number of times each query is performed in the inner table, minimum product is the best solution

5. any operation results on the column in The where clause are calculated by column one by one during SQL Execution. Therefore, it has to perform table search without using the index on the column; if these results are obtained during query compilation, they can be optimized by the SQL optimizer and indexed to avoid table search.

Example: select * from record where substring (card_no, 5378) = '20160301'
& Select * from record where card_no like '201312'

Any operation on the column will cause the table to scan, including database functions and calculation expressions. During the query, try to move the operation to the right of the equal sign.

6. the 'in' in THE where condition is logically equivalent to 'or', so the syntax analyzer will set in ('0', '1 ') convert to column = '0' or column = '1' for execution. We expect it to search for each or clause separately and then add the results so that the index on the column can be used. However, it actually uses the "or policy ", that is, first extract the rows that satisfy each or clause, store them to the worksheet of the temporary database, create a unique index to remove duplicate rows, and finally calculate the results from this temporary table. Therefore, the index on the column is not used in the actual process, and the completion time is also affected by the performance of the tempdb database. The in and or clauses usually use worksheets to invalidate the index. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.