使用编程语言还是数据库服务器进行计算，哪种更好？

Question

假设我有一个位置表（纬度，经度），我想获取到距离我的当前位置一定范围内的所有位置，我的问题是哪种方法更好：将计算包含在查询的where子句中，类似于select * from locations where /* 公式使用纬度和经度的余弦和正弦 */ < 距离。我的第二个选择是获取所有位置并在程序中本地进行计算。如果有需要，我使用Java和MySQL。

- monim

---

无论在数据库查询还是在代码中进行计算的性能差异，对于搜索最近的10个位置，数据库将使用上述查询来搜索它们并返回这十个条目。如果在代码中进行计算，则可能被迫获取所有现有数据集以计算它们之间的距离 - 否则您无法确定哪一个是最近的。 - dhh

你可以使用PostgreSQL和PostGIS代替吗？性能会好得多，非常非常好。 - Matt Ball

相关内容请参考：什么是在MySQL中存储纬度和经度的理想数据类型？; 参考资料：MySQL空间扩展; MySQL提供了计算距离的函数，因此您不必使用正弦/余弦函数。 - Kenney

当然，如果在查询中计算这些值，数据库服务器将仅返回十个值。如果在代码中计算距离，则必须获取所有位置，这将根据数据源的大小而导致大量数据。 - dhh

在大多数情况下，在WHERE子句中执行这样的计算会抵消索引的好处；仅在时间戳字段上调用DATE()函数就需要进行全表扫描。 - Uueerdo

Answer 1

根据数据和计算的复杂性，您可以尝试执行两者混合的方法。使用一个更简单、不太精确但更方便的计算（能够利用索引）来减少发送回最终处理的结果。

例如，如果标准是“距离5英里之外”，则可以计算一个包围框而非涉及几何函数的方程；检索该范围内的行，然后使用更复杂的函数排除“角落”。

或者，如果您的服务器强大而客户端相对于计算能力较弱，则将这些计算转移到服务器上可能更好。

编辑：另一个可能性是将更复杂的计算放在HAVING子句中，在WHERE中保留更简单的计算，这样只需要在通过更简单、更易于索引的过滤器的那些记录上执行。

编辑2：为了给出一个通用的例子（因为我不熟悉使用经度和纬度；您的方程可能需要考虑地球的“包裹”）。

SELECT * 
FROM theTable
WHERE x BETWEEN [minX] AND [maxX] 
AND y BETWEEN [minY] AND [maxY]
HAVING POW(x-[originX], 2) + POW(y-[originY], 2) <= POW([distance], 2)
;

- Uueerdo

---

是的，绝对正确。有人曾经说过：应该在数据库端执行必须在数据库端执行的操作，而不是在应用程序端执行并增加复杂性。现在，所有的数据库管理系统都足够强大，可以进行任何类型的计算，但是根据复杂性和易用性，有时将其卸载到客户端代码中会更好。 - Rahul

另外一件事，不管设计和复杂性如何，我知道有些编程语言比其他语言更快，那么这是否也适用于编程语言和关系型数据库？即在执行计算方面，Java和MySQL哪个更快？ - monim

一般来说，编程语言应该比关系型数据库管理系统（RDBMS）更快。我说“一般”和“应该”，因为RDBMS是由专门从事这些算法类别的开发人员设计和优化的；这些任务在程序中应该运行得更好，但这高度依赖于程序员的技能和所使用的底层库。 - Uueerdo

另一个可能影响性能的问题是，RDBMS服务器可能已经将数据存储在内存中，准备好进行处理，甚至可能已经缓存答案；但是程序需要从磁盘（或通过网络...也许是从RDBMS）加载数据。 - Uueerdo

@Uueerdo，我实际上会持相反的观点。如果您需要对每一行进行逐行处理，并且每行都有很多条件逻辑，则编程语言可能会更快，但是如果您正在进行一般分析、搜索等操作，则在数据库中几乎总是更快。 - zelarian

Answer 2

对于小数据集，如何执行查询都无关紧要。

对于大数据集，两种方式都有问题...

如果您获取所有数据，然后在客户端执行工作，那么这将产生大量的网络流量。还有很多计算。

如果您在SQL中检查距离，那么那里会有很多计算，并且整个数据集都会被扫描，除非...

如果您限制要检查的行以围绕目标点的“正方形”为基础，则可以在某种程度上减少计算。 这将需要 INDEX(lat) 或 INDEX(lng)。但遗憾的是，INDEX(lat, lng) 没有帮助。

我解决了这个问题，并提出了一个复杂的答案，其中涉及存储过程、PARTITIONing 和迭代。 但它（大部分）仅限于“正方形”，因此非常适用于大型数据集。 解释和代码在这里。

- Rick James

Answer 3

您可以使用存储过程来减少向服务器发送相同查询的流量。如果您愿意，甚至可以在客户端浏览器上进行计算，并避免在系统上进行任何计算。

- ergonaut