sql Django中大型表的内存有效(常量)和速度优化迭代




database (3)

如果您只想迭代表中的所有内容,则以下内容在资源方面非常有效,并且比基本迭代器快得多。 注意,由于偏移操作的线性时间,主键分页对于有效实现是必要的。

def table_iterator(model, page_size=10000):
    try: max = model.objects.all().order_by("-pk")[0].pk
    except IndexError: return 
    pages = int(max / page_size) + 1
    for page_num in range(pages):
        lower = page_num * page_size
        page = model.objects.filter(pk__gte=lower, pk__lt=lower+page_size)
        for obj in page:
            yield obj

使用看起来像:

for obj in table_iterator(Model):
    # do stuff

我有一张很大的桌子。 它目前在MySQL数据库中。 我用django。

我需要迭代表中的每个元素来预先计算一些特定的数据(也许如果我更好,我可以做其他事情,但这不是重点)。

我希望通过不断使用内存来尽可能快地保持迭代。

因为它已经明确地限制了* Large * Django QuerySet中的内存使用以及为什么要遍历大量Django QuerySet消耗大量内存? ,对django中所有对象的简单迭代将终止机器,因为它将从数据库中检索所有对象。

寻求解决方案

首先,为了减少你的内存消耗你应该确保DEBUG是假的(或者修补游标: 关闭SQL日志记录,同时保持settings.DEBUG? )以确保django没有在connections存储东西以进行调试。

但即便如此,

for model in Model.objects.all()

是不行的。

甚至没有稍微改进的形式:

for model in Model.objects.all().iterator()

使用iterator()将通过不在内部存储缓存的结果来节省一些内存(尽管不一定在PostgreSQL上!); 但显然仍会从数据库中检索整个对象。

一个天真的解决方案

第一个问题的解决方案是通过chunk_size基于计数器对结果进行切片。 有几种方法可以编写它,但基本上它们都归结为SQL中的OFFSET + LIMIT查询。

就像是:

qs = Model.objects.all()
counter = 0
count = qs.count()
while counter < count:     
    for model in qs[counter:counter+count].iterator()
        yield model
    counter += chunk_size

虽然这是内存效率(与chunk_size成比例的常量内存使用),但它在速度方面确实很差:随着OFFSET的增长,MySQL和PostgreSQL(以及可能是大多数数据库)都会开始窒息并放慢速度。

更好的解决方案

Thierry Schellenbach在这篇文章中提供了一个更好的解决方案。 它过滤PK,这比抵消更快(可能有多快取决于DB)

pk = 0
last_pk = qs.order_by('-pk')[0].pk
queryset = qs.order_by('pk')
while pk < last_pk:
    for row in qs.filter(pk__gt=pk)[:chunksize]:
        pk = row.pk
        yield row
    gc.collect()

这开始变得令人满意。 现在Memory = O(C),速度〜= O(N)

“更好”解决方案的问题

只有在QuerySet中提供PK时,更好的解决方案才有效。 不幸的是,情况并非总是如此,特别是当QuerySet包含distinct(group_by)和/或值(ValueQuerySet)的组合时。

对于那种情况,不能使用“更好的解决方案”。

我们可以做得更好吗?

现在我想知道我们是否可以更快地避免关于没有PK的QuerySets的问题。 也许使用我在其他答案中找到的东西,但仅限于纯SQL:使用游标

由于我对原始SQL非常糟糕,特别是在Django中,这里提出了一个真正的问题:

我们如何为大型表构建更好的Django QuerySet迭代器

我从我读过的内容中得知,我们应该使用服务器端游标(显然(参见参考资料)使用标准的Django Cursor不会达到相同的结果,因为默认情况下python-MySQL和psycopg连接器都会缓存结果)。

这真的是一个更快(和/或更有效)的解决方案吗?

可以使用django中的原始SQL来完成吗? 或者我们应该根据数据库连接器编写特定的python代码?

PostgreSQLMySQL服务器端游标

就目前而言,这是我能得到的......

一个Django chunked_iterator()

现在,当然最好的方法是将此方法用作queryset.iterator() ,而不是iterate(queryset) ,并成为django核心或至少是可插拔应用程序的一部分。

更新感谢评论中的“T”查找带有一些额外信息的django票 。 连接器行为的差异使得最好的解决方案可能是创建一个特定的chunked方法,而不是透明地扩展iterator (听起来对我来说是一个好方法)。 exists一个实现存根,但是一年中没有任何工作,并且看起来作者还没准备好继续这样做。

