mysql - WHERE 子句中的多个 OR 条件

Question

我面临的情况是，我需要收集 100m 平方距离内的某些经纬度数据点的数据。我目前正在运行如下查询，该查询适用于较少的位置。下面针对 15 个数据点的查询大约需要 10 分钟才能运行，但这种方法无法随着更多数据点而扩展。我使用 4000 个经纬度数据点(与美国 map 上的 4000 个位置相关)运行了类似的查询，该查询需要运行 30 个小时。我知道 where 语句逐行扫描整个表，这就是查询运行非常长的原因。即使我选择较少的所需列，查询也需要很长时间才能运行。你们中有人有更好的方法来实现这一目标吗？请指教。

            create table crt1
as
select * from masterdata
where
(round(device_lat,4) >= 33.7306 and round(device_lat , 4)  <= 33.7316 and round(device_lon,4) >= -117.8364 and round(device_lon , 4) <= -117.8354) or 

(round(device_lat,4) >= 37.927 and round(device_lat , 4)  <= 37.928 and round(device_lon,4) >= -122.517 and round(device_lon , 4) <= -122.516) or 

(round(device_lat,4) >= 30.2711 and round(device_lat , 4)  <= 30.2721 and round(device_lon,4) >= -97.7544 and round(device_lon , 4) <= -97.7534) or 

(round(device_lat,4) >= 33.0673 and round(device_lat , 4)  <= 33.0683 and round(device_lon,4) >= -117.2642 and round(device_lon , 4) <= -117.2632) or 

(round(device_lat,4) >= 34.8271 and round(device_lat , 4)  <= 34.8281 and round(device_lon,4) >= -82.3011 and round(device_lon , 4) <= -82.3001) or 

(round(device_lat,4) >= 32.9258 and round(device_lat , 4)  <= 32.9268 and round(device_lon,4) >= -96.8311 and round(device_lon , 4) <= -96.8301) or 

(round(device_lat,4) >= 45.0917 and round(device_lat , 4)  <= 45.0927 and round(device_lon,4) >= -93.4272 and round(device_lon , 4) <= -93.4262) or 

(round(device_lat,4) >= 36.0214 and round(device_lat , 4)  <= 36.0224 and round(device_lon,4) >= -115.0853 and round(device_lon , 4) <= -115.0843) or 

(round(device_lat,4) >= 47.2156 and round(device_lat , 4)  <= 47.2166 and round(device_lon,4) >= -122.2351 and round(device_lon , 4) <= -122.2341) or 

(round(device_lat,4) >= 32.2492 and round(device_lat , 4)  <= 32.2502 and round(device_lon,4) >= -110.8845 and round(device_lon , 4) <= -110.8835) or 

(round(device_lat,4) >= 32.286 and round(device_lat , 4)   <= 32.287 and round(device_lon,4) >= -110.9753 and round(device_lon , 4) <= -110.9743) or 

(round(device_lat,4) >= 36.8477 and round(device_lat , 4)  <= 36.8487 and round(device_lon,4) >= -119.7911 and round(device_lon , 4) <= -119.7901) or 

(round(device_lat,4) >= 36.0842 and round(device_lat , 4)  <= 36.0852 and round(device_lon,4) >= -79.8363 and round(device_lon , 4) <= -79.8353) or 

(round(device_lat,4) >= 39.0612 and round(device_lat , 4)  <= 39.0622 and round(device_lon,4) >= -77.1245 and round(device_lon , 4) <= -77.1235) or 

(round(device_lat,4) >= 32.8389 and round(device_lat , 4)  <= 32.8399 and round(device_lon,4) >= -117.1629 and round(device_lon , 4) <= -117.1619) or 

(round(device_lat,4) >= 61.1948 and round(device_lat , 4)  <= 61.1958 and round(device_lon,4) >= -149.9061 and round(device_lon , 4) <= -149.9051);

Answer 1

首先:在 masterdata(device_lat) 上创建一个索引，在 masterdata(device_lon) 上创建另一个索引

其次，将此查询的每一行重新构造为:

(device_lat >= 32.8389 and device_lat <= 32.8399 and 
 device_lon >= -117.1629 and device_lon <= -117.1619)  or ...

您对round(lat,4)的使用已经破坏了您使用索引进行搜索的能力，这确实使其非常慢:它必须多次扫描您的表。

如果您碰巧正在处理 GPS 数据，或者您碰巧使用球形地球近似值来计算距离，则 round() 函数不会为您带来任何精度。全局位置的实际精度约为小数点后四位，更多位数的精度既不会帮助也不会损害您的准确性。

如果您理解通用横轴墨卡托投影或兰伯特投影等术语，那么您实际上对数据精度的了解比您的问题所表明的要多得多，并且您可能应该使用这些知识。

实际上，说实话，你应该像这样重铸:

 SELECT m.* 
   FROM masterdata AS m
   JOIN (
        SELECT radius AS 0.0005
        ) AS radius
   JOIN  (  /* make a virtual table of your bunch of centerpoints */
        SELECT 33.7311 AS lat, -117.8359 AS long
        UNION ALL
        SELECT 37.9275, -122.5165
        UNION ALL
        SELECT somelat, somelon
        UNION ALL ...
   ) AS points
     ON m.device_lat >= points.lat - radius
    AND m.device_lat <= points.lat + radius
    AND m.device_long >= points.long - (radius / COS(RADIANS(points.lat)))
    AND m.device_long <= points.long + (radius / COS(RADIANS(points.lat)))

这将尽可能高效地为您带来良好的结果。它将调整经度搜索的半径值，以纠正经度线远离赤道的距离更近的事实。它可以让 MySQL 进行优化。

编辑

我刚刚注意到您的 100 平方米要求，我将其解释为地面上 +/- 50 m 的边界框。 (您已接近此处的准确度极限。)

纬度为 111045 米，因此您需要一个半径值 (50.0/111045.0)，该值恰好约为 0.0004503。您在问题中显示的值 0.0005 更像是 111 平方米。

这里有一些背景知识。 http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/