MySQL大批量Update优化

• 参考文章

https://www.cnblogs.com/dasn/articles/6094887.html

需求:

• 根据一系列规则把文件分为几类，方便查询。
• 这里使数据库中表结构冗余几列字段来匹配相应的规则，就是需要一条条去扫数据库中的数据，扫完把数据更新回数据库。
• 问题出在大规模数据的更新上，数据量很大会很耗时。
• 这里二十多万条数据若是逐条更新需要7/8分钟左右，把需要更新的数据插入到临时表再以表为单位进行更新操作就只需要1/2秒，性能提升还是十分明显的。

代码:

def truncate_patch_insert(_sql_util, sql_insert, list_mesh_all, table_name):
    delete_table_sql = "truncate table %s" % table_name
    _sql_util.update_data(delete_table_sql)
    for i in range(0, len(list_mesh_all), 1000):
        try:
            num = 1000 + i
            if num > len(list_mesh_all):
                _sql_util.insert_many_data(sql_insert, list_mesh_all[i: len(list_mesh_all)])
            else:
                _sql_util.insert_many_data(sql_insert, list_mesh_all[i: i + 1000])
        except Exception as err:
            logger.error("insert %s error =%s", (table_name, err))
            _sql_util.rollback()
            _sql_util.db_close()


def make_label_pss(_sql_util, file_all, pss_name):
    list_all = []
    for file_single in file_all:
        file_name = file_single[1].lower()
        short_name = os.path.basename(file_name)
        sub_type = 0
        for k in constPss:
            if file_name.startswith(k):
                sub_type = constPss[k]
                for st in sub_type:
                    (k, v), = st.items()
                    if short_name.find(k) > 0:
                        sub_type = v
                        break
                    sub_type = v
                break
        # print(type(file_single[0]))
        list_all.append([file_single[0], sub_type])

    sql_insert = """INSERT INTO PssTemp ( id, SubType ) VALUES (%s, %s)"""
    truncate_patch_insert(sql_util, sql_insert, list_all, "PssTemp")
    sql_table_update = """UPDATE %s as t1, PssTemp SET t1.SubType=PssTemp.SubType WHERE t1.id=PssTemp.id""" % pss_name
    sql_util.update_data(sql_table_update)


def scan_table(sql_util, mesh_table_name="", pss_table_name=""):
    list_mesh_all = []

    sql_select_pss = """select id, FileName from %s""" % pss_table_name

    res_mesh_pss = sql_util.select_all_data(sql_select_pss)

    make_label_pss(sql_util, res_mesh_pss, pss_table_name)