【性能优化】人大金仓SQL优化实战：一条UPDATE语句从119分钟到1.53秒的蜕变

原创

已于 2026-02-28 18:43:48 修改 · 942 阅读

标签

#性能优化 #sql #网络

于 2026-02-25 18:38:22 首次发布

人大金仓SQL优化实战：一条UPDATE语句从119分钟到1.5秒的蜕变

一、问题背景

近期在某生产系统性能排查中，发现一个简单的UPDATE语句执行时间竟长达1小时59分钟，直接导致业务超时、引起生产系统问题。表结构与数据量如下（已脱敏）：

设备信息表（*_*_list）：记录交换机等设备的基本信息，约69,080行。
端口状态表（*_*_port）：记录每个设备端口的实时状态，约412,073行，按区域划分为17个分区。

业务逻辑：统计每个设备下状态为特定值（如’02’、‘04’）的端口数量，并更新到设备信息表的port_count字段。

原始SQL如下：

UPDATE *_*_list d
SET d.port_count = (
    SELECT COUNT(*)
    FROM *_*_port p
    WHERE p.current_state IN ('02', '04')
      AND p.device_id = d.device_id
);

二、性能分析：逐行循环的噩梦

2.1 无索引状态（1h59m）

在没有任何索引的情况下，执行计划揭示出灾难的根源：

外层对*_*_list全表扫描（69,080行）。
每行执行一次子查询，而子查询需要对*_*_port全表扫描（412,073行）。
总扫描行数 ≈ 69,080 × 412,073 ≈ 284亿行！

这是典型的**逐行循环（Row-by-Row）**导致的性能灾难。每一次子查询都是全表扫描，即使数据量不大，乘积效应也能让数据库崩溃。

2.2 创建复合索引后（10.9秒）

在*_*_port上创建复合索引：

CREATE INDEX idx_device_state ON *_*_port(device_id, current_state);

执行时间降至10.9秒，但仍需执行69,080次索引扫描，每次扫描涉及17个分区，总索引扫描次数高达117万次。

执行计划（已脱敏）：

Update on *_*_list a  (cost=0.00..142132776.59 rows=165004 width=3480) (actual time=10935.552..10935.558 rows=0 loops=1)
  ->  Seq Scan on *_*_list a  (cost=0.00..142132776.59 rows=165004 width=3480) (actual time=817.255..8980.587 rows=69080 loops=1)
        SubPlan 1
          ->  Aggregate  (cost=860.86..860.87 rows=1 width=8) (actual time=0.115..0.115 rows=1 loops=69080)
                ->  Append  (cost=0.42..860.22 rows=258 width=0) (actual time=0.046..0.113 rows=8 loops=69080)
                      ->  Index Only Scan using idx_device_state on *_*_port_rm_01 p  (cost=0.42..31.00 rows=6 wid