Data structure ~sqlserver index using B-Tree

B-Tree related concepts

The method of finding a given keyword in a B-tree is to first fetch the root node, K1,..., kn of the keyword contained in the root node to find the given keyword (available order lookup or binary lookup), and if a keyword equal to the given value is found, the lookup succeeds; otherwise, you can be sure to find the keyword in ki with ki+ 1, pi is a pointer to the sub-root node, at this point the pointer pi refers to the nodes continue to find, until found, or the pointer pi is empty when the lookup failed.

Complexity of Time

The dynamic find tree is mainly: two fork search tree (binary search trees), Balanced binary search tree (Balanced binary search trees), red black tree (Red-black tree), B-tree/b+-tree/b*-tree (B ~tree). The first three are typical two-fork find tree structure, the time complexity of its finding

O (log2N)

Relative to the depth of the tree, reducing the depth of the tree naturally increases the search efficiency.

Performance in SQL Server

We all know that there are two types of storage structures for SQL Server data rows: Heap and B-tree (binary two fork tree). People who have learned the data structure know that the advantages of binary tree are: The rapid use of binary method to find data, data pages using a two-way linked list end-to-end connection. Let's introduce the heap in the data structure. The data in the heap is not in any order and the data page is not connected to the end. So how do you find the data in the heap? The structure and IAM structure of the heap are as follows:

Heap tables only rely on the table's IAM pages (Index allocation mapping page) to link the pages of the heap together, and the page number and page position are recorded in IAM. This allows you to scan data through IAM.
1. In many books, there are two types of storage structures for SQL Server data rows: Heap and B-tree (binary two-tree). And I think SQL Server data pages have two storage structures: Heap and B-tree (binary two-tree).
2. There are two types of data in the page, SQL Server Pages: data pages and index pages. Index pages are stored according to the B-tree structure.
So here's the point:
Index data is stored in the index page, whether it is a clustered index or a nonclustered index.
If there are aggregate indexes in the table, the data is all stored in the index page.
If there are only non-aggregated indexes in the table, the data has a heap page (that is, the actual data row), but the index data is stored in the index page.
If you have both a clustered index and a nonclustered index in the table, both the data and the index are stored in the index page.
B-tree structure, each node is a page, B-Tree will have a page: Root page (that is, the root node), nonclustered indexes and clustered indexes are the same.
Here's the starting track. Introduction Index:

Clustered index:

Some people will ask why a table can have only one clustered index, simply because a table can only be arranged in one order on disk, so a table can have only one clustered index. Non-clustered indexes can have several.
Clustered index Because the data is all stored in the index page, the data can be found along the clustered index. The clustered index structure is as follows:

Nonclustered indexes

1. If the data for the nonclustered index is placed in the heap table (indicating that there is no clustered index), the data for the nonclustered index is placed in the index page. So how does a nonclustered index find data? The leaf node (that is, the leaf page) of a nonclustered index has a row locator, and the row locator points to the position of the row. Consists of file identifiers, page numbers, and the number of rows on the line. The entire pointer is called the row ID (RID).
2. If the data for a nonclustered index is placed in an index table (indicating a clustered index), how do you find the data? The row locator points to the clustered index key. SQL uses the nonclustered index leaf node pointer to the clustered index key value to find the data.

Data structure ~sqlserver index using B-Tree