ProHoster > Օրագիր > Վարչակազմը > PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»

Հազարավոր մենեջերներ վաճառքի գրասենյակներից ամբողջ երկրում գրանցում են մեր CRM համակարգը օրական տասնյակ հազարավոր կոնտակտներ — պոտենցիալ կամ գոյություն ունեցող հաճախորդների հետ շփման փաստեր. Եվ դրա համար նախ պետք է հաճախորդ գտնել, այն էլ՝ շատ արագ։ Եվ դա տեղի է ունենում ամենից հաճախ անունով:

Հետևաբար, զարմանալի չէ, որ ևս մեկ անգամ վերլուծելով «ծանր» հարցումները ամենաբեռնված տվյալների բազաներից մեկում՝ մեր սեփական VLSI կորպորատիվ հաշիվ, ես գտա «վերևում» «արագ» որոնման խնդրանք անունով կազմակերպչական քարտերի համար.

Ավելին, հետագա հետաքննությունը բացահայտեց մի հետաքրքիր օրինակ սկզբում օպտիմիզացում, իսկ հետո կատարողականի դեգրադացիա հարցումն իր հաջորդական ճշգրտմամբ մի քանի թիմերի կողմից, որոնցից յուրաքանչյուրը գործել է բացառապես լավագույն մտադրություններով:

0: ինչ էր ուզում օգտվողը:

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»[KDPV ուստի]

Ի՞նչ է սովորաբար նկատի ունենում օգտատերը, երբ խոսում է անվանական «արագ» որոնման մասին: Գրեթե երբեք չի ստացվում, որ նման ենթալարի «ազնիվ» որոնում է ... LIKE '%роза%' - որովհետև արդյունքը ներառում է ոչ միայն 'Розалия' и 'Магазин Роза'Սակայն 'Гроза' եւ նույնիսկ 'Дом Деда Мороза'.

Օգտագործողը ենթադրում է ամենօրյա մակարդակով, որը դուք նրան կտրամադրեք որոնել ըստ բառի սկզբի վերնագրում և ավելի համապատասխան դարձնել այն սկսվում է մտել է. Եվ դուք դա կանեք գրեթե ակնթարթորեն - միջգծային մուտքագրման համար:

1: Սահմանափակել առաջադրանքը

Եվ դեռ ավելին, մարդը կոնկրետ չի մտնի 'роз магаз', այնպես որ յուրաքանչյուր բառը պետք է որոնել ըստ նախածանցի։ Ոչ, օգտվողի համար շատ ավելի հեշտ է պատասխանել վերջին բառի արագ ակնարկին, քան կանխամտածված «թերճշգրտել» նախորդները. տեսեք, թե ինչպես է ցանկացած որոնողական համակարգ դա անում:

Ընդհանրապես, ճիշտ Խնդրի պահանջների ձևակերպումը լուծման կեսից ավելին է: Երբեմն զգույշ օգտագործման դեպքերի վերլուծություն կարող է զգալիորեն ազդել արդյունքի վրա.

Ի՞նչ է անում վերացական մշակողը:

1.0. արտաքին որոնման համակարգ

Օ,, որոնումը դժվար է, ես ընդհանրապես ոչինչ չեմ ուզում անել. եկեք դա տանք devops-ին: Թող որոնողական համակարգ տեղադրեն տվյալների բազայից դուրս՝ 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;

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»
[նայեք բացատրություն.tensor.ru-ին]

Դե, դա ... 26 ms, 31 ՄԲ կարդալ տվյալներ և ավելի քան 1.7K զտված գրառումներ՝ 10 որոնվածների համար: Վերադիր ծախսերը շատ բարձր են, ավելի արդյունավետ բան չկա՞։

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;

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»
[նայեք բացատրություն.tensor.ru-ին]

Այստեղ հարցումների կատարման զուգահեռացումը մեզ մի փոքր օգնեց՝ ժամանակը կիսով չափ կրճատելով 11 ms. Իսկ մենք պետք է կարդայինք 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;

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»
[նայեք բացատրություն.tensor.ru-ին]

Գերազանց կատարում - ընդհանուր 0.05 մս և 100 ԿԲ-ից մի փոքր ավելի կարդալ! Միայն մենք մոռացանք տեսակավորել ըստ անվանմանորպեսզի օգտվողը չկորչի արդյունքների մեջ.

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

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»
[նայեք բացատրություն.tensor.ru-ին]

