한 SQL 조사 이야기

지난 XNUMX월에 저는 VWO 지원팀으로부터 흥미로운 버그 보고서를 받았습니다. 대규모 기업 클라이언트에 대한 분석 보고서 중 하나의 로드 시간은 엄청나 보였습니다. 그리고 이것이 제가 담당하는 영역이기 때문에 즉시 문제 해결에 집중했습니다.

선사 시대

내가 말하는 내용을 명확하게하기 위해 VWO에 대해 조금 말씀 드리겠습니다. 이는 웹사이트에서 다양한 타겟 캠페인을 시작할 수 있는 플랫폼입니다. A/B 실험 수행, 방문자 및 전환 추적, 판매 퍼널 분석, 히트 맵 표시, 방문 기록 재생 등이 가능합니다.

하지만 플랫폼에서 가장 중요한 것은 보고입니다. 위의 모든 기능은 서로 연결되어 있습니다. 기업 고객의 경우, 막대한 양의 정보는 이를 분석 형식으로 제공하는 강력한 플랫폼 없이는 전혀 쓸모가 없을 것입니다.

플랫폼을 사용하면 대규모 데이터 세트에 대해 무작위 쿼리를 수행할 수 있습니다. 간단한 예는 다음과 같습니다.

Chrome을 사용하거나(유럽에 위치하며 iPhone을 사용하는) 사용자에게 <date d1>부터 <date d2>까지 'abc.com' 페이지의 모든 클릭수를 표시합니다.

부울 연산자에 주의하세요. 클라이언트는 쿼리 인터페이스에서 샘플을 얻기 위해 임의로 복잡한 쿼리를 만드는 데 사용할 수 있습니다.

느린 요청

문제의 클라이언트는 직관적으로 빠르게 작동해야 하는 작업을 수행하려고 했습니다.

"/jobs"가 포함된 URL이 있는 페이지를 방문한 사용자의 모든 세션 기록을 표시합니다.

이 사이트에는 엄청난 양의 트래픽이 있었고 우리는 이 사이트를 위해 백만 개가 넘는 고유 URL을 저장하고 있었습니다. 그리고 그들은 그들의 비즈니스 모델과 관련된 매우 간단한 URL 템플릿을 찾고 싶었습니다.

예비조사

데이터베이스에서 무슨 일이 일어나고 있는지 살펴보겠습니다. 다음은 원래의 느린 SQL 쿼리입니다.

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

타이밍은 다음과 같습니다.

계획된 시간: 1.480ms 실행 시간: 1431924.650ms

쿼리는 150만 행을 크롤링했습니다. 쿼리 플래너에는 몇 가지 흥미로운 세부 정보가 표시되었지만 뚜렷한 병목 현상은 없었습니다.

요청을 더 자세히 연구해 보겠습니다. 보시다시피 그는 그렇습니다 JOIN 테이블 XNUMX개:

1. 세션: 브라우저, 사용자 에이전트, 국가 등 세션 정보를 표시합니다.
2. 녹음_데이터: 기록된 URL, 페이지, 방문 기간
3. URL이: 매우 큰 URL의 중복을 피하기 위해 별도의 테이블에 저장합니다.

또한 모든 테이블은 이미 다음과 같이 분할되어 있습니다. account_id. 이렇게 하면 특히 큰 계정 하나가 다른 계정에 문제를 일으키는 상황이 제외됩니다.

단서를 찾고

자세히 조사해 보면 특정 요청에 문제가 있음을 알 수 있습니다. 다음 줄을 자세히 살펴볼 가치가 있습니다.

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

첫 번째 생각은 아마도 ILIKE 이 모든 긴 URL에 대해(우리는 1,4만 개 이상의 유일한 이 계정에 대해 수집된 URL) 성능이 저하될 수 있습니다.

하지만 아니요, 그게 요점이 아닙니다!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

템플릿 검색 요청 자체에는 5초밖에 걸리지 않습니다. 백만 개의 고유한 URL에서 패턴을 검색하는 것은 분명히 문제가 되지 않습니다.

