[MySQL] B + Tree Index

[MySQL] B + Tree Index   Abstract: [mysql]b+ tree index B + Tree is a classic data structure created by the combination of a balance tree and a two-fork lookup tree, which is a balanced lookup tree designed for disk or other direct access assistive devices, in B + trees, All of the record nodes are key values in the order that they are placed in the leaf node of the same layer, the leaf section ... Syntaxhighlighter.all (); ... [MySQL] B + Tree Index The  b+ tree is a classic data structure created by the combination of a balance tree and a two-fork lookup tree, which is a balanced lookup tree designed for disk or other direct access assistive devices, where all record nodes are stored in the same layer of leaf nodes in the B + tree. The leaf nodes are connected by pointers, which form two-way circular linked lists, and non-leaf nodes (root nodes, minor points) only store key values, and do not hold actual data. Here is an example of a 2-tier B + Tree:   to keep the tree balance is mainly to improve the query performance, but in order to maintain the balance of the tree, the cost is also huge, when there is data insertion or deletion, you need to use split node, left and right rotation methods. B + Tree Because of its high fan-out, so it has a high balance, usually its height is in each of the layers, query can effectively reduce the number of IO. B + Tree indexes can be divided into clustered indexes (clustered index) and nonclustered indexes (that is, secondary indexes, secondary index).   Clustered index InnoDB table when the index organization table, that is, the table data by the primary key B + tree storage, leaf nodes directly stored data, each table can only have one clustered index. The   Secondary Index secondary index (also known as a nonclustered index) refers to all the data that the leaf node does not contain rows, and the leaf node contains a bookmark connection in addition to the key value, and then the corresponding row data is found through the bookmark. Shows the relationship between the secondary and clustered indexes of the InnoDB storage engine:   It can be seen that the secondary index leaf node holds the primary key value, obtains the primary key value, and then finds the entire row of data from the clustered index. For example, if you look for data in a secondary index with a height of 3, you first get the primary key value (3 IO) from the secondary index, and then look for the entire row of data (3 IO) from the clustered index with a height of 3, which requires a total of 6 io. Multiple secondary indexes can exist on a table.   Index Organization table VS heap Table MyISAM table is stored in heap table, heap table does not have primary key, so there is no clustered index, secondary index leaf node is not return primary key value, but return row marker (ROWID), through ROWID to find the corresponding row. Obviously, for heap tables, access is faster (less IO) through secondary indexes, but if the data in the table is often modified in the OLTP application, the ROWID in the secondary index may need to be updated frequently, if the update shadowChanges to the physical address, this overhead is much larger than the Peso lead organization table. Therefore, the Index organization table is still a heap table, depending on your application, if your application is OLAP, data updates are rare, and heap tables are better.    Composite Index composite index refers to the index of multiple columns on a table, here is an example of a composite index: [sql] alter table T add key Idx_a_b (A, b);    is a B + tree structure:   It is clear that this compound index can be used for statements such as where a = xxx and b=xxx. Now look at the case of a single column, where a = XXX can also use the composite index, because the a column is also ordered in the composite index, but for a statement such as where B =xxx the composite index cannot be used because it is unordered.

[mysql]b+ Tree index