我想使用来自不同表的联接来获取一个产品信息。我有三个额外的表格(评论、线程、奖励),我想检查是否存在与该特定产品相关的记录。如果它们存在,则返回一个非null值,否则为null。将来可能会向查询中添加更多此类检查。
如果记录存在,您更喜欢哪个查询进行性能测试?
使用具有多个子查询的exists
:
$sql = "SELECT p.product_id,p.name,m.model,m.model_id,b.brand,me.merchant,
EXISTS(SELECT 1 FROM review WHERE product_id = :id) AS has_review,
EXISTS(SELECT 1 FROM thread WHERE product_id = :id) AS has_thread,
EXISTS(SELECT 1 FROM award WHERE product_id = :id) AS has_award
FROM product p
INNER JOIN model m ON m.model_id = p.model_id
INNER JOIN brand b ON b.brand_id = m.brand_id
INNER JOIN merchant me ON me.merchant_id = m.merchant_id
WHERE p.product_id = :id
LIMIT 1";
$dbh->prepare($sql);
使用多个左联接:
$sql = "SELECT p.product_id,p.name,m.model,m.model_id,b.brand,me.merchant,
(t.product_id is not null) AS has_thread,
(r.product_id is not null) AS has_review,
(a.product_id is not null) AS has_award
FROM product p
INNER JOIN model m ON m.model_id = p.model_id
INNER JOIN brand b ON b.brand_id = m.brand_id
INNER JOIN merchant me ON me.merchant_id = m.merchant_id
LEFT JOIN review r ON re.product_id = p.product_id
LEFT JOIN thread t ON t.product_id = p.product_id
LEFT JOIN award a ON a.product_id = a.product_id
WHERE p.product_id = :id
LIMIT 1";
第一个更可取。
为了提高性能,对于任一版本,都需要review(product_id)
、thread(product_id)
和award(product_id)
上的索引。
为什么使用EXISTS
更好?如果三个表中不存在匹配的行,那么这两个版本应该是等效的(减去第二个查询中最后一个on
子句中的拼写错误)。然而,当确实存在行时,第二个版本将创建这些行的笛卡尔乘积,从而丢弃结果和性能。
注意:我倾向于使用相关的子查询来编写EXISTS
子句,因此该参数只被引用一次:
EXISTS (SELECT 1 FROM review r WHERE r.product_id = p.product_id) AS has_review,
EXISTS (SELECT 1 FROM thread t WHERE t.product_id = p.product_id) AS has_thread,
EXISTS (SELECT 1 FROM award a WHERE a.product_id = p.product_id) AS has_award,