B+/-tree principle and the index analysis of MySQL

b+/-tree principle and the index analysis of MySQLTags: mysqlmysqlmysqlmysql2012-12-23 17:09 20172 People read Comments (2) favorite reports This article has been included in: MySQL Knowledge BaseClassification:MySQL (+)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Directory (?) [+]

B+/-tree Principle B-tree Introduction B-tree is a multi-path search tree (not two-pronged):
1. Definition of any non-leaf node up to only m sons; m>2;
2. The number of sons of the root node is [2, M];
3. The number of sons of non-leaf nodes outside the root node is [M/2, M];
4. Each node is stored at least m/2-1 (rounded) and up to M-1 keywords; (at least 2 keywords)
5. Number of key words for non-leaf nodes = number of pointers to sons-1;
6. Non-leaf node keywords: k[1], k[2], ..., k[m-1]; K[i] < k[i+1];
7. Pointers to non-leaf nodes: p[1], p[2], ..., p[m], where p[1] a subtree that points to a keyword less than k[1], p[m] a subtree that points to a keyword greater than k[m-1], and other p[i] to the subtree of the keyword belonging to (k[i-1], k[i]);
8. All leaf nodes are located on the same floor;
such as: (M=3)

B-Tree Features:
1. The keyword set is distributed throughout the tree;
2. Any keyword appears and appears only in one node;
3. Search may end at non-leaf nodes;
4. Its search performance is equivalent to doing one-time binary search within the complete range of keywords;
5. Automatic level control;

B-Tree search, starting from the root node, the node in the key (ordered) sequence of binary search, if the hit is finished, otherwise enter the query keyword to the range of the son node; repeat until the corresponding son pointer is empty, or is already a leaf node;

B+tree Introduction

B + trees are variants of B-trees and are also a multi-path search tree:

1. Its definition is basically the same as the B-tree, except:

2. The sub-tree pointer of non-leaf node is the same as the number of keywords;

3. The subtree pointer of the non-leaf node p[i], pointing to the subtree (b-tree is open interval) of the key value belonging to [K[i], k[i+1]);

5. Add a chain pointer for all leaf nodes;

6. All keywords appear at the leaf node;

such as: (M=3)

The B + search is basically the same as the B. C-tree, except that the second B-tree only hits the leaf nodes (b-trees can be hit on non-leaf nodes), and its performance is equivalent to doing a binary search in the keyword complete.

Features of B +:

1. All keywords appear in the list of leaf nodes (dense index), and the key words in the list are in order;

2. Cannot be hit on non-leaf nodes;

3. The non-leaf node is equivalent to the index of the leaf node (sparse index), and the leaf node is equivalent to the data layer of storing (key) data;

4. More suitable for file indexing system;

The index in MySQL

B+tree is commonly used for indexing in MySQL, but it differs in implementation based on clustered indexes and nonclustered indexes.

Clustered index The so-called clustered index refers to the primary index file and data file as the same file, clustered index is mainly used in the InnoDB storage engine. The data on the leaf node of the b+tree in the way of the index implementation is itself, the key is the key, and if it is a generic index, data points to the corresponding primary index, as shown in:

A pointer to an adjacent leaf node is added to each leaf node of the B+tree, and a b+tree with sequential access pointers is formed. The purpose of this optimization is to improve the performance of the interval access, example 4, if you want to query key from 18 to 49 of all data records, when the 18 is found, simply follow the node and the pointer in order to traverse to all data nodes, and greatly mentions the efficiency of the interval query.
Non-clustered cable

The non-clustered index refers to the data on the leaf node of B+tree, not the data itself, but the address where it is stored. There is no difference between the primary and secondary indexes, except that the key in the main index must be unique. Mainly used in the MyISAM storage engine, such as:

The non-clustered index has more IO operations than the clustered index to read the data once, so the lookup performance is poor.

MyISAM index compared to InnoDB index

• MyISAM supports full-text indexing (fulltext), compressed indexes,InnoDB not supported;
• InnoDB support Business,MyISAM not support;
• MyISAM sequential storage data, index leaf node to save the corresponding data row address, the secondary index is very similar to the primary key index,InnoDB primary key node to save the data row, the other secondary index holds the value of the primary key index;
• MyISAM key values are separated, index loaded into memory (key_buffer_size), data cache depends on operating system,InnoDB key value is saved together, index is loaded into InnoDB buffer pool with data; MyISAM primary KEY (unique) index stores storage in ascending order,InnoDB is not necessarily
• The cardinality value of the MyISAM index (cardinality, which theShow Index command can see) is accurate, andInnoDB is the estimate. Here is the knowledge of information statistics,MyISAM statistics is saved on the disk, the ALTER TABLE or the Analyze table operation to update this information, and InnoDB is the first time the table opened when the estimated value is stored in the buffer area;
• MyISAM the way the string index is processed in increments, such as the first index is 'preform ' and the second is 'preformence ', then the second save is '7, Ance ', the obvious benefit is to shorten the index, but the drawback is that it does not support the reverse retrieval of the index, must be sequentially traversed to get the index

Why Choose B+/-tree

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.

In simple words, memory reads, memory is composed of a series of storage units, each storage unit stores a fixed size of data, and has a unique address. When the memory needs to be read, the address signal is placed on the address bus to the memory, the memory parse signal and locate to the storage unit, and then put the data on the storage unit on the bus, callback.

When writing memory, the system will write the data and the cell address on the bus and address buses, memory read two bus content, do the corresponding write operations.

Memory access efficiency, in relation to the number of times, reading a data first or after reading a data does not affect access efficiency. Disk access is not the same, and disk I/O involves mechanical operation. Disks are made up of the same size and coaxial circular discs, and the disks can be rotated (each disk must be rotated simultaneously). There is a head bracket on one side of the disk, and the head bracket secures a set of heads and each head is responsible for accessing the contents of one disk. The head does not move, the disk rotates, but the magnetic arm can be moved back and forth for reading data on different tracks. The track is a series of concentric rings (labeled red) that are centered around the platters. The track is divided into a small segment, called sector, is the smallest disk storage unit.

When the disk is read, the system passes the logical address of the data to the disk, and the control circuitry of the disk resolves the physical address, which is which sector of the track. So the head needs to move forward and backward to the corresponding track, the time spent is called seek time, and then the disk rotation to the corresponding sector to the head, the time spent is called the rotation time. Therefore, the appropriate sequence of operations and data storage can reduce the seek time and rotation time.
To minimize I/O operations, disk reads are read-ahead every time and are typically multiples of the page size. Even if only one byte is to be read, the disk reads a page of data (typically 4K) into memory, and the memory and disk Exchange data in pages. Because of the principle of locality, it is common for a data to be used, and the data near it will be used immediately.

B-tree: If a retrieval requires access to 4 nodes, the database System Designer uses the principle of disk pre-reading, the size of the node is designed as a page, then read a node only need one I/O operation, complete the retrieval operation, up to 3 times I/O (root node resident memory). The smaller the data record, the more data each node holds, the smaller the height of the tree, the less I/O operations, and the retrieval efficiency.

B+tree: Non-leaf nodes only exist key, greatly reducing the size of non-leaf nodes, then each node can store more records, the tree is shorter, I/O operations less. So the B+tree has better performance.

B+/-tree principle and the index analysis of MySQL