목록의 다음 용의자는 여러 명입니다. JOIN. 아마도 과도한 사용으로 인해 속도가 느려졌을까요? 대개 JOIN'는 성능 문제의 가장 확실한 후보이지만 우리 사례가 전형적인 것이라고는 생각하지 않았습니다.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

그리고 이것은 또한 우리의 경우가 아니었습니다. JOIN꽤 빠른 것으로 나타났습니다.

용의자 범위 좁혀

가능한 성능 향상을 달성하기 위해 쿼리 변경을 시작할 준비가 되었습니다. 우리 팀과 나는 2가지 주요 아이디어를 개발했습니다.

• 하위 쿼리 URL에 EXISTS 사용: URL에 대한 하위 쿼리에 문제가 있는지 다시 확인하고 싶었습니다. 이를 달성하는 한 가지 방법은 간단히 사용하는 것입니다. EXISTS. EXISTS 수 조건과 일치하는 유일한 문자열을 찾는 즉시 종료되므로 성능이 크게 향상됩니다.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

그렇죠. 래핑된 경우 하위 쿼리 EXISTS, 모든 것을 매우 빠르게 만듭니다. 다음 논리적 질문은 왜 요청이 JOIN-ami와 하위 쿼리 자체는 개별적으로 빠르지만 함께 사용하면 엄청나게 느립니다.

• 하위 쿼리를 CTE로 이동 : 쿼리 자체가 빠른 경우 먼저 빠른 결과를 계산한 다음 이를 기본 쿼리에 제공하면 됩니다.

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

하지만 여전히 매우 느렸습니다.

범인 찾기

이번에는 내 눈앞에 작은 일 하나가 번쩍였고 나는 끊임없이 그것을 옆으로 치워버렸습니다. 하지만 더 이상 남은 것이 없었기 때문에 나도 그녀를 살펴보기로 결정했습니다. 내가 얘기하는거야 && 운영자. 안녕 EXISTS 방금 성능이 향상되었습니다. && 느린 쿼리의 모든 버전에서 유일하게 남아 있는 공통 요소였습니다.

보고 문서, 우리는 그것을 본다 && 두 배열 사이의 공통 요소를 찾아야 할 때 사용됩니다.

원래 요청에서는 다음과 같습니다.

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

즉, URL에서 패턴 검색을 수행한 다음 공통 게시물이 있는 모든 URL과의 교차점을 찾습니다. 여기서 "urls"는 모든 URL이 포함된 테이블을 참조하는 것이 아니라 테이블의 "urls" 열을 참조하기 때문에 다소 혼란스럽습니다. recording_data.

이에 대한 의혹이 커지면서 &&, 생성된 쿼리 계획에서 이에 대한 확인을 찾으려고 했습니다. EXPLAIN ANALYZE (이미 계획을 저장해 두었지만 일반적으로 쿼리 플래너의 불투명도를 이해하려고 노력하는 것보다 SQL로 실험하는 것이 더 편합니다.)

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

에서만 여러 줄의 필터가있었습니다. &&. 이는 이 작업이 비용이 많이 들 뿐만 아니라 여러 번 수행된다는 것을 의미했습니다.

조건을 분리하여 테스트했습니다.

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

이 쿼리는 느렸습니다. 왜냐하면 JOIN-s는 빠르고 하위 쿼리도 빠릅니다. 남은 유일한 것은 && 운영자.

이것은 단지 핵심 작업일 뿐입니다. 패턴을 검색하려면 항상 기본 URL 테이블 전체를 검색해야 하며 항상 교차점을 찾아야 합니다. URL 레코드로는 직접 검색할 수 없습니다. 왜냐하면 이는 단지 ID를 참조하는 것일 뿐이기 때문입니다. urls.

해법으로 가는 길

&& 두 세트 모두 거대하기 때문에 느립니다. 교체하면 수술이 비교적 빨리 될 것 같아요 urls 에 { "http://google.com/", "http://wingify.com/" }.

나는 사용하지 않고 Postgres에서 교차점을 설정하는 방법을 찾기 시작했습니다. &&, 그러나 많은 성공을 거두지 못했습니다.

