mysql - 计数查询花费的时间太长 - 已超过 24 小时

Question

好吧，很可能是我做错了什么，但是按照这里用户的建议，我运行了这个查询:

SELECT id, item, 
   (SELECT COUNT(item) FROM Table1 WHERE id=a.id AND item=a.item) cnt
FROM (SELECT DISTINCT a.id,b.item FROM Table1 a, Table1 b) a
ORDER BY id, item;

在这张 table 上:

ID         ITEM
-----------------
0001        345
0001        345
0001        120
0002        567
0002        034
0002        567
0003        567
0004        533
0004        008
...

为了得到这个结果:

ID  ITEM    CNT
1   8       0
1   34      0
1   120     1
1   345     2
1   533     0
1   567     0
2   8       0
2   34      1
...

但它花费的时间太长，一天后查询仍在运行......有没有办法提高性能？我有大约 400 万行

谢谢

Answer 1

你的问题很复杂。我认为您只想计算 id 和 item 的组合。如果是这样，这是一个简单的聚合:

select id, item, count(*)
from Table1 a
group by id, item;

如果你想显示所有的ids和items，那么使用一个驱动表:

select driver.id, driver.item, coalesce(count(t1.id), 0)
from (select id.id, item.item
      from (select distinct id from Table1) id cross join
           (select distinct item from Table1) item
     ) driver left outer join
     Table1 t1
     on driver.id = t1.id and driver.item = t1.item
group by driver.id, driver.item;

原始查询有这样的语句:

 (SELECT DISTINCT a.id,b.item FROM Table1 a, Table1 b) a

这是做完整的笛卡尔积，然后做不同的。因此，如果您的表有 100,000 行，那么中间表有 10,000,000,000 行用于不同的行(我认为 MySQL 对此优化得并不好)。首先执行 distinct(对于驱动程序)大大减少了数据量。

编辑:

有一类 SQL 问题，您需要查看两个或多个项目的所有组合，然后确定每个人的值(甚至是数据中不存在的值)或找到那些不存在的值 在数据中。这些问题提出了同一个问题:您如何获取有关不在数据中的值的信息？

我提倡的解决方案是创建一个包含所有可能组合的表，然后对剩余的表使用left [outer] join。我将其称为“驱动程序”表，因为此查询中的行通过定义后续连接的总体来“驱动”查询。

此术语与评论中的引用相当一致。评论是从优化器的角度使用术语。一些连接算法——尤其是嵌套循环和索引查找——对连接的两端进行不同的处理；对于这些，一侧是“驾驶/司机”表。例如，从一个大表连接到一个小引用表时，大表是驱动表，另一个表通过索引访问。其他连接算法——例如合并连接和散列连接(通常)——对双方的处理方式相同，因此该概念不太适用。

从逻辑的角度来看，我用它来表示定义人口的查询。一个重要的相似之处在于，对于左/右外连接，这两个定义在实践中是相同的。优化器通常会选择 left join 中的第一个表作为“驱动程序”，因为它定义了输出行。