MySQL 8中MBRContains查询未使用空间索引导致慢查询

该空间索引不生效问题由两个典型配置错误叠加导致，在MySQL 8.0.x全平台版本中复现率极高：

• 坐标顺序不符合MySQL SRID 4326的解析规则
  MySQL对SRID 4326坐标系的几何值固定按经度在前、纬度在后的顺序解析WKT字符串，和日常使用中“纬度在前、经度在后”的习惯相反，也和EPSG官方4326定义的轴顺序存在差异，属于MySQL历史兼容逻辑。
  你当前写的POLYGON所有坐标点均为「纬度 经度」顺序，实际生成的查询矩形落在南纬73°、东经40°的南极区域，和预期的纽约查询范围完全不重叠。如果插入POINT数据时也使用了错误的坐标顺序，表内存储的所有坐标位置都和业务预期不符，空间索引记录的MBR范围完全错乱，优化器会判定索引筛选效率极低。
• 空间列统计信息缺失
  批量插入73.5万行数据后未更新表统计信息，优化器无法拿到准确的索引选择性数据，会默认判定地理坐标系下的范围查询回表成本高于全表扫描，主动放弃可用的空间索引。

从EXPLAIN结果中possible_keys已列出空间索引但key为NULL的表现，可以直接排除索引未创建、列SRID不匹配的问题。

按顺序执行以下操作即可解决：

1. 修正WKT坐标顺序
   所有几何值构造时统一使用「经度在前、纬度在后」的顺序，修正后的查询语句如下：

   SELECT long_lat_id, ST_Latitude(long_lat), ST_Longitude(long_lat)
   FROM location_coordinate
   WHERE MBRContains(
       ST_GeomFromText(
           'POLYGON((
               -73.919978196147 40.79607446677, 
               -73.919978196147 40.70923553323, 
               -74.034611803853 40.70923553323, 
               -74.034611803853 40.79607446677, 
               -73.919978196147 40.79607446677
           ))', 
           4326
       ), 
       long_lat
   );
   

   注意：如果插入存量POINT数据时也使用了「纬度 经度」的错误顺序，必须先修正所有存量数据的坐标，否则查询结果完全不符合预期。
2. 更新表统计信息
   执行以下命令收集最新的列和索引统计数据，供优化器生成正确执行计划：

   ANALYZE TABLE location_coordinate;
   

3. 验证索引生效
   重新执行EXPLAIN查看执行计划，正常结果会显示type: range，key字段值为long_lat，查询耗时会降到100毫秒以内。
   如果完成前两步后优化器仍然选择全表扫描，可通过强制索引验证性能：

   SELECT long_lat_id, ST_Latitude(long_lat), ST_Longitude(long_lat)
   FROM location_coordinate FORCE INDEX(long_lat)
   WHERE MBRContains(
       ST_GeomFromText(
           'POLYGON((
               -73.919978196147 40.79607446677, 
               -73.919978196147 40.70923553323, 
               -74.034611803853 40.70923553323, 
               -74.034611803853 40.79607446677, 
               -73.919978196147 40.79607446677
           ))', 
           4326
       ), 
       long_lat
   );
   

如果业务仅需做矩形范围筛选，不需要球面距离计算、球面空间关系判断，可将列SRID改为0（平面笛卡尔坐标系）。平面空间索引的成本计算逻辑更简单，不会出现优化器误判全表扫描的问题，查询性能比4326地理坐标系高30%左右。

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