mysql - 有没有办法在使用多表时简化联合查询，引用主表(性能优化)

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' 更改为不使用日期函数。

如果 reference.st_date 是一个正确的日期列并且您不需要进行类型转换，那么数据库可以使用基于日期的索引来进行快速查找。通过进行类型转换，数据库现在必须进行扫描以对每一行进行转换。

你说索引在 key1,key2,st_date,latitude,longitude 上。通过使用日期函数，您可能只使用该索引的前两列。我会将 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'