首页 / MYSQL / MYSQL使用空间索引
MYSQL使用空间索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL使用空间索引,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3773字,纯文字阅读大概需要6分钟。
内容图文
我试图利用空间索引.我有一个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空间中,您必须使其中一个几何是常量.
另外,左连接语法对空间没有多大意义(虽然它是受支持的),因为你并没有真正加入一个匹配的属性,而是加入一个二维包含/交集运算符.
另外,我很确定在你的内连接上没有使用索引,如果你看一下解释的键和行输出.
内容总结
以上是互联网集市为您收集整理的MYSQL使用空间索引全部内容,希望文章能够帮你解决MYSQL使用空间索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。