UPDATE nas_backup
SET fiber_serviceability_class = '0',
last_updated_ts = CURRENT_TIMESTAMP
WHERE location_id IN (
SELECT location_id
FROM (
WITH distinct_locs AS (
SELECT location_id, boundary_type
FROM (
SELECT location_id, boundary_type
FROM nc
WHERE technology_type = 'Fibre'
)
GROUP BY location_id, boundary_type
HAVING COUNT( * ) = 1
)
SELECT nas.location_id
FROM distinct_locs, nas_backup nas
WHERE distinct_locs.location_id = nas.location_id
AND distinct_locs.boundary_type = 'FSA'
GROUP BY nas.location_id
)
);
谁能建议一个优化查询的方法?现在需要5分钟以上。
nc表有1600万条记录,nas_backup表有200万条记录
EXISTS
可以帮你一点忙。试试吧:
UPDATE nas_backup
SET fiber_serviceability_class = '0',
last_updated_ts = CURRENT_TIMESTAMP
-- Remove the IN condition and check only that at least one row exists
WHERE EXISTS (
SELECT location_id
FROM (
WITH distinct_locs AS (
SELECT location_id, boundary_type
FROM (
SELECT location_id, boundary_type
FROM nc
WHERE technology_type = 'Fibre'
)
GROUP BY location_id, boundary_type
HAVING COUNT( * ) = 1
)
SELECT
nas.location_id
FROM
distinct_locs
WHERE
distinct_locs.boundary_type = 'FSA'
-- This is the new line
AND distinct_locs.location_id = nas_backup.location_id
GROUP BY
nas.location_id
)
);
然而,如果你能分享你的数据库结构和你的目标,这将更容易帮助我们。
下次请告诉我们你正在使用的DBMS的供应商和版本。
我在这里说的大部分事情都应该由一个好的优化器来完成,所以这主要是为了更好的可读性。
所有过滤器都应用于位置部分,因此将其带到子查询以减少结果集:
SELECT location_id, boundary_type
FROM nc
WHERE technology_type = 'Fibre' AND nc.boundary_type='FSA'
GROUP BY location_id, boundary_type
HAVING COUNT(*) = 1
接下来,使用JOIN语法代替隐式JOIN:
UPDATE nas fiber_serviceability_class = '0', last_updated_ts = CURRENT_TIMESTAMP
FROM nas_backup nas
JOIN (SELECT location_id, boundary_type
FROM nc
WHERE technology_type = 'Fibre' AND nc.boundary_type='FSA'
GROUP BY location_id, boundary_type
HAVING COUNT(*) = 1) loc ON loc.location_id=nas.location_id
我不知道是否有一个特殊的原因与HAVING子句的子查询。如果不知道你的结构和数据,这只是一个猜测,但也许你不需要。这是一个非常简单的查询,然后翻译为"更新每个nas_backup,其中技术类型是光纤和边界类型是FSA在同一时间"。
UPDATE nas fiber_serviceability_class = '0', last_updated_ts = CURRENT_TIMESTAMP
FROM nas_backup nas
JOIN nc ON nas.location_id=nc.location_id
WHERE nc.technology_type='Fibre'
AND nc.boundary_type='FSA'