MySQL按经纬度距离排序取前3条 现有查询结果不符求优化方案

• 直接拼接经纬度参数到SQL中，存在SQL注入风险，同时参数格式异常时会直接导致SQL执行出错，返回结果异常
• 距离计算使用的69.1是英里单位系数，计算出的distance单位为英里，如果你业务预期的筛选阈值25是公里单位，会导致实际筛选范围远小于预期，自然匹配到的结果少甚至无结果
• 经度差权重计算时的COS函数错误使用了表存储的纬度字段，会导致部分场景下距离计算偏差
• 没有添加LIMIT 3语法，不符合返回前3条的需求
• 数值类型的经纬度参数拼接时加了单引号，可能触发隐式类型转换，导致计算逻辑异常

建议使用参数化查询避免SQL注入，同时修正计算逻辑和单位问题，示例代码如下：

var latitude = parseFloat(K.params.latitude)
var longitude = parseFloat(K.params.longitude)
// 单位配置：如果需要用公里作为距离单位，把系数改成111.19即可
const DISTANCE_UNIT_COEFFICIENT = 69.1 
const MAX_DISTANCE = 25
const RESULT_LIMIT = 3

// SQL用占位符传参，避免直接拼接
var sqlString = `
SELECT name, latitude, longitude, 
    SQRT(
        POW(? * (latitude - ?), 2) +
        POW(? * (? - longitude) * COS(? / 57.3), 2)
    ) AS distance
FROM 10561_12865_tblEvents 
HAVING distance < ? 
ORDER BY distance 
LIMIT ?
`
// 按顺序传入参数
var results = K.query(sqlString, [
  DISTANCE_UNIT_COEFFICIENT,
  latitude,
  DISTANCE_UNIT_COEFFICIENT,
  longitude,
  latitude,
  MAX_DISTANCE,
  RESULT_LIMIT
])

如果表内数据量超过1000条，建议增加经纬度范围前置过滤，减少全表距离计算的开销，大幅提升查询效率：

先根据阈值计算出经纬度的大致筛选范围，用WHERE子句先过滤掉明显超出范围的条目，再做精确距离计算
优化后的SQL示例：

SELECT name, latitude, longitude, 
    SQRT(
        POW(? * (latitude - ?), 2) +
        POW(? * (? - longitude) * COS(? / 57.3), 2)
    ) AS distance
FROM 10561_12865_tblEvents 
WHERE 
    latitude BETWEEN ? - (?/? ) AND ? + (?/? )
    AND longitude BETWEEN ? - (?/(? * COS(?/57.3))) AND ? + (?/(? * COS(?/57.3)))
HAVING distance < ? 
ORDER BY distance 
LIMIT 3

对应传参顺序：

• DISTANCE_UNIT_COEFFICIENT
• latitude
• DISTANCE_UNIT_COEFFICIENT
• longitude
• latitude
• latitude, MAX_DISTANCE, DISTANCE_UNIT_COEFFICIENT, latitude, MAX_DISTANCE, DISTANCE_UNIT_COEFFICIENT
• longitude, MAX_DISTANCE, DISTANCE_UNIT_COEFFICIENT, latitude, longitude, MAX_DISTANCE, DISTANCE_UNIT_COEFFICIENT, latitude
• MAX_DISTANCE

内容的提问来源于stack exchange，提问作者innom