What are the indexes of mysql?

Index types in MySQL

MySQL provides a variety of indexes, each index is suitable for different data types and access modes. The following are common index types in MySQL:

1. B-Tree index

• The most commonly used index type, used to quickly find data.
• Each data row is stored in a B-tree, which is a multi-level sorting tree.
• Each query can use the B-tree index to quickly locate data rows.

2. Hash index

• is only applicable to columns with unique hash values.
• Map data rows directly to their hash values, making lookups extremely fast.
• However, hash indexes do not support range queries.

3. Full-text index

• is used for full-text search of text data.
• Break text into words and index them to quickly search for rows of data containing specific words.

4. Spatial index

• is used to perform spatial queries on spatial data (such as geographical coordinates).
• Use R trees or other spatial data structures to store and index data.
• Allows fast finding of intersecting, overlapping or adjacent data objects.

5. Joint index

• An index containing multiple columns.
• Improve the efficiency of querying using multiple columns at the same time.

6. Adaptive Hash Index (AHI)

• The new index type introduced in MySQL 8.0.
• Combines the advantages of hash index and B-Tree index to provide faster search on high cardinality columns.

7. Bitmap index

• is used for efficient querying of collection values ​​(such as arrays and lists).
• Each collection value is mapped to a bitmap, where each bit in the bitmap represents whether the value exists in a given row.
• Supports quick search for collections that contain or lack specific values.

Different types of indexes are optimized for different access patterns. Choosing the right index can significantly improve MySQL query performance.

The above is the detailed content of What are the indexes of mysql?. For more information, please follow other related articles on the PHP Chinese website!