Ел бойынша сату кеңселерінің мыңдаған менеджерлері рекорд жасайды күн сайын ондаған мың байланыс — әлеуетті немесе бар клиенттермен байланыс фактілері. Бұл үшін сіз алдымен клиентті табуыңыз керек, жақсырақ өте тез. Және бұл көбінесе атымен болады.
Сондықтан, ең көп жүктелген дерекқорлардың біріндегі «ауыр» сұрауларды тағы бір рет талдайтынымыз таңқаларлық емес. , мен «жоғарыдан» таптым аты бойынша «жылдам» іздеуді сұраңыз ұйымдастыру карталары үшін.
Оның үстіне, одан әрі тергеу қызықты мысалды анықтады алдымен оңтайландыру, содан кейін өнімділіктің төмендеуі Әрқайсысы тек жақсы ниетпен әрекет еткен бірнеше команданың дәйекті нақтылауымен сұрау.
0: пайдаланушы не қалайды?
[KDPV ]
Пайдаланушы аты бойынша «жылдам» іздеу туралы айтқанда әдетте нені білдіреді? Бұл ешқашан сияқты ішкі жолды «адал» іздеу болып табылмайды ... 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;
Мұнда сұрауды орындауды параллельдеу уақытты екі есе қысқартып, бізге аздап көмектесті 11 мс. Ал 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.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;
Өте жақсы - сұрыптау жұмыс істейді, ал ресурстарды тұтыну «микроскопиялық», «таза» 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 де, джин де бар, бірақ статистикалық түрде бұл белгілі болды. сұраныстардың 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 қайта бастады кейбір жазбалар екі рет - алдымен жолдың басында, сосын тағы да - осы жолдың бірінші сөзінің басында кездеседі. Шекте 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 ГБ жад...
Барлығы
Ақпарат көзі: www.habr.com