Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
The index database introduces the most frequent operation of the index user to the database is the data query. In general, a database needs to search the entire table for a query operation. When the data in the table is very large, it takes a long time to search the data, which causes the server to waste resources. In order to improve the ability of retrieving data, the indexing mechanism is introduced into the database. On the analogy of "index" to some extent, the database can be regarded as a book, the index as a book directory, through the directory to find information in the book, obviously less than the directory of books convenient and fast. What is the actual database index? (Two-part) an index is a separate, physical database structure that is the collection of one or more column values in a table and the corresponding list of logical pointers to the data pages in the table that physically identify those values. The role of an index in a table the storage of a table is made up of two parts, one for the data page of the table and the other for the index page. The index is stored on the index page, and the index page is usually much smaller than the data page. When data retrieval is done, the system searches the index page, finds the pointer to the desired data, and then reads the data directly from the data page through the pointer. The classification of indexes divides the indexes into two classes in the database of SQL Server by storage structure: Cluster index (Clustered index) and nonclustered index (nonclustered index). The cluster index sorts the data in the physical data pages of a table by column and then back to disk, where the cluster index is mixed with the data, and the actual data is stored in its leaf nodes. Because the cluster index sorts the data in a table, it is very quick to find data with a clustered index. But because the cluster index completely rearranges all the data in the table, it requires a particularly large amount of space, roughly 120% of the space occupied by the table's data. A table's data rows can only be stored on disk in one sort way, so a table can have only one cluster index. Non-clustered indexes have a completely detached structure from the data in the table, and nonclustered indexes do not have to sort the data in a physical data page by column. The values and row locators of the keywords that make up the nonclustered index are stored in the leaf nodes of the nonclustered index. The structure and storage content of a row locator depends on how the data is stored. If the data is stored in a clustered index, the index key of the cluster is stored in the row locator, and if the data is not stored in a clustered index, which is also known as heap storage (Heap Structure), the row locator stores a pointer to the data row. A nonclustered index sorts a row locator in a certain way by the value of the keyword, which does not match the order of the table's rows in the data page. Because a non-clustered index uses index page storage, it requires more storage space than a clustered index and is less efficient but a table can only have one clustered index, and a nonclustered index is required when a user needs to build multiple indexes.
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to firstname.lastname@example.org. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
and provide relevant evidence. A staff member will contact you within 5 working days.