Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to calculate the index height and index segment size in the database, which is very detailed and has a certain reference value. Interested friends must read it!

B*-Tree level (depth of the index from its root block to its leaf blocks).

A depth of 0 indicates that the root block and leaf block are the same.

If a field is 50 bytes long, a total of 10 million lines

How many leaf nodes of block are needed?

One entry of leaf node = field key value + rowid=50+6=56B

One block capacity = 8KB

Block= 10 million * 56max 8K = 70000

How many block secondary branch nodes are required

An entry for the branch node = field key value + rowid=50+6=56B

One block capacity = 8KB

Block=7 million * 56/8K=490

How many block first-level branch nodes are required

An entry for the branch node = field key value + rowid=50+6=56B

One block capacity = 8KB

Block=490*56/8K=3.43

How many root nodes are needed

An entry of the root node = field key value + rowid=50+6=56B

One block capacity = 8KB

Block=3.43*56/8K=0.0.2

It is concluded that the height of the index is 3 and the size of the index segment is (1 / 4 / 490 + 70000) * 8KB=563M.

If the field length is 10, then the size of the index segment is equal to (4 8KB=112M), and the height of the index is 2. Because the 4max 5 is less than 1, the first-level branch node should not be divided any more, which is the root node.

An index entry is as small as 7B (field 1B size, rowid 6B size), an index block can hold up to 8K/7=1142 index entries, the root node points to up to 1142 branch nodes, and the branch node points to up to 1142 leaf nodes.

The index height is 0, and the maximum 8KB of the index segment

The index height is 1, and the index segment is the largest (1x 1142) * 8KB=8.92M

The index height is 2, and the index segment is the largest (1 "1142" 1142 "1142) * 8KB=10G

The index height is 3 and the index segment is the largest (1 "1142" 1142 "1142" 1142 "1142" 1142) * 8KB=11T

To compromise, usually a field 20B, index entry 26B, an index block can hold up to 307 index entries, the root node points to a maximum of 307 branch nodes, and the branch node points to a maximum of 307 leaf nodes

The index height is 0, and the maximum 8KB of the index segment

The index height is 1, and the index segment is the largest (1-307) * 8KB=2.4M

The index height is 2, and the index segment is the largest (1: 307: 307: 307) * 8KB=739M

The index height is 3, and the index segment is the largest (1 "307" 307 "307" 307 "307" 307 "307) * 8KB=221G

The above is all the contents of the article "how to calculate the index height and index segment size in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.