mysql - 限价/订单动态字段的慢速 SQL 查询(从 X 点开始的坐标)

Question

我正在尝试对一个包含 700 万条记录的数据库进行 SQL 查询，数据库“geonames”的“纬度”和“经度”都以十进制 (10.7) 为索引，问题是查询太长了慢:

SELECT SQL_NO_CACHE DISTINCT 
       geonameid, 
       name, 
       (6367.41 * SQRT(2 * (1-Cos(RADIANS(latitude)) * Cos(0.704231626533) * (Sin(RADIANS(longitude))*Sin(-0.0669560660943) + Cos(RADIANS(longitude)) * Cos(-0.0669560660943)) - Sin(RADIANS(latitude)) * Sin(0.704231626533)))) AS Distance 
  FROM geoNames 
 WHERE (6367.41 * SQRT(2 * (1 - Cos(RADIANS(latitude)) * Cos(0.704231626533) * (Sin(RADIANS(longitude)) * Sin(-0.0669560660943) + cos(RADIANS(longitude)) * Cos(-0.0669560660943)) - Sin(RADIANS(latitude)) * Sin(0.704231626533))) <= '10') 
ORDER BY Distance

问题是按“距离”字段排序，动态创建时需要很长时间才能渗透到条件“WHERE”，如果我删除条件“WHERE ... <= 10”只需要 0.34 秒，但结果是 700 万条记录，将数据从 MySQL 传输到 PHP 需要将近 120 秒。

假设查询会经常更​​改值，您能否想出任何方法来通过限制距离字段使查询不损失性能？

Answer 1

这种查询不能使用索引，但必须计算每一行 的纬度/经度是否在指定距离内。因此，通常会使用某种形式的预处理来将扫描限制在行的子集内。您可以创建与距离“范围”(2、5、8、10、20 英里/公里——任何对您的应用程序要求有意义的)相对应的表格，然后填充这些范围并使它们保持最新。如果您只需要距离给定位置 10 英里以内的医疗服务提供者，例如酒店或其他任何东西，则无需担心数百或数千英里以外的医疗服务提供者。通过即席查询，您可以在“10 英里以内”范围内加入内部连接，例如，从而从比较扫描中排除计算距离 > 10 的所有行。当位置发生变化时，“优雅”的处理方式是实现一个 RTREE，但是如果您有权访问其他数据，您可以以任何您喜欢的方式定义您的包围区域——例如通过使用邮政编码或县或州。