What are the index types and characteristics of MySQL

Note: This article mainly focuses on the B Tree index data structure of the InnoDB storage engine.

The following mainly introduces the five index types and their respective characteristics and differences.

1.B-Tree index

Because the storage engine does not need to perform a full table scan to obtain data, it starts searching directly from the root node of the index. This can speed up access to data.

Advantages:

B-Tree stores the index sequentially, which is very suitable for searching range data. It is suitable for full key value, key value range or key prefix search. (Search based on the leftmost prefix)

Restrictions:

For the joint index, if the search does not start from the leftmost column, the index cannot be used; it cannot Skip

2. B Tree index

is a variant of B-Tree index. Nowadays, mainstream storage engines do not use pure B-Tree. But the difference between its variants B Tree or T-Tree, etc.

and B-Tree: The main difference between

and B-Tree is the content of B Tree Nodes do not store data, only keys, and leaf nodes do not store pointers

3. Hash index

Implemented based on Hash table, only the Memory storage engine is explicit

Advantages:

Supports hash index

Suitable for equal value queries, such as =, in(), <=>

Disadvantages:

Does not support range query

Because it is not stored in the order of index values, it cannot use the index to complete sorting like the B Tree index. The Hash index is very fast when querying equivalent values, because Hash indexes always index the entire contents of all columns, so matching searches on partial index columns are not supported.

If there are a large number of duplicate key values, the efficiency of the hash index will be very low because of the hash collision problem. Programmers can create an adaptive Hash index based on the B Tree index

4. Full-text index

Both MyISAM and InnoDB support full-text index, with three modes: natural language mode, Boolean mode and query expansion mode

5.R-Tree index

MyISAM supports R-Tree index, which is basically the same as the full-text index

The above is the detailed content of What are the index types and characteristics of MySQL. For more information, please follow other related articles on the PHP Chinese website!