索引类型与适用场景

MySQL InnoDB支持多种索引类型，按功能分类选择合适类型。

索引类型概览

主键索引

定义与特点

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50)
);

-- 特点：
-- 1. 唯一且非空
-- 2. 聚簇索引（数据按主键存储）
-- 3. 一个表只能有一个主键

适用场景

• 表的唯一标识列
• 需要聚簇存储的查询
• 频繁按主键查询的场景

唯一索引

定义与特点

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE INDEX idx_email(email)  -- 唯一索引
);

-- 特点：
-- 1. 列值唯一（允许一个NULL）
-- 2. 非聚簇索引
-- 3. 可有多个唯一索引

适用场景

• 业务唯一标识（邮箱、手机号）
• 防止重复数据
• 需要唯一约束的查询加速

普通索引

定义与特点

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    INDEX idx_user(user_id)  -- 普通索引
);

-- 特点：
-- 1. 无唯一约束
-- 2. 加速查询
-- 3. 最常用索引类型

适用场景

• 高频查询条件列
• 外键列
• 排序/分组列

联合索引

定义与特点

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    created_at DATETIME,
    INDEX idx_user_status(user_id, status)  -- 联合索引
);

-- 特点：
-- 1. 多列组合
-- 2. 遵循最左前缀原则
-- 3. 可覆盖多查询场景

适用场景

• 多条件组合查询
• 覆盖索引设计
• 减少单列索引数量

-- 一个联合索引覆盖多种查询
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 以下查询都可使用
WHERE user_id = 1
WHERE user_id = 1 AND status = 1
WHERE user_id = 1 AND status = 1 AND created_at > '2026-01-01'

全文索引

定义与特点

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content(title, content)
) ENGINE=InnoDB;

-- 特点：
-- 1. 支持文本分词搜索
-- 2. InnoDB 5.6+支持
-- 3. 使用MATCH AGAINST查询

适用场景

• 文章内容搜索
• 商品描述搜索
• 文本模糊匹配

-- 全文检索语法
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库');

-- 自然语言模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

-- Boolean模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+数据库 -Oracle' IN BOOLEAN MODE);

前缀索引

定义与特点

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    INDEX idx_email(email(10))  -- 前10字符索引
);

-- 特点：
-- 1. 索引占用空间小
-- 2. 无法用于覆盖索引
-- 3. 无法用于ORDER BY

适用场景

• 长字符串列
• 前缀区分度高
• 存储空间有限

-- 计算合适前缀长度
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel15
FROM users;
-- 选择区分度接近完整列的最短长度

索引类型选择决策

是否需要唯一约束？
├── 是 → 唯一索引
│      └── 是否为主标识？ → 主键索引
└── 否 → 普通索引
       ├── 多列组合查询？ → 联合索引
       ├── 文本搜索？ → 全文索引
       └── 长字符串？ → 前缀索引

要点总结

• 主键索引是聚簇索引，数据按主键存储
• 唯一索引保证数据唯一性，允许一个NULL
• 普通索引最常用，无约束只加速查询
• 联合索引遵循最左前缀，可覆盖多场景
• 全文索引用于文本分词搜索
• 前缀索引节省空间但限制使用场景