具有距离性能的MYSQL Geo搜索

Question

我有一个mysql选择声明，在我的网站上搜索，这是有性能问题，当网站变得非常繁忙。下面的查询从一个记录超过100 k的表中搜索广告，这些记录位于给定的lat和lon的25英里范围内，并按距离进行排序。用户选择的里程数可能会有所不同。

问题是，我认为这是缓慢的，因为它做的所有记录的计算表，而不是那些在25英里内的拉特和隆。是否可以修改这个查询，以便where子句只选择25英里以内的广告？我读过关于边界框的索引和空间索引，但是我不知道如何将它们应用到这个查询中，我是否需要添加一个where子句来选择记录lat和lon半径25英里的where子句，我如何做到这一点？

SELECT 
    adverts.*, 
    round(sqrt((((adverts.latitude - '53.410778') * (adverts.latitude - '53.410778')) * 69.1 * 69.1) + ((adverts.longitude - '-2.97784') * (adverts.longitude - '-2.97784') * 53 * 53)), 1) as distance
FROM 
    adverts
WHERE 
    (adverts.type_id = '3')
HAVING 
    DISTANCE < 25
ORDER BY 
    distance ASC 
LIMIT 120,10

编辑:更新为包含表模式，请注意，表是更复杂的，查询也是如此，但我已经删除了对此问题不需要的内容。

CREATE TABLE `adverts` (
`advert_id` int(10) NOT NULL AUTO_INCREMENT,
`type_id` tinyint(1) NOT NULL,
`headline` varchar(50) NOT NULL,
`description` text NOT NULL,
`price` int(4) NOT NULL,
`postcode` varchar(7) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
PRIMARY KEY (`advert_id`),
KEY `latlon` (`latitude`,`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

当我对mysql语句进行解释时，行数被设置为67900，这比半径25英里内的行数要大得多，而额外的行数也被设置为“使用where；Using”。

查询耗时0.3秒，速度非常慢，特别是当网站每秒收到大量请求时。

Answer 1

最快的方法是为MySQL使用地理空间扩展，这应该很容易，因为您已经在使用MyISAM表了。这些扩展的文档可以在这里找到：http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html

添加一个具有点数据类型的新列：

ALTER TABLE `adverts` 
ADD COLUMN `geopoint` POINT NOT NULL AFTER `longitude`
ADD SPATIAL KEY `geopoint` (`geopoint`)

然后，可以从现有的纬度和经度字段填充此列：

UPDATE `adverts` 
SET `geopoint` = GeomFromText(CONCAT('POINT(',`latitude`,' ',`longitude`,')'));

下一步是根据输入的纬度和经度创建一个边框，该框将在WHERE子句中用作CONTAINS约束。您将需要根据所需的搜索区域和给定的起点确定一组符合您的需求的X，Y POINT坐标。

最后的查询将搜索搜索POLYGON中的所有POLYGON数据，然后可以使用距离计算进一步细化和排序数据：

SELECT a.*, 
    ROUND( SQRT( ( ( (adverts.latitude - '53.410778') * (adverts.latitude - '53.410778') ) * 69.1 * 69.1 ) + ( (adverts.longitude - '-2.97784') * (adverts.longitude - '-2.97784') * 53 * 53 ) ), 1 ) AS distance
FROM adverts a
WHERE a.type_id = 3
AND CONTAINS(a.geopoint, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))
HAVING distance < 25
ORDER BY distance DESC
LIMIT 0, 30

请注意，上面的GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))')将不工作，您将需要在搜索开始前后用有效点替换坐标。如果期望lat/long发生更改，则应考虑使用触发器来保持POINT数据和相关的SPATIAL KEY更新。对于大型数据集，通过使用HAVING子句计算每条记录的距离和过滤，您将看到性能大大提高。我亲自定义了用于确定距离和创建边界POLYGON的函数。

Answer 2

有几种方法可以加快查询速度，我个人会利用POW函数。

返回X的值，该值提升到Y的幂值。

手动乘法将减缓使用大型表的查询速度，尽管取得了相同的结果。

SELECT a .* , 
    round( sqrt( 
        (POW( a.latitude -'53.410778', 2)* 68.1 * 68.1) + 
        (POW(a.latitude -'-2.97784', 2) * 53.1 * 53.1) 
     )) AS distance
 FROM adverts a
     WHERE a.type_id = 3
     HAVING distance < 25
     LIMIT 0 , 30

上面的查询在带有0.0008 sec记录的表模式上运行(您在同一表模式上测试的查询使用了0.0129 sec)，因此性能有了相当大的提高。

其他优化提示

• 如果在SELECT语句中使用实际列名而不是使用*__.，则sql查询会变得更快。
• 完全引用表名mydatabase.mytable。
• 如果您必须使用ORDER BY，可以使用primary key (它是一个indexed字段，或者在您打算在ORDERING上的字段上创建一个index )。
• 使用mysql框架函数进行数学计算，可以加快计算速度。
• 最后，尝试使用这些步骤使查询尽可能简单(越简单越快)。

源

• http://beginner-sql-tutorial.com/sql-query-tuning.htm
• http://infolab.stanford.edu/~hyunjung/cs346/ioannidis.pdf
• http://www.webmasterworld.com/forum112/59.htm
• http://odetocode.com/code/237.aspx
• http://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/