MySQL 各索引类型（NORMAL、FULLTEXT、SPATIAL、UNIQUE)、索引方法（BTREE、HASH）的区别

MySQL 索引类型与索引方法详解

索引类型

1. NORMAL (普通索引)

• 最基本的索引类型，没有特殊限制
• 仅用于加速查询，不强制唯一性
• 创建语法：CREATE INDEX index_name ON table_name(column_name)
• 适用场景：常用于查询条件中的列，但不需要唯一性约束

2. UNIQUE (唯一索引)

• 与普通索引类似，但要求索引列的值必须唯一
• 允许NULL值（但NULL可以出现多次，因为NULL不等于NULL）
• 创建语法：CREATE UNIQUE INDEX index_name ON table_name(column_name)
• 适用场景：需要确保数据唯一性的列，如用户名、邮箱等

3. FULLTEXT (全文索引)

• 专门用于全文搜索的索引类型
• 仅适用于MyISAM和InnoDB存储引擎（MySQL 5.6+）
• 只能创建在CHAR、VARCHAR或TEXT类型的列上
• 使用特殊的全文搜索语法：MATCH(column) AGAINST('keyword')
• 创建语法：CREATE FULLTEXT INDEX index_name ON table_name(column_name)
• 适用场景：文章内容搜索、产品描述搜索等文本搜索需求

4. SPATIAL (空间索引)

• 用于地理空间数据类型（GEOMETRY, POINT, LINESTRING, POLYGON等）
• 仅适用于MyISAM存储引擎（MySQL 5.7+的InnoDB也支持）
• 使用R-Tree数据结构
• 创建语法：CREATE SPATIAL INDEX index_name ON table_name(column_name)
• 适用场景：地理位置查询、地图应用等

索引方法（索引实现方式）

1. BTREE

• 默认的索引方法，使用B+树数据结构
• 支持范围查询（>, <, BETWEEN等）
• 支持排序（ORDER BY）
• 支持最左前缀匹配（对于复合索引）
• 几乎所有存储引擎都支持BTREE索引
• 适用场景：绝大多数常规查询场景

2. HASH

• 使用哈希表数据结构
• 仅支持等值查询（=, <=>），不支持范围查询
• 查询速度非常快（O(1)时间复杂度）
• 仅Memory/Heap存储引擎显式支持HASH索引
• InnoDB有自适应的哈希索引，但用户无法直接创建
• 适用场景：等值查询且数据不经常变更的场景

对比总结

特性	BTREE	HASH
查询类型支持	等值、范围、排序	仅等值查询
查询复杂度	O(log n)	O(1)
存储引擎支持	几乎所有引擎	主要Memory/Heap引擎
内存使用	中等	较高
适用场景	通用场景	精确匹配查询场景

使用建议

1. 大多数情况下使用默认的BTREE索引即可
2. 需要唯一约束时使用UNIQUE索引
3. 全文搜索需求使用FULLTEXT索引（考虑使用专业的搜索引擎如Elasticsearch可能更好）
4. 地理位置应用使用SPATIAL索引
5. HASH索引仅在Memory引擎且明确只需要等值查询时使用
6. 复合索引注意最左前缀原则

示例代码

-- 普通BTREE索引
CREATE INDEX idx_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 空间索引
CREATE SPATIAL INDEX idx_location ON maps(geom);

-- Memory引擎的HASH索引
CREATE TABLE memory_table (
    id INT,
    name VARCHAR(100),
    INDEX USING HASH (name)
) ENGINE=MEMORY;