在mysql数据表的geometry类型字段上创建空间索引SPATIAL INDEX后，在进行空间查询没有用到索引，导致查询效率很慢。

这个问题以前没遇到过，工作中凡是涉及空间查询操作的都是使用postgresql来做。但为了让自己看起来厉害一点，还是仔细尝试了一番，但没有解决。最后拜托对方知道了告诉我一声。

对方最后还是把问题解决了

mysql存储索引的表 mysql索引表空间_数据库

按照他的说法：

创建表

CREATE TABLE spatial_index_test (geom GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(geom));

我这里只创建了一个字段geom并设定SRID和SPATIAL INDEX

导入数据

-- INSERT INTO spatial_index_test (geom) SELECT ST_GeomFromText(text_wgs, 4326) FROM p;
INSERT INTO spatial_index_test (geom) VALUES (ST_GeometryFromText('POINT(30 120)', 4326));

导入数据时遇到了一个令人头痛的问题，ST_GeomFromText制定SRID后，提示纬度超出了【-90， 90】范围。

mysql存储索引的表 mysql索引表空间_字段_02


我仔细查看了测试数据，又尝试了插入POINT(120, 30)也是同样的错误。如果不指定SRID，默认值为0，这样就会因字段不匹配报错。这个SRID，我百度了下，没有找到，难道没人遇到吗？我找到老班长，用国外搜索引擎搜到了一篇 MySQLのSpatial型カラムにSRIDは必須ではなかった。这个作者也遇到了这个问题，并进行了仔细测试，加了SRID后，经度和纬度的顺序是反着来的：

ST_GeometryFromText('POINT(30 120)', 4326)

测试

SELECT * FROM spatial_index_test
WHERE st_contains(st_buffer(ST_GeomFromText('POINT(30 120)', 4326), 1), geom);

mysql存储索引的表 mysql索引表空间_mysql_03


mysql存储索引的表 mysql索引表空间_mysql_04


用到了空间索引，问题得到了解决。

再想想

这个问题的解决方法，本应该早点发现的。最开始按照MySQL官网的两篇教程测试时是可以应用空间索引的，这两篇 Creating Spatial Indexes 和 Using Spatial Indexes。 但应用了对方提供的测试数据后就出现了问题，仔细想想确实是顺序不一样，后者是直接导入，然后创建索引。。。

总感觉哪里不对劲，导入的时候我也没有设定SRID，会不会是SRID的问题呢？
在原来数据表上创建一个新的字段geom，类型为geometry，并赋值。

UPDATE p SET geom = ST_GeomFromText(CONCAT('POINT(', p.lat, ' ', p.lon, ')'), 4326);

修改字段的SRID（注意修改时，如果这个字段上有索引，要先删掉）：

ALTER TABLE p MODIFY geom GEOMETRY NOT NULL SRID 4326;

再次创建索引，再次测试：

SELECT * FROM p
WHERE st_contains(st_buffer(ST_GeomFromText('POINT(30.5324073 114.2907818)', 4326), 10), geom);

mysql存储索引的表 mysql索引表空间_字段_05


在5w条记录中成功检索到一条记录，耗时0.001s（这可是navicat上写的），也应用到了索引。

总结

实际上，空间索引使用的必须要满足SRID一致才行。

很久没用MySQL有些生疏了，问了几个同事也没能得到想要的答案，最后还是对方提供了解决思路。学到了很多，谢谢你们。