MYSQL 使用空间索引

我正在尝试利用空间索引。我有一个 ip 表和一个包含 ip 块范围的 ip2geo 表。我正在尝试将 Geo ID 分配给 ip2geo 表中的每个 ip

当尝试使用列值进行选择时，空间索引不会被使用。

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 查询以使用空间索引？

UPDATE:

我能够使用插入触发器快速分配 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

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

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

现在，如果不创建触发器或存储过程，我如何更新表中的国家/地区IPs使用地理空间索引ipblocks

最后更新（承诺）使用过的溶液

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 函数正常工作以及要使用的索引，您需要其中一个几何形状是常数。尽管您将看到的 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 空间中，您被迫让其中一个几何图形成为常量。

顺便说一句，左连接语法对于空间没有多大意义（尽管它是受支​​持的），因为您并不是真正在单个匹配的属性上连接，而是在二维包含/交集运算符上连接。

另外，我很确定在您的内部联接中没有使用索引，如果您查看key and rows解释的输出。