Өлкө боюнча сатуу кеңселеринин миңдеген менеджерлери рекорд коюшкан күн сайын он миңдеген байланыштар — потенциалдуу же болгон кардарлар менен байланыш фактылары. Ал эми бул үчүн, биринчи кардар табуу керек, жана жакшыраак абдан тез. Жана бул көбүнчө аты менен болот.
Ошондуктан, таң калыштуу эмес, дагы бир жолу "оор" суроо-талаптарды эң көп жүктөлгөн маалымат базаларынын бири - өзүбүздүн , мен "жогорку жактан" таптым аты боюнча "тез" издөө үчүн суроо уюштуруу карталары үчүн.
Андан тышкары, андан ары иликтөө кызыктуу мисалды көрсөттү биринчи оптималдаштыруу, андан кийин аткарууну начарлатат бир нече командалар тарабынан ырааттуу тактоо менен өтүнүч, алардын ар бири бир гана жакшы ниет менен иш-аракет.
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;
Бул жерде сурамдардын аткарылышын параллелизациялоо бизге бир аз жардам берип, убакытты экиге кыскартты 11ms. Ал эми 1.5 эсе аз окууга туура келди - бардыгы болуп 20MB. Бирок бул жерде, канчалык азыраак болсо, ошончолук жакшы, анткени биз окуган көлөмү канчалык чоң болсо, кэш сагынуу мүмкүнчүлүгү ошончолук жогору болот жана дисктен окулган маалыматтардын ар бир кошумча барагы суроо-талап үчүн потенциалдуу "тормоз" болуп саналат.
1.3: дагы эле LIKE?
Мурунку өтүнүч баарына жакшы, бирок күнүнө жүз миң жолу тартсаң гана келет 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: "файл менен бүтүрүү"
Бирок диапазон боюнча издөөгө мүмкүндүк берген индекс бар жана дагы эле сорттоону кадимкидей колдоно аласыз - кадимки 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;
Мыкты - сорттоо иштейт, ал эми ресурстарды керектөө "микроскопиялык" бойдон калууда. "таза" 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 саптардын саны. Мен бул суроону оптималдаштыруу ыкмасы жөнүндө айтып жатам .
Ооба, бизде азыр дасторкондо бтре да, джин да бар, бирок статистикалык жактан көрүнүп турат суроо-талаптардын 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 ГБ эстутум...
жалпы
Source: www.habr.com