遍历字典向MySQL插入数据耗时过长，如何优化？

我正在编写Python脚本抓取IMDB顶级电影数据并存入MySQL，原本用SQLite3开发正常，但切换到MySQL后，循环插入环节耗时极长，导致生成tabulate表格速度很慢。以下是我当前的代码：

curr.execute('''
CREATE TABLE movie_list
(movie_id INT AUTO_INCREMENT PRIMARY KEY, movie_rank INT, director_id INT, movie_name VARCHAR(55), year INT)
''')
curr.execute('''CREATE TABLE IF NOT EXISTS directors
(director_id INT AUTO_INCREMENT PRIMARY KEY, director VARCHAR(50))
''')
for key, value in html_parsing.movies_list.items():
    curr.execute('''
    INSERT INTO movie_list(movie_rank, movie_name, year) 
    VALUES(%s, %s, %s)
    ''',(key, value[0], value[2]))
    curr.execute('''
    INSERT INTO directors(director)
    VALUES(%s)
    ''',(value[1],))
    curr.execute('''INSERT INTO movie_list(director_id) SELECT director_id FROM directors ''')
    curr.execute('''ALTER TABLE movie_list 
    ADD FOREIGN KEY (director_id) REFERENCES directors(director_id)
    ''')
curr.execute('''
            SELECT
            *
            FROM movie_list LEFT JOIN directors ON movie_list.director_id = directors.director_id''')
df = pd.DataFrame(curr.fetchall())
print(tabulate(df, headers= 'keys', tablefmt= 'psql'))

问题分析 #

你的代码存在几个关键问题，既导致性能低下，也存在数据逻辑错误：

• 循环内重复执行ALTER TABLE添加外键：外键约束只需定义一次，多次执行会引发错误且严重拖慢速度。
• 导演数据无去重：同一导演会被重复插入directors表，造成数据冗余和无效操作。
• 错误更新movie_list的director_id：INSERT INTO movie_list(director_id) SELECT ...语句会新增空白行，而非更新对应电影的导演ID，逻辑完全错误。
• 单条SQL循环执行：每循环一次就发送4条SQL请求，频繁的数据库交互是性能瓶颈的核心原因。

优化方案 #

1. 修正表结构，一次性定义外键

建表时就规划好外键约束，避免循环内修改表结构，同时给导演名加唯一约束防止重复插入：

# 先创建导演表（无依赖）
curr.execute('''
CREATE TABLE IF NOT EXISTS directors
(director_id INT AUTO_INCREMENT PRIMARY KEY, 
 director VARCHAR(50) UNIQUE) -- 唯一约束避免重复导演
''')

# 创建电影表并定义外键
curr.execute('''
CREATE TABLE IF NOT EXISTS movie_list
(movie_id INT AUTO_INCREMENT PRIMARY KEY, 
 movie_rank INT, 
 director_id INT, 
 movie_name VARCHAR(55), 
 year INT,
 FOREIGN KEY (director_id) REFERENCES directors(director_id))
''')

2. 预处理导演数据，批量插入并建立映射

先提取所有导演名去重，批量插入后建立导演名到ID的映射字典，避免循环中重复查询：

# 提取所有导演名并去重
directors = list({value[1] for value in html_parsing.movies_list.values()})

# 批量插入导演，忽略重复项
insert_director_sql = "INSERT IGNORE INTO directors(director) VALUES (%s)"
curr.executemany(insert_director_sql, [(d,) for d in directors])

# 获取导演名与ID的映射
curr.execute("SELECT director_id, director FROM directors")
director_map = {name: idx for idx, name in curr.fetchall()}

3. 批量插入电影数据

把所有电影数据整理成列表，用executemany一次性插入，大幅减少数据库交互次数：

# 整理电影数据，带上对应的director_id
movie_data = []
for rank, value in html_parsing.movies_list.items():
    movie_name = value[0]
    director_name = value[1]
    year = value[2]
    director_id = director_map[director_name]
    movie_data.append((rank, director_id, movie_name, year))

# 批量插入电影
insert_movie_sql = '''
INSERT INTO movie_list(movie_rank, director_id, movie_name, year)
VALUES (%s, %s, %s, %s)
'''
curr.executemany(insert_movie_sql, movie_data)

# 手动提交事务（MySQL默认自动提交，批量操作时手动提交能大幅提升性能）
conn.commit()

4. 查询并生成表格

最后执行查询并生成tabulate表格，这一步的性能会因为前面的优化大幅提升：

curr.execute('''
SELECT
    ml.movie_id, ml.movie_rank, ml.movie_name, ml.year, d.director
FROM movie_list ml
LEFT JOIN directors d ON ml.director_id = d.director_id
''')
# 手动指定列名，避免DataFrame列名混乱
df = pd.DataFrame(
    curr.fetchall(),
    columns=['movie_id', 'movie_rank', 'movie_name', 'year', 'director']
)
print(tabulate(df, headers='keys', tablefmt='psql'))

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