SQL Server Storage Engine-the structure and classification of indexes

5. SQL Server Storage Engine--the structure and classification of indexes

The relational database expresses the relational model in a two-dimensional table, the data in the table is stored on disk in the form of a page, and in SQL Server, the data page is a contiguous space of 8k on disk, so how are all the data pages of a table organized on disk? In two cases:

First, the data pages are unordered, randomly stored on disk, such a table is called the heap table (no clustered index on the table);

The second is that the data pages are stored sequentially on the disk by the values of a table field, and such tables are indexed to the Organization table (the table has a clustered index).

What is an index? The physical structure can be divided into two types: clustered and non-clustered indexes. An index that organizes the data in a table in an orderly manner is called a clustered index, and a table has only one clustered index, and the other B-tree indexes on the table are nonclustered indexes.

(5.1) Structure of the index

Clustered index Structure

1). The clustered index organizes the data in the table in an orderly manner, not the physical order of the data in the data pages on disk, nor the physical order of the data pages on disk, but rather the logical tree-structured linkage between data pages, as follows:

CreateTableT (IDInt, NAMEvarchar100) AgeInt,)GOINSERTInto TVALUES (1,‘Zhang Yi‘,20)INSERTInto TVALUES (2,‘Zhang Yi‘,25)INSERTInto TVALUES (4,‘Tom‘,21st)INSERTInto TVALUES (5,‘Lee‘,23)INSERTinto t values (7, '  lie triple 24) insert into t values (8, "  John Doe ) gocreate clustered index ix_t_id on t (ID)     

　　　　

The data pages in the Index organization table, in addition to the IAM pages, have one-way or two-way linked list connections between the pages.

2). The non-leaf node of the clustered index (that is, the index node) contains only the first key value of the next node (the entry key value) and a pointer to the next node page, the format of the pointer is: File number + page number, length 2byte+4byte=6byte;

3). The leaf node row of the clustered index is the data row in the table;

4). A table without a clustered index with the following structure:

　　　　　　

There are no linked list links between the data pages in the heap table, which are randomly stored on disk and managed only through IAM pages.

5). The database engine according to the system table to determine the current table for the heap table/Index organization table, and find the heap table first_iam_page/Index Organization table first_page, you can expand the scan table;

Nonclustered index structure

1) The nonclustered index is the index to the clustered index, with the following structure:

2) The index node of a nonclustered index, like a clustered index, contains only the first key value of the next node (the entry key value) and a pointer to the next node page, the format of which is: File number + page number, length 2byte+4byte=6byte;

3) The leaf nodes of a nonclustered index store indexed columns and bookmarks.

If it is a heap table, the bookmark is rowid, the length is 8Byte, that is, the data page number (4Byte) + file Number (2Byte) + slot number (2Byte) row positioning string;

If the index organizes the table, the bookmark is the clustered index key;

On T (age)GOWHERE Age=    

At this point you want to find data based on age 20来. If it is a heap table, according to age is supposed to be id=1 this line of data is 8 bytes of rowid, and then according to the ROWID to find all the data of the row, namely (1, ' Zhang One ', 20); If it is an index organization table, the ID value is 1 based on the non-clustered index on time. Then the id=1 is brought into the clustered index for the equivalent lookup, and finally the leaf node of the clustered index gets all the data of the row;

4) There is a pointer to the upper and lower page on the leaf node of either a clustered or nonclustered index.

(5.2) Classification of indexes

Index classification in SQL Server

(1) B + Tree Index A common index organization structure in the current relational database. B + Tree, it is a multi-fork balanced sorting tree, until the leaf node will hit the data, hereinafter referred to as the B-tree, see the relevant "data structure" books;

(2) Full-text index current relational database A tag-based index organization, which is not a B-tree structure, but rather creates an inverted, stacked, and compressed index structure based on individual tags in the text to be indexed.

(3) XML index with the application of XML text, in each relational database has also provided support for this data structure in succession. The XML instance is stored as a binary large object (BLOB) in the XML Type column. For each XML object in a column, the index creates several rows of data. The number of rows in the index is approximately equal to the number of nodes in the XML object.

--------------------------------------------------------------------------------------
Translated from: http://qianzhang.blog.51cto.com/317608/1217346
--------------------------------------------------------------------------------------

SQL Server Storage Engine-the structure and classification of indexes