首页 > 代码库 > 数据库订正脚本性能优化两则：去除不必要的查询和批量插入SQL

数据库订正脚本性能优化两则：去除不必要的查询和批量插入SQL

2024-08-10 13:51:58   219人阅读

 

      最近在做多数据库合并的脚本， 要将多个分数据库的表数据合并到一个主数据库中。 以下是我在编写数据订正脚本时犯过的错误， 记录以为鉴。

 

       不必要的查询

       请看以下语句：     

    regiondb = db.Houyiregiondb()    houyidb = db.Houyidb(read_only=False)    regiondbRet = regiondb.query(vmmacsFromRegiondbSql)    houyidbRet = houyidb.query(vmmacsFromHouyidbSql)    if len(regiondbRet) == 0:        return

        原意很明显， 是为了分别取出 houyidb 和 houyiregiondb 相应的记录， 用于后续对比。 但是这里不假思索地将 houyidb 查询的语句提前了， 结果可能导致  houyidb.query(vmmacsFromHouyidbSql) 成为不必要的查询。如果这个查询会拉取很多数据的话， 就会造成很大浪费。 字节就是钱啊！ 如今的程序员或许不用像以前的程序员那么“抠门”， 也要“精打细算” 才是。 修复办法很简单， 调换下语句顺序即可：

regiondb = db.Houyiregiondb()    regiondbRet = regiondb.query(vmmacsFromRegiondbSql)    if len(regiondbRet) == 0:        regiondb.close()        return    houyidb = db.Houyidb(read_only=False)    houyidbRet = houyidb.query(vmmacsFromHouyidbSql)
        教训：  写程序切忌不假思索。
 
         锁超时
      并发操作主数据库时， 报 Lock wait timeout exceeded; try restarting transaction  锁超时错误。
      经查， 是因为insert X 表的时候同时并发 delete from X where ... 。 insert 在先， delete X 语句等待锁。 由于 insert X 要插入十几万条记录， 耗费超过1分钟， 而  innodb_lock_wait_timeout = 50s ( show variables like "%timeout%";) 因此 delete X 无可挽回地失败了。
      这里需要优化 sql 语句。 优化的办法是： 将 十几万条记录切分成多次提交， 每次提交 1000 条插入语句。代码如下：
def divideIntoGroups(allTuples, numPerGroup=1000):    ‘‘‘       divide tuples into group of tuples ;       each group has no more than numPerGroup tuples       default value of numPerGroup is 1000    ‘‘‘    groups = []    totalNum = len(allTuples)    if totalNum <= numPerGroup:        groups.append(allTuples)        return groups    start = 0    eachEnd = start + numPerGroup    while start < totalNum:        groups.append(allTuples[start:eachEnd])        start += numPerGroup        eachEnd = start + numPerGroup        if eachEnd >= totalNum:            eachEnd = totalNum    return groupsdef insertManyMany(insertSql, allTuples, db):    ‘‘‘       insert many many records , usually more than 10000       insert 1000 once and insert (len/1000+1) times    ‘‘‘    groups = divideIntoGroups(allTuples)    count = 0    for groupTuples in groups:        affectRows = db.executemany(insertSql, groupTuples)        if affectRows:            count += affectRows        db.commit()    needInsertNum = len(allTuples)    isPassedMsg = (‘OK‘ if needInsertNum==count else ‘SOME ERROR‘)    printAndLog("Need insert %d records, and actual %d. %s" % (needInsertNum, count, isPassedMsg))
      调用方法如下：
      insertSql = "insert into student (name, age) value (%s, %s) "
      allTuples = [("zhang", 20), ("qian", 25), ("wang", 23), ... , ("liu", 26)]
      insertManyMany(insertSql, allTuples, db)
    效果很明显。 原来插入 32000 条记录需要 18s, 现在只需要 2-3s ,  原来插入 129968 条记录需要 67s , 现在只需要 12-15s. 同时， 每次提交的插入事务变短， 可以减少锁等待时间。
 
数据库订正脚本性能优化两则：去除不必要的查询和批量插入SQL






























        

            
                
                  
                
                

                    联系

                    我们