결국 우리는 문제를 개별적으로 해결하기로 결정했습니다. 모든 것을 나에게 주십시오. urls URL이 패턴과 일치하는 줄. 추가 조건이 없으면 다음과 같습니다. 

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

대신에 JOIN 방금 하위 쿼리를 사용하고 확장했습니다. recording_data.urls 조건을 직접 적용할 수 있도록 배열 WHERE.

여기서 가장 중요한 것은 && 주어진 항목에 일치하는 URL이 포함되어 있는지 확인하는 데 사용됩니다. 조금만 살펴보면 이 작업이 배열의 요소(또는 테이블의 행)를 통해 이동하고 조건(일치)이 충족되면 중지되는 것을 볼 수 있습니다. 아무것도 생각나지 않나요? 응, EXISTS.

이후 recording_data.urls 하위 쿼리 컨텍스트 외부에서 참조할 수 있습니다. 이런 일이 발생하면 우리는 오랜 친구로 돌아갈 수 있습니다. EXISTS 하위 쿼리를 그것으로 래핑합니다.

모든 것을 종합하면 최종적으로 최적화된 쿼리를 얻을 수 있습니다.

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

그리고 마지막 리드타임 Time: 1898.717 ms 축하할 시간?!?

그렇게 빠르지는 않아요! 먼저 정확성을 확인해야합니다. 나는 매우 의심스러웠다. EXISTS 더 일찍 완료되도록 논리를 변경하므로 최적화됩니다. 요청에 명확하지 않은 오류를 추가하지 않았는지 확인해야 합니다.

간단한 테스트를 실행하는 것이 었습니다. count(*) 다양한 데이터 세트에 대한 느리고 빠른 쿼리 모두에 적용됩니다. 그런 다음 데이터의 작은 하위 집합에 대해 모든 결과가 올바른지 수동으로 확인했습니다.

모든 테스트에서는 지속적으로 긍정적인 결과가 나왔습니다. 우리는 모든 것을 고쳤습니다!

교훈

이 이야기에서 배울 수 있는 교훈은 다음과 같습니다.

1. 쿼리 계획은 전체 내용을 알려주지는 않지만 단서를 제공할 수 있습니다.
2. 주요 용의자가 항상 실제 범인은 아닙니다.
3. 느린 쿼리를 분석하여 병목 현상을 격리할 수 있습니다.
4. 모든 최적화가 본질적으로 환원적인 것은 아닙니다.
5. 사용 EXIST, 가능한 경우 생산성이 크게 향상될 수 있습니다.

출력

쿼리 시간이 ~24분에서 2초로 단축되었습니다. 성능이 상당히 향상되었습니다! 이 글이 크게 나왔음에도 불구하고 우리가 했던 모든 실험은 하루만에 이루어졌고, 최적화와 테스트에 1,5~2시간 정도 소요된 것으로 추정됩니다.

SQL은 두려워하지 않는다면 훌륭한 언어이지만, 배우고 사용해 보십시오. SQL 쿼리가 실행되는 방법, 데이터베이스가 쿼리 계획을 생성하는 방법, 인덱스 작동 방법 및 처리 중인 데이터의 크기를 잘 이해하면 쿼리를 매우 성공적으로 최적화할 수 있습니다. 그러나 계속해서 다양한 접근 방식을 시도하고 천천히 문제를 분석하여 병목 현상을 찾는 것도 마찬가지로 중요합니다.

이와 같은 결과를 얻을 때 가장 좋은 점은 이전에는 로드되지 않았던 보고서가 이제는 거의 즉시 로드되는 눈에 띄게 눈에 띄게 속도가 향상된다는 것입니다.

특별히 감사함 내 동지들 아디티야 미쉬라(Aditya Mishra)의 명령에 따라, 아디트야 가우루 и 바룬 말호트라 브레인스토밍과 딘카르 판디르 마지막 요청에 작별 인사를 하기 전에 중요한 오류를 발견해주셔서 감사합니다!

출처 : habr.com