mysql spatial 索引,MYSQL使用空间索引

我试图利用空间索引.我有一个ips表和一个ip块范围的ip2geo表.我正在尝试从ip2geo表为每个ip分配Geo ID

尝试使用列值进行选择时,不会使用空间索引.

EXPLAIN

SELECT *,

( SELECT locid FROM `ipblocks` i

WHERE MBRCONTAINS(i.ippolygon,

POINTFROMWKB(POINT(h.`ip`, 0))) ) AS locaid

FROM `ips` h LIMIT 1;

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY h ALL NULL NULL NULL NULL 33279 2 DEPENDENT

SUBQUERY i ALL ipblock_spatialidx NULL NULL NULL 4977388 Using where

在过滤器中使用常量时,将使用索引.

EXPLAIN SELECT *,(SELECT locid FROM `ipblocks` i WHERE

MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(3223394542, 0))) ) AS

locaid FROM `ips` h LIMIT 1;

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY h ALL NULL NULL NULL NULL 33279 Using filesort 2 UNCACHEABLE

SUBQUERY i range ipblock_spatialidx ipblock_spatialidx 34 NULL 1 Using where

内部连接时使用索引(检查额外)

EXPLAIN SELECT * FROM `ips` h INNER JOIN `ipblocks` i ON (MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(h.`cp`, 0)))) LIMIT 100 ;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE h ALL NULL NULL NULL NULL 33279

1 SIMPLE i ALL ipblock_spatialidx NULL NULL NULL 4977388

检查每条记录的范围(索引图：0x1)

当离开加入时,没有使用索引.

EXPLAIN SELECT * FROM `ips` h LEFT JOIN `ipblocks` i ON (MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(h.`ip`, 0)))) LIMIT 100 ;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE h ALL NULL NULL NULL NULL 33279

1 SIMPLE i ALL ipblock_spatialidx NULL NULL NULL 4977388

如何优化SQL查询以使用空间索引？

更新：

我能够通过使用插入触发器快速分配GEO国家/地区.但是我仍然需要知道为什么在加入或子查询时我不能使用Spatial索引

BEGIN

DECLARE geoloc VARCHAR(10) DEFAULT NULL;

SELECT country FROM ipblocks i LEFT JOIN iplocations l ON(i.locid=l.locid) WHERE MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(NEW.ip, 0))) LIMIT 1 INTO geoloc;

SET NEW.geo= geoloc;

END

更新2 @John的问题

我的目标是使用以下模式获取表IP

username, ipaddress, country

并使用我购买的带有IP范围的GEO2IP表作为INET_ANOT()表IPblocks

ipfrom,ipto,country,poly [example POLYGON((16777216 -1,16777471 -1,16777471 1,16777216 1,16777216 -1)) ]

现在没有制作触发器或存储过程如何使用ipblocks中的地理空间索引更新表IP中的国家/地区

最后更新(承诺)使用解决方案

SELECT * FROM `iplist` i LEFT JOIN `iplocations` l ON (SELECT GetLocId(INET_ATON(i.`ip`))=l.`locid`) ;

GetLocId使用以下SQL

SELECT locid FROM `ipblocks` i WHERE

MBRCONTAINS(i.ippolygon, POINTFROMWKB(POINT(@INPUTVAR, 0))) INTO locid

并返回locid,它在39ms内匹配40k ips

解决方法:

遗憾的是,您所看到的是MySQL中实现空间函数的方式的一般问题,以及涉及空间函数的子查询的相关弱点.

要使Contains和Intersects函数正常工作,并且要使用索引,您需要使其中一个几何为常量.这似乎没有记录,尽管您将使用带有Intersects / Contains的MySQL看到的所有示例都以这种方式工作.

所以,你不能像在Oracle Spatial或Postgis中那样写这样的东西,

select a.*, b.*

from sometable a, someothertable b

where ST_Intersects(a.geom, b.geom)

and a.someattribute=... and b.someattribute=...;

在这样的查询中,如果表a和b都具有空间索引,则将使用它们,前提是这比您可能放在where子句中的其他属性更具限制性.

这同样适用于自连接,您希望根据某些属性查找与表中所有其他多边形相交的所有多边形,例如,

select a.*

from sometable a, sometable b

where ST_Intersects(a.geom, b.geom) ....

因此,在MySQL空间中,您必须使其中一个几何是常量.

另外,左连接语法对空间没有多大意义(虽然它是受支持的),因为你并没有真正加入一个匹配的属性,而是加入一个二维包含/交集运算符.

另外,我很确定在你的内连接上没有使用索引,如果你看一下解释的键和行输出.

标签：sql,mysql,geospatial,spatial-index

来源： https://codeday.me/bug/20190830/1768905.html