使用Order By with函数计算两点(长，纬度)之间的距离时，MySQL Query Slow [英] MySQL Query Slow when using Order By with function to calculate distance between two points (long, lat)

问题描述

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

I have a query in MySQL which runs a stored function on each row of a table and then orders the rows by the result of the function before returning the first 10 rows.

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

问题在于，在具有10,000行的表上运行需要花费几秒钟的时间，这太慢了.该函数的结果无法计算并存储为表中的另一行，因为它需要一个常量，该常量由PHP给出，并且每次运行查询时都不同.

The problem is that it takes several seconds to run on a table with 10,000 rows which is much too slow. The result of the function can't be computed and stored as another row in the table because it takes a constant which is given by PHP and is different each time the query is run.

函数本身的速度不是问题，因为删除ORDER BY funResult DESC LIMIT 10意味着查询运行时间不到0.01秒.

The speed of the function itself is not the problem, since removing ORDER BY funResult DESC LIMIT 10 means that the query runs in less than 0.01 seconds.

问题必须是对行进行排序-考虑到只需要前10行，有什么方法可以更快地完成此操作?

The problem must be sorting the rows - is there any way this can be done faster, considering the fact that only the first 10 rows are needed?

更新

所使用的简化函数计算每行与指定点之间的距离(其中LAT_B和LON_B是取决于查询的常数):

The simplified function being used calculates the distance between each row and a specified point (where LAT_B and LON_B are constants dependent on the query):

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

推荐答案

选项:

1. 将排序纳入您的存储过程中 定义/逻辑.如果您在存储过程中选择要调用的SQL，请在其中进行排序和限制. -这意味着您不会在存储过程中产生10,000行，而只是诉诸它们.另外，如果表具有索引，则在SQL中进行原始排序可能会更快.

1. Incorporate sorting within your stored procedure definition/logic. If your calling SQL select within your stored procedure perform the sort and limit there. - This means you will not be producing 10,000 rows in the stored procedure, just to resort them. Also, if table has indexes original sort within SQL select may be much faster.

验证表中是否使用了索引.-索引将使您在表上进行选择时可以更快地执行排序.

Verify that indexing is used within your table. - Indexes will cause your sorts to be performed quicker when selecting on the table.

请向我们提供函数定义，这样可以更轻松地为您提供帮助.

最后，尝试移动订单并直接在函数内进行限制，而不是稍后再执行.您的函数可以返回直接排序并准备好的10个结果. 如果需要，可以做两个函数-一个返回完整的结果，另一个返回有限和排序的结果.

Finally, try to move your order by and limit directly within your function as opposed to doing them later. Your function can return the 10 results directly sorted and ready. If you want, make two functions - one that returns the full results and one that returns them limited and sorted.

看到功能后，很明显，您尝试按计算所得的值进行排序.按计算值排序非常缓慢，如以下所述:

After seeing your function it becomes apparent that your trying to order by a calculated value. Ordering by calculated values is extremely slow as also mentioned in:

• https://stackoverflow.com/a/3401611/1688441
• 在计算列上订购时的MySQL性能

• https://stackoverflow.com/a/3401611/1688441
• MySQL-Performance when ordering on calculated column

我试图考虑如何基于col1或col2来预处理/排序"数据，以加快结果的最终排序.如果col1和col2是表的列，并且funResult是一个数学函数，可以将其绘制为图形，则对函数返回值的影响更大.

I am trying to think how you could "pre-process/order" your data based on col1 or col2 in order to speed up the ultimate ordering of your results. If col1 and col2 are columns of the table, and funResult is a mathematical function that can be graphed one of the two has a higher effect on the function return value....

最后，如果col1和col2是myTable的列，则不需要使用存储的函数，但可以使用它进行查询，但这并不会有太大的区别...您的主要问题是通过计算函数进行排序:

Finally, if col1 and col2 are columns of myTable, you do not need to use a stored function but could query with, but this wouldn't make a large difference...Your main problem is ordering by a calculated function:

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

更新2:

挖掘排序问题后，我发现在下面的链接中非常有效地要求并解决了这个问题.关于按计算值进行排序，就像按计算值进行排序一样，它本质上是缓慢的.请参阅以下两个链接以获取其他帮助:

Update 2:

After digging for the problem of sorting be calculated distance I found that this has been asked and solved in a very efficiently at the link below. In relation to sorting by a calculated value, as your sorting by a calculated value it is inherently slow. See the following two links for additional help:

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

• http://www.mooreds.com/wordpress/archives/547 - "Optimizing a distance calculation in a mysql query "
• Fastest Way to Find Distance Between Two Lat/Long Points .

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

Finally, the closest that comes to your answer is this: https://stackoverflow.com/a/4180065/1688441

这篇关于使用Order By with函数计算两点(长，纬度)之间的距离时，MySQL Query Slow的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！