在联合中使用Order By NULL

Question

我有一个查询(见下文)，我有一个自定义开发的UDF，用于计算某些点是在多边形(UNION中的第一个查询)还是圆形(UNION中的第二个查询)形状内。

select  e.inquiry_match_type_id 
        , a.geo_boundary_id 
        , GeoBoundaryContains(c.tpi_geo_boundary_coverage_type_id, 29.287437, -95.055807, a.lat, a.lon, a.geo_boundary_vertex_id ) in_out 
        , e.inquiry_id 
        , e.external_id 
        , COALESCE(f.inquiry_device_id,0) inquiry_device_id 
        , b.external_info1 
        , b.external_info2 
        , b.geo_boundary_id 
        , b.geo_boundary_type_id 
from    geo_boundary_vertex a 
        join geo_boundary b on b.geo_boundary_id = a.geo_boundary_id 
        join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id 
        join trackpoint_index_mem d on d.trackpoint_index_id = c.trackpoint_index_id 
        join inquiry_mem e on e.inquiry_id = b.inquiry_id left 
        outer join inquiry_device_mem f on f.inquiry_id = e.inquiry_id and f.device_id = 3201 
where   d.trackpoint_index_id = 3127  
        and b.geo_boundary_type_id = 3  
        and e.expiration_date >= now() 
group by 
        a.geo_boundary_id 
UNION 
select  e.inquiry_match_type_id      
        , b.geo_boundary_id      
        , GeoBoundaryContains( c.tpi_geo_boundary_coverage_type_id, 29.287437, -95.055807, b.centroid_lat, b.centoid_lon, b.radius ) in_out      
        , e.inquiry_id      
        , e.external_id      
        , COALESCE(f.inquiry_device_id,0) inquiry_device_id      
        , b.external_info1      
        , b.external_info2      
        , b.geo_boundary_id      
        , b.geo_boundary_type_id 
from    geo_boundary b 
        join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id 
        join trackpoint_index_mem d on d.trackpoint_index_id = c.trackpoint_index_id 
        join inquiry_mem e on e.inquiry_id = b.inquiry_id 
        left outer join inquiry_device_mem f on f.inquiry_id = e.inquiry_id and f.device_id = 3201 
where   d.trackpoint_index_id = 3127  
        and b.geo_boundary_type_id = 2  
        and e.expiration_date >= now() 
group by
        b.geo_boundary_id 

当我为查询运行explain时，我得到以下结果：

 id      select_type     table       type     possible_keys                                                                                                                                              key                                  key_len     ref                       rows     Extra                           
 ------  --------------  ----------  -------  ---------------------------------------------------------------------------------------------------------------------------------------------------------  -----------------------------------  ----------  ------------------------  -------  ------------------------------- 
 1       PRIMARY         d           const    PRIMARY                                                                                                                                                    PRIMARY                              4           const                     1        Using temporary; Using filesort 
 1       PRIMARY         c           ref      PRIMARY,fk_mtp_idx_geo_boundary_mtp_idx,fk_mtp_idx_geo_boundary_geo_boundary,fk_mtp_idx_geo_boundary_mtp_mem_idx,fk_mtp_idx_geo_boundary_geo_boundary_mem  fk_mtp_idx_geo_boundary_mtp_idx      4           const                     9                                        
 1       PRIMARY         b           eq_ref   PRIMARY,fk_geo_boundary_inquiry,fk_geo_boundary_geo_boundary_type                                                                                          PRIMARY                              4           gothim.c.geo_boundary_id  1        Using where                     
 1       PRIMARY         e           eq_ref   PRIMARY                                                                                                                                                    PRIMARY                              4           gothim.b.inquiry_id       1        Using where                     
 1       PRIMARY         f           ref      fk_inquiry_device_mem_inquiry                                                                                                                              fk_inquiry_device_mem_inquiry        4           gothim.e.inquiry_id       2                                        
 1       PRIMARY         a           ref      fk_geo_boundary_vertex_geo_boundary                                                                                                                        fk_geo_boundary_vertex_geo_boundary  4           gothim.b.geo_boundary_id  11       Using where                     
 2       UNION           d           const    PRIMARY                                                                                                                                                    PRIMARY                              4           const                     1        Using temporary; Using filesort 
 2       UNION           c           ref      PRIMARY,fk_mtp_idx_geo_boundary_mtp_idx,fk_mtp_idx_geo_boundary_geo_boundary,fk_mtp_idx_geo_boundary_mtp_mem_idx,fk_mtp_idx_geo_boundary_geo_boundary_mem  fk_mtp_idx_geo_boundary_mtp_idx      4           const                     9                                        
 2       UNION           b           eq_ref   PRIMARY,fk_geo_boundary_inquiry,fk_geo_boundary_geo_boundary_type                                                                                          PRIMARY                              4           gothim.c.geo_boundary_id  1        Using where                     
 2       UNION           e           eq_ref   PRIMARY                                                                                                                                                    PRIMARY                              4           gothim.b.inquiry_id       1        Using where                     
 2       UNION           f           ref      fk_inquiry_device_mem_inquiry                                                                                                                              fk_inquiry_device_mem_inquiry        4           gothim.e.inquiry_id       2                                        
 (null)  UNION RESULT    <union1,2>  ALL      (null)                                                                                                                                                     (null)                               (null)      (null)                    (null)   Using filesort                  

 12 record(s) selected [Fetch MetaData: 1ms] [Fetch Data: 5ms] 