Օ՜, ինչ-որ բան այլևս այնքան էլ գեղեցիկ չէ. թվում է, թե ինչ-որ ցուցանիշ կա, բայց տեսակավորումն անցնում է դրա կողքով... Դա, իհարկե, արդեն բազմապատիկ ավելի արդյունավետ է, քան նախորդ տարբերակը, բայց...

1.4. «ավարտել ֆայլով»

Բայց կա մի ինդեքս, որը թույլ է տալիս որոնել ըստ տիրույթի և դեռ նորմալ օգտագործել տեսակավորումը. կանոնավոր բծ!

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;

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»
[նայեք բացատրություն.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;

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»
[նայեք բացատրություն.tensor.ru-ին]

2. ինչպես են խնդրանքները դառնում թթու

Այժմ մենք թողնում ենք մեր խնդրանքը, որպեսզի «եփվի» վեց ամիս կամ մեկ տարի, և մենք զարմացած ենք, որ այն կրկին գտնում ենք «վերևում»՝ հիշողության ընդհանուր օրական «մղման» ցուցանիշներով (բուֆերների ընդհանուր հիթ) մեջ 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. ԴԻՍՏԻՆԿՏ-ը անիմաստ է և անողոք

Ինչ-որ տեղ նման էվոլյուցիայի գործընթացում 2-րդ ենթահաշիվից կորել է NOT LIKE պայման. Հասկանալի է, որ սրանից հետո UNION ALL սկսեց վերադառնալ որոշ մուտքեր երկու անգամ - սկզբում հայտնաբերվել է տողի սկզբում, իսկ հետո նորից՝ այս տողի առաջին բառի սկզբում: Սահմանում, 2-րդ ենթահաշիվների բոլոր գրառումները կարող են համապատասխանել առաջինի գրառումներին:

Ի՞նչ է անում ծրագրավորողը պատճառը փնտրելու փոխարեն: Հարց չկա:

  • կրկնապատկել չափը բնօրինակ նմուշներ
  • կիրառել 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>;

Այսինքն, պարզ է, որ արդյունքը, ի վերջո, նույնն է, բայց 2-րդ CTE ենթակետ «թռչելու» հնարավորությունը շատ ավելի մեծ է դարձել, և նույնիսկ առանց դրա, ակնհայտորեն ավելի ընթեռնելի.

Բայց սա ամենատխուրը չէ։ Քանի որ մշակողը խնդրել է ընտրել DISTINCT ոչ թե կոնկրետների, այլ միանգամից բոլոր ոլորտների համար գրառումները, այնուհետև այնտեղ ավտոմատ կերպով ներառվել է sub_query դաշտը՝ ենթհարցման արդյունքը: Հիմա՝ իրականացնելու համար DISTINCT, տվյալների բազան արդեն պետք է գործարկվեր ոչ թե 10 ենթհարցումներ, այլ բոլոր <2 * N> + 10!

2.4. ամենից առաջ համագործակցություն:

Այսպիսով, մշակողները շարունակեցին ապրել, նրանք չէին անհանգստանում, քանի որ օգտվողը ակնհայտորեն բավարար համբերություն չուներ ռեեստրը «հարմարեցնելու» նշանակալի N արժեքներին՝ յուրաքանչյուր հաջորդ «էջ» ստանալու քրոնիկական դանդաղումով:

Մինչև նրանց մոտ եկան մեկ այլ բաժնի մշակողները և ցանկացան օգտագործել նման հարմար մեթոդ կրկնվող որոնման համար -այսինքն ինչ-որ նմուշից վերցնում ենք մի կտոր, զտում այն ​​լրացուցիչ պայմաններով, նկարում արդյունքը, հետո հաջորդ կտորը (որը մեր դեպքում ստացվում է N-ի մեծացմամբ) և այդպես շարունակ, մինչև լցնենք էկրանը։

Ընդհանուր առմամբ, բռնված նմուշում N-ը հասել է գրեթե 17K արժեքների, և ընդամենը մեկ օրվա ընթացքում առնվազն 4K նման հարցումներ են կատարվել «շղթայի երկայնքով»։ Դրանցից վերջինները համարձակորեն սկանավորվեցին 1 ԳԲ հիշողություն մեկ կրկնության համար...

Ընդհանուր

PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում»

Source: www.habr.com

Добавить комментарий