Index principle and data structure in MySQL database, mysql Index

Index principle and data structure in MySQL database, mysql Index

There are three types of indexes in MySQL:

1. Primary Key Index: Use a primary key as a unique index.

2. Regular indexes: the implementation method is B tree and hash table.

3. Full-text index: the implementation principle is similar to inverted index. It is often used to query fields that contain keywords.

Next I will review the B-TREE and hash-table

1 B-tree

The B-tree can implement many dynamic set operations in O (lgn) time, in addition to database indexes and disk searches.

Tree B is a multi-path search Balanced Multi-Cross Tree with the following attributes:

1. If the root node is not a leaf node, it has at least two Subtrees.

2. The number of keywords contained in each non-root node j satisfies the following requirements: Running m/2 running-1 <= j <= m-1;

3. The degree of all nodes except the root node (excluding the leaf node) is exactly the sum of the number of keywords plus 1. Therefore, the number of internal sub-trees is k: optional m/2 bytes <= k <= m;

4. All leaf nodes are on the same layer.

Elements in each node are sorted in ascending order.