使用函数计算两点（经度，纬度）之间的距离进行排序时，MySQL查询速度变慢

Question

使用函数计算两点（经度，纬度）之间的距离进行排序时，MySQL查询速度变慢

4

我在MySQL中有一个查询，它在表的每一行上运行一个存储的函数，然后按照函数的结果对行进行排序，最后返回前10行。

SELECT rowId, MyFunction(x, y, constX, constY) AS funResult
FROM myTable
ORDER BY funResult DESC
LIMIT 10

问题在于对于有10,000行的表格，运行需要几秒钟时间，这太慢了。函数的结果不能作为另一行存储在表格中，因为它需要一个由PHP给出且每次查询时都不同的常数。

函数本身的速度并不是问题，因为去掉ORDER BY funResult DESC LIMIT 10后，查询可以在少于0.01秒内运行。

问题必须在于排序行的过程 - 是否有任何方法可以更快地完成此操作，考虑到只需要前10行？

更新

正在使用的简化函数计算每行与指定点（其中LAT_B和LON_B是查询相关的常量）之间的距离：

CREATE FUNCTION MyFunction(LAT_A float, LON_A float, LAT_B float, LON_B float)
RETURNS double
DETERMINISTIC
BEGIN

DECLARE tempCalc DOUBLE;
SET tempCalc = 3956 * 2 * ASIN(SQRT( POWER(SIN((LAT_A -abs( LAT_B)) * pi()/180 / 2),2)    
    + COS(LAT_A * pi()/180 ) * COS( abs(LAT_B) *  pi()/180)
    * POWER(SIN((LON_A - LON_B)
    * pi()/180 / 2), 2) ));

RETURN tempCalc;

END

- user882807

2

请展示您的函数定义。 - eggyal

是的，我们也应该看到您的函数定义。 - Menelaos

1

@user882807 我认为你最有效的选择是这样做：https://dev59.com/xnNA5IYBdhLWcg3wVcJx - Menelaos

相关：https://dev59.com/dGjWa4cB1Zd3GeqPsZ5I - Menelaos

为什么不使用简单的勾股定理呢？ - Justo

显示剩余4条评论

5个回答

2

扩展你的函数：

MyFunction(col1, col2, constant) = (col1 - constant) * 2.0 + col2 * 3.0
                                 = 2*col1 + 3*col2 - 2*constant

因此，按照 MyFunction(col1, col2, constant) 排序相当于按照 2*col1 + 3*col2 排序，而不考虑提供的常量。因此，你可以把结果缓存到一个新的索引列中：

ALTER TABLE myTable
  ADD COLUMN tmpResult FLOAT,
  ADD INDEX (tmpResult);

CREATE TRIGGER ins BEFORE INSERT ON myTable FOR EACH ROW
  SET NEW.tmpResult := 2*NEW.col1 + 3*NEW.col2;

CREATE TRIGGER upd BEFORE UPDATE ON myTable FOR EACH ROW
  SET NEW.tmpResult := 2*NEW.col1 + 3*NEW.col2;

UPDATE myTable SET tmpResult = 2*col1 + 3*col2;

然后你的 SELECT 就变成了：

SELECT   rowId, tmpResult - 2*constant AS funResult
FROM     myTable
ORDER BY tmpResult DESC
LIMIT    10

- eggyal

抱歉，我试图让这个函数更简单 - 我已经更新了它以显示实际的功能。 - user882807

1

我猜测你的问题在于函数执行所需的时间。如果你执行以下查询：

SELECT rowId, MyFunction(col1, col2, constant) AS funResult
FROM myTable
LIMIT 10

数据库必须：

计算10行的函数结果
返回这10行

相比之下，如果执行此查询：

   SELECT rowId, MyFunction(col1, col2, constant) AS funResult
   FROM myTable
   ORDER BY funResult DESC
   LIMIT 10

数据库必须：

计算表中所有10000行的函数结果
对10000行进行排序
返回前10行

因此，要确定您的函数是否成为瓶颈，您应确保对于两个查询，都实际计算了所有10000行的函数结果，并检查差异是否存在。

- Frank Schmitt

1

当我移除了 ORDER BY 后，我也移除了 LIMIT，这样该函数将会对每一行运行。 - user882807

1

在mysql中，这种做法实际上要快得多。

用以下命令排序：

select * from database order by 3956 * 2 * ASIN(SQRT( POWER(SIN((LAT_A -abs( LAT_B)) * pi()/180 / 2),2) + COS(LAT_A * pi()/180 ) * COS( abs(LAT_B) *  pi()/180) * POWER(SIN((LON_A - LON_B) * pi()/180 / 2), 2) ));

比使用自定义函数排序要快得多，尽管代码看起来很丑。

请尝试对其进行说明。由于涉及到函数时，MySQL会使用一个临时表，但仅进行数学计算时不会。

- Jon Gabrielson

0

试一下这个

  SELECT rowId, MyFunction(col1, col2, constant) AS funResult
  FROM myTable
  ORDER BY MyFunction(col1, col2, constant)  DESC
  LIMIT 10

- echo_Me

1

我想实际检查一下避免别名是否会有显著的差异... - Menelaos

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

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

- Menelaos · Accepted Answer

选项：

在存储过程定义/逻辑中加入排序。如果您的调用SQL select在存储过程内执行排序和限制-这意味着您不需要在存储过程中生成10,000行再重新排序。而且，如果表具有索引，原始排序可能会更快。
验证索引是否在您的表中使用。 - 在选择表时，索引将使您的排序更快。

请提供函数定义，这样我们可以更容易地帮助您。

最后，请尝试将order by和limit直接移至您的函数中，而不是稍后执行它们。您的函数可以直接返回已排序好的10个结果。如果您想要，可以创建两个函数-一个返回完整结果，另一个返回排序后的结果。

更新：

看到您的函数后，很明显您正在尝试按计算值排序。按计算值排序非常慢，正如以下帖子所述：

我正在考虑如何基于col1或col2“预处理/排序”数据，以加速最终结果的排序。如果col1和col2是表的列，并且funResult是可以绘制成图形的数学函数，则其中一个对函数返回值的影响更大....

最后，如果col1和col2是myTable的列，则无需使用存储函数，但这不会有太大的区别...您的主要问题是按计算函数排序：

SELECT rowId, ((col1-INPUT_CONST)*2)+(col2*3) AS funResult
FROM myTable
ORDER BY funResult DESC
LIMIT 10

更新2：

在研究按计算距离排序的问题时，我发现这个问题已经被问过并在下面的链接中得到了有效解决。关于按计算值排序，由于你是按计算值排序，所以它本质上是很慢的。请参阅以下两个链接以获得更多帮助：

http://www.mooreds.com/wordpress/archives/547 - "优化mysql查询中的距离计算"
最快找到两个纬度/经度点之间距离的方法。

最后，与您答案最接近的是： https://stackoverflow.com/a/4180065/1688441