MySQL 索引类型

MySQL 提供多种索引类型，不同类型有不同的特性和适用场景。

索引类型概览

主键索引

-- 主键索引是特殊的唯一索引
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50)
);

-- 主键特点：
-- 1. 一个表只能有一个主键
-- 2. 不允许NULL值
-- 3. InnoDB用主键组织数据（聚簇索引）

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

唯一索引

-- 唯一索引保证字段值唯一
CREATE TABLE users (
    id INT,
    email VARCHAR(100) UNIQUE  -- 自动创建唯一索引
);

-- 或显式创建
CREATE UNIQUE INDEX idx_email ON users(email);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 允许NULL值，但NULL只能有一个
INSERT INTO users VALUES (1, NULL);
INSERT INTO users VALUES (2, NULL);  -- 可能报错（取决于版本）

普通索引

-- 普通索引最常用，无唯一约束
CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD INDEX idx_name (name);

-- 允许重复值和NULL值
INSERT INTO users VALUES (1, '张三');
INSERT INTO users VALUES (2, '张三');  -- 允许重复

组合索引（复合索引）

-- 组合索引包含多个字段
CREATE INDEX idx_name_age ON users(name, age);

-- 最左匹配原则
-- 查询条件必须从左开始连续匹配

-- 使用索引的情况
SELECT * FROM users WHERE name = '张三';            -- ✓
SELECT * FROM users WHERE name = '张三' AND age = 20; -- ✓
SELECT * FROM users WHERE name = '张三' ORDER BY age; -- ✓

-- 不使用索引的情况
SELECT * FROM users WHERE age = 20;                 -- ✗ (缺少name)
SELECT * FROM users WHERE name LIKE '张%' AND age = 20; -- ✗ (LIKE用前缀)
SELECT * FROM users WHERE age = 20 AND name = '张三'; -- ✗ (顺序颠倒)

全文索引

-- 全文索引用于文本搜索
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);

-- 全文搜索
SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL');

-- 自然语言模式
SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);

-- 布尔模式（支持+-操作符）
SELECT * FROM articles
WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

前缀索引

-- 对长字符串建前缀索引，节省空间
CREATE INDEX idx_email_prefix ON users(email(10));

-- 前缀长度选择原则
-- 既要节省空间，又要保证区分度
SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM users;
-- 值接近1说明区分度足够

聚簇索引与非聚簇索引

-- InnoDB聚簇索引
-- 主键索引的叶子节点存储完整行数据
-- 数据按主键顺序存储

-- 非聚簇索引（二级索引）
-- 叶子节点存储主键值，需回表查询完整数据
SELECT * FROM users WHERE name = '张三';
-- 先查idx_name获得主键id，再回表查完整数据

-- MyISAM只有非聚簇索引
-- 所有索引叶子节点存储数据地址

要点总结

• 主键索引是聚簇索引，数据按主键存储
• 唯一索引保证值唯一，允许NULL
• 组合索引遵循最左匹配原则
• 全文索引用于文本搜索
• 前缀索引节省空间，需平衡区分度