数据库索引类型及其区别

本文总结了 NORMAL、FULLTEXT、SPATIAL 和 UNIQUE 索引的特点及适用场景，帮助开发者根据需求选择合适的索引类型。

1. NORMAL 索引（普通索引）

特点

• 默认的索引类型，用于加速常规查询。
• 无唯一性限制，可以包含重复值。
• 支持等值查询（=）、范围查询（<、>）、前缀通配符（LIKE 'abc%'）。

使用场景

• 需要加速查询性能但不要求数据唯一性，比如 SELECT 或 WHERE 查询中频繁用到的字段。

示例

CREATE INDEX idx_column ON table_name(column_name);

2. FULLTEXT 索引（全文索引）

特点

• 专用于处理大文本字段（如 TEXT 或 VARCHAR），支持全文检索。
• 基于倒排索引结构，适合快速搜索包含特定单词或短语的内容。
• 使用 MATCH() AGAINST() 语法进行全文搜索。
• 默认不支持通配符匹配（如 %），适合复杂的全文搜索。

限制

• 仅支持 CHAR、VARCHAR 和 TEXT 字段类型。
• 只有部分存储引擎支持（如 MyISAM 和 InnoDB）。
• 对于中文等需要分词的语言，可能需要额外配置分词器。

使用场景

• 搜索博客文章、产品描述、日志记录等大段文字内容。
• 实现搜索功能，例如 “查找包含某关键词的文章”。

示例

-- 创建 FULLTEXT 索引
CREATE FULLTEXT INDEX idx_fulltext ON table_name(column_name);

-- 使用 MATCH() AGAINST() 进行全文搜索
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST ('search_text');

3. SPATIAL 索引（空间索引）

特点

• 专用于存储和查询地理空间数据（如点、线、多边形）。
• 基于 R-Tree 数据结构，适合地理位置和空间范围查询。
• 用于 GEOMETRY、POINT、POLYGON 等数据类型。
• 支持快速执行“范围内”的地理位置查询。

限制

• 仅支持 GIS 数据类型。
• 只能用于支持 GIS 的存储引擎（如 MyISAM 或支持 GIS 的 InnoDB）。
• 索引字段必须设置为 NOT NULL。

使用场景

• 地图服务，例如查找附近商店、公交站点。
• 地理信息系统（GIS），如处理地形或区域范围查询。

示例

-- 创建 SPATIAL 索引
CREATE SPATIAL INDEX idx_spatial ON table_name(geometry_column);

-- 查询特定范围内的地理数据
SELECT * FROM table_name
WHERE ST_Within(geometry_column, ST_GeomFromText('POLYGON((...))'));

4. UNIQUE 索引（唯一索引）

特点

• 限制索引字段的值必须唯一（允许一个 NULL 值）。
• 可以用于单列或多列（联合唯一性）。
• 当值重复时，数据库会拒绝插入或更新操作。

使用场景

• 确保字段值的唯一性，例如用户的邮箱、商品编号等。
• 约束业务逻辑，避免数据重复。

示例

-- 创建 UNIQUE 索引
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

-- 创建联合唯一索引
CREATE UNIQUE INDEX idx_unique_multi ON table_name(column1, column2);

小结

根据需求，选择合适的索引类型可以提升查询效率，优化数据存储。