PostgreSQL 안티패턴: 이름별 검색의 반복적 개선 또는 "앞뒤로 최적화"에 대한 이야기

전국 영업점의 수천명의 관리자가 기록을 보유하고 있습니다. 우리의 CRM 시스템 매일 수만 건의 연락 — 잠재 고객 또는 기존 고객과의 의사소통 사실. 이를 위해서는 먼저 고객을 찾아야 하며, 가급적이면 매우 빨리 찾아야 합니다. 그리고 이것은 이름으로 가장 자주 발생합니다.

따라서 가장 많이 로드된 데이터베이스 중 하나인 우리 자신의 데이터베이스에서 "무거운" 쿼리를 다시 한 번 분석하는 것은 놀라운 일이 아닙니다. VLSI 기업 계정, '상단'을 찾았습니다. 이름으로 "빠른" 검색 요청 조직 카드용.

게다가 추가 조사를 통해 흥미로운 사례가 밝혀졌습니다. 첫 번째 최적화 후 성능 저하 여러 팀이 순차적으로 개선하여 요청했으며, 각 팀은 최선의 의도를 가지고만 행동했습니다.

0: 사용자가 원하는 것이 무엇이었나요?

[KDPV 따라서]

사용자가 이름으로 "빠른" 검색을 말할 때 일반적으로 무엇을 의미합니까? 다음과 같은 하위 문자열에 대한 "정직한" 검색은 거의 발생하지 않습니다. ... LIKE '%роза%' - 결과에는 다음뿐만 아니라 'Розалия' и 'Магазин Роза'하지만 'Гроза' 심지어 'Дом Деда Мороза'.

사용자는 일상적인 수준에서 귀하가 그에게 다음을 제공할 것이라고 가정합니다. 단어 시작으로 검색 제목에 더 관련성이 높아지도록 하세요. 다음으로 시작 입력했습니다. 그리고 당신은 그것을 할 것입니다 거의 즉시 - 행간 입력용.

1: 작업을 제한하다

그리고 더욱이 사람은 특별히 입력하지 않을 것입니다 'роз магаз', 따라서 각 단어를 접두어로 검색해야 합니다. 아니요, 이전 단어를 의도적으로 "과소 지정"하는 것보다 사용자가 마지막 단어에 대한 빠른 힌트에 응답하는 것이 훨씬 쉽습니다. 검색 엔진이 이를 어떻게 처리하는지 살펴보세요.

일반적으로, 바르게 문제에 대한 요구 사항을 공식화하는 것은 솔루션의 절반 이상입니다. 때로는 신중한 사용 사례 분석 결과에 큰 영향을 미칠 수 있습니다.

추상 개발자는 어떤 일을 하나요?

1.0: 외부 검색 엔진

아, 검색이 어렵다. 아무것도 하고 싶지 않다. 데브옵스에 맡기자! 데이터베이스 외부에 검색 엔진(Sphinx, ElasticSearch 등)을 배포하도록 하세요.

동기화 및 변경 속도 측면에서 노동 집약적이지만 작업 옵션입니다. 그러나 우리의 경우에는 해당 계정 데이터의 프레임워크 내에서만 각 고객에 대해 검색이 수행되기 때문에 그렇지 않습니다. 그리고 데이터의 변동성은 상당히 높습니다. 관리자가 이제 카드를 입력했다면 'Магазин Роза', 5-10초 후에 그는 자신의 이메일을 표시하는 것을 잊었고 그것을 찾아 수정하고 싶다는 것을 이미 기억할 수 있습니다.

그러므로 - 하자 "데이터베이스에서 직접" 검색. 다행스럽게도 PostgreSQL을 사용하면 이를 수행할 수 있으며 단지 하나의 옵션이 아니라 이에 대해 살펴보겠습니다.

1.1: "정직한" 하위 문자열

우리는 "하위 문자열"이라는 단어를 고수합니다. 하지만 하위 문자열(심지어 정규 표현식으로도!)을 사용한 색인 검색에는 탁월한 방법이 있습니다. 모듈 pg_trgm! 그래야만 올바르게 정렬해야 합니다.

모델을 단순화하기 위해 다음 플레이트를 사용해 보겠습니다.

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

우리는 실제 조직에 대한 7.8만 개의 기록을 업로드하고 색인을 생성합니다.

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

