MySQL - 进阶篇 - 2.4 索引语法

原创已于 2025-09-20 17:00:59 修改 · 912 阅读

25 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-09-20 16:58:03 首次发布

MySQL 专栏收录该内容

13 篇文章

订阅专栏

MySQL - 进阶篇 - 2.4 索引语法

在上一节中，我们学习了索引的分类与聚集索引、二级索引的原理。本节将结合 实际 SQL 语法，介绍索引的创建、查看与删除，并通过案例来演示这些操作。

1) 创建索引

在 MySQL 中，索引的创建语法如下：

CREATE [UNIQUE | FULLTEXT] INDEX index_name
ON table_name (index_col_name, ...);

UNIQUE：创建唯一索引，保证列的值不重复。
FULLTEXT：创建全文索引，适合文本搜索。
默认情况：若不指定，则为普通索引。

2) 查看索引

要查看某张表已有的索引，可以使用以下语句：

SHOW INDEX FROM table_name;

这条语句会展示表中所有的索引信息，包括索引名、列名、是否唯一、索引类型等。

3) 删除索引

如果某个索引不再使用或影响写入性能，可以通过以下语句删除：

DROP INDEX index_name ON table_name;

需要注意：主键索引不能直接删除，如果要删除主键索引，必须先删除主键约束。

案例演示

我们先创建一张用户表 tb_user，并插入测试数据：

CREATE TABLE tb_user (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  name VARCHAR(50) NOT NULL COMMENT '用户名',
  phone VARCHAR(11) NOT NULL COMMENT '手机号',
  email VARCHAR(100) COMMENT '邮箱',
  profession VARCHAR(11) COMMENT '专业',
  age TINYINT UNSIGNED COMMENT '年龄',
  gender CHAR(1) COMMENT '性别 , 1: 男, 2: 女',
  status CHAR(1) COMMENT '状态',
  createtime DATETIME COMMENT '创建时间'
) COMMENT '系统用户表';

插入部分测试数据：

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime)
VALUES 
('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00'),
('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00'),
('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00'),
('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00'),
('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');

A. 为 `name` 字段创建普通索引

CREATE INDEX idx_user_name ON tb_user(name);

name 字段可能重复，因此使用普通索引来加快查询。

B. 为 `phone` 字段创建唯一索引

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

手机号不允许重复，因此唯一索引能保证数据唯一性，同时提升查询性能。

C. 为 `profession, age, status` 创建联合索引

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);

当查询条件同时涉及专业、年龄、状态时，该联合索引能显著提升查询效率。

D. 为 `email` 字段创建索引

CREATE INDEX idx_email ON tb_user(email);

在实际业务中，邮箱常被用于检索用户，因此建立索引能加速查询。

查看所有索引

SHOW INDEX FROM tb_user;

可以看到我们创建的 idx_user_name、idx_user_phone、idx_user_pro_age_sta、idx_email 等索引。

删除索引

DROP INDEX idx_email ON tb_user;

如果不再需要邮箱索引，可以用以上语句删除。

总结

创建索引：CREATE [UNIQUE | FULLTEXT] INDEX
查看索引：SHOW INDEX FROM table_name
删除索引：DROP INDEX index_name ON table_name

通过合理设计索引，可以显著提升 SQL 的执行效率。但同时要注意：索引会增加写操作成本，占用存储空间，因此应避免过度创建。

下一节（2.5）我们将深入 SQL 性能分析，结合 EXPLAIN 语句来理解索引是否被命中，以及 SQL 执行计划。

理解

1. 理论理解

索引语法本质上围绕三件事：创建、查看、删除。

创建：不同类型的索引（普通、唯一、全文、联合索引）提供了不同的约束与性能优化手段。
查看：通过 SHOW INDEX 或 EXPLAIN 等语句，可以确认索引是否生效，避免“建了索引但没用上”的情况。
删除：索引不是越多越好，它会带来存储开销，并在 INSERT/UPDATE/DELETE 时增加维护成本，因此要定期清理无效索引。

理论上，索引的设计应该遵循以下逻辑：

高频查询 → 创建索引；
唯一性约束 → 使用唯一索引；
多字段联合查询 → 使用联合索引；
覆盖查询 → 用索引减少回表；
冷数据 → 索引精简。

换句话说，索引语法只是表象，背后的本质是 用最少的索引，解决最高频、最关键的查询场景。

2. 大厂实战理解

在大厂环境里，索引的使用并不是机械地“建就完了”，而是要在 查询效率、写入性能、存储开销 三者之间做平衡。以下是一些典型实践：

索引数量控制
- BAT 的 OLTP 系统通常会给单表建立 3~5 个核心索引，超过这个数量会进行严格评审。
- 原因在于：写入链路往往比查询更敏感（例如订单系统、支付系统），过多的索引会拖慢写入，甚至导致锁竞争。
联合索引 vs 单列索引
- 实际生产中，单列索引并不常见。更多情况是 联合索引，结合最左匹配原则来覆盖常见查询场景。
- 例如订单表 (user_id, status, create_time) 联合索引，可以同时满足用户查询和后台筛选，而不是为每个字段单独建索引。
冷热分层策略
- 在字节、阿里等公司，数据表会被拆分为“热表”和“冷表”。热表的索引更激进，覆盖查询链路；冷表索引更精简，以减少存储与写入开销。
- 热数据索引甚至会牺牲部分写入性能，因为它们支撑的是 核心业务查询（例如推荐系统的用户画像）。
定期索引巡检
- 大厂 DBA 团队会用工具扫描 SHOW INDEX 结果，找出 冗余索引（功能重复的索引）与 未使用索引（查询计划中从未命中的索引），然后进行下线。
- 例如 (name) 与 (name, age) 并存时，前者可能就是冗余索引。
索引与架构结合
- 在更大规模场景下，单靠 MySQL 索引不足以解决所有问题。
- 搜索场景 → 引入 ElasticSearch；
- 日志分析场景 → 使用 ClickHouse；
- 数据仓库场景 → Hive/Presto。
- MySQL 索引更多是 交易系统的核心优化手段，而不是万能的解决方案。

