RDBMS Index Log

First, the data structure

B-tree:

1) Balance tree, sub-tree is highly consistent, M-order is m-fork tree

2) The leaf nodes are independent of each other

B+tree:

1) Inherit B-tree

2) n subtrees tree node contains n key;

3) All the leaf nodes contain information about all key codes, and pointers to those key code records, and the leaf nodes themselves are linked by the size of the key code from a large order of origin.

4) All non-terminal nodes can be regarded as the index part, and the node contains only the largest (or smallest) key code in its sub-root node.

So the deeper difference is where:

1. Where is the data (Point/record)? If organized by B-tree, data is likely to be in leaf nodes, possibly in non-leaf nodes, and for B+tree, all data is in the leaf.

2. Storage performance of non-leaf nodes

2.1 B-tree, regardless of whether the point of value is stored in the Non-leaf-node (middle tier node) or the true record of value, it takes space (memory, hard disk) to store this part Point/record

2.2 B+tree, Non-leaf-node only Stores index, (itself will appear in the leaf, and its value can be found in the leaf)

2.3 What's the point?

+/-B+tree each middle-tier node can express a larger index range, the system reads memory/hard disk (such as 4K), can get more indexes, for the scene of reading multiple values, the probability of memory paging/disk sector multiplexing is higher than B-tree in the index lookup process. For writing, B+tree need to write a few more layers, it is not good to say than B-tree write faster.

The process of seek, b-tree see Luck, may run from root to half find value, b+tree all the same, fixed all need to run from root to leaf.

3. Scope query, may b+tree better

For example col between and 500, if it is B-tree, find needs 2 times, 100 points, 500 points, then the middle order (first order?). Post Traverse all the node in the middle of the two points to get all the data (because the data is within node). If it is b+tree, only need to find one, first find 100 points, and then follow the leaf-linkednodelist down the road, hit 500 stop.

Second, the storage mode of Leaf-node

1. Cluster index

Key behind the record, Leaf-node is Data-node, there are various statements, such as what index in the order of key and data in the order of the same, is a meaning.

Zhangsan:zhangsan, Male, Class 3, 32012121918231999

Wanger:wanger, Male, Class 3, 32321912831911123

2. Non-cluster Index

The key behind it is *recode, or the address, or point, which means something.

ZHANGSAN:0X3211FF12, or 4:page707,21, finally Zhangsan, male, Class 3, 32012121918231999

Wanger:0x3241d5eb, or 17:page707, 22, last Wanger, Male, 32321912831911123, Class 3,

So where's the difference?

1. Non-cluster Scan/seek to Leaf-node, you need to get a corresponding address again. and Cluste-index don't need

2. Non-cluster, brother (Zhangsan,wanger) index together, but the actual record is often not together, whether it will produce a large number of missing pages interrupted? Cluster can make full use of the pre-read feature (sequential read-write, not random read/write) of the OS's file system, which is more efficient in a large number of read scenarios.

3. A table, can only have one cluster-index, because the record only one copy (consistency, cost considerations?) ), others are Non-cluster-index,non-cluster-index's value pointing to Cluster-index's key.

4. Cluster-index high demand for writing, writing speed depends on the write order, page splitting, tree adjustment, empty drums and so on.

5. Multiple tables, one key to do foreign keys, then you can make this key cluster-index-space, so that the table associated operation consumes less. (Available in Oracle)

For example, student, Student-score. You can store two tables of data in a block,

Block 101: Two tables of data are available

0x10000000 Table_student:zhangsan, Male, 19, ..........

0x10000010 Table_score:zhangsan, 中文版, 90

BLOCK 102:

0x10001000 Table_student:wanger, Male, 20, ...........

0x10001010 Table_score:wanger, 中文版, 88

You can also do two blocks of connections, such as

BLOCK 201: Only one table

0x20000000 Table_student:zhangsan, male, 19, ..........

0x20001000 Table_student:wanger, Male, 20, ...........

BLOCK 202: Only one table

0x10000010 Table_score:zhangsan, 中文版, 90

0x10001010 Table_score:wanger, 中文版, 88

Of course, this example is just a simple structure of the 1:1 relationship, and the relationship of N-Cluster-index is more complicated, in the final analysis, because it ensures that everyone is orderly, so join operations faster .

Third, Update, delete on the impact of the index

RDBMS Index Log