怎么利用 MySQL 存储 GEO 坐标数据优化网站地域查询

结论：MySQL 存 GEO 坐标，正确做法是用 DECIMAL(9,6) 存纬度经度（或用 SRID 4326 的 POINT 列），查询时先走「外接矩形」把候选集缩小，再用 Haversine 公式或 ST_Distance_Sphere() 算精确距离；绝对不要把距离计算函数直接写进 WHERE，那会让索引彻底失效。

GEO 坐标在 MySQL 里该用什么字段类型？

结论：纬度用 DECIMAL(9,6)、经度用 DECIMAL(9,6)，不要用 FLOAT。

FLOAT 只有约 7 位有效十进制数字，而 116.397428 本身就是 9 位，存进去会发生精度丢失，做范围比较时可能出现「明明该命中的点没命中」。DECIMAL(9,6) 精度是 1e-6 度，按地球平均半径 6371 km 换算，1 度纬度 ≈ 111.19 km，所以 1e-6 度 ≈ 0.111 米，远高于网站地域查询的需求。而且 DECIMAL(9,6) 只占 5 字节，比 DOUBLE 的 8 字节还省。

建表可以这样：

CREATE TABLE poi (
  id        BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name      VARCHAR(64) NOT NULL,
  latitude  DECIMAL(9,6) NOT NULL,   -- 纬度 -90 ~ 90
  longitude DECIMAL(9,6) NOT NULL,   -- 经度 -180 ~ 180
  KEY idx_lat_lng (latitude, longitude)
) ENGINE=InnoDB;

注意经度最大 180.000000 是 9 位数字，正好放得下 DECIMAL(9,6)。

什么叫「外接矩形 + 精确距离」两步查询法？

结论：先用矩形框把候选行压到千级，再对候选行算球面距离，这是中小站点性价比最高的方案。

Haversine（半正矢公式）是用经纬度计算球面上两点大圆距离的公式。直接对全表跑 Haversine，等于每行都做 4 次三角函数运算，百万行表必然超时。正确姿势是两步走：

第一步，算出半径 r 对应的高度差和宽度差。查 3 公里：

-- Δlat = 3 / 111.19 ≈ 0.0270
-- Δlng = 3 / (111.19 * COS(RADIANS(39.90923))) ≈ 0.0352

第二步，矩形过滤 + 精确距离一起写：

SELECT id, name, latitude, longitude
FROM poi
WHERE latitude  BETWEEN 39.90923 - 0.0270 AND 39.90923 + 0.0270
  AND longitude BETWEEN 116.397428 - 0.0352 AND 116.397428 + 0.0352
  AND 6371 * 2 * ASIN(SQRT(
        POWER(SIN(RADIANS(latitude - 39.90923) / 2), 2) +
        COS(RADIANS(39.90923)) * COS(RADIANS(latitude)) *
        POWER(SIN(RADIANS(longitude - 116.397428) / 2), 2)
      )) <= 3
ORDER BY latitude
LIMIT 20;

idx_lat_lng 会用上纬度那一段范围扫描，经度作为回表后的过滤条件。如果还想更快，可以加两列 grid_lat = FLOOR(latitude*100)、grid_lng = FLOOR(longitude*100)，建联合索引，把矩形覆盖的网格用 IN 列出来做等值匹配，避免范围扫描。

MySQL 8.0 的 POINT + 空间索引怎么用？

结论：数据量到百万级、且按距离查询很频繁时，用 POINT 列 + SPATIAL INDEX（空间索引，底层是 R-Tree）配合 ST_Distance_Sphere()。

ST_Distance_Sphere() 在 MySQL 5.7.6 引入，直接返回米，省掉手写 Haversine。SRID 4326 表示 WGS84 经纬度坐标系。

ALTER TABLE poi ADD COLUMN location POINT NOT NULL SRID 4326;
UPDATE poi SET location = ST_SRID(POINT(longitude, latitude), 4326);
ALTER TABLE poi ADD SPATIAL INDEX idx_location (location);

注意 POINT(x, y) 的第一个参数是经度、第二个是纬度，写反了是新手最常见的错误。

查询时用矩形多边形做空间过滤：

SELECT id, ST_Distance_Sphere(location,
         ST_GeomFromText('POINT(116.397428 39.90923)', 4326)) AS meters
FROM poi
WHERE ST_Within(location, ST_GeomFromText(
        'POLYGON((116.3622 39.8823, 116.4326 39.8823,
                  116.4326 39.9362, 116.3622 39.9362,
                  116.3622 39.8823))', 4326))
HAVING meters <= 3000
ORDER BY meters
LIMIT 20;

多边形首尾点必须相同（闭合），点内顺序同样是「经度 纬度」。

哪些写法会让索引直接失效？

结论：只要距离函数出现在 WHERE 的普通条件里，就是全表扫描。

三个高频坑：

1. WHERE ST_Distance_Sphere(location, @p) < 3000 不带矩形预过滤——全表扫描，必须先用 ST_Within 或 BETWEEN 收窄。
2. 对索引列套函数，比如 WHERE FLOOR(latitude*100) = 3990——列上做运算，idx_lat_lng 用不上，应改用前面说的 grid_lat 冗余列。
3. 用 ST_Buffer 做「3 公里缓冲区」——MySQL 在 SRID 4326 下 ST_Buffer 的参数单位是「度」不是「米」，画出来的圆是变形的，直接用外接矩形更准确也更省 CPU。

另外要特判：跨 180 度经线和极区。COS(RADIANS(lat)) 在极点附近趋近 0，算出来的 Δlng 会变成无穷大，需要单独限制经度范围。

数据量再大怎么继续优化？

结论：把在线计算改成离线预计算，收益比调 SQL 大得多。

具体做法有三条：一是离线跑一遍逆地理，把 city_id、district_id 冗余进 POI 表并建索引，用户按城市筛选时完全不碰坐标计算；二是对热门城市/商圈做结果缓存，键名用「中心点坐标 + 半径」的哈希，命中率通常能到 80% 以上；三是分页用游标（记录上一次的距离和 id）而不是 OFFSET，避免深分页扫表。

整体思路就是：DECIMAL(9,6) 存准，矩形框收窄，精确公式兜底，热点离线算。四步做到位，百万级 POI 的三公里内查询稳定在几十毫秒并不难。