PostgreSQL Antipatternləri: Axtarışın Ada görə təkrarlanan dəqiqləşdirilməsi və ya "İrəli-geri optimallaşdırma" hekayəsi

Ölkə üzrə satış ofislərindən minlərlə menecer rekorda imza atıb CRM sistemimiz hər gün on minlərlə əlaqə — potensial və ya mövcud müştərilərlə ünsiyyət faktları. Və bunun üçün ilk növbədə müştəri tapmalısınız və tercihen çox tez. Və bu, ən çox adla baş verir.

Buna görə də təəccüblü deyil ki, bir daha ən yüklənmiş verilənlər bazalarından birində - özümüzdə "ağır" sorğuları təhlil edirik. VLSI korporativ hesabı, "yuxarıda" tapdım adı ilə "sürətli" axtarış tələb edin təşkilat kartları üçün.

Üstəlik, sonrakı araşdırmalar maraqlı bir nümunə ortaya çıxardı əvvəlcə optimallaşdırma, sonra isə performansın azalması hər biri yalnız ən yaxşı niyyətlə hərəkət edən bir neçə komanda tərəfindən ardıcıl təkmilləşdirmə tələbi.

0: istifadəçi nə istəyirdi?

[KDPV buradan]

İstifadəçi adı ilə “sürətli” axtarış haqqında danışanda adətən nəyi nəzərdə tutur? Bu, demək olar ki, heç vaxt kimi bir alt sətir üçün "dürüst" axtarışa çevrilmir ... LIKE '%роза%' - çünki o zaman nəticə təkcə daxil deyil 'Розалия' и 'Магазин Роза'Lakin 'Гроза' hətta 'Дом Деда Мороза'.

İstifadəçi gündəlik səviyyədə onu təmin edəcəyinizi düşünür sözün əvvəlinə görə axtarın başlıqda və onu daha uyğun hala gətirin ilə başlayır daxil oldu. Və bunu edəcəksən demək olar ki, dərhal - xəttlərarası giriş üçün.

1: tapşırığı məhdudlaşdırın

Və daha da çox, bir şəxs xüsusi olaraq daxil olmayacaq 'роз магаз', belə ki, hər bir sözü prefikslə axtarmalısınız. Xeyr, istifadəçi üçün son söz üçün tez bir işarəyə cavab vermək, əvvəlkiləri məqsədyönlü şəkildə "aşağı müəyyənləşdirməkdən" daha asandır - hər hansı bir axtarış sisteminin bunu necə idarə etdiyinə baxın.

Ümumiyyətlə, doğru problemə tələblərin formalaşdırılması həllin yarıdan çoxunu təşkil edir. Bəzən ehtiyatlı istifadə hallarının təhlili nəticəyə əhəmiyyətli dərəcədə təsir göstərə bilər.

Mücərrəd tərtibatçı nə edir?

1.0: xarici axtarış motoru

Oh, axtarış çətindir, mən ümumiyyətlə heç nə etmək istəmirəm - gəlin bunu devoplara verək! Verilənlər bazasından kənar axtarış sistemini yerləşdirməyə icazə verin: Sphinx, ElasticSearch,...

Sinxronizasiya və dəyişikliklərin sürəti baxımından çox əmək tələb edən bir iş variantı. Ancaq bizim vəziyyətimizdə deyil, çünki axtarış hər bir müştəri üçün yalnız onun hesab məlumatları çərçivəsində aparılır. Və məlumatların kifayət qədər yüksək dəyişkənliyi var - və əgər menecer indi karta daxil olubsa 'Магазин Роза', sonra 5-10 saniyədən sonra o, artıq e-poçtunu orada göstərməyi unutduğunu xatırlaya bilər və onu tapmaq və düzəltmək istəyir.

Buna görə də - gəlin "birbaşa verilənlər bazasında" axtarın. Xoşbəxtlikdən, PostgreSQL bizə bunu etməyə imkan verir və yalnız bir seçim deyil - biz onlara baxacağıq.

1.1: "dürüst" alt sətir

Biz "alt string" sözündən yapışırıq. Ancaq alt sətirlə (və hətta müntəzəm ifadələrlə!) indeks axtarışı üçün əla seçim var modul pg_trgm! Yalnız bundan sonra düzgün çeşidləmək lazım gələcək.

Modeli sadələşdirmək üçün aşağıdakı lövhəni götürməyə çalışaq:

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

Biz orada real təşkilatların 7.8 milyon qeydini yükləyirik və onları indeksləşdiririk:

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

Xəttlərarası axtarış üçün ilk 10 qeydi axtaraq:

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

[express.tensor.ru saytına baxın]

