mysql - 使用 PHP/Laravel 从 MySQL/MariaDB 获取所有 POI 的方法哪种更快

Question

如果我错了，请纠正我。

用户在我的网站上创建了三种获取最近房屋的方法:

1. 创建一个包含两列(纬度、经度)且均为 float 的表:

这里是:

$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 表示 1km。

在这种方法中，我们还可以使用 harvesine 公式。

1. 将这些列(纬度、经度)合并到一个名为 point 的列中，并再次逐行搜索。

2. 要将多个点(用户创建的房屋坐标)分类为一个国家/地区的一个类别，即城市，如果查询带有 $latitude 和 $longitude 以查看最近的房屋，我将检查它们存储在哪个类别中，以便不搜索所有行，而是仅搜索此查询(坐标)所属的部分。

我猜第 1 种方法很慢，因为每行表的条件，如果我使用 harvesine 公式，它又很慢。

如果我使用 ST_Distance，它似乎又很慢，因为它又需要大量计算。

但如果我使用方法 3，似乎检查每个部分的特定点用户比检查所有行更快。我知道如何为每个家庭设置点，但我不知道如何在另一个表中创建多个家庭位置作为一个部分。

顺便说一句，InnoDB 支持新版本的 MySQL 和 MariaDB 空间索引。

我的问题:

1. 方法 1 是否真的很慢，或者其他 ST_* 函数是否与此方法相同，以使用其中提到的那些公式一一检查所有行？哪个更快？

2. 除了简单的条件之外，方法 2 是否还做了其他事情来使其更快？我的意思是，当使用 POINT 类型而不是 float 并使用 ST_* 函数而不是自己做时，它会做出任何改变吗？我想知道算法是否不同。

3. 如果方法 3 在这三种方法中最快，我如何对点进行分类才能不搜索表中的所有行？

4. 如何使用空间索引使其尽可能快？

5. 如果存在任何其他方法但我没有提到，请问我如何仅通过在 PHP/Laravel 中的 MySQL/MariaDB 中获取坐标来获得最近的房屋？

谢谢大家

Answer 1

您使用哪个公式计算距离并不重要。更重要的是您必须读取、处理和排序的行数。在最好的情况下，您可以为 WHERE 子句中的条件使用索引来限制处理的行数。你可以尝试对你的位置进行分类——但这取决于你的数据的性质，如果这能很好地工作的话。您还需要找出要使用的“类别”。更通用的解决方案是使用 SPATIAL INDEX 和 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

数据来自Free World Cities Database并包含 3173958 (3.1M) 行。

请注意，geoPoint 是多余的，等于 POINT(longitude, latitude)。

假设用户位于伦敦某处

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

并且您想从 cities 表中找到最近的位置。

“微不足道”的查询是

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 列，它可能会更快。但那仍然没有没什么大不了的，因为您不希望用户等待，所以如果可以做得更好，请登录以获取响应。

现在让我们看看如何将 SPATIAL INDEX 与 ST_Within() 一起使用。

您需要定义一个包含最近位置的多边形。一种简单的方法是使用 ST_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 是以度数表示的，因此多边形更像是椭圆而不是圆形。但是在我的测试中，我总是得到与简单而缓慢的解决方案相同的结果。在我的生产代码中使用它之前，我会调查更多的边缘情况。

现在您需要为您的应用程序/数据找到最佳半径。如果它太小 - 你可能得不到结果，或者错过最近的点。如果它太大 - 您可能需要处理太多行。

这里是给定测试用例的一些数字:

• @radius = 0.001: 没有结果
• @radius = 0.01:只有一个位置(有点幸运)- 执行时间~ 0.000 秒
• @radius = 0.1:55 个位置 - 执行时间 ~ 0.000 秒
• @radius = 1.0:2183 个位置 - 执行时间 ~ 0.030 秒