其他参考:

  1. 为什么MYSQL更高的LIMIT偏移会降低查询速度?
  2. 如何在LIMIT子句中使用大偏移量加速MySQL查询?
  3. http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
  4. postgresql:offset + limit变得非常慢
  5. 改进PostgreSQL中的OFFSET性能
  6. http://www.depesz.com/2011/05/20/pagination-with-fixed-order/
  7. 如何在MySQL中的python服务器端游标中获取逐行MySQL ResultSet

编辑:

Django 1.6正在添加持久数据库连接

Django数据库持久连接

在某些情况下,这应该有助于使用游标。 仍然超出我目前的技能(以及学习时间)如何实施这样的解决方案..

此外,“更好的解决方案”肯定不适用于所有情况,不能用作通用方法,只能根据具体情况调整存根...


还有另一种选择。 它不会使迭代更快 ,(事实上它可能会减慢它),但它会使它使用更少的内存。 根据您的需要,这可能是合适的。

large_qs = MyModel.objects.all().values_list("id", flat=True)
for model_id in large_qs:
    model_object = MyModel.objects.get(id=model_id)
    # do whatever you need to do with the model here

只有id被加载到内存中,并根据需要检索和丢弃对象。 请注意增加的数据库负载和较慢的运行时间,这两者都是减少内存使用量的权衡。

我在工作者实例上运行异步计划任务时使用了这个,对于它们来说,如果它们很慢就没关系,但如果它们试图使用太多内存,它们可能会使实例崩溃,从而中止进程。


基本答案: 将原始SQL与服务器端游标一起使用

遗憾的是,直到Django 1.5.2,没有正式的方法来创建服务器端的MySQL游标(不确定其他数据库引擎)。 所以我写了一些魔术代码来解决这个问题。

对于Django 1.5.2和MySQLdb 1.2.4,以下代码将起作用。 此外,它评论很好。

注意:这不是基于公共API,因此它可能会在未来的Django版本中中断。

# This script should be tested under a Django shell, e.g., ./manage.py shell

from types import MethodType

import MySQLdb.cursors
import MySQLdb.connections
from django.db import connection
from django.db.backends.util import CursorDebugWrapper


def close_sscursor(self):
    """An instance method which replace close() method of the old cursor.

    Closing the server-side cursor with the original close() method will be
    quite slow and memory-intensive if the large result set was not exhausted,
    because fetchall() will be called internally to get the remaining records.
    Notice that the close() method is also called when the cursor is garbage 
    collected.

    This method is more efficient on closing the cursor, but if the result set
    is not fully iterated, the next cursor created from the same connection
    won't work properly. You can avoid this by either (1) close the connection 
    before creating a new cursor, (2) iterate the result set before closing 
    the server-side cursor.
    """
    if isinstance(self, CursorDebugWrapper):
        self.cursor.cursor.connection = None
    else:
        # This is for CursorWrapper object
        self.cursor.connection = None


def get_sscursor(connection, cursorclass=MySQLdb.cursors.SSCursor):
    """Get a server-side MySQL cursor."""
    if connection.settings_dict['ENGINE'] != 'django.db.backends.mysql':
        raise NotImplementedError('Only MySQL engine is supported')
    cursor = connection.cursor()
    if isinstance(cursor, CursorDebugWrapper):
        # Get the real MySQLdb.connections.Connection object
        conn = cursor.cursor.cursor.connection
        # Replace the internal client-side cursor with a sever-side cursor
        cursor.cursor.cursor = conn.cursor(cursorclass=cursorclass)
    else:
        # This is for CursorWrapper object
        conn = cursor.cursor.connection
        cursor.cursor = conn.cursor(cursorclass=cursorclass)
    # Replace the old close() method
    cursor.close = MethodType(close_sscursor, cursor)
    return cursor


# Get the server-side cursor
cursor = get_sscursor(connection)

# Run a query with a large result set. Notice that the memory consumption is low.
cursor.execute('SELECT * FROM million_record_table')

# Fetch a single row, fetchmany() rows or iterate it via "for row in cursor:"
cursor.fetchone()

# You can interrupt the iteration at any time. This calls the new close() method,
# so no warning is shown.
cursor.close()

# Connection must be close to let new cursors work properly. see comments of
# close_sscursor().
connection.close()




django