Reading notes-mysql operation and Maintenance 08-Index Implementation Principle 1

The difference between B-and + + trees

The leaf nodes and inner nodes of the 1,b tree are all the information of the data row, and the value of the inner node of the B + tree holds the key (index) information, and the data is on the leaf node.

2, because of the B-tree key and the value of all information, so each page of stored data rows relatively small, with the data development, the tree hair into a high-thin tree; Conversely, a B + tree's inner node only stores key values, so it becomes a chunky tree. So in terms of search, the efficiency of B + tree is higher than the efficiency of the tree.

The query efficiency of the 3,b tree is related to the location of the B tree, while the complexity of the C + trees is fixed for one B + tree.

4,b trees, in general, can save storage space relative to B + trees, but the complexity of insertion and deletion increases significantly, and performance is uneven (sometimes it can be found in a suitable location, sometimes consuming a lot of Io). The B + Tree is a good compromise. The query process is stable, and the insert delete operation is usually a split at most (the node storage at the right location is slow and needs to be split).

5,b tree species All data is stored only once. B + Species In addition to the leaf node to store all the data, the internal node is required to store key data. So in terms of space, the B + tree takes up more space than a tree, but the C + tree improves overall performance.

Design of the Index

Three factors that affect your computer's tasks: memory, processor, and disk speed.

Disk performance is related to read-write order, and sequential reads and writes are much faster than random reads and writes.

How the index is designed to be stored:

1. partition disk space or files by not many blocks or pages of the same size, and each block can store multiple rows.

2, within a block, the data is organized by means of linked lists or arrays.

3, in a block, all the data is sorted by the key value, you can use the classic binary search to quickly locate the corresponding data row.

4, through the block to carry data, through the B + tree to organize the relationship between different blocks.

5, through the key value of the inner node and a position information, the inner node and the lower node or the pointer of the leaf node, it is convenient to find the child nodes of the inner node.

Clustered indexes and level two indexes

The index that stores all the data becomes a clustered index, and the order of the clustered indexes is sorted by primary key (which can be rowid or a self-increment ID or other primary key set by the user).

Back to the table, the data columns on the Level two index cannot all overwrite the query required by the lock, which requires a pointer to a level two index to find the clustered index.

Structure of the clustered index:

Index structure: [PRIMARY key column][trxid][rollptr][other non-primary key columns created when the table is built]

Columns participating in the record comparison: primary key columns

Inner node key column: [PRIMARY key column]+pageno pointer

Note: The above-mentioned primary key, if the user has defined the primary key refers to the user-defined primary key, otherwise the system gives the invisible primary key (Rowid)

Structure of secondary indexes

Index structure: [index column] [PRIMARY key column]

Columns participating in records and comparisons: [Indexed columns] [PRIMARY key columns]

Key column for inner node: [index column] [PRIMARY key column]+pageno

The Magic B + Tree Network

Reading notes-mysql operation and Maintenance 08-Index Implementation Principle 1