What is index? What is an index?

1. Index introduced by the database
   The most frequently performed operations on the databaseData Query. In general, the database needs to search the entire table for data during the query operation. When a table contains a large amount of data, it takes a long time to search for the data, which leads to a waste of server resources. To improve data retrieval capabilities, the database introduces an index mechanism.
2. An index metaphor
   To some extent, we can regard a database as a book, an index as a book directory, and search for information in a book through a directory. Obviously, it is easier and faster than a book without a directory.
3. What is the actual database index? (Composed of two parts)
   IndexIsA separate, physical database structure, which isA set of values of one or more columns in a tableAndList of logical pointers on the data page.
4. Role of the index in the table
   A table is stored in two parts. One part is used to store the data page of the table and the other part is used to store the index page. The index is stored on the index page,
5. Indexing Efficiency Principle
   Generally, index pages are much smaller than data pages. For data retrieval, the system first searches for the index page, finds the pointer to the required data, and then directly reads the data from the data page through the pointer.
6. Index category
   In the SQL Server databaseBy Storage StructureDifferent indexes are divided into two types: Cluster Index and non-cluster index ).
   1. Cluster IndexPhysical Data PageData in by ColumnSortAnd then re-store it to the disk, that isClustered index and data are mixedIts leaf nodes store actual data. Because the Cluster Index sorts the data in the table one by one, it is very fast to search for data using the cluster index. However, because the Cluster Index completely sorts all the data in the table, it requires a very large space, which is approximately equivalent to 120% of the table's data space. The data rows of a table can only be stored on the disk in one sort mode. Therefore, a table can only have one cluster index.
   2. Non-clustered index hasCompletely isolated from table dataNon-clustered indexes do not need to sort the data in the physical data page by column. Leaf nodes with non-clustered indexes store ComponentsValue of the keyword of a non-Cluster IndexAndRow Positioner. The structure and content of the row positioner depend on the data storage method. If the data is stored as a cluster index, the row locator stores the index key of the Cluster Index. If the data is not stored as a cluster index, this method, also known as Heap Structure, stores a pointer to a row of data. Non-clustered indexes sort row locators by keyword values in a certain way. This order does not match the sorting of table rows on the data page. Because non-cluster indexes use index page storage, it requires more storage space than cluster indexes and has low retrieval efficiency. However, one table can only create one cluster index, when you need to create multiple indexes, you need to use non-clustered indexes.

      Summary: clustered index is mixed with physical data and rearranged physical data, just like using pinyin to query the dictionary. unclustered index is completely separated from physical data, the extra space is used to rearrange the keywords, just as the dictionary is used by the department heads.

Relative links:

Http://www.chinaitlab.com/www/news/article_show.asp? Id = 7606
Http://www.windowsitpro.com/SQLServer/Article/ArticleID/8410/8410.html
Http://www.vckbase.com/document/viewdoc? Id = 1307
Http://www.20cn.net/ns/wz/soft/data/20040111171601.htm
Http://www.ddvip.net/database/mssql/index/46.htm