使用php/laravel从mysql/mariadb获取所有poi，哪种方法更快

relj7zay 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(495)

如果我错了就纠正我。
用户在我的网站上创建了三种获取最近房屋的方法：
要创建一个包含两列（纬度、经度）且两列都是浮动的表，请执行以下操作：
在这里：

$latitude = 50;
$longitude = 60;

SELECT * FROM my_table
    WHERE (latitude  <= $latitude+10  AND latitude  >= $latitude-10)
      AND (longitude <= $longitude+10 AND longitude >= $longitude-10)

这里的10表示1公里。
在这种方法中，我们还可以使用harvesine公式。
将这些列（纬度、经度）合并到一个名为point as point type的列中，然后逐个搜索每一行。
要将多个点（用户创建的家庭坐标）分类为一个国家（即城市）的一个部分的类别，如果查询带有$纬度和$经度以查看最近的家庭，我将检查它们存储在哪个类别中，以便不搜索所有行，而只搜索此查询（坐标）所属的部分。
我猜方法1是慢的，因为表的每一行都有条件，如果我使用harvesine公式，速度也慢。
如果我用stèu距离，它似乎又慢了，因为它只是有很多计算。
但是如果我使用第3种方法，那么检查每个特定点用户的每个部分似乎比检查所有行要快。我知道如何为每个家设定点，但我不知道如何创建多个家的立场，作为一个部分可能在另一个表。
顺便说一句，innodb支持新版mysql和mariadb空间索引。
我的问题：
方法1真的很慢吗？或者其他函数与此方法相同，用这些公式逐个检查所有行？哪一个更快？
2号方法除了简单的条件外，是否还有别的作用使其更快？我的意思是，当使用点的类型而不是浮点数，或者使用st函数而不是自己做的时候，它有什么变化吗？我想知道算法是否不同。
如果3号方法是这三种方法中最快的方法，我如何对点进行分类以避免搜索表中的所有行？
如何使用空间索引使其尽可能快？
如果有其他的方法存在，我没有提到，你能告诉我如何通过在mysql/mariadb中的php/laravel中的坐标得到最近的家吗？
谢谢大家

mysql mariadb laravel spatial point-of-interest

来源：https://stackoverflow.com/questions/51429997/which-approach-is-faster-for-getting-all-pois-from-mysql-mariadb-with-php-larave

2条答案

按热度按时间

mf98qq941#

你用哪个公式来计算距离并不重要。更重要的是要读取、处理和排序的行数。在最好的情况下，可以在where子句中使用一个条件索引来限制处理的行数。您可以尝试对您的位置进行分类，但这取决于您的数据的性质，如果这样做效果良好的话。您还需要找出要使用的“类别”。更通用的解决方案是使用空间索引和st_within（）函数。
现在让我们做些测试。。
在我的数据库（mysql 5.7.18）中，我有下表：

CREATE TABLE `cities` (
    `cityId` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
    `country` CHAR(2) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `city` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `accentCity` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `region` CHAR(2) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `population` INT(10) UNSIGNED NULL DEFAULT NULL,
    `latitude` DECIMAL(10,7) NOT NULL,
    `longitude` DECIMAL(10,7) NOT NULL,
    `geoPoint` POINT NOT NULL,
    PRIMARY KEY (`cityId`),
    SPATIAL INDEX `geoPoint` (`geoPoint`)
) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB

数据来自自由世界城市数据库，包含3173958（310万）行。
请注意 geoPoint 是多余的，等于 POINT(longitude, latitude) .
concider用户位于伦敦的某个地方

set @lon = 0.0;
set @lat = 51.5;

你想找到离 cities table。
一个“琐碎”的查询

select c.cityId, c.accentCity, st_distance_sphere(c.geoPoint, point(@lon, @lat)) as dist
from cities c
order by dist
limit 1

结果是

988204 Blackwall 1085.8212159861014

