SQL查询非常慢.没有指标如何提高?

Question

我需要用一个查询从多个表中获取数据，该查询提供了大约10600个结果(行)。问题是查询需要很长时间才能执行。像. .很长时间了…90秒。

是否有任何方法可以在不添加索引的情况下改进查询?表不断更新(插入、更新、删除行)。

下面是查询:

SELECT 
t1.ID
, t1.ref
, t1.type
, GROUP_CONCAT(DISTINCT t3.name) AS parish
, GROUP_CONCAT(DISTINCT t2.village) AS village
, GROUP_CONCAT(DISTINCT t2.code) AS code
, GROUP_CONCAT(DISTINCT t4.year) AS year
FROM table1 t1
LEFT OUTER JOIN table2 AS t2 ON t2.teade_ID = t1.ID
LEFT OUTER JOIN table3 AS t3 ON t2.parish_ID = t3.ID
LEFT OUTER JOIN table4 AS t4 ON t4.teade_ID = t1.ID
GROUP BY t1.ID, t1.ref, t1.type
ORDER BY t1.ID DESC

任何帮助是非常感谢!

Answer 1

计划A -使GROUP BY和ORDER BY匹配:

通常索引主要用于WHERE子句。但是没有过滤，所以索引可以移到GROUP BY。你有什么索引?如果您有PRIMARY KEY(id)，那么更改为简单的这可能是工作:

GROUP BY t1.ID
ORDER BY t1.ID DESC

如果ONLY_FULL_GROUP_BY有问题，您可能需要

GROUP BY t1.ID,      t1.ref,      t1.type
ORDER BY t1.ID DESC, t1.ref DESC, t1.type DESC

无论哪种情况，请注意GROUP BY和ORDER BY如何匹配"对方。有了这个(与您所拥有的不同)，两个子句都可以在一个步骤中完成。因此不需要收集所有行，进行分组，然后排序。去掉排序可以提高速度。

方案B -延迟访问问题ref和type:

SELECT ID, t1x.ref, t1x.type
FROM (
SELECT 
t1.ID
, GROUP_CONCAT(DISTINCT t3.name) AS parish
, GROUP_CONCAT(DISTINCT t2.village) AS village
, GROUP_CONCAT(DISTINCT t2.code) AS code
, GROUP_CONCAT(DISTINCT t4.year) AS year
FROM table1 t1
LEFT OUTER JOIN table2 AS t2 ON t2.teade_ID = t1.ID
LEFT OUTER JOIN table3 AS t3 ON t2.parish_ID = t3.ID
LEFT OUTER JOIN table4 AS t4 ON t4.teade_ID = t1.ID
GROUP BY t1.ID
) x
JOIN t1 AS t1x  USING(ID)
ORDER BY t1.ID DESC

ORDER BY在派生表中被忽略;GROUP BY在外表中是不必要的。

方案C -假设ID是PK，去掉GROUP BY:

SELECT  ID, ref, type
( SELECT GROUP_CONCAT(DISTINCT t3.name)
FROM t3 WHERE t3.ID = t1.ID ) AS parish,
( ... ) AS ...,
( ... ) AS ...,
( ... ) AS ...
FROM t1
ORDER BY ID DESC

子查询具有与原始LEFT JOIN相同的语义。

您的原始查询出现"爆炸-内爆"。首先，join收集所有的教区，等等，导致一个大的中间表。然后，分组将其缩小到只需要的内容。Plan C避免了这种爆炸，也避免了GROUP BY。

此外，不会有排序，因为它可以简单地以相反的顺序扫描表。

Answer 2

在加入前聚合:

SELECT t1.ID, t1.ref, t1.type,
t2.villages, t2.codes,
t3.villages, t4.years
FROM table1 t1 LEFT JOIN
(SELECT t2.teade_ID, GROUP_CONCAT(t2.code) AS codes,
GROUP_CONCAT(t2.village) as villages
FROM table2 t2
GROUP BY t2.teade_ID
) t2
ON t2.teade_ID = t1.ID LEFT JOIN
(SELECT t2.teade_ID, GROUP_CONCAT(t3.village) as villages
FROM table2 t2 JOIN
table3 t3
ON t2.parish_ID = t3.ID
GROUP BY t2.teade_ID
) t3
ON t3.teade_id = t.id LEFT JOIN
(SELECT GROUP_CONCAT(t4.year) AS year
FROM table4 t4
GROUP BY t2.teade_ID
) t4
ON t4.teade_ID = t1.ID
ORDER BY t1.ID DESC;

您可能仍然需要GROUP_CONCAT()中的DISTINCT。从你的问题看不清楚是否还需要这样做。

为什么这样更快?您的版本正在为每个ID生成所有表的叉积——可能会大大增加数据的大小。数据越多，GROUP BY越慢。

还要注意，外部查询中没有聚合。