php - Doctrine Paginator 选择整个表格(非常慢)？

Question

这与此处的前一个问题有关:Doctrine/Symfony query builder add select on left join

我想使用 Doctrine ORM 执行复杂的连接查询。我想选择 10 篇分页的博客文章，留下一个作者，比如当前用户的值(value)，以及帖子上的主题标签。我的查询构建器如下所示:

$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 遍历 $result 输出 10 个 Post 实体，正如预期的那样。我需要做一些额外的配置来正确限制我的分页器吗？

如果这是分页器类的限制，我还有哪些其他选择？编写自定义分页器代码或其他分页器库？

(奖励):我如何混合数组，如 $query->getQuery()->getArrayResult();？

编辑:我在我的函数中遗漏了一个杂散的 orderBy。看起来同时包含 groupBy 和 orderBy 会导致速度变慢(使用 groupBy 而不是分页器)。如果我省略其中一个，查询就会很快。我尝试在我的表中的“时间”列上添加索引，但没有看到任何改进。

我尝试过的事情

// 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

Answer 1

您应该简化您的查询。这将节省一些执行时间。我无法测试您的查询，但这里有一些提示:

• 不要在执行 count() 时进行排序
• 您可以按orderBy('p.id', 'DESC') 排序，将使用索引
• 如果连接表中始终存在至少一条记录，则可以使用 join() 而不是 leftJoin()。否则跳过该记录。
• KNP/Paginator 使用 DISTINCT() 只读取不同的记录，但这可能会导致使用磁盘 tmp 表
• $query->getArrayResult()使用数组隐藏方式，返回多维数组，比大结果集对象隐藏要快得多
• 您可以使用部分select('partial p.{id, other used fields}')，这样您将只加载需要的字段，在使用对象水合时可能会跳过不需要的关系
• 检查 SF profiler EXPLAIN 在 doctrine 部分下的给定查询，可能没有使用索引
• p.hashtags 和 p.likes 是只返回一行还是 oneToMany，结果相乘
• 可能会更改一些 Posts 设计，这会删除一些连接:
  • 将 p.hashtags 字段定义为 @ORM\Column(type="array") 并存储标签的字符串值。稍后可能会在序列化数组上使用全文搜索。
  • 将 p.likesCount 字段定义为 @ORM\Column(type="integer")，这将有喜欢的计数

我使用 KnpLabs/KnpPaginatorBundle并且对于复杂的查询也可能存在速度问题。

通常使用 LIMIT x,z 对于 DB 来说很慢，因为它在整个数据集上运行 COUNT。如果不使用索引，速度会非常慢。

您可以使用不同的方法并通过 ID 前进进行一些自定义分页，但这会使您的方法复杂化。我已经将其用于 SYSLOG 表等大型数据集。但是您失去了排序和总记录计数功能。