Python处理SQLite好友关系表：批量更新好友列表异常问题

原代码核心问题 #

• 嵌套复用同一个cursor：SQLite游标是单向迭代器，内层循环会把游标耗尽，外层循环只能处理第一行数据
• 全表遍历次数过多：每个NULL用户都要全表扫一次，8.5亿行下完全不可行，时间复杂度O(n²)直接爆炸

解决方案思路 #

先一次性构建反向好友映射表（记录每个用户被哪些人加为好友），再基于这个映射批量更新所有用户的好友列表，全程仅需两次全表遍历，时间复杂度O(n)，且分批处理避免内存溢出。

优化后的代码实现 #

import sqlite3
import json

def build_reverse_friend_map(db_path, batch_size=100000):
    """分批构建反向好友映射：key是用户ID，value是所有把该用户加为好友的ID列表"""
    reverse_map = {}
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode=WAL")  # 开启WAL提升写入性能
    cursor = conn.cursor()
    
    cursor.execute("SELECT id, friends FROM Users")
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        for user_id, friends_str in rows:
            if friends_str is None:
                continue
            try:
                friends = json.loads(friends_str)
                for friend_id in friends:
                    if friend_id not in reverse_map:
                        reverse_map[friend_id] = set()
                    reverse_map[friend_id].add(user_id)
            except json.JSONDecodeError:
                # 处理格式错误的JSON数据，可根据需求调整
                print(f"Warning: Invalid JSON for user {user_id}")
    
    conn.close()
    # 把集合转成排序后的列表，方便后续使用
    for k in reverse_map:
        reverse_map[k] = sorted(reverse_map[k])
    return reverse_map

def batch_update_friends(db_path, reverse_map, batch_size=100000):
    """分批更新用户的friends字段"""
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA cache_size=-2000000")  # 调整缓存大小为2GB，根据内存调整
    cursor = conn.cursor()
    
    cursor.execute("SELECT id, friends FROM Users")
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        update_data = []
        for user_id, friends_str in rows:
            # 获取反向映射的好友列表
            reverse_friends = reverse_map.get(user_id, [])
            if friends_str is None:
                # 原friends为NULL，直接用反向列表
                new_friends = reverse_friends
            else:
                # 合并原列表和反向列表，去重排序
                try:
                    original_friends = json.loads(friends_str)
                    combined = set(original_friends + reverse_friends)
                    new_friends = sorted(combined)
                except json.JSONDecodeError:
                    # 格式错误时，直接用反向列表兜底，或根据需求处理
                    new_friends = reverse_friends
            # 转成JSON字符串，空列表也保留（避免NULL）
            new_friends_str = json.dumps(new_friends)
            update_data.append((new_friends_str, user_id))
        
        # 批量执行更新，用事务提升性能
        cursor.executemany("UPDATE Users SET friends = ? WHERE id = ?", update_data)
        conn.commit()
        print(f"Updated {len(update_data)} rows")
    
    conn.close()

if __name__ == "__main__":
    DB_PATH = "your_database.db"
    print("Building reverse friend map...")
    reverse_map = build_reverse_friend_map(DB_PATH)
    print("Updating friends data...")
    batch_update_friends(DB_PATH, reverse_map)
    print("Done!")

关键优化点 #

1. 反向映射预构建：仅需一次全表遍历，就得到所有用户的被关注列表，避免重复扫描
2. 分批处理：用fetchmany控制每次加载的数据量，避免8.5亿行数据占满内存
3. 事务批量更新：每批次更新后才提交事务，大幅减少SQLite的IO操作
4. SQLite性能调优：开启WAL模式、调整缓存大小，提升大数据量下的读写速度
5. 数据去重与排序：用集合去重，排序后保证结果一致性

注意事项 #

• 批量大小batch_size可根据服务器内存调整，内存充足可设大（比如50万），内存有限就设小（比如10万）
• 处理前建议备份数据库，避免数据异常
• 若存在JSON格式错误的行，可根据实际需求调整错误处理逻辑（比如跳过、记录日志等）

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