如何在多个自联接上指定排序顺序

Question

我有一个属性表

CREATE TABLE attributes (
    attribute_id INT,
    product_id INT,
    random INT,
    UNIQUE KEY (attribute_id,random,product_id),
    KEY (product_id)
);

random是在insert上计算出来的随机整数，用于洗牌产品(这对我的需要是可以的)。有一些自连接查询，如

SELECT DISTINCT x.product_id
FROM attibutes x
INNER JOIN attributes y ON x.product_id=y.product_id
INNER JOIN attributes z ON x.product_id=z.product_id
WHERE x.attribute_id IN (20000085,20000090) AND
    y.attribute_id IN (10000007) AND
    z.attribute_id IN (30000050,30000040,30000012)
LIMIT 0,100;

如您所见，我希望选择在每个数字范围内至少有一个属性的产品。MySQL非常聪明地为第一个查询本身选择了表别名，这取决于惟一键的选择性。正如预期的那样，由于键的唯一性，结果将按列random的顺序排序。但是，我如何建议MySQL恢复订单呢？在添加ORDER BY x.random DESC时，可能会发生MySQL使用文件短进行排序的情况，因为如果它使用表别名y进行基本查询(因为属性ID 10000007的选择性更好)，则必须使用别名x的唯一键。问题是:我不知道MySQL使用哪个别名(这是由其查询优化器决定的)。那么，如何指定订单方向呢？

(我想指出的是，该表包含约6 000万行，因此文件短或非文件的使用在响应时间上将非常重要)

Answer 1

您可能会检查此版本是否更快：

SELECT a.product_id
FROM attibutes a
WHERE a.attribute_id IN (20000085, 20000090, 10000007, 30000050, 30000040, 30000012) 
GROUP BY a.product_id
HAVING SUM(a.attribute_id IN (20000085, 20000090) ) > 0 AND
       SUM(a.attribute_id IN (10000007) ) > 0 AND
       SUM(a.attribute_id IN (30000050, 30000040, 30000012) ) > 0
ORDER BY a.rand
LIMIT 0, 100;

GROUP BY应该与SELECT DISTINCT的工作大致相同。按随机数排序仍然会带来开销，但有时从性能的角度来看，这个公式是可行的。

编辑：

如果将随机数放入products表中，下面的操作可能会做您想做的事情：

select p.*
from products p
where exists (select 1 from attributes a where p.product_id = a.product_id and a.attribute_id IN (20000085, 20000090) ) and
      exists (select 1 from attributes a where p.product_id = a.product_id and a.attribute_id IN (10000007) ) and
      exists (select 1 from attributes a where p.product_id = a.product_id and a.attribute_id IN (30000050, 30000040, 30000012) )
order by p.rand
limit 5;

嗯，如果您将随机数存储在products表中，则只需将其join到查询中并在order by中使用。这也可能有效。