What is the database index of what types and features _ database other

Effectively optimize the performance of the VPS, improve the running speed of the VPS server, in addition to a reasonable configuration of the Web server, more is the need for us to be able to optimize the Web site procedures and website database, the optimization of the site database is the most basic optimization measures is to establish the database index, here on What is a database index? What are the types and characteristics?

⑴, what is a database index?

　　

A database index is a structure that sorts the values of one or more columns in a database table, using indexes to quickly access specific information in a database table. In a database, the meaning of an index is not much different from the word "index" in the daily sense (think of a dictionary as a child), a database object used to improve the speed of data access in a database table.

　　

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

　　

② for nonclustered indexes, some queries can even not access data pages;

　　

③ indexes prevent full table scans. Most queries can scan only a small number of index pages and data pages, rather than traverse all data pages;

　　

④ clustered indexes prevent data insertions from being concentrated on the last page of data in a table.

　　

Of course, although database indexes can effectively improve the query speed of database data, it also causes the performance of database system Update data to decrease because most data updates need to update the index at the same time.

　　

⑵, type of database index:

　　

A database index is like a table of contents in front of a book that speeds up querying the database. Depending on the functionality of your database, you can create three indexes in the Database Designer: A unique index, a primary key index, and a clustered index. Although a unique index helps locate information, it is recommended that you use a primary key or a unique index instead for the best performance results.

　　

① a clustered index in which table data is stored in the order in which they are indexed. For the clustered index, the leaf node stores the real data rows, and there is no separate data page.

　　

② nonclustered indexes, the table data store order is independent of the index order. For nonclustered indexes, the leaf node contains the index field value and the logical pointer to the data page data row, which is next to the data page and has the same number of rows as the data table row data.

　　

Only one clustered index can be created on a single table, because the physical order of the real data can only be one. If a table does not have a clustered index, it is called a "heap" (Heap). The data rows in such a table have no specific order, and the end position of all the new rows that are added to the table.

　　

⑶, the basic features of database indexing:

　　

The purpose of indexing is to speed up the lookup or sorting of records in a table. There is a cost to setting up the index for a table: the first is to increase the storage space of the database, and the second is to spend more time inserting and modifying the data (because the index will also change). A database index is a directory of values in some fields that are created to improve the search efficiency of a table. A database index is built on top of some columns in a database table. When you create a database index, you should consider which columns you can create indexes on, and on which columns you cannot create indexes. In general, you should create indexes on these columns.

　　

By understanding the knowledge of database indexing and its basic characteristics, it is not difficult to find that the establishment and use of database index , which is extremely beneficial to the overall performance of the database system, can greatly speed up the reading speed of database database, but at the same time, There are some drawbacks, the creation and maintenance of database indexing, as well as storage will not only consume a lot of time and energy, but also extremely consumes the VPS server hard disk space resources, therefore, for large database maintenance, we also need to use the database index carefully.