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

Question

假设我需要查询一家公司的同事。我有一个名为"transactions“的表，其中包含每个事务的数据。

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中的所有子查询/派生表都是这种情况。

我还尝试分别查询一组000+ it，并使用一个大得离谱的IN()子句(类型为100个orderID)，但事实证明，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，因为您的查询基于orderID将transaction表连接到corporationID=1的transaction表，从而获得corporationIDs...which为1，对吧？

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

如果是这样的话，你只需要做一个分组：

SELECT customerID
FROM transactions
WHERE corporationID = 1
GROUP BY customerID

(或者，如果希望每个订单一行，而不是每个客户一行，请选择并按orderID分组。)

通过使用group by，您忽略了除了employeeID之外还有多个记录是重复的这一事实。

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

SELECT corporationID
FROM transactions
WHERE customerID = 2
GROUP BY corporationID