总结

理论层面：索引语法看似简单，但其应用逻辑是“用最少的代价，解决最重要的查询场景”。
大厂实战层面：索引设计是一种平衡艺术，要兼顾 查询性能、写入性能、存储开销。大厂会通过 联合索引、冷热分层、索引巡检 等手段，保证系统在高并发下依然稳定高效。

大厂面试题

面试题 1

问题：
创建索引时，CREATE INDEX 和 ALTER TABLE ADD INDEX 有什么区别？

参考答案：
本质上两者效果一样，都是为表添加索引。

CREATE INDEX 是专门的语法，适合单独创建索引；
ALTER TABLE ADD INDEX 更常用于建表或表结构调整时，顺带加索引。
在大厂场景中，DBA 更倾向于使用 ALTER TABLE，因为它能和其他修改操作（如添加字段）一起做变更，减少表锁和迁移成本。

面试题 2

问题：
如果你为 phone 字段创建了唯一索引，插入重复值会发生什么？

参考答案：
MySQL 会拒绝插入，并报错 Duplicate entry for key。
唯一索引保证列值唯一，是数据一致性的重要约束。在大厂业务中，例如手机号、邮箱、身份证号等，都会加唯一索引来避免脏数据进入核心系统。

面试题 3

问题：
什么时候该为字段建立联合索引，而不是单列索引？

参考答案：
当查询条件经常涉及多个字段组合时，建立联合索引更高效。
比如 where profession = '软件工程' and age = 23 and status = 1，单列索引会导致多次合并结果，而 (profession, age, status) 联合索引能一次命中。
大厂实际优化经验：对于高频多字段查询，联合索引性能优于多个单列索引，但要遵循 最左匹配原则。

面试题 4

问题：
如何查看一张表上有哪些索引？哪些索引是冗余的？

参考答案：

可以用 SHOW INDEX FROM table_name; 查看索引情况。
判断冗余索引的原则是：如果一个索引的功能被另一个更大范围的索引完全覆盖，那么它就是冗余的。
例如 (name, age) 索引已经存在时，单独的 (name) 索引通常就是冗余的。
大厂 DBA 常用自动巡检工具识别冗余索引，并定期清理，避免占用空间和拖慢写入。

面试题 5

问题：
删除索引可能会带来什么影响？如何在大厂环境中安全地删除索引？

参考答案：
删除索引可能导致部分 SQL 性能急剧下降，甚至引发线上慢查询。
在大厂的标准流程中，删除索引前会：

用 EXPLAIN 检查索引是否还被使用；
观察 SQL 日志，确认索引在过去一段时间内无命中；
先在影子库或灰度环境中模拟删除，确认无影响后，再上线执行 DROP INDEX。
这是一种 渐进式风险控制，保证核心业务稳定。

大厂场景题

场景题 1：用户系统中的唯一索引

背景：
公司有一个 tb_user 表，存储了上千万用户数据。字段包括 id、name、phone、email 等。业务要求：

手机号必须唯一；
邮箱可以重复但常用于检索。

问题：
请问你会如何设计索引？为什么？

参考答案：

为 phone 字段创建唯一索引：
```
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
```
保证唯一性并加速检索。
为 email 字段创建普通索引：
```
CREATE INDEX idx_email ON tb_user(email);
```
因为邮箱常用于模糊查询和登录校验。
这种设计既满足了数据一致性，又提高了查询效率。在大厂的用户系统中，phone 和 email 的索引设计往往是核心。

场景题 2：电商订单系统的联合索引

背景：
orders 表有如下字段：order_id, user_id, order_status, create_time。业务查询常见：

select * from orders 
where user_id = 123 and order_status = 1 
order by create_time desc limit 10;

问题：
如何设计索引来优化上述查询？

参考答案：
创建联合索引 (user_id, order_status, create_time)：

CREATE INDEX idx_user_status_time ON orders(user_id, order_status, create_time);

这样索引同时支持 where user_id + order_status 的条件过滤，并利用 create_time 的顺序，避免文件排序。
这是大厂电商业务中常见的 覆盖高频查询的联合索引设计。

场景题 3：日志系统的索引与分区

背景：
logs 表每天新增数千万条记录，字段包括 id, app_id, log_time, log_level, message。常见查询：

select * from logs 
where app_id = 200 and log_time between '2025-09-01' and '2025-09-07';

问题：
如果只用 CREATE INDEX idx_app_time ON logs(app_id, log_time);，为什么仍可能性能不佳？如何优化？

参考答案：

原因：数据量太大，范围查询导致索引区间过大，扫描代价依然高。
优化：
1. 按 log_time 分区表，只扫描对应分区；
2. 热数据放 MySQL，冷数据迁移到 ClickHouse/ES；
3. 使用覆盖索引 (app_id, log_time) 减少回表。
  这是大厂日志系统常见的 分区 + 分层存储 策略。