Doctrine 关系会影响应用程序的性能吗? [英] Are Doctrine relations affecting application performance?

问题描述

我正在与一个新团队合作一个 Symfony 项目，由于性能问题，他们决定尽可能停止使用 Doctrine 关系.

I am working on a Symfony project with a new team, and they decide to stop using Doctrine relations the most they can because of performances issues.

例如，我必须存储我的关系"的 ID，而不是使用 ManyToOne 关系.

For instance I have to stock the id of my "relation" instead of using a ManyToOne relation.

但我想知道这是否是一个真正的问题?

But I am wondering if it is a real problem?

问题是，它改变了检索信息等的编码方式.

The thing is, it changes the way of coding to retrieve information and so on.

推荐答案

性能问题很可能来自查询未优化的事实.

The performance issue most likely comes from the fact that queries are not optimised.

如果您让 Doctrine(处理查询的 Symfony 组件)自己执行查询(通过使用 findBy()、findAll()、findOneBy() 等)，它将首先获取您所询问的内容，然后进行更多查询，因为它需要来自其他表的数据.

If you let Doctrine (Symfony component that handle the queries) do the queries itself (by using findBy(), findAll(), findOneBy(), etc), it will first fetch what you asked, then do more query as it will require data from other tables.

让我们举个最常见的例子，一个库.

Lets take the most common example, a library.

实体

• 预订
• 作者
• 货架

关系

• 一个Book有一个Author，但是一个Author可以有很多Books(Book <= ManyToOne => 作者)
• 一个Book存储在一个Shelf中(Book <= OneToOne => Sheilf)

• One Book have one Author, but one Author can have many Books (Book <= ManyToOne => Author)
• One Book is stored in one Shelf (Book <= OneToOne => Sheilf)

现在，如果您查询 Book，Doctrine 还将获取 Shelf，因为它是 OneToOne 关系.
但它不会获取 Author.在您的对象中，您将只能访问 book.author.id，因为此信息在 Book 本身中.

Now if you query a Book, Doctrine will also fetch Shelf as it's a OneToOne relation.
But it won't fetch Author. In you object, you will only have access to book.author.id as this information is in the Book itself.

因此，如果在您的 Twig 视图中，您执行 {{ book.author.name }} 之类的操作，因为在初始查询中未获取信息，Doctrine 将添加一个额外的查询获取有关图书作者的数据.

Thus, if in your Twig view, you do something like {{ book.author.name }}, as the information wasn't fetched in the initial query, Doctrine will add an extra query to fetch data about the author of the book.

因此，为防止这种情况，您必须自定义查询，以便一次性获取所需数据，如下所示:

Thus, to prevent this, you have to customize your query so it get the required data in one go, like this:

public function getBookFullData(Book $book) {
    $qb=$this->createQueryBuilder('book');

    $qb->addSelect('shelf')
       ->addSelect('author')
       ->join('book.shelf', 'shelf')
       ->join('book.author', 'author');

    return $qb->getQuery()->getResult();
}

使用此自定义查询，您可以一次获取一本书的所有数据，因此，Doctrine 无需进行额外的查询.

With this custom query, you can get all the data of one book in one go, thus, Doctrine won't have to do an extra query.

所以，虽然这个例子比较简单，但我相信你可以理解，在大型项目中，放任 Doctrine 只会增加额外查询的数量.
我的一个项目，在优化之前，每页加载达到 1500 个查询...

So, while the example is rather simple, I'm sure you can understand that in big projects, letting free rein to Doctrine will just increase the number of extra query.
One of my project, before optimisation, reached 1500 queries per page loading...

另一方面，忽略数据库中的关系并不好.事实上，使用外键和索引的数据库比没有外键和索引的数据库要快.

On the other hand, it's not good to ignore relations in a database. In fact, a database is faster with foreign keys and indexes than without.

如果您希望您的应用程序尽可能快，您必须使用关系来优化您的数据库查询速度，并优化 Doctrine 查询以避免过多的额外查询.

If you want your app to be as fast as possible, you have to use relations to optimise your database query speed, and optimise Doctrine queries to avoid a foul number of extra queries.

最后，我会说顺序很重要.使用 ORDER BY 在 child 之前获取 parent 也将大大减少 Doctrine 可能自行执行的查询次数.

Last, I will say that order matter. Using ORDER BY to fetch parent before child will also greatly reduce the number of query Doctrine might do on it's own.

[旁注]

您还可以将实体注释上的 fetch 方法更改为优化" Doctrine 预制查询.

You can also change the fetch method on your entity annotation to "optimise" Doctrine pre-made queries.

• fetch="EXTRA_LAZY
• fetch="LAZY
• fetch="EAGER

但它并不聪明，而且通常不能真正提供我们真正需要的东西.
因此，自定义查询是最佳选择.

But it's not smart, and often don't really provide what we really need.
Thus, custom queries is the best choice.

这篇关于Doctrine 关系会影响应用程序的性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！