Yaxşı, bu... 26 ms, 31 MB məlumatları oxumaq və 1.7K-dan çox süzülmüş qeydlər - 10 axtarılanlar üçün. Yerüstü xərclər çox yüksəkdir, daha səmərəli bir şey yoxdur?

1.2: mətn üzrə axtarış? Bu FTS!

Həqiqətən, PostgreSQL çox güclü təmin edir tam mətn axtarış sistemi (Tam Mətn Axtarışı), o cümlədən prefiks axtarışı imkanı. Mükəmməl seçimdir, hətta genişləndirmələr quraşdırmaq lazım deyil! Gəlin cəhd edək:

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;

[express.tensor.ru saytına baxın]

Burada sorğunun icrasının paralelləşdirilməsi bizə bir az kömək etdi, vaxtı yarıya endirdi 11 ms. Biz isə 1.5 dəfə az oxumalı olduq - cəmi 20MB. Ancaq burada nə qədər az olsa, bir o qədər yaxşıdır, çünki oxuduğumuz həcm nə qədər böyükdürsə, keşi qaçırma şansı bir o qədər yüksəkdir və diskdən oxunan məlumatların hər bir əlavə səhifəsi sorğu üçün potensial "tormoz" olur.

1.3: hələ də BƏYƏNİRSİNİZ?

Əvvəlki xahiş hamı üçün yaxşıdır, ancaq gündə yüz min dəfə çəksən, gələcək 2TB məlumatları oxumaq. Ən yaxşı halda, yaddaşdan, amma bəxtiniz gətirmirsə, diskdən. Beləliklə, onu daha kiçik etməyə çalışaq.

İstifadəçinin nə görmək istədiyini xatırlayaq ilk "hansı ilə başlayır...". Beləliklə, bu, ən təmiz formadadır prefiks axtarışı köməyi ilə text_pattern_ops! Və yalnız axtardığımız 10-a qədər qeydimiz "kifayət qədər" olmadıqda, FTS axtarışından istifadə edərək onları oxumağı bitirməli olacağıq:

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

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

[express.tensor.ru saytına baxın]

Əla performans - cəmi 0.05ms və 100KB-dən bir qədər çox oxu! Yalnız biz unutduq adına görə sıralayınistifadəçi nəticələrdə itməmək üçün:

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

[express.tensor.ru saytına baxın]

Oh, artıq nəsə o qədər də gözəl deyil - deyəsən, indeks var, amma çeşidləmə onun yanından keçir... Bu, əlbəttə ki, əvvəlki variantdan dəfələrlə effektivdir, amma...

1.4: "faylla bitir"

Ancaq diapazona görə axtarış etməyə və yenə də çeşidləmədən normal istifadə etməyə imkan verən bir indeks var - müntəzəm btree!

CREATE INDEX ON firms(lower(name));

Yalnız bunun üçün sorğu "əl ilə toplanmalıdır":

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

[express.tensor.ru saytına baxın]

Əla - çeşidləmə işləyir və resurs istehlakı "mikroskopik" olaraq qalır "təmiz" FTS-dən minlərlə dəfə daha təsirli! Qalır ki, onu bir sorğuda birləşdirməkdir:

