Mysql Index implementation principle. Clustered index, nonclustered index

MySQL Index implementation:

B-tree,b is balance, which is commonly used for database indexing . Using the B-TREE structure can significantly reduce the intermediate process that is experienced when locating records, thus speeding up the access speed. While B+tree is a variant of b-tree, MySQL generally uses b+tree to implement its index structure.

In general, the index itself is large and cannot be stored in memory, so the index is often stored as an index file on the disk . In this way, the index lookup process will generate disk I/O consumption, relative to memory access, I/O access to the consumption of a few orders of magnitude, so the evaluation of a data structure as an index is the most important indicator of the number of disk I/O operations in the process of incremental complexity. In other words, the structural organization of the index minimizes the number of disk I/O accesses during the lookup process.

To achieve this, the disk reads on demand and requires that the length of the read-ahead is usually an integer multiple of the page. and the database system sets the size of a node equal to one page, so that each node can be fully loaded with only one I/O. Each time you create a new node, request a page space directly, so that a node is physically stored in a page, and the computer storage allocation is page-aligned, the implementation of a node only one time I/O. And put the M value in the B-tree is very large, it will let the height of the tree down, in favor of a full load

B-way Find tree:

The degree of each node of a tree is less than or equal to M.

The number of key values per node less than m per node is less than or equal to the M key value in order for the key values of the subtree to be exactly less than or greater than or between the parent nodes

B-tree Tree:

B-tree is also called balanced multi-path lookup tree. The characteristics of an M-order B-tree (M-fork tree) are as follows:

(where Ceil (x) is a function that takes the upper limit)

1) Each node in the tree has a maximum of M children;

2) root nodes and leaf nodes, each node has at least ceil (M/2) children;

3) Joghen node is not a leaf node, then at least 2 children (special case: No Child root node, that is, the root node is a leaf node, the whole tree has only one root);

4) All leaf nodes appear on the same layer, and the leaf nodes do not contain any keyword information (which can be seen as an external node or a node where the query failed, in fact these nodes do not exist, pointers to these nodes are null);

5) Each non-terminal node contains n keyword information: (N,P0,K1,P1,K2,P2,......,KN,PN). which

A) Ki (I=1...N) is the keyword, and the keywords are sorted by order K (i-1) < Ki.

b) Pi is a contact point pointing to Subtree, and the key of the pointer P (i-1) to all nodes of the subtree is less than Ki, but both are greater than K (i-1).

c) The number of keywords n must meet: Ceil (M/2)-1 <= n <= m-1.

B+tree Tree:

B + trees are variants of B-trees.

There are a couple of different places:

1. The subtree pointer of the non-leaf node is the same as the number of the keywords.
2. Adds a chain pointer to all leaf nodes.
3. All the keywords appear at the leaf node.

Second, clustered index, nonclustered index

Clustered index:

　　The logical order of the key values in the index determines the physical order of the corresponding rows in the table .

The clustered index determines the physical order of the data in the table. A clustered index is similar to a phone book, which arranges data by last name. Because a clustered index specifies the order in which data is physically stored in a table, a table can contain only one clustered index. However, the index can contain multiple columns (combined indexes), just as the phone book is organized by last name and first name.

Nonclustered indexes:　　

The logical order of the indexes in this index is different from the physical storage order of the disk upstream .

The index is described by the data structure of the binary tree, and we can understand it this way: the leaf node of the index is the data node. The leaf node of the non-clustered index is still an index node, but one pointer points to the corresponding data block. Such as:

Mysql Index implementation principle. Clustered index, nonclustered index