mysql - 提高两个联合 (UNION ALL) 大表中的 SELECT 性能

Question

我有两个巨大的表，需要执行以下查询。表 authors_1(~20M 行)和 authors_2(~120M 行)都具有相同的结构。我已经完成了研究并提出了以下查询和表结构。查询仍然需要很长时间(通常在 10 到 20 秒之间)。

这里是查询:

SELECT `fname`, `lname` 
FROM (
    SELECT `fname`, `lname` 
    FROM `authors_1` 
    WHERE 1 AND `lname` LIKE 'AR%' 
    UNION ALL 
    SELECT `fname`, `lname` 
    FROM `authors_2` 
    WHERE 1 AND `lname` LIKE 'AR%') `a` 
GROUP BY CONCAT(`fname`, `lname`) 
ORDER BY `lname` 
LIMIT 0, 999;

这是两个表的相似结构(FT 索引用于其他查询)。

CREATE TABLE `scipers_authors` (
 `a_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `linker` varchar(255) COLLATE utf8_persian_ci NOT NULL,
 `fname` tinytext COLLATE utf8_persian_ci NOT NULL, /*Should this be tinytext because of FT index or I can use VARCHAR(255) while having FT index?*/
 `lname` tinytext COLLATE utf8_persian_ci NOT NULL, /*Same for this one*/
 PRIMARY KEY (`a_id`),
 UNIQUE KEY `linker` (`linker`) USING BTREE,
 KEY `lname_4` (`lname`(4)) USING BTREE,
 KEY `name` (`lname`(128),`fname`(128)) USING BTREE,
 FULLTEXT KEY `fname` (`fname`),
 FULLTEXT KEY `lname` (`lname`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

这是上面显示的示例查询的 EXPLAIN 输出(我不知道如何让它漂亮 like others ，我正在使用 PMA):

1   PRIMARY <derived2>  ALL                 476968  Using temporary; Using filesort 
2   DERIVED authors_1   range   lname_4,name,lname  name    386     184800  Using where 
3   UNION   authors_2   range   lname_4,name,lname  name    386     292168  Using where 
    UNION RESULT    <union2,3>  ALL                     Using temporary 

有什么改进此查询/结构的建议吗？

Answer 1

尝试将 GROUP BY CONCAT(fname, lname) 更改为 GROUP BY fname, lname。进行所有这些串联是不必要的额外工作。

它们之间唯一的语义区别是，如果有两个人的名字和姓氏不同，但连接起来时他们是相同的，例如弗雷达·史密斯和弗雷德·阿史密斯。这样的组合可能不太可能，而且我怀疑您一开始真的希望将它们组合成一个结果。

如果您按所选的同一组列进行分组，则只需使用

SELECT DISTINCT fname, lname

您可以改为使用 UNION DISTINCT 而不是 UNION ALL，因为您希望删除重复项。然后你不需要在外部查询中进行分组或 DISTINCT。