행간 검색을 위해 처음 10개의 레코드를 찾아보겠습니다.

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[explain.tensor.ru 참조]

음, 그런... 26ms, 31MB 1.7개의 검색된 항목에 대해 데이터 및 10K 이상의 필터링된 레코드를 읽습니다. 간접비가 너무 높네요. 좀 더 효율적인 방법은 없을까요?

1.2: 텍스트로 검색하시겠습니까? FTS입니다!

실제로 PostgreSQL은 매우 강력한 기능을 제공합니다. 전체 텍스트 검색 엔진 (전체 텍스트 검색), 접두어 검색 기능 포함. 훌륭한 옵션입니다. 확장 프로그램을 설치할 필요도 없습니다! 해보자:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[explain.tensor.ru 참조]

여기서는 쿼리 실행의 병렬화가 약간 도움이 되었으며, 시간을 절반으로 단축했습니다. 11ms. 그리고 우리는 전체적으로 1.5배 더 적은 양을 읽어야 했습니다. 20MB. 그러나 여기서는 읽을 볼륨이 클수록 캐시 누락 가능성이 높아지고 디스크에서 읽은 모든 추가 데이터 페이지가 요청에 대한 잠재적인 "제동"이 되기 때문에 적을수록 좋습니다.

1.3: 아직도 좋아요?

이전 요청은 누구에게나 좋지만 하루에 수십만 번 당겨야만 올 것입니다. 2TB 데이터를 읽습니다. 가장 좋은 경우는 메모리에서, 운이 좋지 않다면 디스크에서 가져오는 것입니다. 그럼 좀 더 작게 만들어 보겠습니다.

사용자가 보고 싶어하는 것을 기억하자 첫 번째 "...로 시작하는". 그래서 이것은 가장 순수한 형태입니다 접두사 검색 를 통해 text_pattern_ops! 그리고 찾고 있는 최대 10개의 레코드가 "충분하지 않은" 경우에만 FTS 검색을 사용하여 해당 레코드 읽기를 완료해야 합니다.

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[explain.tensor.ru 참조]

우수한 성능 - 전체 0.05ms, 100KB 조금 넘음 읽다! 우리만 잊어버렸어 이름으로 분류하다사용자가 결과에서 길을 잃지 않도록:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[explain.tensor.ru 참조]

아, 뭔가 더 이상 그다지 아름답지 않습니다. 색인이 있는 것 같지만 정렬이 지나갑니다... 물론 이미 이전 옵션보다 몇 배 더 효과적이지만...

1.4: "파일로 마무리"

하지만 범위별로 검색하고 정렬을 정상적으로 사용할 수 있는 색인이 있습니다. 일반 B트리!

CREATE INDEX ON firms(lower(name));

이에 대한 요청만 "수동으로 수집"되어야 합니다.

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[explain.tensor.ru 참조]

훌륭함 - 정렬이 작동하고 자원 소비가 "미시적"으로 유지됩니다. "순수한" FTS보다 수천 배 더 효과적입니다.! 남은 것은 이를 하나의 요청으로 통합하는 것입니다.

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

두 번째 하위 쿼리가 실행됩니다. 첫 번째 항목이 예상보다 적게 반환된 경우에만 마지막 LIMIT 줄 수. 저는 이 쿼리 최적화 방법에 대해 이야기하고 있습니다. 이미 전에 썼어.

그렇습니다. 이제 테이블에는 btree와 gin이 모두 있지만 통계적으로는 요청의 10% 미만이 두 번째 블록 실행에 도달합니다.. 즉, 작업에 대해 사전에 알려진 일반적인 제한 사항을 통해 서버 리소스의 총 소비를 거의 천 배나 줄일 수 있었습니다!

1.5*: 파일 없이도 가능

더 높은 LIKE 잘못된 정렬을 사용하는 것이 방지되었습니다. 그러나 USING 연산자를 지정하여 "올바른 경로에 설정"할 수 있습니다.

기본적으로 가정됩니다. ASC. 또한 절에 특정 정렬 연산자의 이름을 지정할 수 있습니다. USING. 정렬 연산자는 일부 B-트리 연산자 계열보다 작거나 큼에 속해야 합니다. ASC 일반적으로 동등 USING < и DESC 일반적으로 동등 USING >.

우리의 경우 "덜"은 ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[explain.tensor.ru 참조]

2: 요청이 어떻게 변하는가

