Database Object-Index

What is an index?

The index should not be unfamiliar. If you say it is a directory, you will probably know that the index of the database is very similar to the directory of the book, which is called index. the book content is equivalent to the data in the database table. The book directory points to the book content through the page number. Similarly, the index records the key values in the table and provides a pointer to the row in the table. the book directory allows the reader to quickly find the desired content without having to flip every page of the book. indexing allows the database application to find the desired data without scanning the entire table.

 

Is indexing more beneficial?

Indexes can be created on a combination of one or more columns, or different indexes can be created on multiple columns in the database table. However, these columns should be frequently used as query conditions. it should be noted that the more indexes on the table, the better. In the database design process, you still need to select some suitable indexes for the table. Rather, it is a specific choice when creating an index. Theoretically, although an infinite index can be set for a table, the more indexes there are in the table, the higher the overhead required to maintain the index. All indexes must be updated in the database system whenever there is a change in the number of records added, deleted, or updated in the data table.

 

Index category

Indexes can be divided into three types:

Clustered index (clustered index ),

Non-clustered index,

Unique index ).

According to the statement that index fields are composed of compound indexes, a composite index can be either a unique index or a unique index. A maximum of 249 clustered indexes and non-clustered indexes can be created on a table.

 

Conditions for creating an index

(1) Create an index for fields that often appear after the keywords orderby, group by, and distinct.

(2) create an index on the result set fields of union and other set operations.

(3) create an index for fields that are often used for query selection.

(4) Create an index on the attributes that are often used as table connections.

(5) Consider using index coverage. For tables whose data is rarely updated, if you often query only a few of the fields, you can consider creating an index on these fields to change the table scan to an index scan.

 

Restrictions on index creation

(1) Limit the number of indexes.

(2) fields with poor uniqueness are not suitable for independent index creation;

(3) frequent updates to fields are not suitable for index creation;

(4) fields not used as query conditions do not create indexes.