Use of database indexes and database Indexes

Use of database indexes and database Indexes
Preface

Write the background management system for testing and use, and find that loading data is slow, with an average of over 1 second. Then I worked with my colleagues to find the cause, from the back-end code to the front-end,

The final problem is that in the database design, the table does not have a primary or foreign key. After we add the index, the speed is high...

What is database index?

An index is a structure that sorts the values of one or more columns in a database table. You can use an index to quickly access specific information in a database table.

Database indexes are like directories in front of a book, which can speed up database queries.

Index classificationClustered IndexAndNon-clustered IndexTwo types: clustered indexes are stored in the order of physical locations, rather than clustered indexes;

Clustering indexes can improve the speed of multi-row search, rather than clustering indexes can quickly retrieve individual rows.

Advantages and disadvantages of Indexes

First, you can create a unique index to ensure the uniqueness of each row of data in the database table.
Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.
Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.
Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

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

Disadvantages

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.
Second, 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 is larger.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

 

What are the features of indexes?

The unique index ensures that all data in the index column is unique and does not contain redundant data.

A composite index is an index created in two or more columns.

In short, using indexes is good.