[MySQL] B + tree indexes

[MySQL] B + tree index B + tree is a classic data structure generated by the combination of the Balance Tree and the binary search tree, it is a balanced search tree designed for disks or other direct access devices. In the B + tree, all record nodes store key values in the same layer of leaf nodes in sequence. Leaf nodes are connected by pointers to form a bidirectional circular linked list. Non-leaf nodes (root nodes and branch nodes) store only the key value, not the actual data. Next, let's look at a two-layer B + tree example: to maintain the tree balance, we mainly aim to improve the query performance. However, to maintain the tree balance, the cost is also huge, when data is inserted or deleted, You need to split the node, left-hand, right-hand, and other methods. The B + tree has a high balance because of its high fan-out performance. Generally, its height ranges from 2 ~ Layer 3, which can effectively reduce the number of IO queries. B + tree indexes can be divided into clustered indexes and non-clustered indexes (secondary indexes and secondary indexes ). When an InnoDB table is clustered, the table is indexed. That is, the table data is stored in the primary key B + tree, and the leaf node directly stores the data. Each table can have only one clustered index. Secondary index secondary index (also called non-clustered index) refers to a leaf node that does not contain all the data of rows. A leaf node also contains a bookmarked connection in addition to the key value, you can use this bookmark to find the corresponding row data. Displays the relationship between the secondary index and the clustered index of the InnoDB Storage engine: we can see that the secondary index leaf node stores the primary key value. After obtaining the primary key value, then, the whole row of data is searched from the clustered index. For example, if you search for data in a secondary index with a height of 3, first obtain the primary key value (three IO times) from the secondary index ), then, search for the whole row of data (3 IO) from the clustered index with a height of 3, which requires 6 IO operations in total. A table can have multiple secondary indexes. Index organization table VS heap table MyISAM tables are stored as heap tables. The heap table does not have a primary key, so there is no clustered index. The secondary index leaf node does not return the primary key value, the row identifier (ROWID) is returned, and the corresponding row is searched through ROWID. Obviously, for heap tables, accessing through secondary indexes is faster (less IO). However, if the table data is frequently modified in OLTP applications, the ROWID in the secondary index may need to be updated frequently, if updates affect physical address changes, this overhead is much larger than the index organization table. Therefore, the index organization table or heap table depends on your application. If your application is OLAP, there are few data updates, and the heap table is better. Composite index refers to index multiple columns on the table. The following is an example of composite index: [SQL] alter table t add key idx_a_ B (a, B ); B + tree structure: Obviously, this composite index can be used for statements such as where a = xxx and B = xxx. Now let's look at the situation of a single column. where a = xxx can also use this composite index, because column a is also ordered in the composite index, however, a statement such as where B = xxx cannot use the composite index because it is unordered.