Last.FM用户播放量迁移优化：高CPU与更新失效问题求解

我们基于这套Schema存储Last.FM社区用户的专辑/艺人/曲目播放量，用户可通过“索引”账户更新播放量以维持排行榜准确性。数据通过COPY（比execute many效率更高）导入临时表（staging tables），随后通过查询将临时表数据迁移至最终的user_x表。但迁移环节的查询导致CPU占用过高，且多数情况下未执行更新，已确认迁移是问题根源，现寻求更优的迁移实现方案。

当前实现逻辑 #

先更新用户已有播放量记录，再插入剩余记录，具体代码如下：

迁移SQL #

WITH updated AS (
 UPDATE user_artists
 SET listens = sg.listens
 FROM user_artists_staging sg
    WHERE sg.user_id = $1
 AND user_artists.artist_id = (SELECT id FROM artists WHERE name = sg.name LIMIT 1)
 AND user_artists.user_id = sg.user_id
RETURNING user_artists.user_id, user_artists.artist_id
)
                        
INSERT INTO user_artists(user_id, artist_id, listens)
    SELECT user_artists_staging.user_id, fetch_artist(user_artists_staging.name) AS artist_id, user_artists_staging.listens
    FROM user_artists_staging
       LEFT JOIN updated t ON artist_id = t.artist_id
       WHERE user_artists_staging.user_id = $1
GROUP BY user_artists_staging.user_id, name, listens
ON CONFLICT ON CONSTRAINT user_artists_pkey DO NOTHING;

辅助函数fetch_artist #

CREATE OR REPLACE FUNCTION fetch_artist(name VARCHAR)
RETURNS SETOF INT AS $$
    BEGIN
        RETURN QUERY SELECT id FROM artists WHERE LOWER(artists.name) = LOWER(fetch_artist.name) LIMIT 1;
        IF NOT FOUND THEN
            RETURN QUERY INSERT INTO artists (name)
                VALUES (fetch_artist.name)
                ON CONFLICT ON CONSTRAINT artists_name_key DO NOTHING
                RETURNING id;
        end if;
END; $$ LANGUAGE plpgsql;

表结构定义 #

CREATE TABLE IF NOT EXISTS user_artists (
    user_id BIGINT,
    artist_id INT, 
    listens INT,
    
    PRIMARY KEY (user_id, artist_id),

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_artists_staging (
    user_id BIGINT NOT NULL,
    name VARCHAR,
    listens INTEGER
);

问题分析 #

1. 更新语句中子查询效率低下：(SELECT id FROM artists WHERE name = sg.name LIMIT 1)每次更新都要扫描artists表，且未针对LOWER(name)创建索引，导致全表扫描。
2. 函数逐行调用开销大：fetch_artist在INSERT阶段被逐行调用，函数本身的上下文切换加上无索引的查询，进一步拖慢速度。
3. 两次扫描临时表：UPDATE和INSERT分开执行，需要两次扫描临时表，增加CPU负载。
4. 不必要的GROUP BY：INSERT中的GROUP BY没有实际意义，反而增加计算开销。

优化方案 #

1. 补全索引，消除全表扫描 #

给artists表的小写名称创建函数索引，让所有艺人名称匹配查询都能快速定位：

CREATE INDEX IF NOT EXISTS idx_artists_lower_name ON artists (LOWER(name));

给临时表创建索引，加速后续关联操作：

CREATE INDEX IF NOT EXISTS idx_staging_user_name ON user_artists_staging (user_id, name);

2. 重构迁移逻辑，用INSERT ... ON CONFLICT DO UPDATE替代分步骤操作 #

将UPDATE和INSERT合并为一个语句，同时提前批量处理艺人ID，避免逐行调用函数：

-- 给临时表新增artist_id字段（仅第一次执行需要）
ALTER TABLE user_artists_staging ADD COLUMN IF NOT EXISTS artist_id INT;

-- 批量匹配已有艺人的ID到临时表
UPDATE user_artists_staging sg
SET artist_id = a.id
FROM artists a
WHERE LOWER(a.name) = LOWER(sg.name);

-- 批量插入不存在的艺人，并同步ID到临时表
WITH inserted_artists AS (
    INSERT INTO artists (name)
    SELECT DISTINCT name 
    FROM user_artists_staging 
    WHERE artist_id IS NULL
    ON CONFLICT ON CONSTRAINT artists_name_key DO NOTHING
    RETURNING id, name
)
UPDATE user_artists_staging sg
SET artist_id = ia.id
FROM inserted_artists ia
WHERE LOWER(sg.name) = LOWER(ia.name);

-- 一次性合并数据到最终表，存在则更新，不存在则插入
INSERT INTO user_artists (user_id, artist_id, listens)
SELECT user_id, artist_id, listens
FROM user_artists_staging
WHERE user_id = $1
ON CONFLICT (user_id, artist_id) DO UPDATE
SET listens = EXCLUDED.listens;

3. 简化fetch_artist函数（若需保留） #

如果业务上必须使用该函数，重构为更高效的版本，减少不必要的返回操作：

CREATE OR REPLACE FUNCTION fetch_artist(name VARCHAR)
RETURNS INT AS $$
DECLARE
    target_id INT;
BEGIN
    -- 先查询已有艺人
    SELECT id INTO target_id
    FROM artists
    WHERE LOWER(name) = LOWER(fetch_artist.name)
    LIMIT 1;

    -- 不存在则插入并返回新ID
    IF target_id IS NULL THEN
        INSERT INTO artists (name)
        VALUES (fetch_artist.name)
        ON CONFLICT ON CONSTRAINT artists_name_key DO NOTHING
        RETURNING id INTO target_id;
    END IF;

    RETURN target_id;
END; $$ LANGUAGE plpgsql STABLE;

4. 额外优化建议 #

• 使用TEMPORARY TABLE作为临时表，会话结束自动清理，且性能优于普通表。
• 若导入数据量极大，可分批次处理（比如按user_id分段），避免一次性占用过多CPU资源。
• 评估外键的ON DELETE CASCADE是否必要，若无需级联删除，可移除该约束以减少写操作开销。

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