现在，我可以拆分查询并使用ORDER BY NULL技巧来消除文件排序，但是当我试图将其添加到UNION的末尾时，它不起作用。

我正在考虑将查询拆分成两个查询，或者可能完全重写它，以不使用UNION (当然，这有点困难)。我遇到的另一件事是，我们已经在生产中使用了这个，我想限制更改-我希望能够在查询的末尾添加ORDER BY NULL并完成它，但它在UNION中不起作用。

任何帮助都将不胜感激。

Answer 1

通常，ORDER BY可用于UNION中的单个查询，如下所示：

(
SELECT  *
FROM    table1, …
GROUP BY
        id
ORDER BY 
        NULL
)
UNION ALL
(
SELECT  *
FROM    table2, …
GROUP BY
        id
ORDER BY 
        NULL
)

但是，作为状态：

但是，对单个SELECT语句使用ORDER BY并不意味着行在最终结果中的出现顺序，因为UNION缺省情况下会生成一组无序的行。因此，在此上下文中使用ORDER BY通常与LIMIT结合使用，以便使用它来确定要为SELECT检索的所选行的子集，即使它不一定会影响最终UNION结果中这些行的顺序。如果在SELECT中出现没有LIMIT的ORDER BY，它将被优化，因为它无论如何都不会有任何效果。

这当然是明智之举，然而，并不是太聪明，因为他们忘记了优化GROUP BY的排序行为。

因此，就目前而言，您应该向单个查询添加一个非常高的LIMIT：

(
SELECT  *
FROM    table1, …
GROUP BY
        id
ORDER BY 
        NULL
LIMIT 100000000
)
UNION ALL
(
SELECT  *
FROM    table2, …
GROUP BY
        id
ORDER BY 
        NULL
LIMIT 100000000
)

我会把它作为一个bug发布到MySQL上，希望他们会在下一个版本中修复它，但同时你也可以使用这个解决方案。

注意，在SQL Server 2000中使用了一个类似的解决方案(使用TOP 100%)来强制对子查询进行排序，但是，它在2005中停止了工作(对于使用TOP 100%作为优化器的子查询，ORDER BY不起作用)。

使用它是安全的，因为它不会破坏您的查询，即使优化器行为在下一个版本中发生变化，但只会使它们变得像现在一样慢。

Answer 2

也许可以试试这样的东西

SELECT *
FROM
(
    [your entire query here]
) DerivedTable
ORDER BY NULL

我从来没有使用过MySQL，所以如果我错过了这个情节，请原谅:)

编辑:如果您单独运行每个单独的查询(正如您所说的，这是有效的)，但将数据插入到一个临时表中，会发生什么情况？然后，在最后从临时表中执行select操作。

Answer 3

您是否尝试过将UNION更改为UNION ALL？

UNION尝试删除重复的行。为了做到这一点，它必须对中间结果进行排序，这些中间结果可能解释您在执行计划中看到的内容。

来自MySQL Union

默认情况下，UNION联合会从结果集中删除所有重复的行，即使您没有在关键字UNION之后显式地使用DISTINCT。

如果显式使用UNION ALL，则重复的行将保留在结果集中。仅在希望保留重复行或确定结果集中没有重复行的情况下才使用此方法。

编辑

我怀疑这会有什么不同(甚至可能更糟)，但你能尝试一下“等价”的查询吗？

select  *
from    (
          select  b.geo_boundary_id      
                  , GeoBoundaryContains( c.tpi_geo_boundary_coverage_type_id, 29.287437, -95.055807, b.centroid_lat, b.centoid_lon, b.radius ) in_out      
          from    geo_boundary b 
                  join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id 
          where   b.geo_boundary_type_id = 2  
          group by
                  b.geo_boundary_id 
          union all        
          select  a.geo_boundary_id 
                  , GeoBoundaryContains(c.tpi_geo_boundary_coverage_type_id, 29.287437, -95.055807, a.lat, a.lon, a.geo_boundary_vertex_id ) in_out 
          from    geo_boundary_vertex a 
                  join geo_boundary b on b.geo_boundary_id = a.geo_boundary_id 
                  join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id 
          where   b.geo_boundary_type_id = 3  
          group by 
                  a.geo_boundary_id 
        ) s
        inner join (                  
          select  e.inquiry_match_type_id 
                  , e.inquiry_id 
                  , e.external_id 
                  , COALESCE(f.inquiry_device_id,0) inquiry_device_id 
                  , b.external_info1 
                  , b.external_info2 
                  , b.geo_boundary_id 
                  , b.geo_boundary_type_id 
          from    geo_boundary b 
                  join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id 
                  join trackpoint_index_mem d on d.trackpoint_index_id = c.trackpoint_index_id 
                  join inquiry_mem e on e.inquiry_id = b.inquiry_id left 
                  outer join inquiry_device_mem f on f.inquiry_id = e.inquiry_id and f.device_id = 3201 
          where   d.trackpoint_index_id = 3127  
                  and b.geo_boundary_type_id IN (2, 3)
                  and e.expiration_date >= now() 
        ) r on r.geo_boundary_id = s.geo_boundary_id