如何优化云MySQL与本地SQLite3双登录数据库的同步效率

核心逻辑是把逐行请求云端的N次IO操作，缩减为1次云端批量查询，所有校验逻辑放在本地内存执行，同时减少事务提交次数。

具体优化步骤 #

• 第一步：批量取出本地所有登录条目的用户名、oid等必要字段
• 第二步：将所有本地用户名一次性传给云端MySQL，用IN语句批量查询出属于当前公司、且存在于云端的用户名集合
• 第三步：本地内存对比，筛选出需要删除的本地条目oid，批量执行删除操作，统一提交事务

优化后示例代码 #

# 1. 批量查询本地所有需要校验的字段：可根据实际表结构调整字段顺序
c_l.execute("SELECT username, oid FROM login")
local_entries = c_l.fetchall()
if local_entries:
    # 提取所有本地用户名，构造IN查询参数
    local_usernames = [entry[0] for entry in local_entries]
    # 2. 一次查询云端：筛选出属于当前company、且在本地用户名列表中的有效记录
    format_strings = ','.join(['%s'] * len(local_usernames))
    l_db.execute(f"SELECT username FROM login WHERE company = %s AND username IN ({format_strings})", 
                 [company] + local_usernames)
    # 转成集合实现O(1)速度的存在性校验
    valid_cloud_usernames = {item[0] for item in l_db.fetchall()}
    
    # 3. 筛选需要删除的oid
    to_delete_oids = []
    for username, oid in local_entries:
        # 对齐原有逻辑：云端无记录/不属于当前公司、且oid不等于1的条目执行删除
        if username not in valid_cloud_usernames and oid != 1:
            to_delete_oids.append((oid,))
    
    # 批量删除+一次提交事务，避免频繁IO
    if to_delete_oids:
        c_l.executemany("DELETE FROM login WHERE oid = ?", to_delete_oids)
        conn_login.commit()

额外优化点 #

• 给云端MySQL的login表加(company, username)联合索引，可进一步加快批量查询速度
• 如果本地数据量达到万级以上，可分批拉取云端数据，避免IN语句参数过多影响性能
• 同步逻辑可对接云端删除事件做增量触发，无需每次全量扫描，进一步降低同步频次

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