Doctrine 在水合期间添加了额外的查询，导致“正常"的 n+1 问题.一对一和自引用关系

2022-01-16 00:00:00 php symfony doctrine doctrine-orm select-n-plus-1

新闻通过一对多的自引用方式相互关联(一个新闻为父，可以有多个子).更重要的是，每个 News 与 Event 和 Gallery 都有正常的(非自引用)一对一关系.当我运行简单的 DQL 时:

News are related to each other using one-to-many self-referencing approach (one news is parent and can have many children). What's more each News has got normal (non self-referenced) one to one relation with Event and Gallery . When I run simple DQL:

SELECT n FROM AppEntityNews n WHERE n.parent = :id

然后通过 getResults 方法和默认的 HYDRATION_OBJECT 值集水合结果，额外的查询在 getResults 方法中的某处进行.

and then hydrate results by getResults method with default HYDRATION_OBJECT value set, extra queries are made somewhere inside getResults method. 

SELECT t0.* FROM event t0 WHERE t0.news_id = 2 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM gallery t0 WHERE t0.news_id = 2 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM event t0 WHERE t0.news_id = 1 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM gallery t0 WHERE t0.news_id = 1 AND ((t0.deleted_at IS NULL));

其中 news_id = 1 和 news_id = 2 是第一次查询选择的新闻的子代.

Where news_id = 1 and news_id = 2 are children of news selected by first query.

News 也没有自引用的一对多关系(我在这里忽略了它们)，但是水合不会对它们进行额外的查询.仅当 where 语句中涉及 parent 关系时，才会出现此问题.

News has got also not self-referenced one to many relations (I ignored them here), but hydration make no extra queries about them. The problem occurs only when there is parent relation involved in where statement.

如何重现 

// news Entity
/**
 * @ORMEntity(repositoryClass="AppRepositoryNewsRepository")
 * @ORMTable(uniqueConstraints={@UniqueConstraint(name="news_slug_deleted", columns={"slug","deleted_at"})})
 */
class News {
    use SoftDeleteableEntity;

    /**
     * @ORMId()
     * @ORMGeneratedValue()
     * @ORMColumn(type="integer")
     */
    private $id;
    /**
     * @ORMColumn(type="string", length=255)
     */
    private $title;

    /**
     * @GedmoSlug(fields={"title"})
     * @ORMColumn(length=128)
     */
    private $slug;
/**
     * @ORMOneToOne(targetEntity="AppEntityGallery", mappedBy="news", cascade={"persist", "remove"}, orphanRemoval=true)
     *
     * @var Gallery
     */
    private $gallery;

    /**
     * @ORMOneToOne(targetEntity="AppEntityEvent", mappedBy="news", cascade={"persist", "remove"}, orphanRemoval=true)
     *
     * @var Event
     */
    private $event;
    /**
     * One News has Many News.
     * @ORMOneToMany(targetEntity="News", mappedBy="parent")
     */
    private $children;

    /**
     * Many News have One News.
     * @ORMManyToOne(targetEntity="News", inversedBy="children")
     * @ORMJoinColumn(name="parent_id", referencedColumnName="id", nullable=true)
     */
    private $parent;
}


/**
 * @ORMEntity(repositoryClass="AppRepositoryEventRepository")
 * @GedmoSoftDeleteable()
 */
class Event
{
    use SoftDeleteableEntity;

    /**
     * @ORMId()
     * @ORMGeneratedValue()
     * @ORMColumn(type="integer")
     */
    private $id;
    /**
     * @ORMOneToOne(targetEntity="AppEntityNews", inversedBy="event", cascade={"persist"})
     * @ORMJoinColumn(nullable=false)
     */
    private $news;

Gallery 实体类似于 Event 所以我在这里忽略它.

Gallery entity is simmilar to Event so I ignored it here.

// News controller

public function index(NewsRepository $newsRepository, $slug)
{
        $news = $newsRepository->findOneBy(['slug' => $slug]);
        $newsRepository->getConnectedNews($news->getId());
}


// news repository

class NewsRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, News::class);
    }
    public function getConnectedNews($newsId) {
        $query = $this->createQueryBuilder('n')->andWhere('n.parent = :id')->setParameter('id', $newsId);
        return $query->getQuery()->getResult(AbstractQuery::HYDRATE_OBJECT);
    }
}

有 20 个孩子的 news 水化最终得到:20 * 2 + 1 (n*r+1) 个查询，其中:

Hydration of news which has got 20 children ends up with: 20 * 2 + 1 (n*r+1) queries, where:

  • (n) 20 是孩子的数量
  • (r) 2 是一对一关系的个数
  • 1 是基本查询

我想防止对 Doctrine 进行的一对一关系的额外查询.是 Doctrine 错误还是不受欢迎的行为，还是我在某个地方犯了错误?

I want to prevent that extra queries to one to one relations made by Doctrine. Is it Doctrine bug or unwanted behaviour or I made a mistake somewhere?

总而言之，我只想获取所有没有一对一关系的自引用子项，因为我没有要求检索它们，所以它应该只使用一个查询来获取所有子项news 无需对每个检索到的新闻"对象进行额外查询，并且每个对象都是一对一的关系.

All in all I want to just get all self-referenced children without one-to-one relations as I didn't ask to retrieve them so it should use just one query to get all children news without making additional queries for each retrieved 'news' object and each it's one to one relation.

推荐答案

您在该实体中有一对反向 OneToOne 关系.

You have a couple inverse OneToOne relationships in that entity.

逆 OneToOne 关系不能被 Doctrine 延迟加载，并且很容易成为性能问题.

Inverse OneToOne relationships cannot be lazy-loaded by Doctrine, and can easily become a performance problem.

如果您真的需要将这些关系映射到 inverse 端(而不仅仅是在拥有端)，请确保明确地进行适当的连接，或标记这些关联为 FETCH=EAGER 以便 Doctrine 为您进行连接.

If you really need to have those relationships mapped on the inverse side (and not only on the owning side) make sure to make the appropriate joins explicitly, or mark those associations as FETCH=EAGER so that Doctrine makes the joins for you.

例如一个可以避免可怕的n+1"的查询.问题是:

E.g. a query that would avoid the dreaded "n+1" problem would be:

SELECT n, g, e
    FROM AppEntityNews n
    LEFT JOIN n.gallery g
    LEFT JOIN n.event e
WHERE n.parent = :id

您可以在 这里或 阅读更多关于 Doctrine 中的 N+1 问题的信息a href="https://tideways.com/profiler/blog/5-doctrine-orm-performance-traps-you-should-avoid" rel="nofollow noreferrer">这里.

相关文章