MySQL / 系统课时
创建、查看与删除索引
创建、查看与删除索引
索引设计最终需要落实为可执行的数据库操作。本课使用一张示例表完成索引的创建、查看、调整和删除,并通过 SHOW INDEX 与 EXPLAIN 验证索引是否存在以及查询是否可能使用它。
学习目标
- 使用
CREATE INDEX和ALTER TABLE创建索引 - 使用
SHOW INDEX查看已有索引及其列顺序 - 安全删除不再需要的索引并验证结果
准备示例表
先建立一张没有额外二级索引的订单表。主键会自动形成主键索引,后续操作都围绕 customer_id、status 和 created_at 展开。
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;预期结果
返回 orders 表当前的索引信息,其中至少包含名为 PRIMARY 的主键索引。Cardinality 等统计字段的具体值可能不同。创建单列索引
可以使用 CREATE INDEX 为已有表增加普通索引。下面为 customer_id 创建索引,使按客户查询订单时拥有可选的索引访问路径。
CREATE INDEX idx_orders_customer
ON orders (customer_id);
SHOW INDEX FROM orders;预期结果
索引列表中会出现名为 idx_orders_customer 的索引,其索引列为 customer_id。同样的操作也可以写成 ALTER TABLE。两种写法都很常见,团队可以按照数据库变更规范统一风格。
ALTER TABLE orders
ADD INDEX idx_orders_status (status);预期结果
执行成功后,orders 表新增名为 idx_orders_status 的普通索引。创建组合索引
当查询经常同时按客户和时间过滤时,可以建立组合索引。索引定义中的列顺序会被保留下来,并直接影响后续可以高效支持的查询模式。
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
SHOW INDEX FROM orders
WHERE Key_name = 'idx_orders_customer_created';预期结果
返回两行索引列信息,customer_id 的 Seq_in_index 为 1,created_at 的 Seq_in_index 为 2。可以进一步使用 EXPLAIN 查看查询计划,而不是仅凭“索引已经创建”判断查询一定会使用它。
EXPLAIN SELECT id, total_amount
FROM orders
WHERE customer_id = 1001
AND created_at >= '2026-01-01 00:00:00';预期结果
执行计划会列出 possible_keys、key、rows 等信息。存在合适统计信息且优化器选择该组合索引时,key 列会显示 idx_orders_customer_created;实际结果可能随数据分布变化。查看索引定义
SHOW INDEX FROM 表名 是检查索引最直接的方式。重点关注 Key_name、Non_unique、Seq_in_index 和 Column_name:它们分别说明索引名称、是否允许重复、列在组合索引中的位置以及具体索引列。
如果需要查看整张表的建表定义,也可以使用 SHOW CREATE TABLE,其中会包含主键、唯一索引和普通索引的定义。
SHOW CREATE TABLE orders;预期结果
返回 orders 的 CREATE TABLE 定义,其中包含 PRIMARY KEY 以及已经创建的 idx_orders_customer、idx_orders_status 和 idx_orders_customer_created 等索引定义。删除和调整索引
普通索引可以使用 DROP INDEX 删除。删除前应先确认没有关键查询依赖该索引,并在生产环境评估变更期间的资源消耗和查询性能影响。
DROP INDEX idx_orders_status ON orders;
SHOW INDEX FROM orders
WHERE Key_name = 'idx_orders_status';预期结果
DROP INDEX 执行成功;随后查询 idx_orders_status 时返回空结果,表示该索引已经不存在。MySQL 没有通过一条通用语句直接修改普通索引列定义的做法。需要调整索引列或顺序时,通常删除旧索引后创建新索引。例如把原来的单列客户索引调整为客户与状态的组合索引,可以执行:
DROP INDEX idx_orders_customer ON orders;
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);预期结果
旧索引 idx_orders_customer 被删除,新索引 idx_orders_customer_status 创建成功。完成索引操作后,至少应检查索引定义是否符合预期,并对关键 SQL 执行 EXPLAIN。这样才能把“DDL 已成功执行”和“索引确实适合当前查询”两个问题分别验证清楚。