如何优化Python+MySQL城市经纬度与首府字段更新脚本性能？

现有三张数据表：Countries、states、cities，其中cities表包含名称字段及指向states表的外键，已新增lat、long、capital三个字段，数据量约46000条；另有一张包含所需全部字段的主表countrycap_simplecountry，数据量约430万条。

当前使用以下Python脚本更新cities表的经纬度与首府信息，但脚本运行耗时5-6小时，需将耗时压缩至数分钟，求最优优化策略。

原Python脚本：

import pandas as pd
import pymysql
import time
from Levenshtein import distance 
import csv
from functools import lru_cache

start_time = time.time()
connection=pymysql.connect(
    host="localhost",
    user="admin",
    password="Adxxxxx3",
    port=3306,
    db="rixxxx")

mycursor = connection.cursor()

mycursor.execute("SET FOREIGN_KEY_CHECKS=0")


mycursor.execute("SELECT ci.name as city_Name, ci.id as city_id, s.name as state_name, s.id as state_id, co.name as country_name, co.id as country_id FROM countrycap_states s inner join countrycap_cities ci on ci.state_id = s.id inner join countrycap_countries co on co.id = s.country_id ORDER BY `country_name` ASC ")
count_val = mycursor.fetchall()
with open('zzzzcities_unfilled.csv', 'w', encoding='UTF8') as f:
    for old_data in count_val:
        try:
            query_main_simp = "SELECT country, state, city,capital, latitude,longitude FROM `countrycap_simplecountry` where country = '{0}' and state = '{1}' and city = '{2}'".format(old_data[4], old_data[2], old_data[0])
            mycursor.execute(query_main_simp)
            ins_data = mycursor.fetchall()[0]
            # print(ins_data[3])
            if (ins_data[3] == 'admin') or (ins_data[3] == 'primary'):
                in_ss="UPDATE countrycap_cities w JOIN countrycap_states x on w.state_id=x.id JOIN countrycap_countries co on co.id=x.country_id SET w.latitude = {0}, w.longitude = {1}, w.capital_city_admin = '{2}' WHERE w.name='{3}' and co.name='{4}' and x.name ='{5}'".format(ins_data[4],ins_data[5],ins_data[3],ins_data[2],ins_data[0],ins_data[1])
                mycursor.execute(in_ss)
                # print(in_ss)
                connection.commit()

            elif (ins_data[3] == 'minor') or (ins_data[3] == ''):
                in_ss="UPDATE countrycap_cities w JOIN countrycap_states x on w.state_id=x.id JOIN countrycap_countries co on co.id=x.country_id SET w.latitude = {0}, w.longitude = {1} WHERE w.name='{2}' and co.name='{3}' and x.name ='{4}'".format(ins_data[4],ins_data[5],ins_data[2],ins_data[0],ins_data[1])
                mycursor.execute(in_ss)
                # print(in_ss)
                connection.commit()

        except Exception as e:
            writer = csv.writer(f)
            writer.writerow(old_data)

print("--- %s seconds ---" % (time.time() - start_time))

原脚本耗时核心原因是Python与数据库的频繁交互（4.6万次查询+4.6万次更新）、逐行提交事务、更新时重复JOIN三张表，以下是针对性优化方案：

1. 用纯SQL批量更新替代Python循环 #

直接通过MySQL的UPDATE ... JOIN语句完成批量更新，完全避免Python中间层的开销，这是效率提升最显著的一步。

核心更新SQL（分两种情况） #

情况1：更新首府类型为admin/primary的记录

UPDATE countrycap_cities w
JOIN countrycap_states x ON w.state_id = x.id
JOIN countrycap_countries co ON x.country_id = co.id
JOIN countrycap_simplecountry sc 
    ON co.name = sc.country 
    AND x.name = sc.state 
    AND w.name = sc.city
SET 
    w.latitude = sc.latitude,
    w.longitude = sc.longitude,
    w.capital_city_admin = sc.capital
WHERE sc.capital IN ('admin', 'primary');

情况2：更新首府类型为minor/空的记录

UPDATE countrycap_cities w
JOIN countrycap_states x ON w.state_id = x.id
JOIN countrycap_countries co ON x.country_id = co.id
JOIN countrycap_simplecountry sc 
    ON co.name = sc.country 
    AND x.name = sc.state 
    AND w.name = sc.city
SET 
    w.latitude = sc.latitude,
    w.longitude = sc.longitude
WHERE sc.capital IN ('minor', '');

2. 给主表添加联合索引，加速关联查询 #

