B-tree, B+tree and index, joint index

B-tree, B+tree and index, joint index
In 1970, R. Bayer and E. mccreeght proposed a tree suitable for external lookup, which is a balanced multi branch tree called B-tree (or B-tree, B-tree).
A balanced tree of order m is a balanced m-path search tree. It is either an empty tree or a tree that satisfies the following properties:
1. The root node has at least two children;
2. The number of keywords j contained in each non root node satisfies: ǐ m/2-1<=j<=m-1;
3. The degree of all nodes (excluding leaf nodes) except the root node is exactly equal to the total number of keywords plus 1, so the number of internal subtrees k satisfies: ǐ m/2<=k<=m;
4. All leaf nodes are located on the same layer.
In the B-tree, the keywords in each node are arranged in ascending order, and when the child of the node is a non leaf node, the k-1 keywords are exactly the partition of the value range of the keywords contained in the k children.
Because leaf nodes do not contain keywords, they can be considered as having no external nodes in the tree. Pointers to these external nodes are empty, and the number of leaf nodes is exactly equal to the total number of keywords contained in the tree plus 1.
The general form of a node in a B-tree that contains n keywords and n+1 pointers is: (n, P0,K1,P1,K2,P2,…,Kn,Pn）
Among them, Ki is the keyword, K1<K2<...<Kn, Pi is a pointer to the subtree containing keywords between Ki and Ki+1.
Application: The index data structure used in SQL Server databases, the index data structure used in Oracle databases (in addition to bitMap bitmaps)
B+Tree: A tree data structure commonly used in file systems of databases and operating systems. The characteristic of B+trees is that they can maintain stable and orderly data, and their insertion and modification have a relatively stable logarithmic time complexity. The B+tree element is inserted from bottom to top, which is exactly the opposite of a binary tree.
Application: The default storage engines for MySQL, InnoDB and MyISAM, both use B+trees.
B+tree is a variant of B tree, where leaf nodes store keywords and corresponding record addresses, and the layers above the leaf nodes are used as indexes. The definition of an m-order B+tree is as follows: [2]
(1) Each node can have up to m children; [2]
(2) Except for the root node, each node has at least [m/2] children, and the root node has at least two children; [2]
(3) A node with k children must have k keywords. [2]
The search of B+tree is different from that of B tree. When the keyword of a node in the index is equal to the searched keyword, the search does not stop. Instead, it should continue to follow the pointer to the left of the keyword until it reaches the leaf node where the keyword is located. [2]
The difference between B tree and B+tree:
B-tree: A binary tree, where each node only stores one keyword. If it is equal to, it will hit. If it is less than the left node, it will hit. If it is greater than the left node, it will hit
Go to the right node;
B-Tree: A multi-channel search tree, where each node stores M/2 to M keywords, and non leaf nodes store key points
The child nodes of the word range;
All keywords appear in the entire tree only once, and non leaf nodes can be hit;
B+Tree: On the basis of B-tree, add linked list pointers to leaf nodes, and all keywords are located at the leaf nodes
Appearing in, non leaf nodes are used as the index for leaf nodes; B+trees always hit at leaf nodes;
B * Tree: On the basis of B+Tree, add linked list pointers to non leaf nodes to minimize node utilization
Increase from 1/2 to 2/3;
Index:
In a relational database, an index is a separate, physical storage structure that sorts the values of one or more columns in a database table. It is a collection of one or more column values in a table and a corresponding logical pointer list to the data page that physically identifies these values in the table. The function of an index is similar to that of a book's table of contents, which can quickly find the desired content based on the page numbers in the table of contents.
The index provides pointers to data values stored in specified columns of the table, and then sorts these pointers according to the specified sorting order. The database uses an index to find a specific value, and then finds the row containing that value in a clockwise direction. This can make the SQL statements corresponding to the table execute faster and quickly access specific information in the database table.
When there are a large number of records in the table, the first way to query the table is to search for information through a full table search. It involves extracting all records one by one, comparing them with the query conditions, and then returning records that meet the conditions. This will consume a lot of database system time and cause a lot of disk I/O operations; The second method is to establish an index in the table, then find the index values that meet the query criteria in the index, and finally quickly find the corresponding records in the table through the ROWID (equivalent to page numbers) saved in the index.
Federated index:
If you need to use multiple fields for multi condition queries, you can consider establishing a joint index. Generally, fields other than the first field are not frequently used for condition filtering, such as a and b. If you frequently use a or a+b conditions to query and rarely use b conditions alone, you can establish a joint index for a and b. If both a and b need to be used as query conditions independently and frequently, it is better to establish multiple single column indexes.
————————————————
Copyright Statement: This article is an original article by the blogger and follows the CC 4.0 BY-SA copyright agreement. Please include the original source link and this statement when reprinting.
                        
Original link: https://blog.csdn.net/m0_51660523/article/details/139480238