如何优化此IP到位置查询查询？

本文介绍了如何优化此IP到位置查询查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

UPDATE tracker SET t_loc_id =（SELECT cb_loc_id FROM city_blocks WHERE INET_ATON（t_ip）BETWEEN cb_start_ip_num AND cb_end_ip_num LIMIT 1）;

tracker 中大约有300K条记录 city_blocks 中约有3.6百万条记录。

我在 cb_start_ip_num 和 cb_end_ip_num 已经。

我可以加速吗？

b $ b

好吧，我让它运行约2小时，它只有约9K的记录。

解决方案

jcole.us/blog/archives/2007/11/24/on-efficiently-geo-referencecing-ips-with-maxmind-geoip-and-mysql-gis/

根据上面的文章，我将我的IP范围转换为多边形并添加了空间索引，然后运行我的更新查询：

UPDATE tracker JOIN city_blocks ON mbrcontains（cb_ip_poly，pointfromwkb（point（inet_aton（track_ip），0）））SET t_loc_id = cb_loc_id

第一次运行时间为45秒，第二次运行时间为8秒，而另一个查询将运行的估计时间为10小时到4天。

文章还提到了blockhead的解决方案，但它仍然运行得很慢的速度。无法弄清楚为什么...我不知道我的索引是否被破坏（我尝试重建它们）或者它不喜欢在子查询或什么??

说到子查询...这个解决方案不能很好地与子查询一起工作。我认为一个子查询会更快，因为我可以添加极限1，它不会必须加入一切都到一切，但似乎不是这样的情况。技术上没有限制，它将不得不继续搜索，以查看是否是IP在其他潜在的匹配/范围，但我想在这种情况下并不重要，因为一切都正确索引，只有1桶它可以落在到。

UPDATE tracker SET t_loc_id=(SELECT cb_loc_id FROM city_blocks WHERE INET_ATON(t_ip) BETWEEN cb_start_ip_num AND cb_end_ip_num LIMIT 1);

There are about 300K records in tracker and about 3.6M records in city_blocks. It's been running for over 30min now.

I've got unique indexes on cb_start_ip_num and cb_end_ip_num already.

Any way I can speed it up?

Okay, I let it run for about 2 hours and it only did about 9K records.

解决方案

jcole.us/blog/archives/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis/

I converted my IP ranges into polygons and added a spatial index, as per the article above, then ran my update query:

UPDATE tracker JOIN city_blocks ON mbrcontains(cb_ip_poly, pointfromwkb(point(inet_aton(track_ip),0))) SET t_loc_id=cb_loc_id

Which ran in 45 seconds the first time, 8 the second, vs the estimated 10 hours to 4 days that the other query would have taken.

The article also mentions blockhead's solution, but it still ran atrociously slow. Can't figure out why... I don't know if my indexes were broken (I tried rebuilding them) or it didn't like being in a subquery or something??

Speaking of subqueries...this solution doesn't work well at all with a subquery. I figured a subquery would have been faster because I could add the limit 1 and it wouldn't have to join everything to everything, but that doesn't appear to be the case. Technically without the limit it would have to continue searching to see if the are other potential matches/ranges the IP falls within, but I guess it doesn't really matter in this scenario because everything's properly indexed and there's only 1 bucket it can fall in to.