Symfony & Doctrine 2 / 加快查询速度

Question

我在连接 3 个表后查询速度有问题。之前速度是120-500ms。现在是1500-5000ms。可以加快我的查询速度吗？

这是我的查询生成器：

public function findByCategory($category)
{
    $qb = $this->createQueryBuilder('p');
    $qb->select('p');
    $qb->leftJoin('p.details', 'd');
    $qb->leftJoin('p.model', 'm');
    $qb->leftJoin('m.category', 'c');
    $qb->where('c.id = :category');
    $qb->andWhere('d.quantity > 0');
    $qb->setParameter('category', $category);
    $qb->addOrderBy('p.id', 'DESC');
    $qb->setMaxResults(10);
    return $qb->getQuery()->useQueryCache(true)->useResultCache(true, 31536000, uniqid())->getResult();
}

这是格式化查询：

SELECT 
  p0_.id AS id_0, 
  p0_.title AS title_1, 
  p0_.url AS url_2, 
  p0_.description AS description_3, 
  p0_.views AS views_4, 
  p0_.price AS price_5,  
  p0_.is_active AS is_active_6, 
  p0_.created_at AS created_at_7, 
  p0_.updated_at AS updated_at_8, 
  p0_.model_id AS model_id_9, 
  p0_.details_id AS details_id_10 
FROM 
  products p0_ 
  LEFT JOIN product_details p1_ ON p0_.details_id = p1_.id 
  LEFT JOIN models m2_ ON p0_.model_id = m2_.id 
  LEFT JOIN categories c3_ ON m2_.category_id = c3_.id 
WHERE 
  c3_.id = ? 
  AND p1_.`quantity` > 0 
ORDER BY 
  p0_.id DESC 
LIMIT 
  10

谢谢

编辑：产品型号：

/**
     * @var int
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @var string
     *
     * @ORMColumn(name="title", type="string", length=255)
     */
    private $title;
    /**
     * @var string
     *
     * @ORMColumn(name="url", type="string", length=255)
     */
    private $url;
    /**
     * @var string
     *
     * @ORMColumn(name="description", type="text", nullable=true)
     */
    private $description;
    /**
     * @var int
     *
     * @ORMColumn(name="views", type="bigint", nullable=true)
     */
    private $views;
    /**
     * @ORMManyToOne(targetEntity="Models")
     * @ORMJoinColumn(name="model_id", referencedColumnName="id")
     */
    private $model;
    /**
     * @ORMOneToOne(targetEntity="ProductDetails", cascade={"persist"})
     * @ORMJoinColumn(name="details_id", referencedColumnName="id")
     */
    private $details;
    /**
     * @var File
     *
     * @ORMOneToMany(targetEntity="ProductImages", mappedBy="product", cascade={"persist"})
     *
     */
    private $images;
    /**
     * @var File
     *
     * @ORMOneToMany(targetEntity="Cart", mappedBy="productId", cascade={"persist"})
     *
     */
    private $cart;
    /**
     * @var string
     *
     * @ORMColumn(name="price", type="integer", length=255)
     */
    private $price;
    /**
     * @var bool
     *
     * @ORMColumn(name="is_active", type="boolean")
     */
    private $isActive;
    /**
     * created Time/Date
     *
     * @var DateTime
     *
     * @ORMColumn(name="created_at", type="datetime", nullable=false)
     */
    protected $createdAt;
    /**
     * updated Time/Date
     *
     * @var DateTime
     *
     * @ORMColumn(name="updated_at", type="datetime", nullable=false)
     */
    protected $updatedAt;

产品详情型号：

/**
     * @var integer
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @var string
     *
     * @ORMColumn(name="`processor`", type="string", length=255, nullable=true)
     */
    private $processor;
    /**
     * @var string
     *
     * @ORMColumn(name="`graphic_card`", type="string", length=255, nullable=true)
     */
    private $graphicCard;
    /**
     * @var string
     *
     * @ORMColumn(name="`release_year`", type="string", length=4, nullable=true)
     */
    private $releaseYear;
    /**
     * @var string
     *
     * @ORMColumn(name="`ram`", type="string", length=255, nullable=true)
     */
    private $ram;
    /**
     * @var string
     *
     * @ORMColumn(name="`ssd`", type="string", length=255, nullable=true)
     */
    private $ssd;
    /**
     * @var string
     *
     * @ORMColumn(name="`hdd`", type="string", length=255, nullable=true)
     */
    private $hdd;
    /**
     * @var string
     *
     * @ORMColumn(name="`battery`", type="string", length=255, nullable=true)
     */
    private $battery;
    /**
     * @var string
     *
     * @ORMColumn(name="`color`", type="string", length=255, nullable=true)
     */
    private $color;
    /**
     * @var string
     *
     * @ORMColumn(name="`accessories`", type="string", length=255, nullable=true)
     */
    private $accessories;
    /**
     * @var string
     *
     * @ORMColumn(name="`guarantee`", type="string", length=255, nullable=true)
     */
    private $guarantee;
    /**
     * @var string
     *
     * @ORMColumn(name="`condition`", type="string", length=255)
     */
    private $condition;
    /**
     * @var string
     *
     * @ORMColumn(name="`quantity`", type="string", length=255)
     */
    private $quantity;

型号

型号：

/**
     * @var integer
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @ORMManyToOne(targetEntity="Categories")
     * @ORMJoinColumn(name="category_id", referencedColumnName="id")
     */
    private $category;
    /**
     * @var string
     *
     * @ORMColumn(name="name", type="string", length=255)
     */
    private $name;
    /**
     * @var string
     *
     * @ORMColumn(name="url", type="string", length=255)
     */
    private $url;
    /**
     * @var string
     *
     * @ORMColumn(name="image", type="string", length=255)
     */
    private $image;

分类目录 型号：

/**
     * @var int
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @var string
     *
     * @ORMColumn(name="title", type="string", length=50)
     */
    private $title;
    /**
     * @var string
     *
     * @ORMColumn(name="url", type="string", length=255)
     */
    private $url;
    /**
     * @var string
     *
     * @ORMColumn(name="description", type="string", length=140)
     */
    private $description;
    /**
     * @var string
     *
     * @ORMColumn(name="image", type="string", length=100)
     */
    private $image;
    /**
     * @var bool
     *
     * @ORMColumn(name="is_active", type="boolean")
     */
    private $isActive;
    /**
     * created Time/Date
     *
     * @var DateTime
     *
     * @ORMColumn(name="created_at", type="datetime", nullable=false)
     */
    protected $createdAt;
    /**
     * updated Time/Date
     *
     * @var DateTime
     *
     * @ORMColumn(name="updated_at", type="datetime", nullable=false)
     */
    protected $updatedAt;

Answer 1

一个联接不是必需的。您加入类别表只是为了过滤 id，但您可以使用 IDENTITY 执行此操作，这对于引用 FK 列非常有帮助。您的查询应该像这样更快：

public function findByCategory($category)
{
    $qb = $this->createQueryBuilder('p');
    $qb->select('p');
    $qb->leftJoin('p.details', 'd');
    $qb->leftJoin('p.model', 'm');
   // $qb->leftJoin('m.category', 'c'); // this don't go anymore
    $qb->where('IDENTITY(m.category) = :category');
    $qb->andWhere('d.quantity > 0');
    $qb->setParameter('category', $category);
    $qb->addOrderBy('p.id', 'DESC');
    $qb->setMaxResults(10);
    return $qb->getQuery()->useQueryCache(true)->useResultCache(true, 31536000, uniqid())->getResult();
}