In MySQL, indexing is a data structure that improves query speed. Different indexing algorithms are suitable for different query scenarios. This article will introduce in detail several main indexing algorithms of MySQL.
1. B+Tree index (default index)
1.1 Storage structure
B+Tree (B+ Tree) is a balanced multi-way search tree, which is characterized by:
- All data is stored in leaf nodes, and internal nodes only store index values.
- The leaf nodes are connected through a bidirectional linked list to facilitate range query.
- All leaf nodes are in the same layer, maintaining the query efficiency stable.
1.2 Applicable storage engine
- InnoDB (default)
- MyISAM
1.3 Advantages
✅ Suitable for range query (BETWEEN, >, <, etc.) ✅ Suitable for ORDER BY sort query ✅ Leaf nodes form a linked list, supporting efficient sequential traversal
1.4 Limitations
❌ Not suitable for full-text search (requires full-Text index)
❌ Frequent insertion/deletion may cause index splitting
2. Hash index (for equivalent query)
2.1 Storage structure
- Compute the key value map to the hash bucket through the hash function to quickly locate the data.
- Suitable for key-value queries.
2.2 Applicable storage engine
- Memory (Heap) engine
- InnoDB (Adaptive Hash Index, automatic optimization hash index)
2.3 Advantages
✅ Suitable for equal value query (=), fast query speed (O(1) time complexity)
✅ Hash table query will not slow down as the amount of data increases
2.4 Limitations
❌ Not supported scope query (>, <, BETWEEN)❌ Not supported ORDER BY sorting❌ It is easy to have hash conflicts, affecting query efficiency
3. Full-Text (full-text index)
3.1 Storage structure
- Inverted Index, storing the mapping of words -> Document IDs.
- Suitable for full-text search (such as articles, comments, logs).
3.2 Applicable storage engine
- InnoDB
- MyISAM
3.3 Advantages
✅ Suitable for full-text search (MATCH() AGAINST()) ✅ CompareLIKE '%xx%'Query much faster
3.4 Limitations
❌ Not suitable for small data volume (high index maintenance overhead) ❌ Not completely replace search engines (such as Elasticsearch)
4. R-Tree (Spatial Index)
4.1 Storage structure
- R-Tree (multi-dimensional index structure), suitable for storing and querying geographic coordinates (points, rectangles, polygons).
4.2 Applicable storage engine
- MyISAM
- InnoDB (supported after MySQL 8.0
SPATIALIndex)
4.3 Advantages
✅ Suitable for geographic information query (such as "restaurants near a certain point") ✅ Suitable for spatial range query (such as "all data in a certain area")
4.4 Limitations
❌ Only supported by MyISAM, InnoDB is only supported by MySQL 8.0+
❌ Applicable scenarios are relatively narrow, generally used in GIS applications
5. Bitmap index (suitable for low cardinality sequences)
5.1 Storage structure
- Bitmap (Bitmap), use 0/1 bit to record the occurrence of a certain value in different data rows.
5.2 Applicable storage engine
- MySQL is not directly supported (Oracle, PostgreSQL supports)
5.3 Advantages
✅ Suitable for low-cardinality sequences (such as gender, status, boolean values) ✅ Save storage space, and bit operations can be accelerated during querying
5.4 Limitations
❌ Not suitable for high-base sequences (such as mobile phone number, user name) ❌ Not support dynamic updates (update cost is high)
6. Comparison and summary of index algorithms
| Index Type | Applicable storage engine | Applicable query scenarios | Advantages | limitation |
|---|---|---|---|---|
| B+Tree (default) | InnoDB、MyISAM | Range query, sort query, primary key/foreign key | Suitable for most scenarios | Frequent insertion/deletion may cause index splitting |
| Hash index | Memory, InnoDB (adaptive) | Exact match (=) | Fast query speed (O(1)) | Range query, sorting, and fuzzy query are not supported |
| Full-Text Index | InnoDB、MyISAM | Full text search | Suitable for large text fields (such as article search) | Can't completely replace search engines |
| R-Tree (Spatial Index) | MyISAM、InnoDB（8.0+） | GIS Geographic Query | Suitable for spatial data | Available for MyISAM only (8.0+ InnoDB support) |
| Bitmap Index | MySQL does not support it | Low cardinality sequence (eg gender) | Efficient storage and query | High update cost, not suitable for high cardinality sequences |
7. Conclusion
- B+Tree index is used by default (applicable to most cases).
- Equivalent queries use Hash index, but InnoDB does not support them by default (except Adaptive Hash Index).
- Full-Text index is used for full text search, compared
LIKE '%xx%'Query faster. - R-Tree index for GIS query (
SPATIALindex). - Low-base numeric fields (such as gender) can consider Bitmap index, but MySQL does not support it.
Choosing the appropriate index structure can greatly improve MySQL query performance!
This is the end of this article about the summary of several index algorithms that MySQL mainly uses. For more information about MySQL, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!