python - 使用model_to_dict时如何避免N+1查询

Question

我试图避免 N+1 问题，如http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem中所述，但仍然执行其他查询.

我的模型：

from peewee import (SqliteDatabase, Model, BigAutoField, CharField, ForeignKeyField)

db = SqliteDatabase(':memory:')


class TestModel(Model):
    class Meta:
        database = db
        legacy_table_names = False


class TestUser(TestModel):
    id = BigAutoField(primary_key=True)
    name = CharField()


class Book(TestModel):
    id = BigAutoField(primary_key=True)
    name = CharField()
    user = ForeignKeyField(TestUser, backref='books')


class Movie(TestModel):
    id = BigAutoField(primary_key=True)
    name = CharField()
    user = ForeignKeyField(TestUser, backref='movies')


class Tape(TestModel):
    id = BigAutoField(primary_key=True)
    name = CharField()
    user = ForeignKeyField(TestUser, backref='tapes')

我的测试：

from peewee import JOIN
from playhouse.shortcuts import model_to_dict
from playhouse.test_utils import count_queries

from test_n_plus_1l import *


def test_should_avoid_n_plus_one_problem():
    db.create_tables([TestUser, Book, Movie, Tape])

    tu = TestUser.create(name='Test')
    Book.create(name='Book1', user_id=tu.id)
    Movie.create(name='Movie1', user_id=tu.id)
    Tape.create(name='Tape1', user_id=tu.id)

    with count_queries() as counter:
        tu = TestUser.select(TestUser, Book, Movie, Tape) \
            .join_from(TestUser, Book, JOIN.LEFT_OUTER) \
            .join_from(TestUser, Movie, JOIN.LEFT_OUTER) \
            .join_from(TestUser, Tape, JOIN.LEFT_OUTER) \
            .where(TestUser.id == tu.id).get()
        model_to_dict(tu, backrefs=True, manytomany=True, max_depth=4)

    assert counter.count == 1

运行它后，我收到断言错误：

E assert 4 == 1

Peewee 打印了执行的 sql，所以我清楚地看到了 join 被执行了，但是为什么 peewee 执行了额外的查询：

('SELECT "t1"."id", "t1"."name", "t2"."id", "t2"."name", "t2"."user_id", "t3"."id", "t3"."name", "t3"."user_id", "t4"."id", "t4"."name", "t4"."user_id" FROM "test_user" AS "t1" LEFT OUTER JOIN "book" AS "t2" ON ("t2"."user_id" = "t1"."id") LEFT OUTER JOIN "movie" AS "t3" ON ("t3"."user_id" = "t1"."id") LEFT OUTER JOIN "tape" AS "t4" ON ("t4"."user_id" = "t1"."id") WHERE ("t1"."id" = ?) LIMIT ? OFFSET ?', [1, 1, 0])
('SELECT "t1"."id", "t1"."name", "t1"."user_id" FROM "book" AS "t1" WHERE ("t1"."user_id" = ?)', [1])
('SELECT "t1"."id", "t1"."name", "t1"."user_id" FROM "movie" AS "t1" WHERE ("t1"."user_id" = ?)', [1])
('SELECT "t1"."id", "t1"."name", "t1"."user_id" FROM "tape" AS "t1" WHERE ("t1"."user_id" = ?)', [1])

标签： pythonpeewee

Answer 1

问题是每个 TestUser 可能有任意数量的相关书籍、电影或磁带。因此，这是一个您可能受益于使用prefetch()帮助程序的实例 - 因为外键的方向。也就是说，您最好进行分析，因为prefetch()可能不会比仅执行查询更快。

这是使用prefetch()的示例。请注意每个用户如何拥有多个 Tweets 和 Notes，并且每个 Tweet 都有多个 Flags——但我们总共只执行了 4 个查询：

class User(Base):
    name = TextField()

class Tweet(Base):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()

class TweetFlag(Base):
    tweet = ForeignKeyField(Tweet, backref='tweet_flags')
    flag_type = IntegerField()

class Note(Base):
    user = ForeignKeyField(User, backref='notes')
    content = TextField()

db.create_tables([User, Tweet, TweetFlag, Note])

for i in range(10):
    user = User.create(name='user-%s' % i)
    for t in range(10):
        tweet = Tweet.create(user=user, content='%s/tweet-%s' % (user.name, t))
        for f in range(3):
            TweetFlag.create(tweet=tweet, flag_type=f)

    for n in range(5):
        Note.create(user=user, content='%s/note-%s' % (user.name, n))

from playhouse.shortcuts import model_to_dict
from playhouse.test_utils import count_queries


with count_queries() as counter:
    q = User.select().order_by(User.name)
    p = prefetch(q, Tweet, TweetFlag, Note)

    accum = []
    for res in p:
        accum.append(model_to_dict(res, backrefs=True))


print(counter.count)
print(accum[0])

输出：

4
{'id': 1,                                
 'name': 'user-0',   
 'notes': [{'content': 'user-0/note-0', 'id': 1},       
           {'content': 'user-0/note-1', 'id': 2},       
           {'content': 'user-0/note-2', 'id': 3},                                                              
           {'content': 'user-0/note-3', 'id': 4},
           {'content': 'user-0/note-4', 'id': 5}],
 'tweets': [{'content': 'user-0/tweet-0',               
             'id': 1,                                   
             'tweet_flags': [{'flag_type': 0, 'id': 1},                                                        
                             {'flag_type': 1, 'id': 2},
                             {'flag_type': 2, 'id': 3}]},

...