SQL Server多表更新删除优化请求：清理冗余数据遇性能瓶颈

Hey, I’ve dealt with exactly this kind of redundant data cleanup on multi-table relational databases before—those chained updates and deletes can crawl when you’re working with large datasets, especially if you’re doing them one at a time. Let’s walk through actionable optimizations to speed this up, plus prevent future headaches.

1.1 Add Indexes to Foreign Key Columns First #

• If your Location.city_id, Customer.city_id, and Shop.city_id columns don’t have indexes, every join/update/delete is doing a full table scan. That’s the #1 culprit for slow operations here.
• Check existing indexes with SHOW INDEX FROM Location; (adjust syntax for your database engine) and add missing ones if needed:

-- Example for MySQL/PostgreSQL
CREATE INDEX idx_location_city_id ON Location(city_id);
CREATE INDEX idx_customer_city_id ON Customer(city_id);
CREATE INDEX idx_shop_city_id ON Shop(city_id);

• Run this during low-traffic hours—index creation can lock tables (or use online DDL if your engine supports it, like MySQL 8.0+).

1.2 Batch Operations Instead of Single-Row Processing #

Instead of handling one duplicate city at a time, batch all your updates and deletes to cut down on transaction overhead and lock contention. Here’s a PostgreSQL example (adjust syntax for your DB):

-- Step 1: Identify duplicate cities and pick a "keep" ID (e.g., the oldest record)
WITH duplicate_cities AS (
    SELECT city_name, MIN(id) AS keep_id, ARRAY_AGG(id) AS duplicate_ids
    FROM City
    GROUP BY city_name
    HAVING COUNT(id) > 1
)
-- Step 2: Batch update all related tables to point to the keep ID
UPDATE Location
SET city_id = dc.keep_id
FROM duplicate_cities dc
WHERE Location.city_id = ANY(dc.duplicate_ids) AND Location.city_id != dc.keep_id;

-- Repeat the UPDATE for Customer and Shop tables
UPDATE Customer
SET city_id = dc.keep_id
FROM duplicate_cities dc
WHERE Customer.city_id = ANY(dc.duplicate_ids) AND Customer.city_id != dc.keep_id;

UPDATE Shop
SET city_id = dc.keep_id
FROM duplicate_cities dc
WHERE Shop.city_id = ANY(dc.duplicate_ids) AND Shop.city_id != dc.keep_id;

-- Step 3: Batch delete all duplicate city records
DELETE FROM City
WHERE id IN (SELECT unnest(duplicate_ids) FROM duplicate_cities)
AND id NOT IN (SELECT keep_id FROM duplicate_cities);

1.3 Split Batches into Smaller Chunks #

If even full batch operations time out, split them into smaller chunks (e.g., 1000 rows at a time) to avoid long-held table locks:

-- Example: Update Location in chunks (MySQL syntax)
WHILE EXISTS (
    SELECT 1 FROM Location
    JOIN (
        SELECT city_name, MIN(id) AS keep_id, GROUP_CONCAT(id) AS duplicate_ids
        FROM City GROUP BY city_name HAVING COUNT(id) > 1
    ) dc ON FIND_IN_SET(Location.city_id, dc.duplicate_ids)
    WHERE Location.city_id != dc.keep_id
) DO
    UPDATE Location
    JOIN (
        SELECT city_name, MIN(id) AS keep_id, GROUP_CONCAT(id) AS duplicate_ids
        FROM City GROUP BY city_name HAVING COUNT(id) > 1
    ) dc ON FIND_IN_SET(Location.city_id, dc.duplicate_ids)
    SET Location.city_id = dc.keep_id
    WHERE Location.city_id != dc.keep_id
    LIMIT 1000;
END WHILE;

Adjust the LIMIT value based on your server’s capacity.

1.4 Temporarily Disable Non-Essential Constraints/Indexes #

During cleanup, disable foreign key checks and non-primary indexes to speed up writes (only do this during a maintenance window with no active writes):

-- MySQL example
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE Location DISABLE KEYS;
ALTER TABLE Customer DISABLE KEYS;
ALTER TABLE Shop DISABLE KEYS;

-- Run your batch updates/deletes here

SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE Location ENABLE KEYS;
ALTER TABLE Customer ENABLE KEYS;
ALTER TABLE Shop ENABLE KEYS;

For PostgreSQL, you can drop non-primary indexes temporarily and rebuild them after cleanup.

The root issue isn’t auto-increment primary keys—it’s missing constraints to stop duplicate cities from being inserted in the first place:

• Add a unique constraint to your City table’s identifying column (e.g., city name):

ALTER TABLE City ADD CONSTRAINT uq_city_name UNIQUE (city_name);

• If you need to allow minor variations (e.g., "New York" vs "new york"), add a normalized column (e.g., city_normalized) with a unique constraint, and normalize incoming data before insertion.
• Consider adding foreign key constraints with ON UPDATE CASCADE if you ever need to adjust city IDs in the future, but ideally, once cleaned up, you shouldn’t need to modify primary keys.

For MySQL/InnoDB, adjust these settings temporarily during cleanup to boost write performance:

• Increase innodb_buffer_pool_size to 70-80% of available RAM (so more data stays in memory).
• Set innodb_flush_log_at_trx_commit = 2 (reduces disk IO by flushing logs every second instead of every transaction—revert to 1 after cleanup for full ACID compliance).
• Disable autocommit (SET autocommit = 0;) and wrap all batch operations in a single transaction to reduce redo log overhead.

内容的提问来源于stack exchange，提问作者schikkamksu