MySQL 8.0.32中GROUP BY POINT列未用空间索引致查询缓慢

针对升级到MySQL 8.0.32后空间查询变慢、索引不生效的问题，整理以下解决方向：

1. 调整空间函数参数顺序，触发正确的索引使用 #

MySQL InnoDB空间索引仅在列作为空间函数的第一个参数时才会被优化器选中，原查询的MBRContains(常量多边形, 列)写法不符合该规则，导致索引无法生效。

可以用逻辑等价的MBRWithin函数替换，将列放在第一个参数位置，既保证逻辑正确，又能触发空间索引：

SELECT
  ST_Latitude(granularity_30) as lat,
  ST_Longitude(granularity_30) as lng
FROM
  `GeoPriceCache`
WHERE
  MBRWithin(granularity_30, ST_GeomFromText('Polygon((
    -64.36807421875 56.379500183529,
    -64.36807421875 18.086774255995,
    -132.79092578125 18.086774255995,
    -132.79092578125 56.379500183529,
    -64.36807421875 56.379500183529
    ))', 4326, 'axis-order=long-lat'))
GROUP BY granularity_30;

MBRWithin(列, 多边形)与MBRContains(多边形, 列)逻辑完全一致，同时满足索引触发条件。

2. 验证空间数据的SRID一致性 #

尽管已指定列和生成的多边形使用SRID 4326，仍需检查表中数据是否存在SRID不匹配的情况：

SELECT DISTINCT ST_SRID(granularity_30) FROM GeoPriceCache;

若返回结果包含非4326的值，说明存在脏数据，需修正后才能让空间索引正常工作。

3. 优化分组操作的性能 #

按POINT类型列分组的开销远高于数值类型，因为MySQL需要对几何对象做复杂的哈希和比较。可以：

• 添加两个数值列存储经纬度：

  ALTER TABLE GeoPriceCache ADD COLUMN granularity_30_lat DECIMAL(10,8) NOT NULL;
  ALTER TABLE GeoPriceCache ADD COLUMN granularity_30_lng DECIMAL(11,8) NOT NULL;
  

• 用批量更新同步数据：

  UPDATE GeoPriceCache 
  SET granularity_30_lat = ST_Latitude(granularity_30),
      granularity_30_lng = ST_Longitude(granularity_30);
  

• 修改查询语句，直接使用数值列分组：

  SELECT
    granularity_30_lat as lat,
    granularity_30_lng as lng
  FROM
    `GeoPriceCache`
  WHERE
    MBRWithin(granularity_30, ST_GeomFromText('Polygon((...))', 4326, 'axis-order=long-lat'))
  GROUP BY granularity_30_lat, granularity_30_lng;
  

这种方式能大幅降低分组阶段的CPU开销。

4. 更新表统计信息，帮助优化器做正确选择 #

MySQL 8.0优化器依赖表的统计信息判断是否使用索引，升级后可能存在统计信息过时的情况：

ANALYZE TABLE GeoPriceCache;

执行后重新查看EXPLAIN结果，确认优化器是否自动选择空间索引。

5. 检查InnoDB配置是否适配空间查询 #

• 确保innodb_buffer_pool_size足够大，能容纳空间索引和表数据，避免频繁磁盘IO：专用数据库服务器建议设置为内存的50%-70%。
• 检查innodb_spatial_index_optimization参数是否开启（MySQL 8.0默认开启）：

  SHOW VARIABLES LIKE 'innodb_spatial_index_optimization';
  

  若未开启，执行SET GLOBAL innodb_spatial_index_optimization = ON;（需重启生效）。

6. 评估查询筛选范围的合理性 #

如果查询多边形范围过大，导致大部分数据被筛选出来，空间索引的优势会被削弱（此时全表扫描和索引扫描开销差距不大）。可以缩小测试范围，验证索引在小范围查询时的性能表现，判断是否因数据筛选比例过高导致性能问题。

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