我目前正在从事一个涉及专利的项目，该项目已撤回美国专利商标局网站，作为该项目的一部分，我正在使用伊利诺伊大学人员创建的数据库
(论文:http://abel.lis.illinois.edu/UPDC/USPTOPatentsDatabaseConstruction.pdf)
(我正在使用的表的架构略有过时，仅缺少非索引/键值:http://i.imgur.com/44LHS3L.png)

现在正如标题所说，我正在尝试优化查询:

SELECT 
        PN,
        AN,
        grants.GrantID,
        grants.FileDate,
        grants.IssueDate,
        grants.Kind,
        grants.ApplicationID,
        assignee_g.OrgName,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', assignee_g.City, assignee_g.State, assignee_g.Country) separator ';') as Assignee,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', inventor_g.FirstName, inventor_g.LastName) separator ';') as Inventor,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', inventor_g.City, inventor_g.State, inventor_g.Country) separator ';') as Inventor_address,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', usclass_g.Class, usclass_g.Subclass) separator ';') as USClass,
        intclass_g.Section,
        intclass_g.Class,
        intclass_g.Subclass,
        intclass_g.MainGroup,
        intclass_g.SubGroup
FROM
    (
    SELECT grants.GrantID as CitingID, CitedID as PN, grants2.ApplicationID AS AN
    FROM
        gracit_g, grants, grants as grants2
    Where
        grants.GrantID IN (*A 
                                         couple 
                                           Thousand
                                              keys*)
            and grants.GrantID = gracit_g.GrantID and grants2.GrantID = CitedID 
    LIMIT 500000) tbl1,
             grants, assignee_g, inventor_g, usclass_g, intclass_g
WHERE
    grants.GrantID = tbl1.CitingID
        and grants.GrantID = assignee_g.GrantID
        and grants.GrantID = inventor_g.GrantID
        and grants.GrantID = usclass_g.GrantID
        and grants.GrantID = intclass_g.GrantID
GROUP BY PN, GrantID
LIMIT 50000000

几乎每一个专利都被后面的专利引用 我想记录引用它的专利的信息。我似乎遇到的问题是我的“GROUP BY PN，GrantID”导致“使用临时文件，使用文件排序”，这严重减慢了我的工作速度。

这是我的解释给我的(抱歉，如果格式不完整，我找不到如何制作表格)

1
初级
派生2
全部
8716
可能的键:空
键:空
key_len: 空
引用:空
使用临时的；使用文件排序

1
初级
赠款
eq_ref
初级
初级
62
tbl1.CitingID
1

1
初级
受让人_g
引用
主要，FK_PublicationID_PUBLICATION_ASSIGNEE_P
初级
62
tbl1.CitingID
1

1
初级
intclass_g
引用
初级，fk_publicationid_PUBLICATION_INTERNATIONALCLASS_P
初级
62
tbl1.CitingID
1

1
初级
inventor_g
引用
初级，fk_PublicationID_Inventor_p
初级
62
tbl1.CitingID
1

1
初级
usclass_g
引用
初级，fk_publicationid_PUBLICATION_USCLASS_P
初级
62
tbl1.CitingID
2

2
派生
赠款
范围
初级
初级
62
引用:空
2179
在哪里使用；使用索引

2
派生
gracit_g
引用
PRIMARY,FK_PublicationID_PUBLICATION_PCITATION_P,CitedID
初级
62
uspto_patents.grants.GrantID
4
在哪里使用

2
派生
赠款2
eq_ref
初级
初级
62
uspto_patents.gracit_g.CitedID
1

gracit_g 的 SHOW CREATE 是:

CREATE TABLE `gracit_g` (
`GrantID` varchar(20) NOT NULL,
`Position` int(11) NOT NULL,
`CitedID` varchar(20) DEFAULT NULL,
`Kind` varchar(10) DEFAULT NULL COMMENT 'identify whether citedDoc is a document or foreign patent',
`Name` varchar(100) DEFAULT NULL,
`Date` date DEFAULT NULL,
`Country` varchar(100) DEFAULT NULL,
`Category` varchar(100) DEFAULT NULL,
PRIMARY KEY (`GrantID`,`Position`),
KEY `FK_PublicationID_PUBLICATION_PCITATION_P` (`GrantID`),
KEY `CitedID` (`CitedID`),
CONSTRAINT `FK_GrantID_GRANT_PCITATION_G0` FOREIGN KEY (`GrantID`) REFERENCES `grants`   (`GrantID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

grants 的 SHOW CREATE 是:

 CREATE TABLE `grants` (
 `GrantID` varchar(20) NOT NULL,
 `Title` varchar(500) DEFAULT NULL,
 `IssueDate` date DEFAULT NULL,
 `Kind` varchar(2) DEFAULT NULL,
 `USSeriesCode` varchar(2) DEFAULT NULL,
 `Abstract` text,
 `ClaimsNum` int(11) DEFAULT NULL,
 `DrawingsNum` int(11) DEFAULT NULL,
 `FiguresNum` int(11) DEFAULT NULL,
 `ApplicationID` varchar(20) NOT NULL,
 `Claims` text,
 `FileDate` date DEFAULT NULL,
 `AppType` varchar(45) DEFAULT NULL,
 `AppNoOrig` varchar(10) DEFAULT NULL,
 `SourceName` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`GrantID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

非常感谢您抽出宝贵的时间，不幸的是我必须回去 sleep 了，因为现在继续工作已经太晚了(或者现在还太早)

一个建议是将其更改为 1 个查询而不是子查询:

  SELECT 
        gracit_g.citedID,
        info_grant.GrantID,
        info_grant.FileDate,
        info_grant.IssueDate,
        info_grant.Kind,
        info_grant.ApplicationID,
        assignee_g.OrgName,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', assignee_g.City, assignee_g.State, assignee_g.Country) separator ';') as Assignee,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', inventor_g.FirstName, inventor_g.LastName) separator ';') as Inventor,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', inventor_g.City, inventor_g.State, inventor_g.Country) separator ';') as Inventor_address,
        GROUP_CONCAT(DISTINCT CONCAT_WS(', ', usclass_g.Class, usclass_g.Subclass) separator ';') as USClass,
        intclass_g.Section,
        intclass_g.Class,
        intclass_g.Subclass,
        intclass_g.MainGroup,
        intclass_g.SubGroup
FROM
    gracit_g, grants as info_grant, assignee_g, inventor_g, usclass_g, intclass_g
WHERE
        gracit_g.GrantID IN (*KEYS*)
        and info_grant.GrantID = gracit_g.GrantID
        and info_grant.GrantID = assignee_g.GrantID
        and info_grant.GrantID = inventor_g.GrantID
        and info_grant.GrantID = usclass_g.GrantID
        and info_grant.GrantID = intclass_g.GrantID
GROUP BY gracit_g.citedID, info_grant.GrantID
LIMIT 50000000

这已将它从 21 秒持续时间/10 秒获取减少到 13 秒持续时间/8 秒获取，我仍然希望改进，因为我有很多 key 要通过。

最佳答案

您的查询格式为:

SELECT some_fields
FROM (
    SELECT other_fields
    FROM table1, table2
    WHERE join_condition_table1_table2 AND some_other_condition
) AS subquery, table3
WHERE join_condition_subquery_table3
GROUP BY another_field

你需要重写如下:

SELECT some_fields
FROM table1, table2, table3
WHERE
    join_condition_table1_table2
    AND join_condition_subquery_table3 -- actually rewrite this ans a join of either table1 and table3, or table2 and table3
    AND some_other_condition
GROUP BY another_field

正如@Ollie Jones 所指出的，选择既不属于GROUP BY 条件也不在聚合中的字段(在SELECT 子句中)是危险的功能。如果这些字段不唯一依赖于 GROUP BY 条件中的字段，则这些字段的值未定义。

[编辑]

几个建议:

• 按此顺序在 gracit_g(citedID, GrantID) 上添加索引(ALTER TABLE gracit_g ADD INDEX(citedID, GrantID);)并更改您的 GROUP BY 子句到 GROUP BY gracit_g.citedID, gracit_g.GrantID。优化器可能会喜欢使用此索引来计算 GROUP BY 子句。

• 如果您的 VARCHAR 主键实际上是数字，请将它们的类型更改为合适的整数类型。如果不是，请添加一个数字代理键并将其用作主键。整数比较要快得多，并且您会在所有联接中进行大量比较。

• 在额外的列或额外的表中预先计算连接值，例如 CONCAT_WS(', ', assignee_g.City, assignee_g.State, assignee_g.Country) separator ';') (后者将需要每个表的额外连接)

• 增加 tmp_table_size和 max_heap_table_size服务器选项。如果临时表的增长大于这两个值(以字节为单位)中的任何一个，那么临时表就不能保存在内存中，而是将被写入磁盘。您可能会受益于此处异常大的值，因为您正在处理异常大的结果集。

不知道还有没有别的事情要做。您可能需要考虑返回较小的结果集(更少的列、更多的过滤器或更小的 LIMIT)。

关于mysql - Brain-Dead MySQL 选择优化(使用临时，使用文件排序)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17143847/