MySQL使用空间索引优化附近用户查询失效求助

我的user表存有2万+用户，需实现一次展示10个附近用户的查询。最初使用以下基于Haversine公式的SQL：

EXPLAIN SELECT *, ( 3959 * ACOS( COS( RADIANS(42.29297046302411) ) * COS( RADIANS( User.lat ) ) * COS( RADIANS(User.long) - RADIANS(-108.7299657985568)) + SIN(RADIANS(42.29297046302411)) * SIN( RADIANS(User.lat)))) AS distance from user as User GROUP BY distance HAVING distance < 100 LIMIT 10

该查询速度极慢，EXPLAIN显示为全表扫描。为优化性能，我尝试使用空间索引：添加了POINT类型的polygon列存储经纬度，并创建空间索引，使用以下SQL查询：

EXPLAIN SELECT id,first_name, st_distance_sphere(polygon, POINT(73.1270688, 31.4554433)) AS distance
FROM user
WHERE ST_Distance_Sphere(polygon, POINT(73.1270688, 31.4554433)) < 100
ORDER BY distance ASC

但结果仍为全表扫描，未达到优化效果。我的表结构如下：

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(150) NOT NULL,
 `last_name` varchar(150) NOT NULL,
 `lat` varchar(255) NOT NULL,
 `long` varchar(255) NOT NULL,
 `polygon` point NOT NULL,
 `created` datetime NOT NULL,

 PRIMARY KEY (`id`),
 KEY `lat` (`lat`,`long`),
 SPATIAL KEY `polygon` (`polygon`)
) ENGINE=MyISAM AUTO_INCREMENT=22431 DEFAULT CHARSET=latin1

请问我哪里操作有误？如何才能让该查询更快？

问题根源 #

• 空间索引未触发：ST_Distance_Sphere()无法直接触发空间索引，MySQL空间索引仅支持基于地理边界范围的过滤（如ST_Contains()、ST_Intersects()），直接用距离计算的WHERE条件会导致全表扫描。
• 经纬度列类型错误：lat和long被定义为varchar类型，数值计算时需要额外类型转换，且普通联合索引(lat,long)完全无效——字符串类型的经纬度无法做数值范围筛选。
• 命名混淆：POINT类型列命名为polygon易造成误解，虽不影响功能，但建议改为location这类清晰名称。

优化步骤 #

1. 修复经纬度列类型（可选但推荐）

将lat和long从varchar改为数值类型，避免计算时的类型转换开销：

ALTER TABLE user MODIFY COLUMN lat DECIMAL(10,8) NOT NULL;
ALTER TABLE user MODIFY COLUMN `long` DECIMAL(11,8) NOT NULL;

2. 用边界框过滤触发空间索引

先构造目标点周围的矩形边界框，通过ST_Intersects()筛选落在框内的点，再计算精确距离排序。这样MySQL会先利用空间索引快速缩小数据范围，再对少量数据做精确计算：

SELECT 
    id, first_name,
    ST_Distance_Sphere(polygon, POINT(73.1270688, 31.4554433)) AS distance
FROM user
WHERE 
    ST_Intersects(
        polygon,
        ST_MakeEnvelope(
            POINT(73.1270688 - 0.15, 31.4554433 - 0.15),  -- 西南角偏移量，对应约10公里范围
            POINT(73.1270688 + 0.15, 31.4554433 + 0.15)   -- 东北角偏移量，可根据目标距离调整
        )
    )
HAVING distance < 100
ORDER BY distance ASC
LIMIT 10;

注：偏移量可根据目标距离调整，确保覆盖100公里范围，避免漏数据。

3. 验证索引生效

执行EXPLAIN查看执行计划，若type列显示range/ref，且key列显示空间索引名（polygon），说明索引已被利用。

4. 额外优化建议

• 若使用MySQL 8.0+，建议改用InnoDB引擎，其空间索引支持更完善，同时具备更好的事务性与稳定性。
• 定期执行ANALYZE TABLE user;更新表统计信息，帮助优化器正确选择索引。

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