What are the types and features of database indexes?

What are the types and features of database indexes?

It effectively optimizes the VPS performance and increases the running speed of the VPS server. In addition to configuring the WEB Server properly, we need to be able to optimize our website programs and database, the most basic optimization measure for website database optimization is to create a database index. Here we will introduce it,What is database index? What types and features are there?

(1) What is database index?

　　

A database index is a structure that sorts the values of one or more columns in a database table. You can use indexes to quickly access specific information in the database table. In databases, the meaning of indexes is no different from the word "Index" in the daily sense (think about dictionary scanning when I was a child). It is a database object used to increase the speed of accessing database table data.

　　

① In some cases, indexes can also be used to avoid sorting operations;

　　

② For non-clustered indexes, some queries may not even access data pages;

　　

③ Index can avoid full table scan. Most queries can only scan a small number of index pages and data pages, rather than traversing all data pages;

　　

④ Clustered indexes can prevent data insertion from concentrated on the last data page of the table.

　　

Of course, althoughDatabase indexThis can effectively improve the query speed of database data, but may also lead to a reduction in the performance of database system update data, because most data updates need to update indexes at the same time.

　　

(2) database index type:

　　

Database indexes are like directories in front of a book, which can speed up database queries. Based on the functions of the database, you can create three indexes in the Database Designer: unique index, primary key index, and clustered index. Although the unique index helps to locate information, we recommend that you use a primary key or unique index to obtain the best performance results.

　　

① Clustered index. Table data is stored in the order of indexes. For clustered indexes, leaf nodes store real data rows and no separate data pages exist;

　　

② Non-clustered indexes. The table data storage sequence is irrelevant to the index sequence. For non-clustered indexes, the leaf node contains the index field value and the logical pointer to the data row of the data page. This layer is close to the data page, and the number of rows is consistent with the data row data volume of the data table.

　　

Only one clustered index can be created on a table, because the physical order of real data is only one. If a table has no clustered index, it is called a "Heap ). There is no specific sequence of data rows in such a table, and all new rows will be added to the end of the table.

　　

(3) Basic Features of database indexes:

　　

The purpose of creating an index is to speed up the search or sorting of records in the table. The cost for setting indexes for a table is: first, the storage space of the database is increased, second, it takes a lot of time to insert and modify data (because the index also needs to change ). Database indexes are directories created for values in certain fields to improve the search efficiency of tables.Database indexIs created on top of some columns in the database table. When creating database indexes, you should consider which columns can create indexes and which Columns cannot create indexes. In general, you should create indexes on these columns.

　　

By understanding the database index knowledge and its basic features, it is not difficult to find, establish and useDatabase indexIt has a very beneficial effect on providing the overall performance of the database system and can accelerate the reading speed of the database. However, it also has some drawbacks, the creation, maintenance, and storage of database indexes not only consume a lot of time and energy, but also consume VPS server hard disk space and other resources. Therefore, the maintenance of large databases, we also need to use it with caution.Database index.