是否有任何方法可以简化联合查询，同时使用多个表，并参考主表(性能优化)

Question

我想知道是否有任何方法来改进查询、加速/简化，同时只引用一个主表使用多个表UNION子句。

下面是场景，这里的表reference是我的主表，geo_loc1、geo_loc2、geo_loc2是其他表，它们将基于key1和key2连接，这两个键存在于所有表中。

SELECT 
    reference.*,
    geo_loc1.bathy,
    geo_loc1.gravity,
    geo_loc1.magnet,
    'data1' as type
FROM 
    reference,geo_loc1 
WHERE
    reference.latitude between -30 and -10 AND 
    reference.longitude between 10 and 50 AND 
    DATE( reference.st_date ) BETWEEN '2000-07-05' AND '2011-11-10'  AND
    reference.key1 = geo_loc1.key1 AND
    reference.key2 = geo_loc1.key2
UNION
SELECT 
    reference.*,
    geo_loc2.bathy,
    Null as gravity,
    geo_loc2.magnet,
    'data2' as type
FROM 
    reference,geo_loc2 
WHERE
    reference.latitude between -30 and -10 AND 
    reference.longitude between 10 and 50 AND 
    DATE( reference.st_date ) BETWEEN '2000-07-05' AND '2011-11-10'  AND
    reference.key1 = geo_loc2.key1 AND
    reference.key2 = geo_loc2.key2
UNION
SELECT 
    reference.*,
    Null as bathy,
    Null as gravity,
    geo_loc3.magnet,
    'data3' as type
FROM 
    reference,geo_loc3 
WHERE
    reference.latitude between -30 and -10 AND 
    reference.longitude between 10 and 50 AND 
    DATE( reference.st_date ) BETWEEN '2000-07-05' AND '2011-11-10'  AND
    reference.key1 = geo_loc3.key1 AND
    reference.key2 = geo_loc3.key2

是否可以简化或改进此查询？

Answer 1

就性能而言，我可以看到您可能会改变的两件事。

1)将UNION改为UNION ALL。UNION将检查结果是否重复，这增加了您不需要的开销。

2)考虑将DATE( reference.st_date ) BETWEEN '2000-07-05' AND '2011-11-10'更改为不使用date函数。

如果reference.st_date是一个合适的date列，并且不需要进行类型转换，那么DB可以使用基于日期的索引来快速查找。通过执行类型转换，DB现在必须进行扫描才能对每一行进行转换。

你说索引在key1,key2,st_date,latitude,longitude上。通过使用date函数，您可能只使用该索引的前两列。我要么将st_date更改为日期，要么更改要与之比较的值，这样就不需要转换了。

还可以考虑使用适当的INNER JOIN，而不是将联接条件作为WHERE子句添加。使用正确的语法可以更好地阅读，并且更容易出错。

使用UNION ALL和INNER JOIN的示例应该如下所示。注意，这显然是未经测试的，但是您应该知道这个想法。

编辑删除了date()函数的使用，该函数将进一步加快它的速度

SELECT 
    reference.*,
    geo_loc1.bathy,
    geo_loc1.gravity,
    geo_loc1.magnet,
    'data1' as type
FROM 
    reference
INNER JOIN geo_loc1 ON
    reference.key1 = geo_loc1.key1 AND
    reference.key2 = geo_loc1.key2
WHERE
    reference.latitude between -30 and -10 AND 
    reference.longitude between 10 and 50 AND 
    reference.st_date BETWEEN '2000-07-05 00:00:00' AND '2011-11-10 23:59:59'
UNION ALL
SELECT 
    reference.*,
    geo_loc2.bathy,
    Null as gravity,
    geo_loc2.magnet,
    'data2' as type
FROM 
    reference
INNER JOIN geo_loc2 ON
    reference.key1 = geo_loc2.key1 AND
    reference.key2 = geo_loc2.key2
WHERE
    reference.latitude between -30 and -10 AND 
    reference.longitude between 10 and 50 AND 
    reference.st_date BETWEEN '2000-07-05 00:00:00' AND '2011-11-10 23:59:59'
UNION ALL
SELECT 
    reference.*,
    Null as bathy,
    Null as gravity,
    geo_loc3.magnet,
    'data3' as type
FROM 
    reference
INNER JOIN geo_loc3 ON
    reference.key1 = geo_loc3.key1 AND
    reference.key2 = geo_loc3.key2
WHERE
    reference.latitude between -30 and -10 AND 
    reference.longitude between 10 and 50 AND 
    reference.st_date BETWEEN '2000-07-05 00:00:00' AND '2011-11-10 23:59:59'

Answer 2

您可以安全地使用UNION ALL而不是UNION，因为您的查询不能包含任何重复的行。

但是，您将主要从好的索引中获得性能，而不是通过不同的查询编写方式。