7.5 索引创建与删除：普通索引、唯一索引、主键索引、联合索引、全文索引

索引是提高查询效率最直接的手段，也是日常表结构设计中频繁接触的对象。MySQL 支持多种索引类型，每种类型适用于不同的查询场景。本节将系统介绍各类索引的创建与删除语法，并结合实际使用给出建议。

7.5.1 普通索引

普通索引（Normal Index）是最基础的索引类型，没有任何约束限制，仅用于加速数据检索。允许索引列中包含重复值和 NULL 值。

创建语法

-- 方式一：在创建表时指定
CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_email (email)          -- 普通索引
);

-- 方式二：在已存在的表上创建
CREATE INDEX idx_name ON user(name);

-- 方式三：通过 ALTER TABLE 添加
ALTER TABLE user ADD INDEX idx_name_email (name, email);

通常建议给索引起一个有意义的名字，比如以 idx_ 为前缀，后可跟列名组合，便于后期维护时识别。

删除语法

-- 方式一：DROP INDEX
DROP INDEX idx_name ON user;

-- 方式二：ALTER TABLE
ALTER TABLE user DROP INDEX idx_email;

注意：删除索引时需指定表名，因为同一数据库中不同表可能存在同名的索引。

7.5.2 唯一索引

唯一索引（Unique Index）与普通索引功能相似，但增加了唯一性约束，即索引列中的值必须唯一，允许有一个 NULL 值（多个 NULL 是否允许取决于具体存储引擎和索引实现，InnoDB 中通常允许多个 NULL）。它既能加速查询，又能从数据库层面防止重复数据写入。

创建语法

-- 建表时指定
CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE INDEX uk_email (email)     -- 唯一索引
);

-- 已存在表上创建
CREATE UNIQUE INDEX uk_email ON user(email);

-- ALTER TABLE 方式
ALTER TABLE user ADD UNIQUE INDEX uk_email (email);

命名前缀通常使用 uk_ 以示区分，这在运维与团队协作中是一种好习惯。

使用建议
唯一索引除了加速 WHERE email = ? 这类等值查询外，还常用于业务层面的去重保障，比如用户表的手机号、订单流水号等业务唯一键。与在应用层做校验相比，数据库层的唯一约束是最后一道防线，能彻底杜绝并发情况下重复数据的插入。

删除语法

DROP INDEX uk_email ON user;
-- 或
ALTER TABLE user DROP INDEX uk_email;

7.5.3 主键索引

主键索引（Primary Key）是一种特殊的唯一索引，不允许列值为 NULL，且一张表只能有一个主键。在 InnoDB 中，主键索引就是聚簇索引，表中数据行按主键顺序物理存储。因此主键设计直接影响整体性能。

创建语法

通常在建表时定义主键，后续也可以通过 ALTER TABLE 添加或删除，但会触发整表重建，开销极大，需谨慎操作。

-- 建表时定义
CREATE TABLE user (
    id INT AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)                -- 主键索引
);

-- 通过 ALTER TABLE 添加（不常见）
ALTER TABLE user ADD PRIMARY KEY (id);

InnoDB 强制要求必须有主键，如果建表时未显式指定，引擎会隐式选择第一个非空唯一索引作为主键；若不存在，则自动创建一个隐藏的 6 字节 ROW_ID 作为聚簇索引。因此强烈建议显式定义主键，避免隐式行为带来的性能隐患。

删除语法

ALTER TABLE user DROP PRIMARY KEY;

注意：删除主键前需要确保没有外键依赖，且如果主键列是自增列，可能需要先修改列属性。大部分业务表永远不应该删除主键。

主键设计建议

• 尽量采用短小且有序的数据类型，如自增整数（INT 或 BIGINT），这样聚簇索引的插入都是顺序追加，减少了页分裂开销。
• 避免使用随机字符串（如 UUID）作为主键，随机值会导致大量随机 I/O 和页分裂，严重降低写入性能。
• 从业务语义来说，主键应保持稳定不变，任何可能被修改的列都不适合做主键。

7.5.4 联合索引

联合索引（Composite Index）又称多列索引，是在多个列上建立的索引。它能高效支持同时包含这些列的查询条件，特别是遵循最左前缀原则的场景。

创建语法

-- 建表时指定
CREATE TABLE order_detail (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    INDEX idx_order_product (order_id, product_id)   -- 联合索引
);

