索引类型：B-Tree、Hash、GiST、GIN

什么是索引

索引是数据库的"目录"，帮助快速定位数据，避免全表扫描。

没有索引

-- 全表扫描：逐行检查
SELECT * FROM users WHERE email = 'alice@example.com';
-- 扫描 1,000,000 行

有索引

CREATE INDEX idx_email ON users(email);

SELECT * FROM users WHERE email = 'alice@example.com';
-- 只读取几行（通过索引快速定位）

加速原理：索引按排序顺序组织数据，使用二分查找（O(log n)）而非线性扫描（O(n)）。

B-Tree 索引

B-Tree（平衡树）是最常用的索引类型，支持等值和范围查询。

结构

        [50]
       /    \
   [25]      [75]
   /  \      /  \
[10][30] [60][90]

每个节点包含多个键值，叶子节点存储数据指针。

适用场景

CREATE INDEX idx_age ON users(age);

-- 等值查询
SELECT * FROM users WHERE age = 30;

-- 范围查询
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age BETWEEN 20 AND 40;

-- 排序
SELECT * FROM users ORDER BY age;

复合索引

CREATE INDEX idx_name_age ON users(last_name, first_name);

-- 索引生效
SELECT * FROM users WHERE last_name = 'Smith';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

-- 索引部分生效
SELECT * FROM users WHERE last_name = 'Smith' ORDER BY first_name;

-- 索引不生效（跳过第一列）
SELECT * FROM users WHERE first_name = 'John';

最左前缀原则：复合索引按从左到右的顺序匹配。

PostgreSQL vs MySQL

两者都默认使用 B-Tree，实现细节略有不同：

• PostgreSQL：真正的 B-Tree
• MySQL InnoDB：B+Tree（叶子节点链接，适合范围扫描）

Hash 索引

Hash 索引使用哈希函数，只支持等值查询。

PostgreSQL

CREATE INDEX idx_email_hash ON users USING HASH (email);

-- 索引生效
SELECT * FROM users WHERE email = 'alice@example.com';

-- 索引不生效（不支持范围）
SELECT * FROM users WHERE email > 'alice@example.com';

MySQL

MySQL InnoDB 不支持 Hash 索引（Memory 引擎支持）。

适用场景

• 只需等值查询
• 数据分布均匀
• 空间比 B-Tree 小（某些情况）

实际使用较少，B-Tree 通常足够快。

GiST 索引（PostgreSQL）

GiST（Generalized Search Tree，通用搜索树）支持复杂数据类型。

适用场景

-- 几何数据
CREATE INDEX idx_location ON places USING GIST (location);
SELECT * FROM places WHERE location <-> point(0,0) < 10;

-- 全文搜索
CREATE INDEX idx_content ON documents USING GIST (to_tsvector('english', content));
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('database');

-- 范围类型
CREATE INDEX idx_daterange ON events USING GIST (date_range);
SELECT * FROM events WHERE date_range @> '2026-01-15'::date;

GIN 索引（PostgreSQL）

GIN（Generalized Inverted Index，通用倒排索引）适合多值列。

适用场景

-- 数组
CREATE INDEX idx_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['sql', 'database'];

-- JSONB
CREATE INDEX idx_metadata ON products USING GIN (metadata);
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- 全文搜索（比 GiST 更快）
CREATE INDEX idx_content_gin ON documents USING GIN (to_tsvector('english', content));

全文搜索索引

PostgreSQL

-- 创建 tsvector 列
ALTER TABLE articles ADD COLUMN content_tsv tsvector;
UPDATE articles SET content_tsv = to_tsvector('english', content);

-- 创建索引
CREATE INDEX idx_content_fts ON articles USING GIN (content_tsv);

-- 查询
SELECT * FROM articles WHERE content_tsv @@ to_tsquery('database & performance');

MySQL

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

-- 查询
SELECT * FROM articles WHERE MATCH(content) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

空间索引

PostgreSQL + PostGIS

CREATE INDEX idx_geom ON locations USING GIST (geom);

-- 查询附近的点
SELECT * FROM locations WHERE ST_DWithin(geom, ST_Point(0, 0), 1000);

MySQL

CREATE SPATIAL INDEX idx_location ON places(location);

-- 查询
SELECT * FROM places WHERE MBRContains(GeomFromText('POLYGON(...)'), location);

索引类型对比

类型	等值查询	范围查询	排序	数据库	适用场景
B-Tree	快	快	支持	所有	通用，默认选择
Hash	最快	不支持	不支持	PG	仅等值查询
GiST	快	快	支持	PG	几何、全文
GIN	最快	慢	不支持	PG	数组、JSON、全文

何时使用哪种索引

使用 B-Tree（默认）

• 大多数查询场景
• 需要范围查询
• 需要排序

使用 Hash

• 只有等值查询
• 数据量巨大且分布均匀

使用 GiST

• 几何数据
• 需要范围查询的全文搜索

使用 GIN

• 数组、JSON 查询
• 高性能全文搜索
• 多值匹配

练习

1. 创建 B-Tree 索引：在 users 表的 email 列创建索引。
2. 复合索引：创建 (last_name, first_name) 复合索引，测试最左前缀。
3. Hash 索引：创建 Hash 索引，对比 B-Tree 的性能（PostgreSQL）。
4. 全文搜索：创建全文索引，搜索包含关键词的文章。
5. JSONB 索引：创建 GIN 索引，查询 JSON 字段（PostgreSQL）。
6. 数组索引：创建 GIN 索引，查询包含特定元素的数组（PostgreSQL）。
7. 空间索引：创建空间索引，查询范围内的点。
8. 性能对比：对比有无索引的查询性能（1000000 行数据）。

完成练习后，打开本章答案页核对。

下一步

学会了索引类型。下一节学习索引管理：如何创建、删除、维护索引。

回到第六章概览 · 返回SQL 学习路线