在我们的生产服务器上,我们需要将900k个图像分割成不同的dir并更新400k行(MySQL和InnoDB引擎)。我编写了一个python脚本,该脚本将执行以下步骤:从数据库中选择小数据块(10行)
生成新目录
将文件复制到创建的dir并重命名
更新数据库(更新时有一些触发器将加载服务器)
重复
我的代码:import os, shutil
import database # database.py from tornado
LIMIT_START_OFFSET = 0
LIMIT_ROW_COUNT = 10
SRC_PATHS = ('/var/www/site/public/upload/images/',)
DST_PATH = '/var/www/site/public/upload/new_images/'
def main():
offset = LIMIT_START_OFFSET
while True:
db = Connection(DB_HOST, DB_NAME, DB_USER, DB_PASSWD)
db_data = db.query('''
SELECT id AS news_id, image AS src_filename
FROM emd_news
ORDER BY id ASC
LIMIT %s, %s''', offset, LIMIT_ROW_COUNT)
offset = offset + LIMIT_ROW_COUNT
news_images = get_news_images(db_data) # convert data to easy-to-use list
make_dst_dirs(DST_PATH, [i['dst_dirname'] for i in news_images]) # make news dirs
news_to_update = copy_news_images(SRC_PATHS, DST_PATH, news_images) # list of moved files
db.executemany('''
UPDATE emd_news
SET image = %s
WHERE id = %s
LIMIT 1''', [(i['filename'], i['news_id']) for i in news_to_update])
db.close()
if not db_data: break
if __name__ == '__main__':
main()
任务很简单,但我对表演有点紧张。在
如何使脚本更有效?在
升级版:
毕竟我使用了原始脚本,没有任何修改。大约花了5个小时。一开始很快,最后很慢。在