MYSQL使用空间索引

Question

MYSQL使用空间索引

5

我尝试使用空间索引。我有一个IP表，以及一个具有IP块范围的ip2geo表。我正在尝试从ip2geo表为每个IP分配地理位置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           主查询      h       全部    无                   无              无      无      33279   2   DEPENDENT
    子查询      i                   全部    ipblock_spatialidx  无              无      无      4977388 使用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国家。但是我仍然需要知道为什么在连接或子查询时无法使用空间索引。

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的问题

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

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的地理空间索引来更新表IPs中的国家，而不需要创建触发器或存储过程？

最后一个更新（承诺）使用的解决方案。

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个ip地址

- c3cris

你可能不太喜欢我的回答，但我已经使用MySQL空间功能工作了好几年（我是5.6引入的新多边形空间功能的测试人员之一），遗憾的是，需要将其中一个几何图形设为常量是事实。涉及空间的子查询似乎也不起作用。因此，只要您在触发器中使用常量，就可以解决问题。但您并没有做错任何事情，这只是一个限制。 - John Powell

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Powell · Accepted Answer

您所看到的是MySQL中空间函数实现的一般问题，以及涉及空间函数的子查询的相关弱点。为了使包含和相交函数正常工作，并使用索引，您需要将其中一个几何图形设为常量。尽管所有MySQL中Intersects/Contains的示例都是这样工作的，但似乎并没有记录这一点。因此，您不能像在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空间中，您被迫将其中一个几何图形设为常量。

顺便说一下，左连接语法在空间上并不太合理（尽管它是受支持的），因为您实际上不是在单个匹配属性上进行连接，而是在二维包含/相交运算符上进行连接。

另外，我相当确定在您的内部连接中未使用索引，如果您查看explain的key和rows输出。