mysql - 如何使用大型数据集(数百万行)的连接和子查询优化 MySQL 查询

Question

我正在尝试将国际专利数据库 (PATSTAT) 的四个大表(35-2 亿行)连接到符合一些要求的被引用最多的专利前 15 名。

第一个表 (t9) 列出了一组(系列)应用程序对另一组应用程序的引用。另一个表 (t1) 基本上将所有内容链接在一起，同时包含家庭和申请 ID，以及提交年份表 t2 和 tls209_appln_ipc 用于识别要包含的 appln_id。

我最终得出的代码如下:

SELECT t9.cited_docdb_family_id, COUNT(t9.cited_docdb_family_id) AS cited, t3.appln_id
FROM docdb_family_citation t9 
LEFT JOIN 
(SELECT
t1.appln_id, t1.docdb_family_id from tls201_appln t1
LEFT JOIN tls204_appln_prior t2 on t1.appln_id=t2.appln_id 
WHERE
t1.appln_filing_year BETWEEN 2010 AND 2015
AND
t2.appln_id IS NULL
AND
t1.appln_id IN (SELECT distinct appln_id from tls209_appln_ipc where ipc_subclass_symbol in ("A61K", "C07K", "A61P", "Cl2N", "C07D", "Cl2P", "C07H", "C12Q", "C07J"))) t3 ON t9.cited_docdb_family_id=t3.docdb_family_id
GROUP BY t9.cited_docdb_family_id
ORDER BY cited DESC
LIMIT 15

问题是在我的 session 超时之前，在 PATSTAT 基于 Web 的在线界面中运行的查询没有收敛。有没有办法提高这个查询的效率？

-编辑-
tls_209_appln_ipc 包含 1.95 亿行 appln_id 和 ipc_subclass_symbol。 appln_id 在此表中可能出现零次或多次。在我的查询中，我只需要 docdb_family_id，如果它们链接的 appln_id 中的 any 链接到 any ipc_subclass_symbol我列出来了。

Answer 1

这是您的查询:

SELECT t9.cited_docdb_family_id, COUNT(t9.cited_docdb_family_id) AS cited, t3.appln_id
FROM docdb_family_citation t9 LEFT JOIN 
     (SELECT t1.appln_id, t1.docdb_family_id
      from tls201_appln t1 LEFT JOIN
           tls204_appln_prior t2
           on t1.appln_id=t2.appln_id 
      WHERE t1.appln_filing_year BETWEEN 2010 AND 2015 AND
            t2.appln_id IS NULL AND
            t1.appln_id IN (SELECT distinct appln_id
                            from tls209_appln_ipc
                            where ipc_subclass_symbol in ("A61K", "C07K", "A61P", "Cl2N", "C07D", "Cl2P", "C07H", "C12Q", "C07J"
                                                         )
                           )
           ) t3
      ON t9.cited_docdb_family_id = t3.docdb_family_id
GROUP BY t9.cited_docdb_family_id
ORDER BY cited DESC
LIMIT 15;

此查询具有优化范围。首先，在 MySQL 中应谨慎使用子查询，因为子查询是具体化的。您在这里不需要子查询。您可以只链接 left join 操作。其次，select distinct 在 in 子查询中没有用处。此外，通常 exists 速度更快。

我首先将其重写为:

SELECT t9.cited_docdb_family_id, COUNT(t9.cited_docdb_family_id) AS cited, t1.appln_id
FROM docdb_family_citation t9 LEFT JOIN 
     tls201_appln t1
     on t9.cited_docdb_family_id = t1.docdb_family_id and
        t1.appln_filing_year BETWEEN 2010 AND 2015 and
        exists (select 1 from tls209_appln_ipc t209
                where t209.appln_id = t1.appln_id AND
                      t209.ipc_subclass_symbol in ("A61K", "C07K", "A61P", "Cl2N", "C07D", "Cl2P", "C07H", "C12Q", "C07J")
               ) and
        not exists (select 1 from tls204_appln_prior t2
                    where t1.appln_id = t2.appln_id 
                   )
GROUP BY t9.cited_docdb_family_id
ORDER BY cited DESC
LIMIT 15;

对于此查询，您需要以下索引:tls204_appln_prior(appln_id)、tls209_appln_ipc(appln_id, ipc_subclass_symbol)、tls201_appln(cited_docdb_family_id, appln_id).

我不喜欢 on 子句中的 exists 和 not exists ，但这似乎是你的语义寻找。我强烈怀疑有更好的方法来编写查询，但你的问题没有提供足够的信息。更好的方法是先聚合 t1 表，然后将结果left join 聚合到 t9 表。但是，嵌套的 left join 和 exists 会让人感到困惑。