提高查询性能

Question

我需要从一个PostgreSQL数据库中读取并连接大量的行（约500k），并将它们写入到一个MySQL数据库中。

我的天真的方法看起来像这样：

    entrys = Entry.query.yield_per(500)

    for entry in entrys:
        for location in entry.locations:
            mysql_location = MySQLLocation(entry.url)
            mysql_location.id = location.id
            mysql_location.entry_id = entry.id

            [...]

            mysql_location.city = location.city.name
            mysql_location.county = location.county.name
            mysql_location.state = location.state.name
            mysql_location.country = location.country.name

            db.session.add(mysql_location)

    db.session.commit()

每个“Entry”大约有1到100个“Locations”。

由于所有内容都保留在内存中，直到会话提交，因此此脚本现在运行了约20小时，并且已经消耗了> 4GB的内存。

尝试提前提交时，我遇到了像this这样的问题。

如何提高查询性能？它需要变得更快，因为行数将在未来几个月内增长到约2500k。

- dbanck

---

你为什么不能使用抽取、转换、加载方法呢？ - AndrewS

基本上 pg_dump dbname | mysql dbname - Jochen Ritzel

@JochenRitzel，我正在将多个表中的多行合并为MySQL中的一行。我不知道pg_dump如何帮助。 - dbanck

你尝试过从Postgres中提取数据到CSV，然后将CSV加载到MySQL吗？ - Ihor Romanchenko

Answer 1

你的幼稚方法之所以存在缺陷，正如你已经知道的那样，是因为占用内存的东西是悬空在内存中等待刷新到mysql的模型对象。

最简单的方法就是根本不使用ORM进行转换操作。直接使用SQLAlchemy表对象，因为它们也更快。

另外，你可以创建两个会话，并将两个引擎绑定到单独的会话中！然后你可以为每个批次提交mysql会话。

- Antti Haapala -- Слава Україні

---

我支持使用两个独立的会话选项，其中一个会在每个批次中使用expunge_all()进行清理。此外，你(@dbanck)遇到的问题也可以通过使用范围查询而不是yield_per来解决。 - van