无法让MariaDB 5.5.68使用空间索引的技术问询

问题背景 #

当前使用MariaDB 5.5.68版本，虽知晓版本老旧，但因应用需求暂需使用。执行空间关联查询后，EXPLAIN结果及实际性能均显示空间索引未被利用。

查询语句及EXPLAIN结果 #

查询语句：

explain select bdcfabric.location_id from bdccoverage, bdcfabric where st_intersects(bdccoverage.shape,bdcfabric.bxlocation)\G

EXPLAIN输出：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bdccoverage
         type: ALL
possible_keys: SHAPE
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 886
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bdcfabric
         type: ALL
possible_keys: bxlocation_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1105588
        Extra: Using where; Using join buffer (flat, BNL join)

空间索引信息 #

bdccoverage表索引 #

执行语句：

show indexes from bdccoverage\G

输出：

*************************** 4. row ***************************
        Table: bdccoverage
   Non_unique: 1
     Key_name: SHAPE
 Seq_in_index: 1
  Column_name: SHAPE
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:

bdcfabric表索引 #

执行语句：

show indexes from bdcfabric\G

输出：

*************************** 2. row ***************************
        Table: bdcfabric
   Non_unique: 1
     Key_name: bxlocation_idx
 Seq_in_index: 1
  Column_name: bxlocation
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:

表结构信息 #

bdccoverage表结构 #

执行语句：

describe bdccoverage;

输出：

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| SHAPE      | geometry    | NO   | MUL |         |                |

bdcfabric表结构 #

执行语句：

describe bdcfabric;

输出：

+-------------------------+------------+------+-----+---------+-------+
| Field                   | Type       | Null | Key | Default | Extra |
+-------------------------+------------+------+-----+---------+-------+
| bxlocation              | point      | NO   | MUL |         |       |
+-------------------------+------------+------+-----+---------+-------+

索引未被使用的原因分析 #

针对MariaDB 5.5.68版本，空间索引未被触发的常见原因如下：

• 版本对空间连接的支持限制：MariaDB 5.5系列对空间索引在JOIN场景下的优化支持不完善，尤其是跨表的ST_Intersects关联查询，优化器可能无法识别到可以利用空间索引加速连接，更倾向于全表扫描+连接缓冲区的方式。
• 空间索引的子部分设置问题：两个空间索引都设置了Sub_part: 32，仅索引空间数据的前32字节。对于geometry类型字段，前32字节可能仅包含基础元数据而非完整空间边界信息，导致优化器认为该索引无法有效过滤数据，从而放弃使用。
• 优化器统计信息缺失：索引的Cardinality均为NULL，说明表的统计信息未正确生成或更新。优化器依赖统计信息判断索引价值，缺失统计信息会让优化器无法评估空间索引的过滤效率，进而选择全表扫描。
• 连接顺序与小表驱动逻辑缺陷：当前查询中bdccoverage作为驱动表（仅886行），但优化器未对百万级行的bdcfabric使用空间索引。5.5版本的优化器可能无法正确处理"小表驱动大表"场景下的空间索引利用，尤其是关联条件为空间函数时。

临时解决建议 #

• 更新表统计信息：执行ANALYZE TABLE bdccoverage, bdcfabric;，让优化器获取更准确的索引基数数据。
• 移除空间索引的Sub_part设置：重新创建空间索引，确保索引包含完整空间数据：

  ALTER TABLE bdccoverage DROP INDEX SHAPE;
  ALTER TABLE bdccoverage ADD SPATIAL INDEX SHAPE(SHAPE);
  ALTER TABLE bdcfabric DROP INDEX bxlocation_idx;
  ALTER TABLE bdcfabric ADD SPATIAL INDEX bxlocation_idx(bxlocation);
  

• 强制使用空间索引：通过FORCE INDEX提示优化器选择空间索引：

  SELECT bdcfabric.location_id 
  FROM bdccoverage 
  JOIN bdcfabric FORCE INDEX(bxlocation_idx) 
  ON ST_Intersects(bdccoverage.shape, bdcfabric.bxlocation);
  

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