지리 공간 ST_Contains를 사용한 Mysql Select 카운트는 여러 행에서 매우 느립니다.

Dec 17 2020

지역에서 모든 장소를 계산하는 mysql 쿼리가 있습니다. 하나의 ID 만 쿼리하면 정말 빠르며 두 개 이상의 ID를 쿼리하면 정말 느립니다.

Areas.geometry 및 Places.location은 SPATIAL 인덱스입니다.

영역 테이블에는 3 개의 행 (모두 복잡한 지오메트리가 있습니다. 3 행이 더 복잡함) 만 있고 상점에는 3000 개의 행이 있습니다. 테스트하려면 가져올 데모 SQL 파일을 작성합니다. geospatial-exemple.sql

몇 가지 예 :

이 쿼리는 260ms에서 실행됩니다.

    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) 


이 쿼리는 320ms에서 실행됩니다.

    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를 하드 코딩하려고했습니다.

이 쿼리는 380ms에서 실행됩니다.

    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 버그인지 또는 다른 방법이 있는지 알고 있다면? 조인 쿼리로 작업하면 동일한 결과가 제공됩니다.

답변

1 Solarflare Dec 18 2020 at 21:02

John Powells의 답변 에 따르면 공간 인덱스에 대한 문서화되지 않은 제한이 있습니다.

포함 및 교차 함수가 제대로 작동하고 인덱스를 사용하려면 도형 중 하나가 상수 여야합니다. Intersects / Contains가있는 MySQL에서 볼 수있는 모든 예제가 이런 방식으로 작동하지만 이것은 문서화되지 않은 것 같습니다.

따라서 각각 하나의 영역으로 여러 쿼리를 실행하는 것이 실제로 더 빠릅니다.

함수를 만들 수있는 권한이있는 경우 함수에서 하위 쿼리를 실행하여 해결 방법을 사용할 수 있습니다. areas.geometry이제 함수 는에 대한 상수 매개 변수로 작동합니다 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);

각 영역을 개별적으로 실행하는 것과 유사하며 두 개의 개별 쿼리를 사용하는 것과 유사한 실행 시간을 가져야합니다.

MichaelEntin Dec 18 2020 at 04:14

나는 그것을 조인으로 표현하고 MySQL이 더 빨리 실행되는지 확인하려고 노력할 것입니다. MySQL이 공간 조인을 최적화했는지 확실하지 않지만 내가 작업 한 데이터베이스에서는 더 빠를 것입니다.

다음과 같은 것 (구문을 확인하지 않았습니다) :

SELECT areas.name, count(*) as places_count
FROM places p JOIN areas a
ON ST_Contains(a.geometry, p.location)
WHERE a.type = "city"
GROUP BY 1;