countrycap_simplecountry表是关联核心，添加联合索引可让JOIN操作速度提升几个数量级：

CREATE INDEX idx_sc_country_state_city ON countrycap_simplecountry (country, state, city);

3. 批量提交事务，减少事务开销 #

原脚本每次更新都执行connection.commit()，4.6万次提交会产生巨大IO开销。优化后：

• 执行完两条批量更新SQL后，仅需执行一次COMMIT；
• 若用Python执行SQL，也需在所有更新语句执行完后再提交，避免逐行提交。

4. 用SQL导出匹配失败的记录 #

原脚本中捕获异常写入CSV的操作，可直接用SQL完成，避免Python循环写入：

SELECT 
    ci.name as city_Name, 
    ci.id as city_id, 
    s.name as state_name, 
    s.id as state_id, 
    co.name as country_name, 
    co.id as country_id 
FROM countrycap_states s 
JOIN countrycap_cities ci ON ci.state_id = s.id 
JOIN countrycap_countries co ON co.id = s.country_id 
LEFT JOIN countrycap_simplecountry sc 
    ON co.name = sc.country 
    AND s.name = sc.state 
    AND ci.name = sc.city
WHERE sc.city IS NULL
INTO OUTFILE '/tmp/zzzzcities_unfilled.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

注：需确保MySQL有对应路径的写入权限，或导出到MySQL允许的目录。

5. 临时关闭外键检查后恢复 #

原脚本已执行SET FOREIGN_KEY_CHECKS=0加速更新，但执行完所有操作后必须恢复：

SET FOREIGN_KEY_CHECKS=1;

避免数据库完整性问题。

6. 清理Python中未使用的模块 #

原脚本导入的pandas、Levenshtein、lru_cache等模块未实际使用，直接删除即可减少不必要的加载开销。

优化后的极简Python脚本（如需用Python执行） #

import pymysql
import time

start_time = time.time()
connection = pymysql.connect(
    host="localhost",
    user="admin",
    password="Adxxxxx3",
    port=3306,
    db="rixxxx"
)

mycursor = connection.cursor()

try:
    # 关闭外键检查
    mycursor.execute("SET FOREIGN_KEY_CHECKS=0")
    
    # 执行批量更新1
    update_sql1 = """
    UPDATE countrycap_cities w
    JOIN countrycap_states x ON w.state_id = x.id
    JOIN countrycap_countries co ON x.country_id = co.id
    JOIN countrycap_simplecountry sc 
        ON co.name = sc.country 
        AND x.name = sc.state 
        AND w.name = sc.city
    SET 
        w.latitude = sc.latitude,
        w.longitude = sc.longitude,
        w.capital_city_admin = sc.capital
    WHERE sc.capital IN ('admin', 'primary');
    """
    mycursor.execute(update_sql1)
    
    # 执行批量更新2
    update_sql2 = """
    UPDATE countrycap_cities w
    JOIN countrycap_states x ON w.state_id = x.id
    JOIN countrycap_countries co ON x.country_id = co.id
    JOIN countrycap_simplecountry sc 
        ON co.name = sc.country 
        AND x.name = sc.state 
        AND w.name = sc.city
    SET 
        w.latitude = sc.latitude,
        w.longitude = sc.longitude
    WHERE sc.capital IN ('minor', '');
    """
    mycursor.execute(update_sql2)
    
    # 导出匹配失败的记录
    export_sql = """
    SELECT 
        ci.name as city_Name, 
        ci.id as city_id, 
        s.name as state_name, 
        s.id as state_id, 
        co.name as country_name, 
        co.id as country_id 
    FROM countrycap_states s 
    JOIN countrycap_cities ci ON ci.state_id = s.id 
    JOIN countrycap_countries co ON co.id = s.country_id 
    LEFT JOIN countrycap_simplecountry sc 
        ON co.name = sc.country 
        AND s.name = sc.state 
        AND ci.name = sc.city
    WHERE sc.city IS NULL
    INTO OUTFILE '/tmp/zzzzcities_unfilled.csv' 
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n';
    """
    mycursor.execute(export_sql)
    
    # 一次性提交所有事务
    connection.commit()
finally:
    # 恢复外键检查
    mycursor.execute("SET FOREIGN_KEY_CHECKS=1")
    connection.close()

print("--- %s seconds ---" % (time.time() - start_time))

预期效果 #

通过以上优化，更新操作耗时会从5-6小时压缩至数分钟甚至更短，核心是将Python逐行交互替换为MySQL原生批量操作，利用数据库的索引优化和批量处理能力。

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