时间戳

首页 > 解决方案 > 具有地理空间 ST_Contains 的 Mysql 选择计数对于多行非常慢

mysql - 具有地理空间 ST_Contains 的 Mysql 选择计数对于多行非常慢

问题描述

我有一个 mysql 查询来获取一个区域的所有地方计数。如果我只查询一个 id 它真的很快，如果我查询两个或更多的 id 那么它真的很慢。

Areas.geometry 和 Places.location 是 SPATIAL 索引。

区域表中只有 3 行（都具有复杂的几何形状。第 3 行更复杂）和商店中的 3000 行。如果你想测试，我会建立一个演示 sql 文件来导入：geospatial-exemple.sql

一些例子：

此查询在 260 毫秒内运行：

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(a.geometry,p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (1)

在此处输入图像描述

此查询在 320 毫秒内运行：

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(a.geometry,p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (3)

在此处输入图像描述

此查询在50 秒内运行：

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(a.geometry,p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (1,3)

在此处输入图像描述

我还尝试使用更复杂的 MULTIPOLYGON 对查询中的 area.geometry 进行硬编码

此查询在 380 毫秒内运行：

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(ST_GeomFromText("MULTIPOLYGON((...))",
                                    4326,
                                    'axis-order=long-lat'),p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (1,3)

在此处输入图像描述

所以很明显，运行多个查询比只运行一个并等待一分钟要快。如果有人知道这是 mysql 错误还是有其他方法可以做到这一点？使用 Join 查询给出相同的结果。

标签： mysqlinnodbgeospatial

解决方案

根据John Powells 的回答，空间索引存在未记录的限制：

为了使 Contains 和 Intersects 函数正常工作以及要使用的索引，您需要将其中一个几何图形设为常量。这似乎没有记录在案，尽管您将在 MySQL with Intersects/Contains 中看到的所有示例都以这种方式工作。

因此，每个区域运行多个查询确实会更快。

但是，如果您有权创建函数，则可以通过在函数中运行子查询来使用解决方法，areas.geometry现在 where 将作为常量参数ST_Contains()：

CREATE FUNCTION fn_getplacescount(_targetarea GEOMETRY) 
RETURNS INT READS SQL DATA
RETURN (SELECT COUNT(*) FROM places p WHERE ST_Contains(_targetarea, p.location));

现在

SELECT a.name, fn_getplacescount(a.geometry) AS places_count 
FROM areas a WHERE a.id in (1,3);

将类似于单独运行每个区域，并且应该具有与使用两个单独查询相似的执行时间。

推荐阅读