执行时间：~4.970秒
如果你用不太复杂的函数 ST_Distance() ，您将得到相同的结果，执行时间约为4.580秒—差别不大。
请注意，您不需要在表中存储地理点。你可以好好利用 (point(c.longitude, c.latitude) 而不是 c.geoPoint . 令我惊讶的是，它甚至更快（大约3.6秒的时间） ST_Distance 约4.0秒 ST_Distance_Sphere ). 如果我没有一个 geoPoint 一点也不。但这仍然没有多大关系，因为如果您可以做得更好，您不希望用户等待响应。
现在让我们看看如何将空间索引与 ST_Within() .
您需要定义一个包含最近位置的多边形。一个简单的方法是使用st\u buffer（），它将生成一个具有32个点的多边形，几乎是一个圆*。

set @point = point(@lon, @lat);
set @radius = 0.1;
set @polygon = ST_Buffer(@point, @radius);

select c.cityId, c.accentCity, st_distance_sphere(c.geoPoint, point(@lon, @lat)) as dist
from cities c
where st_within(c.geoPoint, @polygon)
order by dist
limit 1

结果是一样的。执行时间约为0.000秒（这是我的客户机（heidisql）所说的）。

请注意 @radius 以度表示，因此多边形更像椭圆而不是圆。但在我的测试中，我总是得到与简单而缓慢的解决方案相同的结果。在我的产品代码中使用它之前，我会调查更多的边缘情况。

现在需要为应用程序/数据找到最佳半径。如果它太小-你可能得不到结果，或错过最近的点。如果太大-可能需要处理太多行。
以下是给定测试用例的一些数字：
@半径=0.001：无结果
@半径=0.01：正好一个位置（有点幸运）-执行时间~0.000秒
@半径=0.1:55个位置-执行时间~0.000秒
@半径=1.0:2183个位置-执行时间~ 0.030秒

赞(0）回复(0）举报 2021-06-20

chy5wohz2#

边界框和哈弗斯线
在你的简报里 SELECT ，您使用的是“边界框”方法，即在Map上绘制一个粗糙的正方形。然而，它也有一些缺陷。
50和60的单位大概是度；你说10是公里。你不能把它们混在一起而不转换其中一个。
经度比纬度短；一 cos() 要解决这个问题。
有了这些帮助，边界框可以显著地过滤行，然后可选的haversine测试可以绕过测试的范围。

INDEX(latitude)
INDEX(longitude)

这种方法具有“中等”性能——其中一个索引将与边界框一起使用，从而快速地将候选对象限制为全球范围内的东西（或南北）条纹。但这可能仍然是很多候选人。
通过过滤掉大部分行，haversine调用的数量并不算太差；不要担心函数的性能。
如果你有一百万个家庭，最后一个包含5个家庭的边界框（加上一些没有通过haversine检查的家庭）可能会涉及几千行——因为只使用了两个索引中的一个。这仍然比获取所有百万行并用距离函数检查每一行要好得多。
点和空间索引
切换到 POINT 需要切换到 SPATIAL 索引。在这种模式下， ST_Distance_Sphere() 可替代哈弗林(注意：该函数仅在最新版本中存在。）
通过过滤掉大部分行，调用 ST_Distance 或者 ST_Distance_Sphere 不算太糟；不要担心函数的性能。 SPATIAL 搜索使用r-树。我对他们在你的询问中的表现感觉不太好。
方法3
通过从另一个点分类开始，可以增加复杂性。还需要检查相邻区域以查看是否有附近的点。没有更多细节，我无法判断相对表现。
我的方法
我有一些复杂的代码，可以扩展到任意多个点。因为您的数据集可能足够小，可以缓存在ram中，所以对您来说可能太过繁琐了。http://mysql.rjweb.org/doc.php/latlng
对于只有一百万个家庭来说，上面的两个索引可能“足够好”，这样你就不需要求助于“我的算法”。我的算法只需要接触20行就可以得到所需的5行——不管行的总数是多少。
其他注意事项
如果同时储存lat/lng和 POINT ，table会很笨重；如果尝试混合边界框和 ST 功能。

赞(0）回复(0）举报 2021-06-20

我来回答

使用php/laravel从mysql/mariadb获取所有poi，哪种方法更快

2条答案

相关问题

热门标签

最新问答