记一次MySQL SPATIAL 空间索引失效处理

事情经过：
1.项目原本使用的云服务器自建MySQL数据库，版本5.7，需要上阿里云RDS
2.在上云的同时准备升级MySQL到8.0.26版本
3.测试环境先行切换到阿里云RDS，没有发生问题
4.生产环境使用停机切换的方式更换到阿里云RDS（开发人员里没有双写迁移的操作经验）
5.某个使用MySQL几何空间索引的表，在数据量级达到百万时，阿里云开始发送慢SQL警告

马上使用客户端将其中一条sql语句拉出来看执行计划，好家伙，直接拉了条全表扫描的sql。可以看到执行器根本没有使用任何索引，是一个ALL类型的全表扫描，zoom_index索引也因为筛出的行记录数占比过大而被优化器放弃使用，此时表中的数据量为147万

回到原有的自建MySQL 5.7，查看同一个SQL的执行计划，完美走了索引，虽然是range级别，但仅需200毫秒：

此时开始查找原因，搜索关键字，最后找到了MySQL的这篇文章：

Upgrading Spatial Indexes to MySQL 8.0

先上我的解决方案，在MySQL 8.0 中空间索引失效的表里，给索引列设置统一的SRID值（将表名wms_request和列名polygon替换为你的表名和几何图形列名）

UPDATE wms_request SET polygon= ST_SRID(polygon, 0)
WHERE ST_SRID(polygon) != 0;

然后，删除原有的已经创建的SPATIAL索引，因为我们需要重建新的索引

DROP INDEX polygon_idx ON wms_request ;

下一步，我们把目标字段限制NOT NULL且限制SRID统一为0（记得替换为你的表名、列名、几何图形数据类型）

ALTER TABLE wms_request 
MODIFY COLUMN polygon geometry NOT NULL SRID 0;

最后我们重建SPATIAL空间索引

CREATE SPATIAL INDEX polygon_idx ON wms_request (polygon);

大功告成，再次查看此时的执行计划，已恢复正常

空间索引生效，百万级别的表数据查询也仅需几百毫秒

原因分析

MySQL 多年来一直有空间索引，但在旧版本（比如我们项目升级前的5.7）中一直都是笛卡尔（X 和 Y 坐标）索引。MySQL 8.0 以后增加了对地理（纬度-经度）索引的支持。这带来的问题是：你不能直接从5.7版本的空间索引直接升级到8.0

8.0的查询优化器需要知道你建立的索引是笛卡尔索引还是地理索引。这取决于你的列数据中的 SRID。但是5.7 及更早版本不将索引限制为单个 SRID，因此以前的空间索引可能同时包含笛卡尔数据和地理数据。只是 MySQL 忽略了 SRID（5.7 及更早版本就是这样），于是不会影响你用，但8.0要求你必须指定一个坐标系。因此，8.0 的SQL优化器不能用 5.7建立的索引。

如果我们的数据库中某个表有空间索引，并且打算升级到 8.0，你需要：

1. 确保几何图形的索引列中只有一个 SRID
2. 告诉 MySQL 使用哪个 SRID 作为索引（给每一行设置统一的SRID）
3. 重新创建索引

文章当中同样提到了，如果你要从5.7的空间索引升级到8.0，应该先做什么。当然如果你已经切换到了8.0，然后遇到了索引失效，可以参考我上面的步骤进行排查解决