数据库是MySQL 5.7.10,InnoDB引擎,建了空间索引, 但是查询的时候没有走该索引,为什么?
建表语句如下:
建表语句如下:
CREATE TABLE `geo` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `geo` GEOMETRY NOT NULL, PRIMARY KEY (`id`), SPATIAL INDEX `geo` (`geo`) ) COLLATE=""utf8mb4_general_ci"" ENGINE=InnoDB AUTO_INCREMENT=1
插入了一些数据
insert into geo(geo) values(ST_GeomFromText(""POINT(108.875000 34.216020)"")); insert into geo(geo) values(ST_GeomFromText(""POINT(109.569098 36.646357)"")); insert into geo(geo) values(ST_GeomFromText(""POINT(109.550988 36.633384)"")); insert into geo(geo) values(ST_GeomFromText(""POINT(109.472800 36.624116)"")); insert into geo(geo) values(ST_GeomFromText(""POINT(109.487460 36.563614)"")); insert into geo(geo) values(ST_GeomFromText(""POINT(109.532016 36.686191)"")); insert into geo(geo) values(ST_GeomFromText(""POINT(109.319010 36.987505)""));
创建一个多边形
SET @g3 = ST_GeomFromText(""Polygon((108 36.5,108 36.7,109.5 36.7,109.5 36.5,108 36.5))"");
explain如下:
mysql> explain select st_x(geo),st_y(geo) from geo where mbrcontains(@g3,geo)>0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: geo partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
解决方案:11分
把>0去掉?
解决方案:10分
字段套上mbrcontains函数就不走索引了