MYSQL 地理搜索及距离性能

Question

MYSQL 地理搜索及距离性能

9

我在网站上有一个mysql选择语句用于搜索，但是当网站非常繁忙时会出现性能问题。下面的查询从具有超过100k记录的表中搜索广告，在给定的纬度和经度范围内（25英里以内），并按距离排序。英里数可能因用户而异。

问题在于，我认为它很慢，因为它对表中所有记录进行计算，而不是在纬度和经度范围内的记录。是否可以修改此查询，使where子句仅选择25英里以内的广告？我已经了解了边界框和空间索引，但我不确定如何将它们应用于此查询，我是否需要添加where子句来选择与纬度和经度25英里半径的记录，如何实现？

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语句上执行explain时，行数设置为67900，这比25英里半径内的实际数量要多得多，额外设置为“使用where; 使用filesort”。

查询需要0.3秒，这非常慢，特别是当网站每秒接收到大量请求时。

- user1052096

我已经注意到这个查询存在一些问题，我有一些想法可以让它更快。你能给我们预览一下表结构吗？（例如主键等） - classicjonesynz

2个回答

6

有几种方法可以加速您的查询，个人建议利用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

上述查询在具有10000条记录的表模式上运行时间为0.0008秒（您在相同的表模式上测试的查询花费了0.0129秒），因此性能有了显著提升。

其他优化提示：

- 如果在SELECT语句中使用实际列名而不是*，则SQL查询会变得更快。 - 完全引用表名mydatabase.mytable。 - 如果必须使用ORDER BY，请使用主键（它是一个索引字段，或者在要排序的字段上创建索引）。 - 使用mysql框架函数进行数学计算可以加速过程。 - 最后，尽可能使查询简单（越简单越快）。

来源

http://beginner-sql-tutorial.com/sql-query-tuning.htm（SQL查询调优入门教程）
http://infolab.stanford.edu/~hyunjung/cs346/ioannidis.pdf（数据库系统中的查询优化）
http://www.webmasterworld.com/forum112/59.htm（SQL查询优化技巧）
http://odetocode.com/code/237.aspx（SQL Server中的查询优化技巧）
http://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/（SQL Server中使用SELECT-FROM-JOIN-WHERE-GROUP BY-HAVING-ORDER BY进行简单查询）

- classicjonesynz

谢谢，我没想到我可以在where子句中引用a.DISTANCE，因为距离是在select语句中计算的，它实际上不是表中的字段。 - user1052096

经过更多阅读，我发现WHERE Clause实际上无法访问用户定义变量。我已经适当地编辑了我的答案。谢谢。 - classicjonesynz

更新了答案，并附上在一张有10,000条记录的表上进行测试的一些结果。 - classicjonesynz

你好，感谢更新。你的查询会更快，因为它没有按距离排序的子句，这会大大减慢查询速度，但这是必需的。然而，这仍然没有回答我的原始问题，如果表中有100,000条记录，它将在整个100,000条记录上进行距离计算，而不管having语句如何。我想更新查询，使其具有where子句，只包括距离纬度和经度25英里以内的记录。 - user1052096

你需要计算MBR矩形的左上角和右下角坐标。有了这些信息，如果在纬度和经度列上建立索引，就可以快速过滤掉大多数记录。最后，执行Haversine算法以获得更准确的结果。这是优化查询的唯一方法，除非你可以使用MySQL 5.6的空间函数，但我不知道它们的性能如何。 - Richard

显示剩余13条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- doublesharp · Accepted Answer

使用MySQL的地理空间扩展是最快的方法，因为您已经在使用MyISAM表格。这些扩展的文档可以在此处找到：http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html 添加一个新的列，其数据类型为POINT：

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 坐标，这些坐标要根据所需的搜索区域和给定的起始点来确定。

您最终的查询将搜索所有在搜索多边形内的 POINT 数据，并可以使用距离计算进一步细化和排序数据：

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))')将无法正常工作，您需要使用有效的坐标点替换它以匹配您搜索的起始点。如果您希望经纬度发生改变，则应考虑使用触发器来保持POINT数据和相关SPATIAL KEY的最新状态。对于大型数据集，您应该看到大大提高了性能，而不是为每个记录计算距离并使用HAVING子句进行过滤。我个人会定义一些函数以便确定距离和创建边界POLYGON。