MySQL 管理运维：删除列导致索引变化

在 MySQL 数据库中，表的结构和性能优化是数据库管理员和开发人员需要经常考虑的问题。当我们在表中删除某一列时，可能会对该表上的索引产生影响，进而影响到数据库的性能。下面，我将详细介绍一个关于 MySQL 中删除列导致索引变化的案例。

准备 MySQL 8 实验环境：

(root@localhost) [yandb]> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.01 sec)

假设我们有一个名为 orders 的表，该表记录了用户的订单信息。表中包含以下字段：order_id（订单ID）、user_id（用户ID）、product_id（产品ID）、quantity（数量）和 order_date（订单日期）。

为了提高查询性能，我们在 user_id 字段上创建了索引，并在 (product_id, quantity) 两个字段上创建了联合索引。

create table orders (
order_id int primary key,
user_id int,
product_id int,
quantity int,
order_date datetime,
key (user_id),
key (product_id, quantity)
);

随着业务的发展，我们发现 quantity 字段不再需要，决定将其从表中删除。

但随着字段的删除，可能导致索引发生变化，进而影响到其他查询，甚至影响性能。

alter table orders drop column quantity;

查看表结构：

show create table orders\G

(root@localhost) [yandb]> show create table orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `order_date` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `user_id` (`user_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

发现删除列之后，复合索引会变成普通索引。

但是，相同案例，在 PostgreSQL 数据库中，则有不同表现。

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

创建测试表：

create table orders (
order_id int primary key,
user_id int,
product_id int,
quantity int,
order_date date
);
create index idx1 on orders(user_id);
create index idx2 on orders(product_id, quantity);

查看表结构：

postgres=# \d orders
                 Table "public.orders"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 order_id   | integer |           | not null | 
 user_id    | integer |           |          | 
 product_id | integer |           |          | 
 quantity   | integer |           |          | 
 order_date | date    |           |          | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "idx1" btree (user_id)
    "idx2" btree (product_id, quantity)

删除 quantity 字段：

postgres=# alter table orders drop column quantity;
ALTER TABLE
postgres=# \d orders
                 Table "public.orders"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 order_id   | integer |           | not null | 
 user_id    | integer |           |          | 
 product_id | integer |           |          | 
 order_date | date    |           |          | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "idx1" btree (user_id)

postgres=# 

发现 "idx2" btree (product_id, quantity) 索引被一并删除。

总结，删除字段操作需要仔细评估，在不同数据库中表现可能不一致，下面提示三点注意事项：

测试验证：在对生产环境做变更之前，一定要在测试环境进行测试验证，以得到符合预期的结果。
备份数据：在进行任何结构修改之前，务必备份数据库。这样，如果出现问题，我们可以快速恢复到原始状态。
监控性能：删除列或索引后，密切监控数据库的性能。观察查询响应时间、资源利用率等指标，确保系统稳定运行。

– END –