使用Doctrine 2 DQL查询海量联接数据的最有效方法 [英] What’s most efficient way to query a LEFT JOIN with tons of data with Doctrine 2 DQL

问题描述

我有一个与 oneToMany价格挂钩的实体硬币。价格每分钟更新一次，有数百万个条目。

I have an entity "coin" linked with a oneToMany on prices. Prices is updated every minute and has millions of entries.

我要实现的是一个DQL查询，它将获取最新的价格。

What I’m trying to achieve is a DQL query that will grab the last price to date.

现在我看到2种方法，而且我想知道哪种方法在性能方面最好：

Right now I see 2 ways to do it and I’m wondering which one is best in term of performance:

I可以在数据库中查找带有 prices的价格。 lastupdated等于最后一次更新。

I could look for prices in the DB with ‘prices’.’lastupdated’ equal to the last update.

或者我可以获取最近的100个价格ID（我们更新了100个硬币每分钟，并在数据库中添加100个新行），并在我的左联接上添加一个LIMIT 100和ORDER BY'id'DESC。
我知道在带有理论的左联接上使用LIMIT是很棘手的，但是我在这里找到了解决方案：
如何使用Doctrine（DQL）限制左联接的结果
，并在此处： https://www.colinodell.com/blog/201703/limiting-subqueries-doctrine-2 -dql

OR I could grab the last 100 price ids (we update 100 coins every minute and add 100 new rows in the Database) with a LIMIT 100 and ORDER BY ‘id’ DESC on my left join. I know it’s tricky to use LIMIT on a LEFT JOIN with doctrine but I found a solution here: How to limit results of a left-join with Doctrine (DQL) and here: https://www.colinodell.com/blog/201703/limiting-subqueries-doctrine-2-dql

我想知道什么将花费最少的资源来执行该查询。

I’m wondering what will take the least amount of ressources to execute that query.

我当然使用的是getArrayResults（），并且使用的是局部和原则缓存。

Of course I’m using getArrayResults() and am using partials and doctrine cache.

您对此有何看法？
谢谢！

What is your opinion on that? Thanks!

推荐答案

我一直在优化我的Doctrine请求，并获得了一些很棒的性能改进

I have been working on optimizing a my Doctrine request and got some awesome perf improvement I’ll be sharing here if anyone is looking at a similar solution.

首先，尽可能使用where子句限制左连接
其次，使用部分对象
第三，使用数组结果。

First, limit your left join with a where clause as much as possible Second, use partial objets Third, use Array results. This actually changes everything.

/**
 * @return Coins[] Returns an array of Crypto objects
 */

public function findOneByTickerRelationnal($ticker)
{
    $em = $this->getEntityManager();
    $updatesrepository = $em->getRepository(Updates::class);
    $updates = $updatesrepository->findOneBy(['id'=> 1 ]);

    // This is where I’ve been doing additional work to limit my left join as much as possible with a ‘with’ on left join
    $recentMarkets = $updates->getMarket();
    $recentPrices = $updates->getPrice();
    $recentSources = $updates->getSources();

    $cryptos = $this->createQueryBuilder('c')
        ->select('partial c.{id, name, ticker}’) //<= use of partial is a plus but you need to know exactly which fields you want
        ->leftJoin('c.prices', 'p','WITH', 'p.last_updated >= :recentPrices')
        ->addSelect('partial p.{id, price_usd, daily_volume_usd, change_1h, change_1d, change_7d, rank}')
        ->leftJoin('c.markets', 'm','WITH', 'm.last_updated >= :recentMarkets')
        ->addSelect('partial m.{id, cur_supply, market_cap, max_supply}')
        ->leftJoin('c.sources', 's','WITH', 's.last_updated >= :recentSources')
        ->addSelect('s')
        ->where('c.ticker = :ticker')
        ->setParameter('recentPrices', $recentPrices)
        ->setParameter('recentMarkets', $recentMarkets)
        ->setParameter('recentSources', $recentSources)
        ->setParameter('ticker', $ticker)
        ->getQuery()
        ->getArrayResult(); //<=Changes everything 

    $results = $cryptos[0];

    return $results;
}

这篇关于使用Doctrine 2 DQL查询海量联接数据的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！