SQL替代在单个表上执行内部连接

Question

我有一个大表(TokenFrequency)，其中有数百万行。结构如下的TokenFrequency表：

表- TokenFrequency

• id - int，主键
• 源- int，外键
• 标记焦
• 计数- int

我的目标是选择两个源中有相同令牌的所有行。例如，如果我的表如下所示：

id -源-令牌--计数 1 2 3 4 5 6 7

我需要一个SQL查询来给我源1、源2和计数之和。例如：

source1 - source2 -4

我有一个类似这样的查询：

SELECT  F.source AS source1, S.source AS source2, F.token, 
       (F.count + S.count) AS sum 
FROM       TokenFrequency F 
INNER JOIN TokenFrequency S ON F.token = S.token 
WHERE F.source <> S.source

这个查询运行良好，但我遇到的问题是：

1. 我有一个TokenFrequency表，它有数百万行，因此需要一个更快的选项来获得这个结果。
2. 我现在的查询是提供重复的。例如，它的选择： source1=2，source2=3，token=cat，count=4 source1=3，source2=2，token=cat，count=4 这并不是什么大问题，但如果有办法消除这些问题，进而获得提速，那将是非常有用的。

我的主要问题是当前查询的查询速度，它需要几个小时才能完成。我认为，桌子上的内部连接本身就是问题所在。我确信必须有一种方法来消除内部连接，并使用TokenFrequency表的一个实例获得类似的结果。我提到的第二个问题也可能提高查询的速度。

我需要一种方法来重构这个查询，以更快、更有效的方式提供相同的结果。

谢谢。

Answer 1

我需要更多的信息来诊断速度问题，但是要删除这些陷阱，请将其添加到WHERE中：

AND F.source<S.source

Answer 2

试试这个：

SELECT token, GROUP_CONCAT(source), SUM(count)
FROM TokenFrequency
GROUP BY token;

这应该运行得更快，并消除重复。但是源将在逗号分隔的列表中返回，所以您必须在应用程序中引爆它。

您还可以尝试在token, source, count列上创建一个复合索引(按该顺序)，并使用EXPLAIN进行分析，以确定MySQL是否足够聪明，可以将其用作该查询的覆盖指数。

更新：，我好像误解了你的问题。您不需要每个令牌的计数之和，而是每个给定令牌的每个源的计数之和。

我相信内部加入是最好的解决办法。SQL的一个重要指导原则是，如果需要计算两个不同行的表达式，则需要执行联接。

但是，我前面提到的一种优化技术是使用覆盖索引，以便您需要的所有列都包含在索引数据结构中。好处是您的所有查找都是O(log )，并且查询不需要执行第二个I/O操作就可以读取物理行以获得其他列。

在这种情况下，您应该像我前面提到的那样，在列token, source, count上创建覆盖索引。还尝试分配足够的缓存空间，以便将索引缓存在内存中。

Answer 3

如果令牌没有被索引，那么它当然应该是。