原则派生选择整个表（很慢）？ [英] Doctrine Paginator selects entire table (very slow)?

问题描述

这与以前的问题有关： Doctrine / Symfony查询构建器在左侧连接中添加选择

This is related to a previous question here: Doctrine/Symfony query builder add select on left join

我想使用Doctrine ORM执行复杂的连接查询。我想选择10个分页的博客帖子，左边加入一个作者，像当前用户的价值，和标题的帖子。我的查询生成器如下所示：

I want to perform a complex join query using Doctrine ORM. I want to select 10 paginated blog posts, left joining a single author, like value for current user, and hashtags on the post. My query builder looks like this:

$query = $em->createQueryBuilder()
            ->select('p')              
            ->from('Post', 'p')
            ->leftJoin('p.author', 'a')
            ->leftJoin('p.hashtags', 'h')
            ->leftJoin('p.likes', 'l', 'WITH', 'l.post_id = p.id AND l.user_id = 10')
            ->where("p.foo = bar")
            ->addSelect('a AS post_author')
            ->addSelect('l AS post_liked')
            ->addSelect('h AS post_hashtags')
            ->orderBy('p.time', 'DESC')
            ->setFirstResult(0)
            ->setMaxResults(10);

// FAILS - because left joined hashtag collection breaks LIMITS
$result = $query->getQuery()->getResult(); 

// WORKS - but is extremely slow (count($result) shows over 80,000 rows)
$result = new \Doctrine\ORM\Tools\Pagination\Paginator($query, true);

奇怪的是，分页符上的count（$ result）显示了我表中的总行数80,000），但是通过foreach遍历$结果，输出10个Post实体，如预期的那样。我需要做一些额外的配置来正确地限制我的分页符吗？

Strangely, count($result) on the paginator shows the total number of rows in my table (over 80,000) but traversing the $result with foreach outputs 10 Post entities, as expected. Do I need to do some additional configuration to properly limit my paginator?

如果这是分页类的一个限制，我有什么其他选项？编写自定义分页代码或其他分页程序库？

If this is a limitation of the paginator class what other options do I have? Writing custom paginator code or other paginator libraries?

（加值）：如何水平数组，如$ query-> getQuery（） - > getArrayResult（）;？

(bonus): How can I hydrate an array, like $query->getQuery()->getArrayResult();?

编辑：我在我的功能中省略了一个流氓订单。它看起来像包括groupBy和orderBy导致减速（使用groupBy而不是分页符）。如果我省略一个或另一个，查询很快。我尝试在表中的时间列添加一个索引，但没有看到任何改进。

I left out a stray orderBy in my function. It looks like including both groupBy and orderBy causes the slowdown (using groupBy rather than the paginator). If I omit one or the other, the query is fast. I tried adding an index on the "time" column in my table, but didn't see any improvement.

我试过的东西

// works, but makes the query about 50x slower
$query->groupBy('p.id');
$result = $query->getQuery()->getArrayResult();

// adding an index on the time column (no improvement)
indexes:
    time_idx:
        columns: [ time ]

// the above two solutions don't work because MySQL ORDER BY
// ignores indexes if GROUP BY is used on a different column
// e.g. "ORDER BY p.time GROUP BY p.id is" slow

推荐答案

在一天结束时，我应用程序中使用的许多查询太复杂，无法正确使用Paginator，并且我无法使用Paginator使用阵列水合模式。

At the end of the day, many of the queries used in my application are too complex to make proper use of the Paginator, and I wasn't able to use array hydration mode with the Paginator.

根据 MySQL文档，如果GROUP BY在不同的列上使用，则ORDER BY无法由索引解析。因此，我最终使用几个后处理查询来填充我的基础结果（ORDERed和LIMITed），一对多关系（如标签）。

According to MySQL documentation, ORDER BY cannot be resolved by indexes if GROUP BY is used on a different column. Thus, I ended up using a couple post-processing queries to populate my base results (ORDERed and LIMITed) with one-to-many relations (like hashtags).

连接从连接的表中加载单个行，我能够在基本有序查询中加入所需的值。例如，当加载当前用户的类似状态时，仅需要加载一些喜欢的组合，以指示当前帖子是否已被喜欢。类似地，一个给定的帖子只有一个作者的存在产生一个连接的作者行。例如

For joins that load a single row from the joined table, I was able to join the desired values in the base ordered query. For example, when loading the "like status" for a current user, only one like from the set of likes needs to be loaded to indicate whether or not the current post has been liked. Similarly, the presence of only one author for a given post produces a single joined author row. e.g.

$query = $em->createQueryBuilder()
        ->select('p')              
        ->from('Post', 'p')
        ->leftJoin('p.author', 'a')
        ->leftJoin('p.likes', 'l', 'WITH', 'l.post_id = p.id AND l.user_id = 10')
        ->where("p.foo = bar")
        ->addSelect('a AS post_author')
        ->addSelect('l AS post_liked')
        ->orderBy('p.time', 'DESC')
        ->setFirstResult(0)
        ->setMaxResults(10);

// SUCCEEDS - because joins only join a single author and single like
// no collections are joined, so LIMIT applies only the the posts, as intended
$result = $query->getQuery()->getArrayResult(); 

这将以以下格式生成结果：

This produces a result in the form:

[
  [0] => [
    ['id'] => 1
    ['text'] => 'foo',
    ['author'] => [
       ['id'] => 10,
       ['username'] => 'username',
    ],
    ['likes'] => [
       [0] => [
         ['post_id'] => 1,
         ['user_id'] => 10,
       ]
    ],
  ], 
  [1] => [...],
  ...
  [9] => [...]
]

然后在第二个查询我加载邮件的标签加载在上一个查询。例如

Then in a second query I load the hashtags for posts loaded in the previous query. e.g.

// we don't care about orders or limits here, we just want all the hashtags
$query = $em->createQueryBuilder()
        ->select('p, h')              
        ->from('Post', 'p')
        ->leftJoin('p.hashtags', 'h')
        ->where("p.id IN :post_ids")
        ->setParameter('post_ids', $pids);

其中产生以下内容：

[
  [0] => [
    ['id'] => 1
    ['text'] => 'foo',
    ['hashtags'] => [
       [0] => [
         ['id'] => 1,
         ['name'] => '#foo',
       ],
       [2] => [
         ['id'] => 2,
         ['name'] => '#bar',
       ],
       ...
    ],
  ], 
  ...
]

然后我只是遍历包含主题标签的结果，并将其附加到原始（有序和限制）结果。这种方法的结果要快得多（尽管它使用更多的查询），因为它避免了GROUP BY和COUNT，充分利用MySQL索引，并允许更复杂的查询，例如我发布的一个此处。

Then I just traverse the results containing hashtags and append them to the original (ordered and limited) results. This approach ends up being much faster (even though it uses more queries), as it avoids GROUP BY and COUNT, fully leverages MySQL indexes, and allows for more complex queries, such as the one I posted here.

这篇关于原则派生选择整个表（很慢）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！