Рекордне хиљаде менаџера из продајних канцеларија широм земље десетине хиљада контаката дневно — чињенице комуникације са потенцијалним или постојећим клијентима. А за ово прво морате пронаћи клијента, и то по могућности врло брзо. А то се најчешће дешава по имену.
Стога није изненађујуће што, још једном анализирајући „тешке“ упите на једној од најоптерећенијих база података – нашој , нашао сам "у врху" захтев за „брзо“ претраживање по имену за организационе картице.
Штавише, даља истрага је открила занимљив пример прво оптимизација, а затим деградација перформанси захтев са његовим узастопним дорадом од стране неколико тимова, од којих је сваки деловао искључиво у најбољој намери.
0: шта је корисник желео?
[КДПВ ]
Шта корисник обично мисли када говори о „брзој“ претрази по имену? Скоро никада се не испостави да је то „искрена“ потрага за поднизом попут ... LIKE '%роза%' – јер тада резултат укључује не само 'Розалия' и 'Магазин Роза'Али 'Гроза' па чак и 'Дом Деда Мороза'.
Корисник на свакодневном нивоу претпоставља да ћете му ви пружити тражи по почетку речи у наслову и учинити релевантнијим да почиње у ушао. И урадићете то скоро тренутно - за међулинијски унос.
1: ограничити задатак
И још више, особа неће посебно ући 'роз магаз', тако да морате да тражите сваку реч по префиксу. Не, кориснику је много лакше да одговори на брзи наговештај за последњу реч него да намерно „подспецификује“ претходне - погледајте како било који претраживач то решава.
Генерално, исправно формулисање захтева за проблем је више од половине решења. Понекад пажљива анализа случаја употребе .
Шта ради апстрактни програмер?
1.0: екстерни претраживач
О, тражење је тешко, не желим ништа да радим - хајде да га дамо девопсу! Дозволите им да примене претраживач изван базе података: Спхинк, ЕластицСеарцх,...
Радна опција, иако радно интензивна у смислу синхронизације и брзине промена. Али не у нашем случају, јер се претрага врши за сваког клијента само у оквиру података о његовом рачуну. А подаци имају прилично велику варијабилност - и ако је менаџер сада ушао у картицу 'Магазин Роза', онда се после 5-10 секунди можда већ сети да је заборавио да наведе своју е-пошту и жели да је пронађе и исправи.
Стога – хајде претражи „директно у бази података“. На срећу, ПостгреСКЛ нам то омогућава, а не само једну опцију – погледаћемо их.
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: претрага по тексту? То је ФТС!
Заиста, ПостгреСКЛ пружа веома моћан (Претрага пуног текста), укључујући могућност претраживања префикса. Одлична опција, не морате чак ни да инсталирате екстензије! Хајде да покушамо:
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;
Овде нам је мало помогла паралелизација извршавања упита, преполовивши време на 11мс. А морали смо да читамо 1.5 пута мање – укупно 20МВ. Али овде, што мање, то боље, јер што је већи волумен који читамо, веће су шансе да добијемо промашај кеш меморије, а свака додатна страница података прочитаних са диска је потенцијална „кочница“ за захтев.
1.3: и даље ЛИКЕ?
Претходни захтев је добар за све, али само ако га повучете сто хиљада пута дневно, доћи ће КСНУМКСТБ читати податке. У најбољем случају, из меморије, али ако немате среће, онда са диска. Па хајде да покушамо да га смањимо.
Сетимо се шта корисник жели да види прво "који почиње са...". Дакле, ово је у свом најчистијем облику уз помоћ text_pattern_ops! И само ако „немамо довољно“ до 10 записа које тражимо, онда ћемо морати да завршимо читање помоћу ФТС претраге:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Одличне перформансе - укупно 0.05 мс и нешто више од 100 КБ читати! Само смо ми заборавили Сортирај по именутако да се корисник не изгуби у резултатима:
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 број линија. Говорим о овом методу оптимизације упита .
Дакле, да, сада имамо и бтрее и гин на столу, али статистички се испоставило да мање од 10% захтева стигне до извршења другог блока. Односно, са таквим типичним ограничењима која су унапред позната за задатак, успели смо да смањимо укупну потрошњу серверских ресурса за скоро хиљаду пута!
1.5*: можемо и без датотеке
Изнад LIKE Спречени смо да користимо погрешно сортирање. Али може се „поставити на прави пут“ тако што ћете навести оператор УСИНГ:
Подразумевано се претпоставља
ASC. Поред тога, можете навести име одређеног оператора сортирања у клаузулиUSING. Оператор сортирања мора бити члан мање или веће од неке породице оператора Б-стабла.ASCобично еквивалентанUSING <иDESCобично еквивалентанUSING >.
У нашем случају, „мање“ је ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: како захтеви постају кисели
Сада остављамо наш захтев да се „крчка” шест месеци или годину дана, и изненађени смо што га поново налазимо „на врху” са показатељима укупног дневног „пумпања” меморије (бафферс схаред хит) ин КСНУМКСТБ – односно чак и више него што је првобитно било.
Не, наравно, наш посао је порастао и наш посао је повећан, али не за исти износ! То значи да је овде нешто сумњиво - хајде да то схватимо.
2.1: рођење страница
У неком тренутку, други развојни тим је желео да омогући „скок“ са брзог претраживања индекса на регистар са истим, али проширеним резултатима. Шта је регистар без навигације страница? Хајде да зајебемо!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Сада је било могуће приказати регистар резултата претраге са учитавањем „страница по страницу“ без икаквог стреса за програмера.
Наравно, у ствари, за сваку следећу страницу података чита се све више (све из претходног пута, што ћемо одбацити, плус неопходан „реп“) - то јест, ово је јасан анти-образац. Али било би исправније започети претрагу на следећој итерацији од кључа сачуваног у интерфејсу, али о томе други пут.
2.2: Желим нешто егзотично
У неком тренутку програмер је желео диверзификујте добијени узорак подацима из друге табеле, за коју је цео претходни захтев послат ЦТЕ:
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. потупита могу се подударати са записима првог.
Шта програмер ради уместо да тражи узрок?.. Нема сумње!
- дупло веће оригинални узорци
- применити ДИСТИНЦТда бисте добили само појединачне инстанце сваке линије
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. ЦТЕ потупит је постала много већа, а чак и без овога, јасно читљивије.
Али то није најтужније. Пошто је програмер тражио да изабере DISTINCT не за одређене, већ за сва поља одједном записа, онда је поље под_упит — резултат потупита — аутоматски укључено тамо. Сада, да извршим DISTINCT, база података је већ морала да се изврши не 10 потупита, већ свих <2 * Н> + 10!
2.4: сарадња изнад свега!
Дакле, програмери су живели даље - нису се трудили, јер корисник очигледно није имао довољно стрпљења да „подеси“ регистар на значајне Н вредности уз хронично успоравање пријема сваке следеће „странице“.
Све док програмери из другог одељења нису дошли код њих и желели да користе тако згодан метод за итеративно претраживање - односно, узмемо комад из неког узорка, филтрирамо га по додатним условима, нацртамо резултат, па следећи комад (што се у нашем случају постиже повећањем Н), и тако све док не попунимо екран.
Генерално, у уловљеном примерку Н је достигао вредности од скоро 17К, а за само један дан најмање 4К оваквих захтева је извршено „дуж ланца“. Последње од њих су смело скенирали 1 ГБ меморије по итерацији...
Укупно
Извор: ввв.хабр.цом