问优化MySQL函数的group_concat查询

EN

这个查询需要多长时间？

SELECT p.section, GROUP_CONCAT(s.line)
FROM pattern p join
     service s
     ON p.service = s.code
GROUP BY p.section

我认为您可以在子查询中执行group_concat()，因此外部查询不需要聚合。当子查询中有一个表时，这可以加快查询速度。在你的情况下，有两个。

最终结果如下：

link.section = pattern.section

SELECT SQL_NO_CACHE . . .,
       (SELECT GROUP_CONCAT(s.line)
        FROM pattern p join
             service s
             ON p.service = s.code
        WHERE p.section = link.section
       ) as lines
FROM link JOIN
     naptan.stop
     ON stop.atco_code = link.stop JOIN
     naptan.locality
     ON locality.code = stop.nptg_locality_ref;

对于此查询，您需要以下附加索引：pattern(section, service)和service(code, line)。

我不知道这是否可行，但值得一试。

注意:这是假设您实际上不需要group by来处理其余的列。

备注:您正在使用非标准的MySQL扩展到组。它恰好适合您，因为link.stop被加入到stop.atco_code，而stop.atco_code本身就是一个主键。但你得非常小心。

我建议你添加一些复合索引。您可以在service上加入service，然后基于section加入。所以，添加这个索引。

ALTER TABLE pattern ADD INDEX service_section (service, section, line);

这将使查询只使用索引，而不必命中表本身来检索连接或GROUP_CONCAT()操作所需的信息。(您也可以删除service上的索引，这个新索引使其成为多余的索引)。

类似地，您希望在(section, stop)表上创建一个索引link，并去掉仅对section的索引。

在stop上，您正在使用大多数列，并且已经在atco_code上有了一个索引(PK)，所以让它成为一个索引。

最后，在locality上将一个索引放在(code,name)上。

所有这些索引猴子业务都应该减少MySQL为满足您的查询而必须做的工作。

现在，一旦您将WHERE anything = anything添加到查询中，您可能需要向这些索引中的一个或多个添加列。您肯定应该阅读多列、标引和分组；良好的索引是数据成功的关键因素。

在插入了许多行之后，还应该在每个表上运行ANALYZE TABLE xxxx，以确保查询优化器能够看到有关表和索引内容的适当信息。