mysql - 更好地查询两个表上的 LEFT JOIN 并仅获得匹配的结果

Question

我有一个 order 表，它与两个连接表相关联:order_buyer 和 order_seller。

表结构如下:

CREATE TABLE IF NOT EXISTS `order` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    -- some fields...

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS order_seller (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT(10) UNSIGNED NOT NULL,
    seller_id INT(10) UNSIGNED NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    CONSTRAINT fk_order_seller_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE CASCADE,
    CONSTRAINT fk_order_seller_seller_id FOREIGN KEY (seller_id) REFERENCES user (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS order_buyer (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT(10) UNSIGNED NOT NULL,
    buyer_id INT(10) UNSIGNED NOT NULL,

    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    CONSTRAINT fk_order_buyer_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE CASCADE,
    CONSTRAINT fk_order_buyer_buyer_id FOREIGN KEY (buyer_id) REFERENCES user (id) ON DELETE CASCADE
);

如果我是查看我的订单历史记录的买家，我想获取所有 order_buyer.buyer_id 等于我的用户 ID 的订单。卖家也是如此。

但是我的查询是这样的，我不知道是买家还是卖家在查询；我只有一个用户 ID。所以我需要其中一个匹配，如果有一个匹配，那么我想提取该订单。

下面的查询不起作用，因为它给了我每一个订单:

SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
    ON order_seller.seller_id = 1
    AND order_seller.order_id = order.id
LEFT JOIN order_buyer
    ON order_buyer.buyer_id = 1
    AND order_buyer.order_id = order.id
WHERE order.status = "PENDING"

下面的查询通过从连接中删除 AND 并使其成为结果集的 WHERE IN 子句来解决上述问题:

SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
    ON order_seller.seller_id = 1
LEFT JOIN order_buyer
    ON order_buyer.buyer_id = 1
WHERE order.status = "PENDING"
    AND order.id IN (order_buyer.order_id, order_seller.order_id)

我的问题是，有没有更好的查询来实现同样的事情？这对我来说似乎有点脏。感觉不自然。

Answer 1

您可以将两个查询结合起来:

SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
    ON order_seller.seller_id = 1
    AND order_seller.order_id = order.id
LEFT JOIN order_buyer
    ON order_buyer.buyer_id = 1
    AND order_buyer.order_id = order.id
WHERE order.status = "PENDING"
    AND order.id IN (order_buyer.order_id, order_seller.order_id)

它应该比您的第二个查询更快，但返回相同的结果。

你也可以把最后一个条件改成

COALESCE(order_buyer.order_id, order_seller.order_id) IS NOT NULL

这与 Gordon 的第一个查询基本相同。

但是 - 这里的问题是引擎需要先读取所有挂单，然后才能按用户 ID 过滤它们。 EXISTS 子查询也会遇到同样的问题。

相反，我会使用 UNION ALL 查询:

SELECT `order`.*
FROM `order`
JOIN order_seller ON order_seller.order_id = order.id
WHERE order.status = "PENDING" AND order_seller.seller_id = 1
UNION ALL
SELECT `order`.*
FROM `order`
JOIN order_buyer ON order_buyer.order_id = order.id
WHERE order.status = "PENDING" AND order_buyer.buyer_id = 1

为避免过多的代码重复，您可以使用 UNION ALL 子查询:

SELECT o.*
FROM (
    SELECT order_id FROM order_seller WHERE seller_id = 1
    UNION ALL
    SELECT order_id FROM order_buyer WHERE buyer_id = 1
) x
JOIN `order` o ON o.id = x.order_id
WHERE o.status = "PENDING"

您至少需要一个关于order(status) 的索引。由于主键 (order.id) 是它的隐式部分，因此索引可以同时用于 JOIN-ON 和 WHERE 子句。对于其他表，order_seller(seller_id) 和 order_buyer(buyer_id) 上的索引可能没问题。但是 order_seller(seller_id, order_id) 和 order_buyer(buyer_id, order_id) 的复合索引会更好。