需要帮助优化lat / Lon geo搜索mysql
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了需要帮助优化lat / Lon geo搜索mysql,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4904字,纯文字阅读大概需要8分钟。
内容图文
![需要帮助优化lat / Lon geo搜索mysql](/upload/InfoBanner/zyjiaocheng/907/548310a6c82942e3b03bd8c4af2be3ca.jpg)
我有一个mysql(5.0.22)myisam表,里面有大约300k记录,我想在5英里范围内进行纬度/离子距离搜索.
我有一个覆盖lat / lon字段的索引,当我选择lat / lon时,它是快速的(毫秒响应).但是当我选择表格中的其他字段时,可能会慢慢减慢到5-8秒.
我正在使用myisam来利用全文搜索.其他索引表现良好(例如,从列表中选择*,其中slug =’xxxxx’).
如何优化查询,表格或索引以加快速度?
我的架构是:
CREATE TABLE `Listing` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(125) collate utf8_unicode_ci default NULL,
`phone` varchar(18) collate utf8_unicode_ci default NULL,
`fax` varchar(18) collate utf8_unicode_ci default NULL,
`email` varchar(55) collate utf8_unicode_ci default NULL,
`photourl` varchar(55) collate utf8_unicode_ci default NULL,
`thumburl` varchar(5) collate utf8_unicode_ci default NULL,
`website` varchar(85) collate utf8_unicode_ci default NULL,
`categoryid` int(10) unsigned default NULL,
`addressid` int(10) unsigned default NULL,
`deleted` tinyint(1) default NULL,
`status` int(10) unsigned default '2',
`parentid` int(10) unsigned default NULL,
`organizationid` int(10) unsigned default NULL,
`listinginfoid` int(10) unsigned default NULL,
`createuserid` int(10) unsigned default NULL,
`createdate` datetime default NULL,
`lasteditdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`lastedituserid` int(10) unsigned default NULL,
`slug` varchar(155) collate utf8_unicode_ci default NULL,
`aclid` int(10) unsigned default NULL,
`alt_address` varchar(80) collate utf8_unicode_ci default NULL,
`alt_website` varchar(80) collate utf8_unicode_ci default NULL,
`lat` decimal(10,7) default NULL,
`lon` decimal(10,7) default NULL,
`city` varchar(80) collate utf8_unicode_ci default NULL,
`state` varchar(10) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`id`),
KEY `idx_fetch` USING BTREE (`slug`,`deleted`),
KEY `idx_loc` (`state`,`city`),
KEY `idx_org` (`organizationid`,`status`,`deleted`),
KEY `idx_geo_latlon` USING BTREE (`status`,`lat`,`lon`),
FULLTEXT KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;
我的查询是:
SELECT Listing.name, Listing.categoryid, Listing.lat, Listing.lon
, 3956 * 2 * ASIN(SQRT( POWER(SIN((Listing.lat - 37.369195) * pi()/180 / 2), 2) + COS(Listing.lat * pi()/180) * COS(37.369195 * pi()/180) * POWER(SIN((Listing.lon --122.036849) * pi()/180 / 2), 2) )) rawgeosearchdistance
FROM Listing
WHERE
Listing.status = '2'
AND ( Listing.lon between -122.10913433498 and -121.96456366502 )
AND ( Listing.lat between 37.296909665016 and 37.441480334984)
HAVING rawgeosearchdistance < 5
ORDER BY rawgeosearchdistance ASC;
解释没有geosearch的计划:
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+ | 1 | SIMPLE | Listing | range | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Using where | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+
用geosearch解释计划:
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | Listing | range | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Using where; Using filesort | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
这是覆盖索引的解释计划.以正确的顺序排列列有很大的不同:
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+ | 1 | SIMPLE | Listing | range | idx_geo_cover | idx_geo_cover | 12 | NULL | 453 | Using where; Using index; Using filesort | +----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+
谢谢!
解决方法:
您可能只在lat / lon查询中使用“覆盖索引”.当查询使用的索引包含您要选择的数据时,会出现覆盖索引. MySQL只需要访问索引而不需要访问数据行. See this for more info.这可以解释为什么lat / lon查询如此之快.
我怀疑计算和返回的行数,减慢了查询的时间. (加上必须为having子句创建的任何临时表).
内容总结
以上是互联网集市为您收集整理的需要帮助优化lat / Lon geo搜索mysql全部内容,希望文章能够帮你解决需要帮助优化lat / Lon geo搜索mysql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。