sql优化实战 把full join改为left join +union all(从5分钟降为10秒)

SQL优化是提高数据库性能的重要手段之一，本文将详细讲解如何通过将FULL JOIN改为LEFT JOIN + UNION ALL的方式，将查询时间从5分钟降为10秒。

什么是FULL JOIN？

FULL JOIN是一种关联查询方式，它会返回左右两个表中所有的记录，即使没有匹配的记录也会被显示出来。在SQL语句中，FULL JOIN可以通过“FULL OUTER JOIN”关键字来实现。

为什么FULL JOIN在性能上较差？

FULL JOIN的性能问题在于它需要对两个表进行全表扫描，因此当数据量较大时，会导致查询速度变慢，甚至无法完成查询任务。

具体来说，如果LEFT JOIN的一张表A和FULL JOIN的一张表B，A表中只有10条记录，而B表中有10000条记录，则FULL JOIN需要比LEFT JOIN多扫描9990条记录。

改为LEFT JOIN + UNION ALL的原理？

FULL JOIN虽然返回了左右两个表中所有记录，但在很多情况下我们只需要其中的部分记录。那么，为了避免FULL JOIN的性能问题，我们可以考虑用LEFT JOIN + UNION ALL的方式来替代FULL JOIN。

具体来说，我们可以先将两个表进行左连接操作，然后再将右表中没有匹配的记录通过UNION ALL方式连接到查询结果集中，如下所示：

SELECT A.*, B.*
FROM A LEFT JOIN B ON A.id = B.id
UNION ALL
SELECT A.*, B.*
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

其中，第一部分LEFT JOIN可以返回A表中所有记录和与之匹配的B表记录。第二部分则将B表中没有匹配的记录通过RIGHT JOIN的方式，并过滤掉所有已经在LEFT JOIN中匹配了的记录，最终将剩余记录连接到结果集中。

改写后的SQL语句中，使用到了两个关键字：LEFT JOIN和UNION ALL。LEFT JOIN用于左连接两个表，UNION ALL用于将两个结果集合并输出。

示例说明1

假设我们需要查询两张表A和B中的所有记录，并按照id升序排序。现有的FULL JOIN语句如下：

SELECT A.*, B.*
FROM A
FULL JOIN B
ON A.id = B.id
ORDER BY id ASC

现在我们将其改为LEFT JOIN + UNION ALL的方式，如下所示：

SELECT A.*, B.*
FROM A LEFT JOIN B
ON A.id = B.id
UNION ALL
SELECT A.*, B.*
FROM A RIGHT JOIN B
ON A.id = B.id
WHERE A.id IS NULL
ORDER BY id ASC

这样，我们可以在不影响查询结果的前提下，大大提高查询效率。

示例说明2

假设我们需要查询两张表A和B中的所有记录，且A表中的记录需要按照id升序排序，B表中的记录需要按照age降序排序。现有的FULL JOIN语句如下：

SELECT A.*, B.*
FROM A
FULL JOIN B
ON A.id = B.id
ORDER BY A.id ASC, B.age DESC

现在我们将其改为LEFT JOIN + UNION ALL的方式，如下所示：

SELECT A.*, B.*
FROM A LEFT JOIN B
ON A.id = B.id
UNION ALL
SELECT A.*, B.*
FROM A RIGHT JOIN B
ON A.id = B.id
WHERE A.id IS NULL
ORDER BY A.id ASC, B.age DESC

这样，我们可以保持原有的排序要求，并在不影响查询结果的情况下，将查询时间从5分钟降为10秒。

综上，将FULL JOIN改为LEFT JOIN + UNION ALL是一种优化查询性能的有效方式，尤其在涉及大量数据时效果更为明显。