Ҳазорон роҳбарони офисҳои фурӯш дар саросари кишвар сабт мекунанд ҳар рӯз даҳҳо ҳазор тамос — далелҳои муошират бо мизоҷони эҳтимолӣ ё мавҷуда. Ва барои ин, шумо бояд аввал муштарӣ пайдо кунед, ва беҳтараш хеле зуд. Ва ин аксар вақт бо ном рӯй медиҳад.
Аз ин рӯ, тааҷҷубовар нест, ки бори дигар таҳлили дархостҳои "вазнин" дар яке аз базаҳои пурборшаванда - худи мо , ман "дар боло" ёфтам дархост барои ҷустуҷӯи "зуд" аз рӯи ном барои кортҳои ташкилот.
Илова бар ин, тафтишоти минбаъда як мисоли ҷолибро нишон дод аввал оптимизатсия ва баъд таназзули фаъолият дархост бо такмили пайдарпайи он аз ҷониби якчанд дастаҳо, ки ҳар яки онҳо танҳо бо ниятҳои беҳтарин амал мекарданд.
0: корбар чӣ мехост?
[КДПВ ]
Вақте ки онҳо дар бораи ҷустуҷӯи "зуд" бо ном сӯҳбат мекунанд, корбар одатан чӣ маъно дорад? Он қариб ҳеҷ гоҳ ҷустуҷӯи "ростқавл" барои зерсатри монанди он рӯй намедиҳад ... LIKE '%роза%' — зеро он гох натича на танхо дар бар мегирад 'Розалия' и 'Магазин Роза'аммо 'Гроза' ва ҳатто 'Дом Деда Мороза'.
Истифодабаранда дар сатҳи ҳаррӯза гумон мекунад, ки шумо ба ӯ пешниҳод мекунед ҷустуҷӯ аз рӯи аввали калима дар сарлавҳа ва мувофиқтар кардани он, ки бо оғоз меёбад дохил шуд. Ва шумо ин корро хоҳед кард қариб дарҳол - барои вуруди байнисатрӣ.
1: маҳдуд кардани вазифа
Ва ҳатто бештар аз он, шахс ба таври махсус ворид намешавад 'роз магаз', то ки шумо бояд ҳар як калимаро аз рӯи префикс ҷустуҷӯ кунед. Не, барои корбар ҷавоб додан ба маслиҳати зуд барои калимаи охирин назар ба дидаву дониста "кам муайян кардани" калимаҳои қаблӣ осонтар аст - бубинед, ки ҳама гуна системаи ҷустуҷӯӣ инро чӣ гуна ҳал мекунад.
Дар маҷмӯъ, рост ба миён гузоштани талабхо ба проблема бештар аз нисфи халли масъала мебошад. Баъзан таҳлили истифодаи бодиққат .
Таҳиягари абстрактӣ чӣ кор мекунад?
1.0: муҳаррики ҷустуҷӯии беруна
Оҳ, ҷустуҷӯ душвор аст, ман умуман коре кардан намехоҳам - биёед онро ба девопҳо диҳем! Бигзор онҳо системаи ҷустуҷӯии берунаро дар пойгоҳи додаҳо ҷойгир кунанд: Sphinx, ElasticSearch,...
Варианти корӣ, ҳарчанд меҳнатталаб аз ҷиҳати ҳамоҳангсозӣ ва суръати тағирот. Аммо на дар ҳолати мо, зеро ҷустуҷӯ барои ҳар як муштарӣ танҳо дар доираи маълумоти ҳисоби ӯ анҷом дода мешавад. Ва маълумот дорои тағирёбии хеле баланд аст - ва агар менеҷер ҳоло ба корт ворид шудааст 'Магазин Роза', он гоҳ пас аз 5-10 сония ӯ аллакай дар ёд дорад, ки ӯ нишон додани почтаи электронии худро фаромӯш кардааст ва мехоҳад онро пайдо кунад ва ислоҳ кунад.
Бинобар ин - биёед ҷустуҷӯ "бевосита дар пойгоҳи додаҳо". Хушбахтона, PostgreSQL ба мо имкон медиҳад, ки ин корро кунем, на танҳо як вариант - мо онҳоро дида мебароем.
1.1: зерсатри "ростқавл"
Мо ба калимаи "зери зер" часпидаем. Аммо барои ҷустуҷӯи индекс аз рӯи зерсатр (ва ҳатто бо ибораҳои муқаррарӣ!) як аъло аст ! Танҳо он вақт лозим меояд, ки дуруст ҷудо карда шавад.
Биёед кӯшиш кунем, ки лавҳаи зеринро барои содда кардани модел гирем:
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;
Хуб, ин... 26мс, 31МБ хондани маълумот ва зиёда аз 1.7К сабтҳои филтршуда - барои 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;
Дар ин ҷо параллелизатсияи иҷрои дархост ба мо каме кӯмак кард ва вақтро то нисфи кам кард 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;
Фаъолияти аъло - умумӣ 0.05ms ва каме бештар аз 100KB хонед! Фақат фаромӯш кардем аз рӯи ном ҷудо кунедто корбар дар натиҷаҳо гум нашавад:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Оҳ, чизе дигар он қадар зебо нест - ба назар чунин менамояд, ки индекс вуҷуд дорад, аммо ба навъҳо ҷудо кардан аз он мегузарад... Ин, албатта, аз варианти қаблӣ аллакай чанд маротиба самараноктар аст, аммо...
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;
Аъло - ҷудокунӣ кор мекунад ва истеъмоли захираҳо "микроскопӣ" боқӣ мемонад. назар ба ФТС-и "тоза" ҳазорҳо маротиба самараноктар аст! Ҳама чиз боқӣ мемонад, ки онро дар як дархост якҷоя кунед:
(
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 ва ҳам ҷин дорем, аммо аз рӯи омор маълум мешавад, ки камтар аз 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;
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: 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>;Яъне, маълум аст, ки натиҷа, дар ниҳоят, комилан якхела аст, аммо имкони "парвоз" ба зерпурсишҳои 2-юми CTE хеле баланд шуд ва ҳатто бидуни ин, равшантар хондан мумкин аст.
Аммо ин аламовартарин чиз нест. Азбаски таҳиякунанда хоҳиш кард, ки интихоб кунад DISTINCT на барои сохахои конкретй, балки барои хамаи сохахо якбора сабтҳо, пас майдони sub_query - натиҷаи зерпурсиш - ба таври худкор ба он ҷо дохил карда шуд. Акнун, барои иҷро кардан DISTINCT, база бояд аллакай иҷро мешуд на 10 зерпурсиш, балки ҳама <2 * N> + 10!
2.4: ҳамкорӣ пеш аз ҳама!
Ҳамин тавр, таҳиягарон зиндагӣ мекарданд - онҳо ташвиш надиданд, зеро корбар бешубҳа сабри кофӣ надошт, то феҳристро ба арзишҳои назарраси N бо сустшавии музмини қабули ҳар як "саҳифа"-и минбаъда "таҷҳиз кунад".
То он даме, ки аз дигар шӯъбаи таҳиягарон ба назди онҳо омаданд ва мехостанд, ки чунин усули мувофиқро истифода баранд барои ҷустуҷӯи такрорӣ -яъне аз ягон намуна порчаеро мегирем, онро аз руи шартхои иловаги филтр мекунем, натичаашро мекашем, баъд порчаи навбатиро (ки дар мавриди мо бо зиёд кардани N ба даст меояд) ва гайра то пур кардани экран давом медихем.
Умуман, дар намунаи сайд N ба арзишҳои қариб 17K расид, ва танҳо дар як рӯз камаш 4K чунин дархостҳо "қад-қади занҷир" иҷро карда шуданд. Охирин онхоро далерона аз назар гузаронданд 1 ГБ хотира барои як такрор...
Ҳамагӣ
Манбаъ: will.com