使用大型 IN() 子句或派生表联接优化 MySQL 查询

Question

假设我需要查询一家公司的员工。我有一个表“交易”，其中包含每笔交易的数据。

CREATE TABLE `transactions` (
  `transactionID` int(11) unsigned NOT NULL,
  `orderID` int(11) unsigned NOT NULL,
  `customerID` int(11) unsigned NOT NULL,
  `employeeID` int(11) unsigned NOT NULL, 
  `corporationID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`transactionID`),
  KEY `orderID` (`orderID`),
  KEY `customerID` (`customerID`),
  KEY `employeeID` (`employeeID`),
  KEY `corporationID` (`corporationID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

查询此表中的员工相当简单，但有一个问题：每个员工只注册一次交易记录，因此一个公司的每个订单可能有多个记录。

例如，如果公司 1 的员工 A 和 B 都参与向公司 2 销售吸尘器，则“交易”表中将有两条记录：每位员工一个，公司 1 两个。不过，这不能影响结果。公司 1 的一项交易，无论涉及多少名员工，都必须被视为一项交易。

很容易，我想。我将只在派生表上进行联接，如下所示：

SELECT corporationID FROM transactions JOIN (SELECT DISTINCT orderID FROM transactions WHERE corporationID = 1) AS foo USING (orderID)

查询返回与公司 1 进行交易的公司列表。这正是我所需要的，但速度非常慢，因为 MySQL 无法使用corporationID索引来确定派生表。据我所知，MySQL 中的所有子查询/派生表都是这种情况。

我还尝试单独查询 orderID 的集合，并使用一个大得离谱的 IN() 子句（通常超过 100 000 个 ID），但事实证明 MySQL 在使用大得离谱的 IN() 子句上的索引时也存在问题结果查询时间并没有改善。

还有其他可用的选择吗，还是我已经用尽了它们？

Answer 1

如果我理解你的要求，你可以尝试这个。

select distinct t1.corporationID
from transactions t1
where exists (
    select 1
    from transactions t2
    where t2.corporationID =  1
    and t2.orderID = t1.orderID)
and t1.corporationID != 1;

或者这个：

select distinct t1.corporationID
from transactions t1
join transactions t2
on t2.orderID = t1.orderID
and t1.transactionID != t2.transactionID
where t2.corporationID = 1
and t1.corporationID != 1;

Answer 2

你的数据对我来说没有意义，我认为你正在使用corporationID，你的意思是客户ID在其中的某个点，因为你的查询将交易表连接到基于orderID的corporateID = 1的交易表以获取corporateID...那么就是 1，对吗？

您能具体说明一下 customerID、employeeID 和 CorporateID 的含义吗？我如何知道员工 A 和 B 来自公司 1 - 在这种情况下，公司 1 是公司 ID，公司 2 是客户，因此存储在客户 ID 中？

如果是这种情况，您只需要执行分组依据：（

SELECT customerID
FROM transactions
WHERE corporationID = 1
GROUP BY customerID

或者如果您想要每个订单一行而不是每个客户一行，则选择并按 orderID 分组。）

通过使用分组依据，您忽略了以下事实：除员工 ID 外，多条重复记录。

相反，返回所有已出售给公司 2 的公司。

SELECT corporationID
FROM transactions
WHERE customerID = 2
GROUP BY corporationID