The database introduces the index user's most frequent operation of the database is data query. Under normal circumstances, the database needs to be data searches for the entire table when performing query operations. When the data in the table is much large, the search data takes a long time, which causes the server's resources to waste. In order to improve the ability to retrieve data, the database introduces an indexing mechanism. The metaphor of "index" can some extent, you can view the database as a book, regard the index as a book, through the directory lookup information, obviously more than a directory, is convenient, fast. What is the actual database index? (Two Parts Composition) Index is a separate, physical database structure, which is a logical pointer list of data pages in a list or several column values and a corresponding pointing table. The storage of the role of the index in the table is a table consisting of two parts, a part of the data page that stores the table, and the other part stores the index page. The index is stored on the index page, and the index efficient principle is usually much smaller than the data page relative to the data page. When data retrieval, the system first searches for the index page, find the pointer to the required data, and then read the data from the data page by the pointer. The indexed classification is divided into two categories: cluster index (nonclustered index) in the database of SQL Server.
The data in the physical data page of the cluster index is sorted by column, and then re-stored on the disk, that is, the cluster index and data are mixed, and the actual data is stored in its leaf node. Since the cluster index is sorted by one by one in the table, it is quickly displayed with the cluster index. However, because all the data of the cluster index will be completely rearranged, the space it needs is particularly large, which is equivalent to 120% of the space occupied by the data in the table. The data line of the table can only be stored on the disk in a sort mode, so a table can only have a cluster index. Non-clustered indexes have a structure that is fully separated from the table, using non-clustered indexes without sorting data from the physical data page. The value and row locator of keywords constituting non-clustered indexes are stored in the leaf node of non-clustered indexes. The structure and storage content of the line locator depends on the storage method of the data. If the data is stored in a cluster index mode, the row locator is stored in the row locator index key; if the data is not stored in a cluster index, this method is also referred to as a heap structure, the line The locator is stored by a pointer to the data line. Non-clustered indexes sort the line locator in a certain manner in a certain manner, this order does not match the rows of the table in the data page. Since the non-clustered index uses the index page storage, it is more storage space than the cluster index, and the retrieval efficiency is low, but a table can only build a cluster index. When the user needs to create multiple indexes, it is necessary to use a non-clustered index. Summary: Clustered Index is mixed with physical data and reorganizes physical data. It is like using a pinyin pattern; UNCLUSTERED INDEX is completely separated from physical data, using additional space to rearrange the keyword, just like the use department Treasure the dictionary.
Relative Links:
Http://www.chinaitlab.com/www/news/Article_show.asp?id=7606Http://www.windowsitpro.com/sqlser/article/ArticleID/8410/8410.htmlhttp://www.vckbase.com/ Document / ViewDoc /? ID = 1307HTTP: //www.20cn.net/ns/wz/soft/data/20040111171601.htmhttp: //www.ddvip.net/database/msql/index/46.htm
