PostgreSQL անթույլատրելի օրինակներ: պատմություն անվանման միջոցով որոնման քանակային վերամշակման մասին, թե՝ "Օպտիմացում առաջ և հետ"

Հազարավոր վաճառքի թիմի մենեջերներ ողջ երկիրում արձանագրում են մեր CRM համակարգում որօրյա տասնյակ հազարավոր контакտներ ՝ կապերի փաստեր հնարավոր կամ արդեն գործնական հարաբերությունների մեր հաճախորդների հետ։ Եվ այս հաճախորդին գտնելու համար նախ պետք է գտնել, և preferably շատ արագ։ Սա, առավել հաճախ, իրականանում է անունով։

Այդ իսկ պատճառով, ոչ էլ հանելիս, քանիցս առանձնացնելով «բարդ» հարցումները մեր կողմից շատ օգտագործվող բազայում՝ մեր սեփական կորպորատիվ SBIS հաշվում,, ես հայտնաբերեցի «դեպքերում» հարցման «արագ» որոնման համար անունով կազմակերպությունների քարտերի համար։

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

0: ինչ է ցանկանում օգտվողը

[КДПВ այստեղից]

Ինչպե՞ս օգտվողը հասկանում է «արագ» որոնումը անունով։ Շատ լավ կարող է լինել, որ դա երբեք չի լինում «հավատարիմ» որոնում վայերաչափի տեսքով ... LIKE '%րոզա%' — քանի որ այդ ժամանակ արդյունքների մեջ ներառվում են ոչ միայն 'Րոզալիա' և 'Խանութ Րոզա', սակայն 'Գրոզա' և նույնիսկ 'Դեդի Մորոզա'.

Օգտվողը սակայն նկատում է, որ դուք պետք է ապահովեք ոչնչի որոնում՝ բառի սկզբից նկարագրումը՝ և ցույց տաք ավելի համապատասխան այն, ինչ սկսվում է մտածմունքը։ Եվ դուք դա կկատարեք մ hampir հետագա լրացման ՝ ենթադրյալ բացարձակ քանակով երկառելմ՝

1: սահմանափակ համապատասխանությունը

Եվ առավել ևս մարդը հատուկ չի լինի մուտք գործելու 'րոզ խանութ', որպեսզի ամեն բառը դուք ստիպած լինեք որոնել նախաբառերին։ Ոչ, օգտվողին արձագանքելը արագ առաջարկի համար վերջին բառի համար շատ ավելի հեշտ կլինի, քան հատուկ «չորացնել» նախորդները՝ դիտեք, թե ինչպես դա աշխատում է ցանկացած որոնման համակարգում։

Ընդհանուր առմամբ, ճիշտ թребования կազմավորելը հարաբերակապես ավելի քան կեսի լուծում է։ Ա veces վկայակցաբերեցունի анализа use case կարող է ազդեցություն ունենալ արդյունքի վրա.

Ի՞նչ է անում վերին մակարդակի մշակողը։

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

Օյ, որոնումը կոմպլեքս է, ինչ-որ մի բան արդեն չեմ ուզում դրա մասին մտածել — թող նրանց devops էլ դա անեն։ Թող նրանք մեզ փորձարկեն արտաքին մոտաւերակում՝ Sphinx, ElasticSearch,…

Աշխատող տարբերակ, թեև դժվարաբաժանման համար աշխատանքային գործընթացում։ Բայց ոչ մեր դեպքում, քանի որ որոնումը կատարվում է յուրաքանչյուր հաճախորդի համար միայն իրենց հաշվի տվյալների շրջանակներում։ Ասymmetric 数据具有 достаточно высокую изменчивость — և եթե հիմա մենեջերն ընդգրկում է քարտ 'Խանութ Րոզա',, ապա 5-10 վայրկյանից նա արդեն կարող է հիշել, որ մոռացել է նշելու email-ը և ցանկանում է գտնել և նորոգել այն։

Լավ, եկեք որոնենք «որոշակի բազայի հիման վրա»։ Որոշ հեքիաթ, 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]

