MYSQL使用空间索引

2023-02-07 17:33:04

我试图利用空间索引.我有一个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空间中,您必须使其中一个几何是常量.

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

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

码农公寓

相关文章