python–MySQL中的Sqlalchemy批量更新工作非常慢

概述我正在使用SQLAlchemy 1.0.0,并希望批量生成一些UPDATE(更新,如果匹配主键,则不执行任何 *** 作)查询.我做了一些实验,发现批量更新看起来比批量插入或批量upsert慢得多.你能不能帮助我指出为什么它的工作速度如此之慢,或者有没有其他方法/想法用SQLAlchemy进行BULK UPDATE(不是BULK UPSERT)？下面是MYSQL中的

我正在使用sqlAlchemy 1.0.0,并希望批量生成一些UPDATE(更新,如果匹配主键,则不执行任何 *** 作)查询.

我做了一些实验,发现批量更新看起来比批量插入或批量upsert慢得多.

你能不能帮助我指出为什么它的工作速度如此之慢,或者有没有其他方法/想法用sqlAlchemy进行BulK UPDATE(不是BulK UPSERT)？

下面是MysqL中的表：

CREATE table `test` (  `ID` int(11) unsigned NOT NulL,`value` int(11) DEFAulT NulL,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAulT CHARSET=utf8mb4 ColLATE=utf8mb4_unicode_ci;

和测试代码：

from sqlalchemy import create_engine,textimport timedriver = 'MysqL'host = 'host'user = 'user'password = 'password'database = 'database'url = "{}://{}:{}@{}/{}?charset=utf8".format(driver,user,password,host,database)engine = create_engine(url)engine.connect()engine.execute('TruncATE table test')num_of_rows = 1000rows = []for i in xrange(0,num_of_rows):    rows.append({'ID': i,'value': i})print '--------- test insert --------------'sql = '''    INSERT INTO test (ID,value)    VALUES (:ID,:value)'''start = time.time()engine.execute(text(sql),rows)end = time.time()print 'Cost {} seconds'.format(end - start)print '--------- test upsert --------------'for r in rows:    r['value'] = r['ID'] + 1sql = '''    INSERT INTO test (ID,:value)    ON DUPliCATE KEY UPDATE value = VALUES(value)'''start = time.time()engine.execute(text(sql),rows)end = time.time()print 'Cost {} seconds'.format(end - start)print '--------- test update --------------'for r in rows:    r['value'] = r['ID'] * 10sql = '''    UPDATE test    SET value = :value    WHERE ID = :ID'''start = time.time()engine.execute(text(sql),rows)end = time.time()print 'Cost {} seconds'.format(end - start)

num_of_rows = 100时的输出：

--------- test insert --------------Cost 0.568960905075 seconds--------- test upsert --------------Cost 0.569655895233 seconds--------- test update --------------Cost 20.0891299248 seconds

num_of_rows = 1000时的输出：

--------- test insert --------------Cost 0.807548999786 seconds--------- test upsert --------------Cost 0.584554195404 seconds--------- test update --------------Cost 206.199367046 seconds

数据库服务器的网络延迟大约为500毫秒.

看起来像批量更新它一个接一个地发送和执行每个查询,而不是批处理？

提前致谢.

最佳答案即使数据库服务器(如您的情况)具有非常糟糕的延迟,您也可以通过技巧加快批量更新 *** 作.您可以使用stage-table非常快速地插入新数据,然后对目标表执行一次join-update,而不是直接更新表.这样做的另一个好处是可以大大减少必须发送到数据库的语句数量.

这如何与UPDATE一起使用？

假设您有一个表条目,并且您始终有新数据,但您只想更新已存储的数据.您创建目标表entrIEs_stage的副本,其中只包含相关字段：

entrIEs = table('entrIEs',Metadata,Column('ID',Integer,autoincrement=True,primary_key=True),Column('value',Unicode(64),nullable=False),)entrIEs_stage = table('entrIEs_stage',autoincrement=False,unique=True),)

然后使用批量插入插入数据.如果您使用MysqL的多值插入语法(sqlAlchemy本身不支持,但可以毫无困难地构建),这可以进一步加速.

INSERT INTO enrIEs_stage (`ID`,`value`)VALUES(1,'string1'),(2,'string2'),(3,'string3'),...;

最后,使用stage-table中的值更新destination-table的值,如下所示：

 UPDATE entrIEs e JOIN entrIEs_stage es ON e.ID = es.ID SET e.value = es.value;

然后你就完成了.

插入怎么样？

当然,这也可以加速插入.由于您已经在stage-table中拥有了数据,所以您需要做的就是发出INSERT INTO … SELECT语句,其中的数据还没有在destination-table中.

INSERT INTO entrIEs (ID,value)SELECT FROM entrIEs_stage esleft JOIN entrIEs e ON e.ID = es.IDHAVING e.ID IS NulL;

关于这一点的好处是你不必执行INSERT IGnorE,REPLACE或ON DUPliCATE KEY UPDATE,这将增加你的主键,即使它们什么也不做. 总结

以上是内存溢出为你收集整理的python – MySQL中的Sqlalchemy批量更新工作非常慢全部内容，希望文章能够帮你解决python – MySQL中的Sqlalchemy批量更新工作非常慢所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。