数据库索引类型
本节按「你会在建表/改表时真正用到的分类」整理索引类型，并说明适用场景与注意点。仍以 MySQL InnoDB 为主。
1. 主键索引（PRIMARY KEY）
- 唯一且非空；一张表只能有一个主键。
- InnoDB 用主键作为聚簇索引组织整行数据。
- 没有显式主键时，InnoDB 会选第一个非空唯一索引，再不行用隐藏的 6 字节 row id。
建议：
- 优先使用稳定、尽量短的主键（如自增 BIGINT 或有序 UUID 变体）。
- 避免用经常变更的业务字段当主键（更新主键代价高，二级索引都存主键）。
- 宽主键会放大所有二级索引的叶子体积。
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
2. 唯一索引（UNIQUE）
- 保证列（或列组合）取值唯一，允许多个
NULL（MySQL 中NULL不参与相等比较的细节需注意）。 - 既是约束，也是可被优化器使用的索引。
CREATE UNIQUE INDEX uk_users_email ON users (email);
何时用： 业务上必须唯一的字段（邮箱、手机号、订单号），既防脏数据，又加速按该字段查询。
3. 普通二级索引（KEY / INDEX）
- 最常见：加速非主键列上的过滤、排序、连接。
- 不强制唯一。
CREATE INDEX idx_orders_user_id ON orders (user_id);
4. 联合索引（复合索引）
多列按定义顺序组成一棵 B+ 树的键。
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
叶子键顺序等价于：先按 user_id 排序，user_id 相同再按 created_at 排序。
二级索引叶子还带上主键（便于回表），可记成：(user_id, created_at) → id。
4.1 树结构举例
假设表中有如下订单行（主键 id）：
| id | user_id | created_at |
|---|---|---|
| 1 | 10 | 2026-01-01 |
| 2 | 10 | 2026-01-05 |
| 3 | 10 | 2026-01-08 |
| 4 | 20 | 2026-01-03 |
| 5 | 20 | 2026-01-07 |
| 6 | 30 | 2026-01-02 |
联合索引 (user_id, created_at) 的比较规则是字典序（先比第一列，相同再比第二列）：
(10, 01-01) < (10, 01-05) < (10, 01-08) < (20, 01-03) < (20, 01-07) < (30, 01-02)
简化成一棵 B+ 树（非叶子只存分界键；叶子存完整索引键 + 主键，并用链表串起来）：
非叶子（分界）
┌─────────────────────┐
│ (20, 01-03) │
└──────────┬──────────┘
┌───────────────┴───────────────┐
▼ ▼
叶子页 L1 叶子页 L2
┌──────────────────────┐ ┌──────────────────────┐
│ (10,01-01) → id=1 │ ──────► │ (20,01-03) → id=4 │
│ (10,01-05) → id=2 │ │ (20,01-07) → id=5 │
│ (10,01-08) → id=3 │ │ (30,01-02) → id=6 │
└──────────────────────┘ └──────────────────────┘
▲ 同一 user_id=10 的行已按 created_at 排好
要点：
- 只有一棵树，键是「多列拼成的元组」，不是「每个 user_id 下面再挂一棵 created_at 树」。
user_id相同的行在叶子上天然相邻，且内部已按created_at有序。- 非叶子上的分界键同样是元组，例如
(20, 01-03)表示「小于该键的走左，大于等于的走右」（具体实现细节会微调，但排序语义如此）。
4.2 查询如何落到这棵树上
| 条件 | 在树上怎么走 | 是否用到联合索引 |
|---|---|---|
user_id = 10 | 定位到 user_id=10 的连续叶子区间，扫 3 条 | 是（最左列） |
user_id = 10 AND created_at > '2026-01-05' | 先到 user=10，再在该段内从 01-05 往后扫 | 是（两列都用） |
user_id = 10 ORDER BY created_at | 叶子顺序即排序结果，可避免 filesort | 是 |
created_at > '2026-01-05' | 无法从最左列定位，叶子上日期是「打散」在不同 user 下的 | 否（跳过最左列） |
「只按 created_at 查」时，同一日期会散落在不同 user_id 段里，B+ 树无法从根做一次有序范围定位，所以通常用不上这棵联合索引。
详见 索引优化 · 最左前缀。
列顺序经验法则：
- 等值条件列优先于范围条件列。
- 区分度高的列往往更靠前（需结合真实查询，不是绝对）。
- 需要覆盖的投影列可挂在联合索引尾部。
5. 前缀索引
只对字符串的前 (N) 个字符建索引，节省空间，但可能降低区分度，且通常无法用于覆盖完整列值。
CREATE INDEX idx_users_email_prefix ON users (email(20));
示例：如果 email 很长，但业务只需要“按前缀筛选”（例如搜索/输入前几位就过滤候选），可以用前缀索引：
SELECT id, email
FROM users
WHERE email LIKE 'abc123%';
适用：长 VARCHAR/TEXT 且前缀已足够区分；不适合强依赖完整列等值且需高区分度的场景。
6. 全文索引（FULLTEXT）
用于自然语言检索，不是 B+ 树等值/范围那种用法。
CREATE FULLTEXT INDEX ft_articles_body ON articles (title, body);
-- MATCH (title, body) AGAINST ('关键词' IN NATURAL LANGUAGE MODE)
注意：InnoDB 全文有最小词长、停用词等限制；中文通常还需 ngram 解析器或外部搜索引擎（Elasticsearch 等）。
7. 其他常见形态（简表）
| 类型 | 作用 | 备注 |
|---|---|---|
| 自增主键 | 顺序插入，减少页分裂 | 高并发插入时仍要注意热点 |
| 降序索引 | 匹配 ORDER BY col DESC | MySQL 8.0+ 支持显式降序 |
| 函数索引 / 表达式索引 | 对表达式结果建索引 | MySQL 8.0+；用于固定函数条件 |
| 空间索引（SPATIAL） | 地理数据 | 专用类型与查询函数 |
| 哈希索引 | 等值极快 | InnoDB 自适应哈希是内部机制，不能像 Memory 引擎那样随意建 |
8. 选型速查
| 需求 | 更倾向 |
|---|---|
| 表的组织与主键查询 | 主键（聚簇） |
| 业务唯一约束 + 查询 | UNIQUE |
高频 WHERE / JOIN | 普通或联合二级索引 |
| 多条件 + 排序同一路径 | 联合索引 |
| 超长字符串 | 前缀索引或哈希化业务键 |
| 模糊全文搜索 | FULLTEXT 或搜索引擎 |
下一篇：索引优化与失效场景。