优化SQL距离查询

Question

我正在运行一个基于位置返回结果的MySQL查询。然而，最近我注意到这真的减缓了我的PHP应用程序。我使用了CodeIgniter，分析器显示查询需要4.2秒。geoname表有500,000行。我在关键列上有一些索引，除此之外还有什么方法可以加速这个查询？

这是我的SQL：

SELECT `products`.`product_name`
     , `geoname`.`geonameid`
     , `geoname`.`latitude`
     , `geoname`.`longitude`
     , `products`.`product_id`
     , AVG(ratings.vote) as rating
     , count(comments.comment_id) as total_comments
     ,   (6371 * acos(cos(radians(38.7666667)) 
               * cos(radians(geoname.latitude)) 
               * cos(radians(geoname.longitude) - radians(-3.3833333)) 
             +   sin(radians(38.7666667)) 
               * sin(radians(geoname.latitude)))
         ) AS distance
FROM (`foods`)
JOIN `geoname` ON `geoname`.`geonameid` = `products`.`geoname_id`
LEFT JOIN `ratings` 
  ON `ratings`.`var_id` = `products`.`product_id`
LEFT JOIN `comments` 
  ON `comments`.`var_id` = `products `.`product_id`
WHERE `products`.`product_id` != 82
GROUP BY `products`.`product_id`
HAVING `distance` < 99
ORDER BY `distance`
LIMIT 10

- Alex

Answer 1

让我们从查询本身开始。cos(radians(geoname.latitude))和其他函数似乎是不变的，因此我们可以进行一些预处理并将计算出的值存储在表中。（计算三角函数主要涉及使用成本高昂的级数展开）。

6371 * acos(cos(radians(38.7666667)) - 这等于radians(38.76667) * 6371，那为什么不用呢？这很费事。

其次，如果您不是非常关心精度，可以预先计算0到pi/2之间的10000个点的弧度值——这应该会给出一个好的近似值，最多四位小数，例如小于一公里。

(6371 * acos(cos(radians(38.7666667))
 * cos(radians(geoname.latitude))
 * cos(radians(geoname.longitude) - radians(-3.3833333))
+ sin(radians(38.7666667))
* sin(radians(geoname.latitude))))

请记住，当a > pi/2且a < pi时，sin(a)等于sin(pi - a)。当a> pi且a < 3/2 pi时，等于-sin(a-pi)。当a > 3/2 pi且a < 2pi时，等于-sin(2pi - a)。cos函数也可以类似地定义。

尝试一下，看看是否有帮助。 卢克

- luckyluke

---

acos(cos()) == max(abs(), 180), 而他正在使用 acos(cos() * cos() * cos() + sin() * sin())。 - Cees Timmerman

Answer 2

如果您可以将任何搜索位置近似为空间中的10000个点中的1000个，那么实际上，您可以沿以下方式在辅助表中存储距离：

create table distance (
position1_id int,
position2_id int,
distance int -- probably precise enough
)

在 position1_id 和 distance 上建立索引。该表可能有 10 ^ 6 到 10 ^ 8 行，但使用索引数据可以快速检索最近的 position2_id。即使这对于您来说不够精确（因为需要接受有限的分辨率），但它仍然可以让您快速消除在特定情况下不关心的位置，可能超过99％。

- Tomislav Nakic-Alfirevic

Answer 3

如果您要求MySQL EXPLAIN PLAN，我认为您会发现距离计算使您的索引变得无用。您正在强制查询引擎执行TABLE SCAN。

挽救这种情况的唯一方法是将距离放入单独的列中并对其进行索引。

- duffymo

---

将距离放在单独的列中：距离非常取决于您测量的某个点（这可能会在每个查询中发生更改）。 - Felix Kling

是的，我知道，但你可以将它放入临时表中，然后对其进行索引。 - duffymo

无论如何都得进行计算，最好有一个索引来帮助找到你想要的内容。 - duffymo

计算距离（需要从您的答案进行表扫描），将其放入表中，索引并执行“新”查询会更快吗？--更新：这只是我的想法。 - Felix Kling

经过思考，这种方法可能会变慢，因为现在你需要创建新的索引。关键是你现有的索引没有什么用处，因为你真正需要的是距离索引。无论哪种方式，都听起来像是表扫描。 - duffymo

Answer 4

你可以通过简单地除以57.29577951来排除radians()函数。这将消除每行六个数学计算。总的公式对于大型数据集上的关系查询连接并不友好。尽管如此，这里有一个不同的查询，试图在连接之前缩小视图。我不确定它是否会在测试和调整之前运行得更快或更慢。最终，我会决定在主键上构建一个统计表，并在其他表上配置触发器来维护它，这样你的最终距离计算查询将立即针对一个非常小的表运行。为了真正的卓越，我会类似地构建一个审计表，针对统计表进行趋势汇总。

select p.product_name,
g.geonameid,
g.latitude,
g.longitude,
p.product_id,
avg(r.votes) as rating,
c.total_comments,
g.distance
(select product_id, geoname_id, product_name from products where product_id != 82) p
inner join 
(select geonameid, latitude, longitude, (6371 * acos(cos(38.7666667/57.29577951) 
               * cos(latitude/57.29577951) 
               * cos((longitude/57.29577951) - (-3.3833333/57.29577951)) 
             +   sin(38.7666667/57.29577951) 
               * sin(latitude/57.29577951))
         ) AS distance
from geoname group by geonameid having distance < 99) g on p.geoname_id = g.geonameid
left join
(select var_id, count(vote) votes from ratings group by var_id) r on p.product_id = r.var_id
left join 
(select var_id, count(comment_id) total_comments from comments group by var_id) c on p.product_id = c.var_id
group by p.product_id  
order by g.distance
limit 10

- CyberTechie