mysql - subselect 使复杂的查询真的很慢

Question

所以我想为一个项目做以下事情。

我有 3 个表。前两个我们现在关心(第三个是为了让您更好地理解):

author {id, name}
authorship {id, id1, id2}
paper {id, title}

authorship 将作者与论文联系起来，authorship.id1 指 author.id，authorship.id2 指 paper.id。

我想做的是制作一个图表，每个作者都有一个节点，边由两位作者之间的共同论文数量决定。

w=1 - union_of_common_papers/intersection_of_common_papers

所以我构建了(在 stackoverflow 的一些帮助下)一个 sql 脚本，它返回所有的共同作者以及共同论文的并集和交集的数量。之后，我将使用 java 的数据。内容如下:

SELECT DISTINCT a1.name, a2.name, (
  SELECT  concat(count(a.id2), ',', count(DISTINCT a.id2)) 
  FROM authorship a 
  WHERE a.id1=a1.id or a.id1=a2.id) as weight
FROM authorship au1 
INNER JOIN authorship au2 ON au1.id2 = au2.id2 AND au1.id1 <> au2.id1 
INNER JOIN author a1 ON au1.id1 = a1.id 
INNER JOIN author a2 ON au2.id1 = a2.id;

这完成了我的工作并返回如下列表:

+-----------------+---------------------+---------+
| name            | name                | weight  |
+-----------------+---------------------+---------+
| Kurt            | Michael             | 161,157 |
| Kurt            | Miron               | 138,134 |
| Kurt            | Manish              | 19,18   |
| Roy             | Gregory             | 21,20   |
| Roy             | Richard             | 74,71   |
....

在权重中，我可以看到 2 个数字 a，b，其中 b 是交集，b-a 是普通论文的并集。

但这需要很多时间。所有的开销都是由这个额外的子选择

  (SELECT  concat(count(a.id2), ',', count(DISTINCT a.id2)) 
  FROM authorship a 
  WHERE a.id1=a1.id or a.id1=a2.id) as weight

如果没有这一行，所有记录 (1M+) 都会在不到 2 分钟的时间内返回。这条线 50 条记录需要超过 1.5 分钟

我在linux上通过命令行使用mysql

我有什么可以优化它的想法吗？

• 作者有 ~130,000 条记录
• 作者 ~1,300,000 条记录
• 查询应返回 ~1,200,000 条记录

这就是解释此查询返回的内容。不知道怎么用。

+----+--------------------+-------+--------+---------------------+-----------+---------+--------------+---------+-----------------+
| id | select_type        | table | type   | possible_keys       | key       | key_len | ref          | rows    | Extra           |
+----+--------------------+-------+--------+---------------------+-----------+---------+--------------+---------+-----------------+
|  1 | PRIMARY            | a1    | ALL    | PRIMARY             | NULL      | NULL    | NULL         |  124768 | Using temporary |
|  1 | PRIMARY            | au1   | ref    | NewIndex1,NewIndex2 | NewIndex1 | 5       | dblp.a1.ID   |       4 | Using where     |
|  1 | PRIMARY            | au2   | ref    | NewIndex1,NewIndex2 | NewIndex2 | 5       | dblp.au1.id2 |       1 | Using where     |
|  1 | PRIMARY            | a2    | eq_ref | PRIMARY             | PRIMARY   | 4       | dblp.au2.id1 |       1 |                 |
|  2 | DEPENDENT SUBQUERY | a     | ALL    | NewIndex1           | NULL      | NULL    | NULL         | 1268557 | Using where     |
+----+--------------------+-------+--------+---------------------+-----------+---------+--------------+---------+-----------------+

Answer 1

您应该能够直接从外部查询中的连接获取数据。

您可以通过计算两个作者相同的不同 id2 来计算共同论文的数量。

您可以将论文总数计算为每位作者的不同论文数减去共同论文数(因为否则，这些将被计算两次):

SELECT a1.name, a2.name,
       COUNT(distinct case when au1.id2 = au2.id2 then au1.id2 end) as CommonPapers,
       COUNT(distinct au1.id2) + COUNT(distinct au2.id2) - COUNT(distinct case when au1.id2 = au2.id2 then au1.id2 end) as TotalPapers
FROM authorship au1 INNER JOIN
     authorship au2
     ON au1.id2 = au2.id2 AND au1.id1 <> au2.id1 INNER JOIN
     author a1
     ON au1.id1 = a1.id INNER JOIN
     author a2
     ON au2.id1 = a2.id
group by a1.name, a2.name;

在您的数据结构中，id1 和id2 是糟糕的名称。您是否考虑过像 idauthor 和 idpaper 之类的东西？

由于初始内部连接，上述查询正确计算了交集，但不是总数。解决此问题的一种方法是完全外部连接，但在 MySQL 中不允许这样做。我们可以使用额外的子查询来做到这一点:

SELECT a1.name, a2.name,
       COUNT(distinct case when au1.id2 = au2.id2 then au1.id2 end) as CommonPapers,
       (ap1.NumPapers + ap2.NumPapers - COUNT(distinct case when au1.id2 = au2.id2 then au1.id2 end)
       ) as TotalPapers
FROM authorship au1 INNER JOIN
     authorship au2
     ON au1.id2 = au2.id2 AND au1.id1 <> au2.id1 INNER JOIN
     author a1
     ON au1.id1 = a1.id INNER JOIN
     author a2
     ON au2.id1 = a2.id inner join
     (select au.id1, count(*) as numpapers
      from authorship au
     ) ap1
     on ap1.id1 = au1.id1 inner join
     (select au.id1, count(*) as numpapers
      from authorship au
     ) ap2
     on ap2.id1 = au2.id1 inner join
group by a1.name, a2.name;