The secret to accurate data indexing

What is an index

An index is created by the user, can be modified and deleted, and is actually stored in the database as a physical entity. It is a collection and sum of the values ​​of [one column or several columns] in a certain table. A corresponding list of logical pointers to the data pages in the table that physically mark these values.

Advantages of index

First

By creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

Second

It can greatly speed up data retrieval, which is also the main reason for creating an index.

Third

It can speed up the connection between tables and is particularly meaningful in achieving referential integrity of data.

Fourth

When using grouping and sorting clauses for data retrieval, the time for grouping and sorting in the query can also be significantly reduced.

Fifth

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

Disadvantages of index

1

Time-consuming

It takes time to create and maintain indexes. The time increases with the amount of data.

2

Occupies

Space

Indexes need to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of space. Physical space, if you want to build a clustered index, the space required will be larger.

3

Maintenance

Slower

When adding, deleting and modifying data in the table, the index must also be dynamically maintained. This reduces the speed of data maintenance.

Classification of indexes

1Clustered index

Table data is stored in the order of the index. For a clustered index, the leaf nodes store the actual data rows, and there is no longer a separate data page.

2 Non-clustered index

The storage order of table data has nothing to do with the index order. For a non-clustered index, the leaf node contains the index field value and the logical pointer to the data row of the data page. This layer is adjacent to the data page, and its number of rows is consistent with the amount of data table rows.

Note: Only one clustered index can be created on a table, because the physical order of real data can only be one. If a table does not have a clustered index, it is called a "heap". The data rows in such a table are in no particular order; all new rows will be added at the end of the table. The index of the database can quickly find the location when searching. For database insertion, it is generally inserted into the last row, and the index cannot improve the performance of the insertion.