이제 우리는 XNUMX개월 또는 XNUMX년 동안 "끓이기" 요청을 남겨두고 일일 총 메모리 "펌핑" 지표와 함께 다시 "상단"에 있다는 사실에 놀랐습니다.버퍼 공유 적중)에서 5.5TB -즉, 원래보다 훨씬 더 많습니다.

아니요, 물론 비즈니스도 성장했고 작업량도 늘어났습니다. 하지만 그 정도는 아니었습니다! 이것은 여기에 뭔가 수상한 것이 있다는 것을 의미합니다. 알아 보겠습니다.

2.1: 페이징의 탄생

어느 시점에서 다른 개발 팀은 동일하지만 확장된 결과를 사용하여 빠른 첨자 검색에서 레지스트리로 "점프"할 수 있는 기능을 만들고 싶었습니다. 페이지 탐색이 없는 레지스트리란 무엇입니까? 망치자!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

이제 개발자는 스트레스 없이 "페이지별" 로딩으로 검색 결과 레지스트리를 표시할 수 있게 되었습니다.

물론 실제로는 이후의 데이터 페이지마다 점점 더 많은 양의 데이터가 읽혀집니다. (이전 시간의 모든 내용과 필요한 "꼬리"를 추가함) - 즉, 이는 명확한 반패턴입니다. 그러나 인터페이스에 저장된 키를 사용하여 다음 반복에서 검색을 시작하는 것이 더 정확할 것입니다.

2.2: 이국적인 것을 원해요

어느 시점에서 개발자는 원했습니다. 결과 샘플을 데이터로 다양화 전체 이전 요청이 CTE로 전송된 다른 테이블에서:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

그럼에도 불구하고 하위 쿼리는 반환된 10개의 레코드에 대해서만 평가되므로 나쁘지 않습니다.

2.3: DISTINCT는 무의미하고 무자비합니다.

2차 서브쿼리에서 그런 진화 과정 어딘가에서 잃어버린 NOT LIKE 조건. 이 이후에는 분명하다. UNION ALL 돌아오기 시작했다 일부 항목이 두 번 - 처음에는 줄의 시작 부분에서 발견되고, 그 다음에는 다시 - 이 줄의 첫 번째 단어의 시작 부분에서 발견됩니다. 한계 내에서 두 번째 하위 쿼리의 모든 레코드는 첫 번째 하위 쿼리의 레코드와 일치할 수 있습니다.

개발자는 원인을 찾는 대신 무엇을 합니까?.. 질문없습니다!

• 크기를 두 배로 늘리다 원본 샘플
• DISTINCT를 적용하다각 줄의 단일 인스턴스만 얻으려면

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

즉, 결과는 결국 똑같다는 것이 분명하지만 두 번째 CTE 하위 쿼리로 "날아갈" 확률이 훨씬 높아졌으며 이것이 없어도 확실히 더 읽기 쉬워요.

그러나 이것은 가장 슬픈 일이 아닙니다. 개발자가 선택하라고 해서 DISTINCT 특정 분야가 아닌 모든 분야를 동시에 대상으로 레코드가 있으면 하위 쿼리의 결과인 sub_query 필드가 자동으로 여기에 포함됩니다. 이제 실행하려면 DISTINCT, 데이터베이스가 이미 실행되어야 했습니다. 10개의 하위 쿼리가 아니라 모두 <2 * N> + 10!

2.4: 무엇보다도 협력!

따라서 개발자는 계속 살았습니다. 사용자는 각 후속 "페이지"수신이 만성적으로 느려지면서 레지스트리를 중요한 N 값으로 "조정"할 충분한 인내심이 없었기 때문에 신경 쓰지 않았습니다.

다른 부서의 개발자들이 와서 이렇게 편리한 방법을 사용하고 싶어지기 전까지는 반복 검색을 위해 - 즉, 일부 샘플에서 조각을 가져와 추가 조건으로 필터링하고 결과를 그린 다음 다음 조각(우리의 경우 N을 증가시켜 달성) 등을 화면을 채울 때까지 계속합니다.

일반적으로 포획된 표본에서는 N은 거의 17K 값에 도달했습니다., 단 하루 만에 이러한 요청 중 최소 4K가 "체인을 따라" 실행되었습니다. 그 중 마지막은 대담하게 스캔되었습니다. 반복당 1GB 메모리...

전체로

출처 : habr.com