结合BTREE和地理/空间索引以加快查询速度？

2023-11-11 13:38:52

我有一个带有500,000行的mysql myisam表.在此表中,我提供了不同类型的地点和纬度信息.经度坐标.根据用户的不同,我想在由纬度和经度定义的点的一定距离内选择某种地点.

我有一个空间索引和一个关于纬度,经度,类型的多列索引.如果特定区域内的行数不是太大,则itsel上的那些索引会很好地工作.

问题是在某些情况下,我需要从某个点(由纬度,经度坐标定义)开始使用很大的半径,因为所需类型的位置很少.但是问题是当我搜索某种类型时,说“ x”,因为我的半径很大,mysql说“ x”会搜索大约20,000行,说“ 200 km”.但是,在现实世界中,距某点200公里之内只有5个类型为“ x”的地方.

我读到某处BTREE和SPATIAL索引无法合并.但是,我想寻求一个解决方案,使我能够根据纬度,经度和类型的输入非常快速地选择这5个地方.

我尝试了以下2种方法：

方法1-空间索引：

SELECT * FROM destinations 
WHERE MBRWithin(lat_lng_point, GeomFromText('Polygon((49.8413216059 12.8478000082, 48.0426783941 12.8478000082, 48.0426783941 15.5861999918, 49.8413216059 15.5861999918, 49.8413216059 12.8478000082))')) 
AND destinations.type = 'x'

方法2-关于纬度,经度的多列索引,输入：

SELECT * FROM destinations FORCE INDEX (lat_long_type_main)
WHERE latitude > 49.7786783941 AND latitude < 51.5773216059 
AND longitude > 10.0927907742 AND longitude < 12.9312092258 
AND type = 'x'

方法1仍然比方法2快得多,因为它们分别需要2到5秒.同样,使用第二种方法扫描(通过使用explain)的行数比第一种方法大.

使用方法1和方法2时,说明中的行数恰好是地理坐标在指定区域内的行数,并舍弃了该类型.我可以理解,对于方法1,类型不在索引中,但是对于方法2,我不希望对类型进行大表扫描,因为类型在索引中.

如果我可以使用纬度,经度和类型索引创建一个直接返回5点的索引,我希望此查询会更快.

由于我有许多此类查询,因此加快它们的速度非常重要.非常感谢您的帮助.

解决方法:

如果您只需要边界矩形搜索,则空间索引将产生最佳性能.

但这不是您所需要的.我相信,您需要在类型列和纬度/经度边界框范围内搜索某个单一值.无法创建具有空间成分并索引其他列的复合索引.

将FLOAT或DOUBLE数据用于纬度和经度

将FLOAT或DOUBLE数据类型用于纬度和经度列,以加快搜索速度. FLOAT对于GPS分辨率的地点查找器应用具有足够的精度. DOUBLE也可以正常工作.由于FLOAT数据项每个占用四个字节,而DOUBLE则占用八个字节,因此您会发现FLOAT的查找速度稍快.但这是一个微不足道的改进.

您可以将DECIMAL(8,4)或类似的数据类型用于经/纬度.但是FLOAT同样出色,而且速度明显更快.

如果lat / long值位于varchar()列中,则结果会出错或查询速度非常慢,因为范围扫描操作将无法正常进行.

使用复合BTREE索引

为此,我相信您最好的解决方案是在(类型,纬度,经度)上创建复合BTREE索引. MySQL将使用您指定的类型值和所需的下限纬度值随机访问此索引,然后对索引进行范围扫描,直到到达上限纬度.

索引范围扫描的说明

这是一个解释.可以随机访问BTREE索引以查找特定值,或者从任何起点按顺序访问以寻找下一个值.这是一个例子.假设您在名为data的列上有一个索引,并且其中包含具有值的行

如果您在4和9之间指定WHERE数据,MySQL将随机访问索引到第一个大于或等于4的值,然后顺序访问它,直到它获取到最后一个小于或等于9的值.这称为a范围扫描,看起来像这样.

 1
 2
 3
 5    <-- random access to here.
 5    <-- scan to here
 6    <-- ... and here
 8    <-- ... and here
 9    <-- ... and here
11    <-- stop scanning right before this row.

此扫描速度非常快.

复合索引范围扫描的说明

现在,让我们考虑您的问题中有关类型和纬度的复合索引.该索引中可能包含这些值.

type  latitude
 a    49.5
 a    49.8
 a    49.9 
 a    52.0
 b    58.3
 x    49.5
 x    49.8   <-- random access to here 
 x    51.2   <-- ... scan to here
 x    51.8   <-- stop scanning right before this row
 y    49.0
 y    49.5

看起来像WHERE type =’x’并且纬度介于49.7和51.5之间的查询可以使用相同的范围扫描技巧.它寻找要捕获的第一行,然后扫描到最后一行.复合索引中列的顺序很重要,因为顺序排序是在列值的串联上进行的.

查找单个类型的经/纬度位置

您可以在问题中使用第二个查询或其他查询,以利用我建议的索引.

SELECT * 
  FROM destinations
 WHERE latitude  BETWEEN 49.7786783941 AND 51.5773216059 
   AND longitude BETWEEN 10.0927907742 AND 12.9312092258 
   AND type = 'x'

我不确定使用索引中包含的经度是否更好.值得尝试.

通过避免选择来提高性能*

专家提示：避免在此类查询中使用SELECT *.如果枚举查询中需要的字段,则可以创建一个可以直接满足查询要求的覆盖索引.那将非常快.例如,如果您的查询是

SELECT airport_code, name, latitude, longitude
  FROM destinations
 WHERE latitude  BETWEEN 49.7786783941 AND 51.5773216059 
   AND longitude BETWEEN 10.0927907742 AND 12.9312092258 
   AND type = 'x'

然后,可以通过对此复合BTREE索引进行范围扫描来直接满足您的查询.

(type, latitude, longitude, airport_code, name)

注意：创建BTREE索引不需要做任何特殊的事情.这是默认值.

不要夸大您的经/纬度精度

专家提示：您可能会以51.5773216059之类的精度给出坐标来欺骗自己.这显然是大约11微米的精度. GPS仅能达到约5米,而且地球的形状不是球形,可导致基于纬度的简单距离计算在同一水平上分解.

编辑我刚刚对我的邮政编码测试数据进行了实验,创建复合索引有很大帮助.

码农公寓

相关文章