需要帮助优化mysql的经纬度geo搜索。

Question

我有一个包含大约300,000条记录的mysql（5.0.22）myisam表，我想在五英里半径内进行lat/lon距离搜索。

我有一个覆盖lat/lon字段的索引，当我只选择lat/lon时它很快（毫秒级响应）。但是，当我选择表中的其他字段时，速度变得非常慢，需要5-8秒。

我使用myisam来利用全文搜索。其他索引表现良好（例如，从Listing中选择* where 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 | 使用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 | 使用where条件; 使用文件排序 |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

这是使用覆盖索引的执行计划。正确顺序的列有很大的区别：

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+
| 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     | 使用where条件; 使用索引; 使用文件排序 |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+

谢谢！

- Jeff

---

发布快速和慢速查询的解释计划。 - jonstjohn

看起来你的那张表有太多列了。通过对数据结构进行规范化，你可以从查询中挤出一些性能。 :) - tom

Answer 1

我认为你应该考虑使用PostgreSQL（结合Postgis）。

我已经放弃了MySQL的地理空间数据（暂时），因为有以下原因：

• MySQL仅支持MyISAM表上的空间数据类型/空间索引，具有MyISAM的固有缺点（涉及事务、参照完整性等）
• MySQL仅在MBR基础上实现一些OpenGIS规范（最小边界矩形），这对于大多数严肃的地理空间查询处理而言几乎没有用处（请参见MySQL手册中的此链接）。您很可能会在不久的将来需要其中一些功能。

使用适当的（GIST）空间索引和适当的查询，PostgreSQL / Postgis可以非常快速。

示例：确定“小”多边形选择与包含超过5百万个非常复杂多边形的表之间的重叠多边形，计算这些结果之间的重叠量+排序。平均运行时间：30至100毫秒之间（当然，此特定机器具有大量RAM。不要忘记调整您的PostgreSQL安装...（请阅读文档））。

- ChristopheD

---

+1，是的，这是一个空间问题，因此它需要一个空间解决方案。 - none

Answer 2

你可能在使用仅涉及经纬度的查询中的“覆盖索引”。当查询使用的索引包含您所选择的数据时，就会出现覆盖索引。MySQL只需要访问索引而不是数据行。请参阅此处以获取更多信息。这就解释了为什么经纬度查询如此快。

我怀疑计算和返回的行数太多会使较长的查询变慢（加上必须为having子句创建的任何临时表）。

- jonstjohn

---

你接近与覆盖索引相关的问题。我所拥有的索引没有涵盖足够的列。我扩展了它以涵盖所有需要的列，速度更快了，但仍需要1-1.7秒。如果我想包含电话、电子邮件、网站等信息，我还必须将字符集减少到latin1（相对于utf8的1个字节而不是3个字节）。 - Jeff

Answer 3

当我实现地理半径搜索时，我只是将所有美国邮政编码及其经纬度加载到内存中，然后使用我的起始点和半径来获取半径内的邮政编码列表，并将其用于我的数据库查询。当然，我使用solr进行搜索，因为搜索空间在2000万行范围内，但相同的原则应适用。由于我正在使用手机，所以对此回答的肤浅表示歉意。

- Hardwareguy

Answer 4

在select语句中应该尽量避免进行大量的数学计算。这很可能是你的许多减速问题的根源。请记住，SQL是一种查询语言；它真的没有为三角函数进行优化。

如果您进行一个非常简单的距离搜索（这将返回更多的结果），然后再筛选您的结果，那么SQL将更快，您的整体结果也会更快。

如果您想在查询中使用距离，则至少使用平方距离计算；sqrt计算非常慢。平方距离更容易使用。平方距离计算仅是使用距离的平方而不是距离本身；它更简单。对于笛卡尔坐标系，由于直角三角形短边的平方和等于斜边的平方，因此计算平方距离（只需求和两个平方）比计算距离要容易得多；您所要做的就是确保您正在对要比较的距离进行平方（因此，与其找到精确距离并将其与您的期望距离（假设为5）进行比较，不如找到平方距离，并将其与期望距离的平方（如果您的期望距离为5，则为25）进行比较）。

- Paul Sonier

---

你知道在哪里可以找到更多有关平方距离计算的信息吗？ - Jeff

@Jeff：我会把它加入到答案中。 - Paul Sonier

这里有一个简单的建议：将您的大圆距离计算定义为存储函数，并重新设计查询，使其成为纬度和经度之后的第三个AND子句。先进行纬度查询，然后进行经度查询。在全球范围内，南北一海里大约相当于一度纬度，但是经度一度的距离因纬度而异。 - O. Jones

Answer 5

根据您的列表数量，您可以创建一个包含以下内容的视图：

Listing1Id、Listing2ID、Distance

基本上只需“预先计算”所有距离即可。

然后，您可以执行以下操作：

从v_Distance d中选择listing2ID，其中distance < 5且listing1ID = XXX

- Puttzy