-- 已存在表上创建
CREATE INDEX idx_order_product ON order_detail(order_id, product_id);

使用要点

• 联合索引的列顺序非常关键。查询条件如果跳过了最左列，索引会失效。例如索引 (a, b, c)，对于 WHERE b = 1 AND c = 2 无法使用该索引，而对于 WHERE a = 1、WHERE a = 1 AND b = 1 等则能够使用。
• 设计联合索引时，通常将区分度高、查询频繁的列放在最前面。但也要兼顾业务 SQL 的具体写法，最好根据实际查询模式来调整顺序。
• 一个联合索引等同于建立了多个索引：对于 (a, b, c) 索引，它覆盖了对 (a) 和 (a, b) 的查询，因此无需再为 (a) 单独建索引。这样可以有效减少冗余索引，降低成本。

删除语法

和普通索引相同：

DROP INDEX idx_order_product ON order_detail;

7.5.5 全文索引

全文索引（Fulltext Index）用于加速对大段文本内容的模糊搜索，尤其适合 LIKE '%keyword%' 这种普通索引无法优化的场景。MySQL 5.6 之后 InnoDB 引擎也开始支持全文索引，到 8.0 已相当成熟。

创建语法

-- 建表时指定
CREATE TABLE article (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX ft_body (body)   -- 全文索引
);

-- 已存在表上创建
CREATE FULLTEXT INDEX ft_title_body ON article(title, body);

-- ALTER TABLE 方式
ALTER TABLE article ADD FULLTEXT INDEX ft_body (body);

查询方式

全文索引不能使用普通的 = 或 LIKE 来触发，必须使用特定全文搜索函数：

-- 自然语言模式
SELECT * FROM article WHERE MATCH(body) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);

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

删除语法

DROP INDEX ft_body ON article;
-- 或者
ALTER TABLE article DROP INDEX ft_body;

实用建议

• 全文索引对于 LIKE '%keyword%' 类型的需求提升巨大，但它并不是支持中文分词的首选方案（内置的分词器对中文按字切分，查询精度不高）。中文场景可以借助第三方插件（如 ngram 解析器或外部搜索引擎 Elasticsearch）。
• 全文索引有最小单词长度限制（默认 innodb_ft_min_token_size=3），过短的词不会被索引，需要根据业务调整。
• 全文索引的维护需要更多磁盘空间和更新时间，不适合更新极其频繁的列。

7.5.6 查看现有索引

在实际工作中，常常需要查看某个表已经有哪些索引，以避免重复创建或误删。常用命令：

-- 查看某个表的索引信息
SHOW INDEX FROM table_name;

-- 通过建表语句查看
SHOW CREATE TABLE table_name;

SHOW INDEX 结果会显示索引名（Key_name）、列名（Column_name）、唯一性（Non_unique）、索引类型（Index_type，如 BTREE、FULLTEXT）等，可以作为日常巡检的依据。

7.5.7 综合实践建议

1. 命名规范：普通索引用 idx_ 前缀，唯一索引用 uk_，主键可用 pk_ 或直接系统生成。规范的命名让 EXPLAIN 输出清晰易读，也便于 DBA 审核。
2. 不要盲目添加索引：每个索引都会占用磁盘空间，并拖慢写操作（INSERT、UPDATE、DELETE 需维护索引）。针对核心 SQL 通过执行计划分析后再添加索引，遵循“按需创建，尽量覆盖”原则。
3. 联合索引优先于多个单列索引：学会用联合索引替代多个单列索引，能有效减少索引数量，并利用索引覆盖优化查询。
4. 定期清理冗余索引：比如存在 (a, b) 联合索引，单独为 a 建的索引就是冗余的。可用 sys.schema_redundant_indexes 视图（MySQL 8.0）来辅助分析。
5. 谨慎操作线上表：在生产环境执行 CREATE INDEX 或 DROP INDEX 时，可能会短暂锁表（特别是早期版本或使用 ALGORITHM=COPY 时）。MySQL 8.0 支持 ALGORITHM=INPLACE 和 LOCK=NONE，能在线创建索引减少影响，但仍建议在业务低峰期执行并充分测试。

索引是数据库性能优化的核心工具，掌握创建和删除的语法只是第一步，真正用好索引还需要结合执行计划分析和业务查询特征。后续章节将更详细地展开索引优化的实战方法。