sql - Django 中大表的内存效率(常数)和速度优化迭代

Question

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

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

我想在不断使用内存的情况下尽可能快地保持迭代。

因为它已经清楚地在 Limiting Memory Use in a *Large* Django QuerySet和 Why is iterating through a large Django QuerySet consuming massive amounts of memory? ，对 django 中所有对象的简单迭代将杀死机器，因为它将从数据库中检索所有对象。

寻求解决方案

首先，为了减少内存消耗，您应该确保 DEBUG 为 False(或猴子修补光标: turn off SQL logging while keeping settings.DEBUG? )以确保 django 不会在 connections 中存储内容用于调试。

但即便如此，

for model in Model.objects.all()

是不行的。

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

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

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

天真的解决方案

solution in the first question是根据 chunk_size 对基于计数器的结果进行切片.有几种写法，但基本上都归结为 OFFSET + LIMIT在 SQL 中查询。

就像是:

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(可能还有大多数数据库)将开始卡住并变慢。

更好的解决方案

this post 中提供了更好的解决方案蒂埃里·谢伦巴赫 (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()

这开始变得令人满意。现在内存 = O(C)，速度 ~= O(N)

“更好”解决方案的问题

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

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

我们能做得更好吗？

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

由于我对原始 SQL 非常糟糕，特别是在 Django 中，真正的问题来了:

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

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

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

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

PostgreSQL 中的服务器端游标并在 MySQL

暂时就这么多了……

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

更新 感谢评论中的“T”找到 django ticket带有一些附加信息。连接器行为的差异使得最好的解决方案可能是创建一个特定的 chunked方法而不是透明扩展 iterator (对我来说听起来是个好方法)。
一个实现 stub exists ，但一年没有任何作品，而且作者似乎还没有准备好跳进去。

其他引用:

Why does MYSQL higher LIMIT offset slow the query down?

How can I speed up a MySQL query with a large offset in the LIMIT clause?

http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

postgresql: offset + limit gets to be very slow

Improving OFFSET performance in PostgreSQL

http://www.depesz.com/2011/05/20/pagination-with-fixed-order/

How to get a row-by-row MySQL ResultSet in python MySQL中的服务器端游标

编辑:

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

Django Database Persistent Connections

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

此外，“更好的解决方案”绝对不适用于所有情况，不能用作通用方法，只能根据具体情况进行调整...

Answer 1

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

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

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

注意:这不是基于公共(public) API，因此它可能会在 future 的 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()