mysql - 优化 group_concat 函数的 MySQL 查询

Question

SELECT SQL_NO_CACHE link.stop, stop.common_name, locality.name, stop.bearing, stop.latitude, stop.longitude
FROM service
JOIN pattern ON pattern.service = service.code
JOIN link ON link.section = pattern.section
JOIN naptan.stop ON stop.atco_code = link.stop
JOIN naptan.locality ON locality.code = stop.nptg_locality_ref
GROUP BY link.stop

上述查询大约需要 800 毫秒到 1000 毫秒才能运行。

如果我附加一个 group_concat 语句，则查询需要 8 - 10 秒:

SELECT SQL_NO_CACHE link.stop, link.stop, stop.common_name, locality.name, stop.bearing, stop.latitude, stop.longitude, group_concat(service.line) lines

如何使用 group_concat 语句更改此查询，使其在 2 秒内运行？

SQL fiddle :http://sqlfiddle.com/#!9/414fe

两个查询的

EXPLAIN 语句:http://i.imgur.com/qrURgzV.png

Answer 1

这个查询需要多长时间？

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。