mysql union null,mysql – 在UNION中使用Order By NULL

我有一个查询(见下文),我有一个自定义开发的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

当我为查询运行解释时,我得到以下内容：

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 ALL (null) (null) (null) (null) (null) Using filesort

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

现在,我可以拆分查询并使用ORDER BY NULL技巧来删除文件,但是当我尝试将它添加到UNION的末尾时它不起作用.

我正在考虑将查询分成2个查询或者可能完全重写它不使用UNION(当然这有点困难).我反对的另一件事就是我们已经开始使用它了,我想限制更改 – 我本来希望能够在查询结束时添加ORDER BY NULL并完成它,但是它与UNION不起作用.

任何帮助将不胜感激.

最佳答案:

通常,ORDER BY可以用于UNION中的各个查询,如下所示：

(

SELECT *

FROM table1, …

GROUP BY

id

ORDER BY

NULL

)

UNION ALL

(

SELECT *

FROM table2, …

GROUP BY

id

ORDER BY

NULL

)

但是,作为docs状态：

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

这当然是一个聪明的举动,但不是太聪明,因为他们忘了优化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

)

我将它作为一个错误发布到MySQL,希望他们能在下一个版本中修复它,但同时你可以使用这个解决方案.

请注意,类似的解决方案(使用TOP 100％)用于强制SQL Server 2000中的子查询的排序,但是,它在2005年停止工作(ORDER BY对于优化器的TOP 100％的子查询没有影响).

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

标签：mysql,sql

来源： https://codeday.me/bug/20190515/1109348.html