(
  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;

Qeyd edək ki, ikinci alt sorğu yerinə yetirilir yalnız birincisi gözləniləndən az qayıtdıqda son LIMIT xətlərin sayı. Mən bu sorğunun optimallaşdırılması metodundan danışıram əvvəl yazmışdı.

Bəli, indi masada həm btree, həm də cin var, lakin statistik olaraq belə çıxır ki, sorğuların 10%-dən az hissəsi ikinci blokun icrasına çatır. Yəni, tapşırıq üçün əvvəlcədən məlum olan belə tipik məhdudiyyətlərlə biz server resurslarının ümumi istehlakını demək olar ki, min dəfə azalda bildik!

1.5*: biz faylsız edə bilərik

Ali LIKE Yanlış çeşidləmədən istifadə etməyimizin qarşısı alındı. Lakin USING operatorunu göstərməklə onu “doğru yola qoymaq” olar:

Varsayılan olaraq güman edilir ASC. Əlavə olaraq, bənddə xüsusi çeşidləmə operatorunun adını təyin edə bilərsiniz USING. Çeşidləmə operatoru B-ağacı operatorlarının bəzi ailəsinin kiçik və ya daha böyük üzvü olmalıdır. ASC adətən ekvivalentdir USING < и DESC adətən ekvivalentdir USING >.

Bizim vəziyyətimizdə "az"dır ~<~:

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

[express.tensor.ru saytına baxın]

2: sorğular necə turş olur

İndi biz altı ay və ya bir il "qaynamaq" istəyimizi tərk edirik və yaddaşın ümumi gündəlik "nasos" göstəriciləri ilə yenidən "yuxarıda" tapdığımıza təəccüblənirik (buferlər paylaşılan hit) içində 5.5TB - yəni ilkin olduğundan daha çox.

Yox, təbii ki, işimiz böyüdü və iş yükümüz artdı, amma eyni miqdarda deyil! Bu o deməkdir ki, burada nəsə balıq var - gəlin bunu anlayaq.

2.1: peyjinqin doğulması

Bir nöqtədə, başqa bir inkişaf komandası eyni, lakin genişləndirilmiş nəticələrlə sürətli bir alt yazı axtarışından reyestrə "atlamağı" mümkün etmək istədi. Səhifə naviqasiyası olmayan reyestr nədir? Gəlin onu batıraq!

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

İndi tərtibatçı üçün heç bir stress olmadan axtarış nəticələrinin reyestrini “səhifə-səhifə” yükləmə ilə göstərmək mümkün idi.

Əlbəttə, əslində, məlumatların hər bir sonrakı səhifəsi üçün daha çox oxunur (əvvəlki vaxtdan, atacağımız hər şey, üstəlik lazımi "quyruq") - yəni bu aydın bir antipatterndir. Ancaq axtarışı növbəti iterasiyada interfeysdə saxlanılan açardan başlamaq daha düzgün olardı, lakin bu barədə başqa vaxt.

2.2: Mən ekzotik bir şey istəyirəm

Bir anda tərtibatçı istədi əldə edilən nümunəni məlumatlarla şaxələndirin bütün əvvəlki sorğunun CTE-yə göndərildiyi başqa bir cədvəldən:

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

Və buna baxmayaraq, pis deyil, çünki alt sorğu yalnız 10 qaytarılmış qeyd üçün qiymətləndirilir, əgər olmasa ...

2.3: DISTINCT mənasız və amansızdır

2-ci alt sorğudan belə təkamül prosesində haradasa məğlub oldu NOT LIKE vəziyyət. Aydındır ki, bundan sonra UNION ALL qayıtmağa başladı bəzi girişlər iki dəfə - əvvəlcə sətirin əvvəlində, sonra yenə - bu sətirin birinci sözünün əvvəlində tapılır. Limitdə 2-ci alt sorğunun bütün qeydləri birincinin qeydləri ilə üst-üstə düşə bilər.

Tərtibatçı səbəb axtarmaq əvəzinə nə edir?.. Sual yoxdur!

• ikiqat ölçü orijinal nümunələr
• DISTINCT tətbiq edinhər sətrin yalnız tək nümunələrini əldə etmək

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>;

Yəni, nəticənin tamamilə eyni olduğu aydındır, lakin 2-ci CTE alt sorğusuna "uçmaq" şansı daha da artdı və hətta onsuz da, aydın daha oxunaqlıdır.

Ancaq bu, ən kədərlisi deyil. Tərtibatçı seçmək istədikdən bəri DISTINCT konkret olanlar üçün deyil, eyni anda bütün sahələr üçün qeydlər, sonra sub_sorğu sahəsi — alt sorğunun nəticəsi — avtomatik olaraq oraya daxil edilmişdir. İndi icra etmək DISTINCT, verilənlər bazası artıq icra edilməli idi 10 alt sorğu deyil, hamısı <2 * N> + 10!

2.4: hər şeydən əvvəl əməkdaşlıq!

Beləliklə, tərtibatçılar yaşadılar - narahat olmadılar, çünki istifadəçinin hər bir sonrakı "səhifənin" alınmasında xroniki bir yavaşlama ilə qeyd dəftərini əhəmiyyətli N dəyərlərinə "tənzimləmək" üçün kifayət qədər səbri yox idi.

Başqa bir departamentdən tərtibatçılar onlara gəlib belə rahat üsuldan istifadə etmək istəyənə qədər iterativ axtarış üçün - yəni hansısa nümunədən bir parça götürürük, onu əlavə şərtlərlə süzürük, nəticəni çəkirik, sonra növbəti parçanı (bizim vəziyyətimizdə bu N-i artırmaqla əldə edilir) və ekranı doldurana qədər davam edirik.

Ümumiyyətlə, tutulan nümunədə N demək olar ki, 17K dəyərlərinə çatdı, və cəmi bir gündə ən azı 4K belə sorğu “zəncir boyu” yerinə yetirildi. Onların sonuncusu cəsarətlə skan edildi Hər iterasiya üçün 1 GB yaddaş...

Ümumi

Mənbə: www.habr.com