MySQL / 系统课时

创建、查看与删除索引

创建、查看与删除索引

索引设计最终需要落实为可执行的数据库操作。本课使用一张示例表完成索引的创建、查看、调整和删除,并通过 SHOW INDEX 与 EXPLAIN 验证索引是否存在以及查询是否可能使用它。

学习目标

  • 使用 CREATE INDEX 和 ALTER TABLE 创建索引
  • 使用 SHOW INDEX 查看已有索引及其列顺序
  • 安全删除不再需要的索引并验证结果

准备示例表

先建立一张没有额外二级索引的订单表。主键会自动形成主键索引,后续操作都围绕 customer_id、status 和 created_at 展开。

sql
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL
);

SHOW INDEX FROM orders;

预期结果

text
返回 orders 表当前的索引信息,其中至少包含名为 PRIMARY 的主键索引。Cardinality 等统计字段的具体值可能不同。

创建单列索引

可以使用 CREATE INDEX 为已有表增加普通索引。下面为 customer_id 创建索引,使按客户查询订单时拥有可选的索引访问路径。

sql
CREATE INDEX idx_orders_customer
ON orders (customer_id);

SHOW INDEX FROM orders;

预期结果

text
索引列表中会出现名为 idx_orders_customer 的索引,其索引列为 customer_id。

同样的操作也可以写成 ALTER TABLE。两种写法都很常见,团队可以按照数据库变更规范统一风格。

sql
ALTER TABLE orders
ADD INDEX idx_orders_status (status);

预期结果

text
执行成功后,orders 表新增名为 idx_orders_status 的普通索引。

创建组合索引

当查询经常同时按客户和时间过滤时,可以建立组合索引。索引定义中的列顺序会被保留下来,并直接影响后续可以高效支持的查询模式。

sql
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

SHOW INDEX FROM orders
WHERE Key_name = 'idx_orders_customer_created';

预期结果

text
返回两行索引列信息,customer_id 的 Seq_in_index 为 1,created_at 的 Seq_in_index 为 2。

可以进一步使用 EXPLAIN 查看查询计划,而不是仅凭“索引已经创建”判断查询一定会使用它。

sql
EXPLAIN SELECT id, total_amount
FROM orders
WHERE customer_id = 1001
  AND created_at >= '2026-01-01 00:00:00';

预期结果

text
执行计划会列出 possible_keys、key、rows 等信息。存在合适统计信息且优化器选择该组合索引时,key 列会显示 idx_orders_customer_created;实际结果可能随数据分布变化。

查看索引定义

SHOW INDEX FROM 表名 是检查索引最直接的方式。重点关注 Key_name、Non_unique、Seq_in_index 和 Column_name:它们分别说明索引名称、是否允许重复、列在组合索引中的位置以及具体索引列。

如果需要查看整张表的建表定义,也可以使用 SHOW CREATE TABLE,其中会包含主键、唯一索引和普通索引的定义。

sql
SHOW CREATE TABLE orders;

预期结果

text
返回 orders 的 CREATE TABLE 定义,其中包含 PRIMARY KEY 以及已经创建的 idx_orders_customer、idx_orders_status 和 idx_orders_customer_created 等索引定义。

删除和调整索引

普通索引可以使用 DROP INDEX 删除。删除前应先确认没有关键查询依赖该索引,并在生产环境评估变更期间的资源消耗和查询性能影响。

sql
DROP INDEX idx_orders_status ON orders;

SHOW INDEX FROM orders
WHERE Key_name = 'idx_orders_status';

预期结果

text
DROP INDEX 执行成功;随后查询 idx_orders_status 时返回空结果,表示该索引已经不存在。

MySQL 没有通过一条通用语句直接修改普通索引列定义的做法。需要调整索引列或顺序时,通常删除旧索引后创建新索引。例如把原来的单列客户索引调整为客户与状态的组合索引,可以执行:

sql
DROP INDEX idx_orders_customer ON orders;

CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

预期结果

text
旧索引 idx_orders_customer 被删除,新索引 idx_orders_customer_status 创建成功。

完成索引操作后,至少应检查索引定义是否符合预期,并对关键 SQL 执行 EXPLAIN。这样才能把“DDL 已成功执行”和“索引确实适合当前查询”两个问题分别验证清楚。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建、查看与删除索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通