Mysql index principle-B +/-Tree

Hi. baidu. comlzpskyitem70b944dffe4a9e16e1f46f27 index is used to query data more quickly. There are many query algorithms and a large number of corresponding data structures. Generally, the index data structure of databases is B + Tree. 1. The official definition of B-Tree is hard to understand. A common example is B-Tree. Suppose:

Http://hi.baidu.com/lzpsky/item/70b944dffe4a9e16e1f46f27 index, is to query data faster, there are many query algorithms, the corresponding data structure is also a lot, the database commonly used index data structure is generally B + Tree. 1. The official definition of B-Tree is hard to understand. A common example is B-Tree. Suppose:

Http://hi.baidu.com/lzpsky/item/70b944dffe4a9e16e1f46f27

Indexes are used to query data more quickly. There are many query algorithms and many corresponding data structures. Generally, the database uses B + Tree as the index data structure.

1. B-Tree

I personally think that the official definition of B-Tree is hard to understand. The general point is to give an example. Assume that an English dictionary with a word + detailed explanation constitutes a record, and now the word needs to be indexed, then the word is the key, and the word + detailed explanation is the data, b-Tree defines a record with a Binary Group {key, data. If a node has three records, there will be four corresponding pointers to point to the next node. B-Tree is ordered and balanced, and all leaf nodes are on the same layer, that is, there will be no more branches and fewer branches.

Because B-Tree is ordered, its search is simple. First, the root node starts the binary search and returns the node. Otherwise, the next node is searched along the interval pointer. For example, query the word "false.

2. B + Tree

Different from B-Tree, B + Tree has only keys and no data for each node, and leaf nodes have no pointers. That is to say, the data structure of the leaf node of the B + Tree is different from that of the inner node.

Generally, the database uses B + Tree and has been optimized. For example, the sequential access pointer is added to the leaf node to improve the interval query efficiency. For example, the first character of the query is f ~ T. Then, you only need to find the first word fabric starting with f, and traverse it along the leaf node until you find the last word starting with t.

?

This section briefly introduces B-/+ Tree. As to why database indexes choose B-Tree and B + Tree in a large number of data structures, the following describes the principle of computer storage.

3. Read Memory and read Disk

The efficiency of memory reading and disk reading is very different.

To put it simply, memory reading is composed of a series of storage units. Each storage unit stores data of a fixed size and has a unique address.

When you need to read the memory, place the address signal to the address bus and upload it to the memory. The memory parses the signal and locates it in the storage unit. Then, the data on the storage unit is placed on the Data Bus for return.

When writing memory, the data and unit addresses to be written by the system are put on the data bus and the address bus respectively. The memory reads the content of the two bus and performs corresponding write operations.

Memory access efficiency depends on the number of times. Reading data A first or later does not affect access efficiency. Disk access is different. Disk I/O involves mechanical operations.

A disk is a circular disk with the same size and coaxial. the disk can be rotated (each disk must be rotated at the same time ). One side of the disk has a head bracket, which is fixed with a set of heads, each of which is responsible for accessing the content of a disk. The magnetic head does not move, and the disk rotates, but the magnetic arm can move before and after it is used to read data on different channels. The track is a series of concentric rings (marked with red circles) divided with disks as the center ). Tracks are divided into small segments, called slice, which is the smallest storage unit of a disk.

When a disk is read, the system sends the Logical Data address to the disk. the control circuit of the disk resolves the physical address, that is, the sector of the track. As a result, the head needs to move back and forth to the corresponding track. The consumed time is called the track seeking time. Then, the disk rotates and forwards the corresponding sector to the head. The consumed time is called the rotation time. Therefore, proper operation sequence and data storage can reduce the seek time and rotation time.

To minimize I/O operations, the disk reads data in advance each time, usually an integer multiple of the page size. Even if you only need to read one byte, the disk will read one page of data (usually 4 K) into the memory, and the memory and disk will exchange data in pages. Because of the locality principle, a piece of data is usually used, and nearby data will be used immediately.

4. Search Performance Analysis

B-Tree: If a retrieval request requires access to four nodes, the database system designer designs the node size as a page based on the disk pre-read principle, to read a node, only one I/O operation is required. To complete this retrieval operation, a maximum of three I/O operations are required (root node resident memory ). The smaller the data record, the more data each node stores, the smaller the tree height, the fewer I/O operations, and the retrieval efficiency.

B + Tree: only the key is saved on the inner node, which greatly reduces the size of the inner node. Therefore, each node can store more records and the Tree is shorter, i/O operations are reduced. Therefore, B + Tree has better performance.

5. Other Indexing Methods

HASH index: an index that is located by HASH. This index is rarely used and is used for single-value queries. The adaptive index of InnoDB is the HASH index.

Bitmap index: the field value is fixed and small, such as gender and status. When you query multiple such fields and/or at the same time, the efficiency is extremely high. You can directly obtain the result by bit and/or. Therefore, the application scope is limited.

Author: javaACMer published at 23:12:40, November 5

Read: 79 comments: 0 view comments

Original article address: B +/-Tree of mysql index principle. Thank you for sharing it with me.