同索引的两个相似SPATIAL列查询表现差异问题

问题背景 #

在MySQL 8.0中对邮政编码多边形做简化处理以减少坐标数量，表zip_city包含：

• boundary：原始MultiPolygon类型列，SRID 4326，已建空间索引
• boundary_simplified：简化后的同类型同SRID列，已建空间索引
• is_point：辅助列，对应索引idx_is_point

出现异常：

• ST_CONTAINS(boundary, @point)查询耗时0.03秒，ST_CONTAINS(boundary_simplified, @point)耗时4.84秒
• EXPLAIN显示后者未使用空间索引，仅命中idx_is_point
• 创建boundary索引耗时23.25秒，boundary_simplified索引仅需0.75秒
• 已尝试删除重建索引、无索引测试、FORCE/USE INDEX强制指定索引，问题未解决

排查思路 #

• 验证简化后几何体的合法性
  执行SELECT COUNT(*) FROM zip_city WHERE ST_ISVALID(boundary_simplified) = 0;检查是否存在无效多边形，空间索引对无效几何体无法正常生效。同时用ST_GEOMETRYTYPE(boundary_simplified)确认所有值严格为MULTIPOLYGON，避免简化过程中生成其他几何类型（如混合集合）导致索引匹配失败。

• 对比空间索引的统计信息
  查看SHOW INDEXES FROM zip_city中两列索引的Cardinality值，如果boundary_simplified的基数远低于boundary，说明简化后大量多边形特征高度重复，优化器会认为索引筛选性不足，选择全表扫描。也可查询information_schema.STATISTICS获取更详细的索引统计数据，确认优化器能获取到有效统计信息。

• 分析执行计划的细节
  用EXPLAIN ANALYZE（MySQL 8.0+支持）查看实际执行流程，重点关注：

  • 查询中的点是否与列的SRID完全一致，避免隐式SRID转换导致索引失效
  • 优化器对空间索引的成本估算结果，判断是否因成本过高放弃索引

• 测试简化程度的影响
  尝试调整简化容差值（如ST_SIMPLIFY(boundary, 0.001) vs ST_SIMPLIFY(boundary, 0.01)），生成不同简化程度的列并创建索引，测试查询性能和索引使用情况。如果仅过度简化版本出现问题，可能是简化后多边形边界过于“趋同”，导致R树索引无法有效划分空间，优化器认为全表扫描更高效。

• 重建表与索引的物理存储
  执行ALTER TABLE zip_city FORCE;重建表和索引的物理结构，消除可能的索引碎片或存储异常。同时确认表使用InnoDB引擎（MyISAM空间索引逻辑与InnoDB不同），且两列的存储参数（如NOT NULL属性）完全一致。

• 查看优化器决策过程
  开启optimizer trace：

  SET optimizer_trace="enabled=on";
  -- 执行你的查询语句
  SELECT * FROM zip_city WHERE ST_CONTAINS(boundary_simplified, ST_GEOMFROMTEXT('POINT(xxx xxx)', 4326)) AND is_point = 0;
  -- 查看trace结果
  SELECT * FROM information_schema.OPTIMIZER_TRACE;
  SET optimizer_trace="enabled=off";
  

  通过trace信息明确优化器放弃空间索引的具体原因，比如是索引的IO成本估算过高，还是统计信息缺失导致错误判断。

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