我有一个大型MySQL表,其中包含+250.000000行,我在其中存储不同类型的点和坐标。该表包含以下列:
NodeId Lat-Lon
到目前为止,Lat和Lon字段都是FLOAT(10,7)类型,但我正在尝试将它们更改为INT,因为查询这个表可能非常慢,即使我已经将Lat/Lon列和Lon列一起索引,并且单独索引了它们。
如果我没有记错的话,MySQL在搜索时只使用一个索引。
我想将这两列更改为Integer的原因是,例如,我会按Lat对表进行分区,并按Lon对其进行索引,以加快搜索速度。
基本上,我有两个问题正在寻找答案:
1.这种方法会让我的边界框查询更快吗?
2.将纬度和经度坐标存储为整数值有什么缺点吗?
编辑:我没有提到的是,原始FLOAT值在存储为整数之前要乘以10.000.000。
您不太可能从中获得搜索性能的提高
- 对表进行分区或
- 将lat/lon的数据类型从FLOAT更改为INTEGER
为什么不呢?
- FLOAT和INTEGER存储的数据量相同:32位
- FLOAT为GPS分辨率数据提供了足够的精度。如果您知道并关心UTM和Lambert投影之间的差异,请使用DOUBLE
- 索引范围搜索适用于FLOAT、DOUBLE和INTEGER
- 如果对表进行分区,则需要做额外的工作来防止索引范围搜索碰到很多分区。命中大量分区会使搜索速度变慢
如果你在一个特定的纬度/经度边界框中寻找点,那么在MySQL中会是这样的:
SET @radius := 50; /* 50km */
SET @units := 111.045l /* kilometers per degree */
SET @lat := 40.7484;
SET @lon := ,-73.9857;
SELECT ...
WHERE table.latitude
BETWEEN @lat - (@radius / @units )
AND @lat + (@radius / @units )
AND table.longitue
BETWEEN @lon - (@radius / @units * COS(RADIANS(@lat))))
AND @lon + (@radius / @units * COS(RADIANS(@lat))))
请注意,这采用的形式
table.latitude BETWEEN constant AND constant
AND table.longitude BETWEEN constant AND constant
这两项中的第一项是对latitude
列进行直接的范围扫描。如果它被索引,即使它是FLOAT
数据类型,也很快。(latitude, longitude)
上的复合索引应该很好,尤其是如果您可以保持较小的搜索半径。
现在,有一个复杂的问题。有了25亿个点,您的查询可能会做这样的事情。
table.point_type = constant
AND table.latitude BETWEEN constant AND constant
AND table.longitude BETWEEN constant AND constant
在这种情况下,您需要(point_type, latitude, longitude)
上的复合索引,这样查询就可以做正确的事情。对于这样大小的表,您确实需要了解查询才能正确获取索引。
最后,根据您的点数,您可以考虑使用MySQL的地理空间扩展进行位置搜索。上面写着。http://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/但是,当其中一个元素是地理空间时,不能创建复合索引。
这将绕过所有其他技术。然而,这需要一些准备工作:http://mysql.rjweb.org/doc.php/latlng
正如该博客所指出的,在您的情况下,乘以10000并存储在MEDIUMINT中可以节省500MB。分辨率为16米/52英尺。如果您需要更高的分辨率,则建议使用INT解决方案(16mm/<1英寸)。浮动,没有不必要的(10,7)给你1.7米/5.6英尺。