MYSQL 使用空间索引

Question

我正在尝试使用空间索引。我有一个 ips 表和一个带有 ip block 范围的 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

When using a constant in the filter the index get used.

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

更新 @John 的第 2 个问题

我的目标是获取具有以下架构的表 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 中的国家/地区

上次更新( promise ) 使用的解决方案

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，它在 39 毫秒内匹配了 40k 个 ips

Answer 1

不幸的是，您所看到的是 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 spatial 中，您被迫让其中一个几何图形成为常量。

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

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