有没有办法优化在每一行上运行函数的 MySQL 查询？

Question

我有一个 MySQL 查询，它根据标准从数据库中提取经纬度，测试这些点是否在多边形内，并返回多边形内的点。

一切正常。问题是查询大约需要花费时间。 20秒返回结果。有没有办法优化这个查询，让查询速度更快？

SELECT latitude, longitude
FROM myTable
WHERE offense = 'green' AND myWithin(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON(( ...bunch of lat longs...))' )
) = 1;

我运行了 EXPLAIN SELECT... 产生了

id |选择类型 |表|类型 | 可能的键 |关键| key_len |参考| 行 |额外

1 SIMPLE myTable ALL NULL NULL NULL NULL 137003 使用 where

是否有一种方法可以优化在数据库中的每个纬度和经度上运行的查询，或者这是否已经达到最佳状态？

我正在考虑对另一个表进行选择，然后查询结果表，但我希望有一种方法可以提高此查询的性能。

如果有人有任何建议或想法，我很想听听。

谢谢，

拉克斯米迪

Answer 1

多边形有多大？您可以在整个多边形周围定义一个“边界矩形”，然后执行以下操作：

SELECT latitude, longitude
FROM myTable
WHERE
  offense = 'green' AND
  latitude BETWEEN rect_left AND rect_right AND
  longitude BETWEEN rect_top AND rect_bottom AND
  myWithin(
    POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ),
    POLYFROMTEXT( 'POLYGON(( ...bunch of lat longs...))' )) = 1;

这样，它可以使用纬度和经度上的索引来缩小必须运行复杂内容的点的数量。

Answer 2

我看到两个明显的优化途径：

• 在运行函数 O(n) 次之前减少结果集。现在您正在运行该函数 137003 次 - 如果您无法进一步过滤结果集，则几乎没有办法避免这种情况。

• 使函数更快，这样您仍然运行它 137k 次，但每次调用花费的时间更少，从而减少总运行时间。

现在，您的函数每行运行需要 0.1459 毫秒，这确实不错。您可能想尝试找到某种方法来进一步减少必须运行它的行数。通过巧妙使用 WHERE 来减少结果集还有一个附带的好处，即允许数据库为您进行一些优化，这就是您希望使用它的方式。