What index types exist beyond B-tree (partial, expression, GIN, GiST, BRIN, bitmap, full-text)?
What index types exist beyond B-tree (partial, expression, GIN, GiST, BRIN, bitmap, full-text)?
Different data and queries need different index structures. Choose by the operators your queries use.
| Index type | Best for | Where |
|---|---|---|
| B-tree | equality, ranges, sorting | all engines (default) |
| Hash | equality only | PostgreSQL, MySQL MEMORY, memory-optimized SQL Server tables |
| Partial / filtered | index only the rows you query | PostgreSQL, SQL Server (WHERE clause on the index) |
| Expression / functional | indexing LOWER(email) or a computed value |
PostgreSQL, Oracle, MySQL 8.0.13+; SQL Server via computed column |
Covering (INCLUDE) |
index-only reads | PostgreSQL 11+, SQL Server |
| GIN | many values per row: JSONB, arrays, full-text, trigrams |
PostgreSQL |
| GiST / SP-GiST | geometry, ranges, nearest-neighbour, exclusion constraints | PostgreSQL |
| BRIN | huge tables physically ordered by the column (time series, append-only logs): tiny index | PostgreSQL |
| Bitmap | low-cardinality columns in analytics | Oracle (as an index type) |
| Full-text | word search, ranking | MySQL FULLTEXT, SQL Server full-text, Oracle Text, PostgreSQL GIN on tsvector |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.