SQL优化需求：查找员工30英里范围内的英国邮政编码

现有两张SQL表：

• [UK Post Code]：包含270万条记录，字段为Post Code、Longitude、Latitude
• Employee：包含200条记录，字段为Employee Name、Post Code、Longitude、Latitude

需求是查询每个员工30英里范围内的所有[UK Post Code]邮政编码，当前使用的SQL语句运行极慢，需优化方案。

原SQL语句 #

SELECT DISTINCT 
    e.name,
    e.postal_code AS employee_postcode,
    p.postcode AS covered_postcode,
    p.latitude AS covered_latitude,
    p.longitude AS covered_longitude
FROM
    Employee e
JOIN
    [UK Post Code] p ON 3959 * ACOS(
        COS(RADIANS(e.latitude)) * COS(RADIANS(p.latitude)) * COS(RADIANS(CONVERT(DECIMAL(9,6), e.longitude) - CONVERT(DECIMAL(9,6), p.longitude))) +
        SIN(RADIANS(e.latitude)) * SIN(RADIANS(p.latitude))
    ) <= 30

期望结果示例 #

完整邮政编码结果 #

仅外码（outward code）结果 #

优化方案 #

1. 先做边界预筛选，减少计算量 #

通过经纬度的大致范围先过滤掉明显超出30英里的记录，再执行精确距离计算，避免对全表做复杂三角函数运算：

• 纬度：1度≈69英里，30英里≈0.4348度
• 经度：英国纬度区间（50-60度）内，1度≈43-53英里，保守取30英里≈0.7度

优化后SQL：

SELECT DISTINCT 
    e.name,
    e.postal_code AS employee_postcode,
    p.postcode AS covered_postcode,
    p.latitude AS covered_latitude,
    p.longitude AS covered_longitude
FROM
    Employee e
JOIN
    [UK Post Code] p 
    ON p.latitude BETWEEN e.latitude - 0.4348 AND e.latitude + 0.4348
    AND p.longitude BETWEEN e.longitude - 0.7 AND e.longitude + 0.7
    AND 3959 * ACOS(
        COS(RADIANS(e.latitude)) * COS(RADIANS(p.latitude)) * COS(RADIANS(e.longitude - p.longitude)) +
        SIN(RADIANS(e.latitude)) * SIN(RADIANS(p.latitude))
    ) <= 30

注：移除了不必要的CONVERT(DECIMAL(9,6))，若字段本身为数值类型可直接计算，减少类型转换开销

2. 利用空间索引加速查询 #

如果数据库支持空间类型（如SQL Server的GEOGRAPHY、MySQL的SPATIAL），将经纬度转换为空间字段并创建索引，能大幅提升距离查询效率：

以SQL Server为例：

-- 为UK Post Code表添加地理字段并创建空间索引
ALTER TABLE [UK Post Code] ADD geo_location AS GEOGRAPHY::Point(Latitude, Longitude, 4326) PERSISTED;
CREATE SPATIAL INDEX idx_postcode_geo ON [UK Post Code](geo_location);

-- 为Employee表添加地理字段
ALTER TABLE Employee ADD geo_location AS GEOGRAPHY::Point(Latitude, Longitude, 4326) PERSISTED;

空间函数查询SQL：

SELECT DISTINCT 
    e.name,
    e.postal_code AS employee_postcode,
    p.postcode AS covered_postcode,
    p.latitude AS covered_latitude,
    p.longitude AS covered_longitude
FROM
    Employee e
JOIN
    [UK Post Code] p 
    ON e.geo_location.STDistance(p.geo_location) <= 30 * 1609.34 -- 转换为米（STDistance返回单位为米）

3. 预计算外码分组，简化查询（仅外码需求场景） #

如果只需要外码结果，可先对[UK Post Code]按外码分组，计算每组的平均经纬度，再和员工经纬度计算距离，避免重复计算同一外码下的所有邮编：

WITH PostcodeOutward AS (
    SELECT 
        LEFT(postcode, CHARINDEX(' ', postcode) - 1) AS outward_code,
        AVG(latitude) AS avg_lat,
        AVG(longitude) AS avg_lon
    FROM [UK Post Code]
    GROUP BY LEFT(postcode, CHARINDEX(' ', postcode) - 1)
)
SELECT DISTINCT
    e.name,
    LEFT(e.postal_code, CHARINDEX(' ', e.postal_code) - 1) AS employee_outward_code,
    po.outward_code AS covered_outward_code
FROM Employee e
JOIN PostcodeOutward po
    ON 3959 * ACOS(
        COS(RADIANS(e.latitude)) * COS(RADIANS(po.avg_lat)) * COS(RADIANS(e.longitude - po.avg_lon)) +
        SIN(RADIANS(e.latitude)) * SIN(RADIANS(po.avg_lat))
    ) <= 30

注：此方法为近似结果，适合不需要精确到单个邮编的场景

4. 分批处理员工数据 #

将200个员工分成小批次查询，避免一次性关联大表导致资源过载，示例如下：

WITH EmployeeBatch AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY name) AS row_num
    FROM Employee
)
SELECT DISTINCT 
    e.name,
    e.postal_code AS employee_postcode,
    p.postcode AS covered_postcode,
    p.latitude AS covered_latitude,
    p.longitude AS covered_longitude
FROM
    EmployeeBatch e
JOIN
    [UK Post Code] p 
    ON p.latitude BETWEEN e.latitude - 0.4348 AND e.latitude + 0.4348
    AND p.longitude BETWEEN e.longitude - 0.7 AND e.longitude + 0.7
    AND 3959 * ACOS(
        COS(RADIANS(e.latitude)) * COS(RADIANS(p.latitude)) * COS(RADIANS(e.longitude - p.longitude)) +
        SIN(RADIANS(e.latitude)) * SIN(RADIANS(p.latitude))
    ) <= 30
WHERE e.row_num BETWEEN 1 AND 20 -- 每次调整批次范围

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