Պետք է ասել, որ այսպես… 26մս, 31MB ճանաչված տվյալների եւ ավելի քան 1.7K զտված գրանցումներ — 10 փնվողի համար։ Վարկերը չափազանց մեծ են, կարելի է արդյոք ավելի արդյունավետ ՄԵՆՈՒՄ!

1.2: տվյալների որոնո՞ւմ։ դա արդեն FTS է!

Բնականում PostgreSQL-ն շատ ուժեղ ամբողջական հասանելիության որոնման համակարգ (Full Text Search), ներառյալ նախորդ բառերի որոնման հնարավորություն։ Շատ լավ տարբերակ, նույնիսկ ընդարձակ ձեր անհրաժեշտություն չպետք է օգտագործել։ Եկեք փորձենք։

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]

Այստեղ մեզ մի քիչ օգնել է հարցման նույնականացումը, ջարդել ժամանակը դեպի 11մս. Եվ մենք պետք է կարդանք 1.5 անգամ ավելի քիչ — ընդամենը 20MB. Իսկ այստեղ, որքան քիչ — այնքան լավ, քանի որ ինչքան մեծ ծավալով անհատական տվյալներ կարդանք, այնքան ավելի բարձր են շանսերը ստանալ կաշառքի բաց թողման համար, և դրանից հետո նշվող առանցքային էջը տվյալների — հնարավոր է «կասեցում» հարցման համար.

1.3: արդյոք LIKE:

Չնայած այս նախորդ հարցումը լավ է, բայց եթե մենք դրա վրա աշխատենք հարյուր հազար անգամ մի օր, ապա հունվար կկարողանա 2TB ճանաչված տվյալների։ Ամեն դեպքում — հիշիրք աշխատած փաստաթուղթ, եթե ճակատագիր չլինի, ապա նույնպես շատացող աղբյուրներից։ Ուրեմն եկեք փորձենք այն փոքրացնել:

