Mysql - 选择用户在其个人距离范围内的纬度/经度位置记录

Question

我需要知道以下场景(Mysql)的数据库查询的最佳实践(最佳性能):

http://sqlfiddle.com/#!9/72191ca/1

我有一个带有纬度/经度位置的“起始项”(点“Key”，蓝点 lat:47.471630，lng: 8.297835 )。在用户表中，有用户(A、B、C 等)及其经纬度位置和个人范围(以公里为单位)。

我需要查询用户表以查找 key 在预定义范围/距离内的 id。

应该优化查询 - 将大约 40'000 个用户与“关键”纬度/经度位置进行比较。

这是我当前使用的查询。性能相当不错，但是是否有另一种可以使用索引的解决方案来做到这一点？

DROP TABLE IF EXISTS users;

CREATE TABLE `users` (
  `user_id` char(1) NOT NULL,
  `lat` decimal(8,5) NOT NULL DEFAULT '0.00000',
  `lng` decimal(8,5) DEFAULT '0.00000',
  `user_range_km` decimal(10,1) NOT NULL DEFAULT '1.0',
  PRIMARY KEY (`user_id`),
  KEY `lat` (`lat`,`lng`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `users` (`user_id`, `lat`, `lng`, `user_range_km`) VALUES
('A', '47.46911', '8.29560', '0.4'),
('B', '47.48169', '8.30264', '0.4'),
('C', '47.49261', '8.31598', '2.9');

SELECT h.*, ( 6371 * acos( cos( radians(47.471630) ) * cos(  radians( h.lat ) ) * cos( radians( h.lng ) - radians(8.297835) ) + sin( radians(47.471630) ) * sin( radians( h.lat ) ) ) ) AS distance 
FROM users h 
HAVING distance <= h.user_range_km;

+---------+----------+---------+---------------+------------------+
| user_id | lat      | lng     | user_range_km | distance         |
+---------+----------+---------+---------------+------------------+
| A       | 47.46911 | 8.29560 |           0.4 | 0.32671077638732 |
| C       | 47.49261 | 8.31598 |           2.9 |  2.7021411331883 |
+---------+----------+---------+---------------+------------------+

在我的示例中，A 和 C 在其定义的距离内具有 key ，因此我需要从查询中获取 A 和 C。请参阅 SQL Fiddle

Answer 1

有 5 种方法可以完成该任务。您的代码是其中之一，是最慢的代码。这是我对它们的讨论:http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

最简单的下一步是使用“边界框”技术。它涉及向 WHERE 添加 2 个子句和两个 INDEX。

围绕一个“ key ”的“多个用户”只是颠倒了角色。传统的问题是关于“用户”周围的“多个项目(企业、卡车等)”。围绕“键”构建边界框。

只有 4 万用户，边界框技术可能就足够了。

好吧，你又多了一条皱纹。尽管如此，BB 应该给你一个很好的第一过滤器。在您的示例中，使用

MAX(user_range_km) -- which is 2.9

作为 BB 的半径。 (或者是“边界正方形”宽度的一半，如评论中所述。)

然后，不要简单地根据 2.9 测试每个“距离”，而是根据 user_range_km 进行测试。