Հիշեք, որ օգտագործողը ցանկանում է տեսնել ապ primero «որոնք սկսվում են ...». Եվ դա ի վերջո նախնական որոնում հաստատող 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.05մս եւ մի փոքր ավելի 100KB գնացել է! Սակայն մենք մոռացել ենք դասակարգումը անվանմամբ, որպեսզի օգտագործողը չկորցնի արդյունքներում:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('ռոզա' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[տեսնել explain.tensor.ru]

ՈՒփ, ինչ-որ բան արդեն այնքան գեղեցիկ չէ — կարծես թե ինդեքսը գոյություն ունի, բայց դասակարգումը գնացած է նրա կողքով... Բավականին արդյունավետ է այս եղանակը, քան նախորդը, բայց...

1.4: «նրբորեն վերամշակել»

Բայց կա ինդեքս, որը թույլ է տալիս ինչպես диапазոնի որոնումն անել, այնպես էլ դասակարգումը ճիշտ օգտագործել — ընդհանուր btree!

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 երեխաների քանակը: Այս եղանակի հարցումներն ես արդեն գրել եմ վերևում Այո, մենք այժմ ունենք շերտ таблицայում simultaniously btree եւ gin, բայց վիճակագրորեն ստացվեց, որ.

10%-ից պակաս հարցումներ հասնում են կատարման երկրորդ բլոկին . Այսպիսով, այս հայտնի գրանցված սահմանափակումներով մենք կարողացանք նվազեցնել սերվերի ընդհանուր ռեսուրսների սպառումը գրեթե հազար անգամ:1.5*: մենք կստանանք առանց հղկելու

Վերևում

LIKE մեզ թույլ չի տվել օգտագործել սխալ դասավորություն։ Բայց նորից կարող ենք «ուղղել सही ճանապարհը»՝ USING օպերատորի ցուցմամբ: Համարենային կախվածություն ենթադրվում է

ASC . Ընդդիմադիր, կարող է նշել հատուկ դասավորման օպերատորի անունըUSING . Դասավորման օպերատորը պետք է լինի առավել կամ ավելի քիչ երկուսի B-ծառի օպերատորների ընտանիքից:հաճախ պարունակում է . Ընդդիմադիր, կարող է նշել հատուկ դասավորման օպերատորի անունը USING < DESC և USING > USING < Մեր դեպքում «քիչ» է.

~<~ SELECT * FROM firms WHERE lower(name) LIKE ('ռոզա' || '%') ORDER BY lower(name) USING ~<~ LIMIT 10;:

2: ինչպես «թարմանում են» հարցումները

[տեսնել explain.tensor.ru]

Այժմ թողնում ենք մեր հարցումը «թողնելու» կես տարի-տարին, և զարմանքով այլևս հայտնաբերում ենք այն «թոփում» օրական «ոգեշնչման» ցուցանիշներով (

buffers shared hit)5.5TB այսինքն՝ դեռևս ավելին, քան նախնական էր: Ոչ, իհարկե, և մեր բիզնիսը աճել է, և բեռը մեծացել է, սակայն այնքան էլ այնպես չէ! Նշանակում է, որ այստեղ ինչ-որ բան էլի կա՝ եկեք հասկանալ

2.1: էջավորման ծնունդ

Մի պահ այլ ծրագրավորողների թիմը ցանկանում էր իրականացնել արագ ենթադրյալ որոնման հնարավորությունը «պրոդրոորվել» առկա գրանցումներ յուրաքանչյուր մեկ խմբի աշխատանքի հետ։ Իսկ որ գյուղի առանց էջային նավիգացիայի? Դարձել ես:

( ... LIMIT + 10) UNION ALL ( ... LIMIT + 10) LIMIT 10 OFFSET ;

Այժմ կարելի էր անխափան ծրագրավորողին ցուցադրել որոնման արդյունքների գրանցումը «այսպես-էջային» ներբեռնումով:

Իհարկե, իրականում,

յուրաքանչյուր հաջորդ տվյալները ընթերցվում են մեծ թվով ավելին (հաշվի առնելով նախորդ անգամվա բոլոր նկատառումներն ու անհրաժեշտ «թևիկը»)՝ այսինքն՝ դա անբարենպաստ մոդել է: Իհարկե, ճիշտ կլիներ՝ որոնումը մեկնարկել հաջորդ կրկնությանը պահված բանալիից՝ ինտերֆեյսում, բայց այս մասին՝ հաջորդ անգամ:

2.2: ուզում ենք էգզոտիկա

Մի ժամանակ ծրագրավորողը ուզեց տարբերակել արդյունքների ընտրությունը այլ գրանցումներով, որի համար նախորդ հարցումը ուղարկվել է CTE:

WITH q AS (
  ...
  LIMIT  + 10
)
SELECT
  *
, (SELECT ...) sub_query -- որոշակի հարցում կապված աղյուսակին
FROM
  q
LIMIT 10 OFFSET ;

Եվ նույնիսկ այսպես՝ վատ չէ, քանի որ ներբեռնված հարցումը հաշվարկվում է միայն 10 վերադարձված գրանցման համար, եթե ոչ...

2.3: DISTINCT նշանակություն չունի և անխոնջ

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

Ինչ է անում ծրագրավորողը՝ պատճառը որոնելու փոխարեն?.. Ոչ մի հարց!

• երկնքում կրկնապատկենք չափը առաջին ընտրությունների
• կիրառենք DISTINCT, որպեսզի ստանանք միայն մեկական օրինակ յուրաքանչյուր տողի

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

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

Բայց դա ամենամասնագետը չէ: Քանի որ ծրագրավորողը խնդրեց ընտրել DISTINCT ոչ թե կոնկրետ, այլ անմիջապես բոլոր դաշտերից գրանցումները, ապա անկասկած անցել է նաև sub_query դաշտը՝ ենթահայցի արդյունքը: Այժմ, իրականացնել DISTINCT, տվյալ սվազիկը կատարել է արդեն ոչ թե 10 ենթահայց, այլ բոլոր + 10!

2.4: համագործակցությունը ամեն ինչից վեր է!

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

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

Ընդհանուր առմամբ, բռնված օրինակին N հասել է գրեթե 17K-ի կարգի, բայց ամբողջ ստրկուճում կատարվել է «գործնական» ոչ պակաս քան 4K այդ հարցումները: Վերջինները վստահորեն ստուգվել են արդեն 1GB հիշողության վրա յուրաքանչյուր կրկնության ընթացքում…

Ընդհանուր առմամբ

Ընտանիք: habr.com