Улс даяарх борлуулалтын албадын мянга мянган менежерүүд рекорд тогтоожээ Өдөр бүр хэдэн арван мянган хүн холбогддог - боломжит эсвэл одоо байгаа үйлчлүүлэгчидтэй харилцах баримтууд. Үүний тулд та эхлээд үйлчлүүлэгчээ олох хэрэгтэй бөгөөд маш хурдан байх ёстой. Мөн энэ нь ихэвчлэн нэрээр нь тохиолддог.
Тиймээс, хамгийн их ачаалалтай мэдээллийн сан болох бидний өөрийн гэсэн "хүнд" асуулгад дахин дүн шинжилгээ хийх нь гайхах зүйл биш юм. , би "дээд талд" олсон нэрээр нь "хурдан" хайлт хийх хүсэлт байгууллагын картуудын хувьд.
Түүгээр ч зогсохгүй нэмэлт мөрдөн байцаалтын явцад нэгэн сонирхолтой жишээ илэрсэн эхлээд оновчлол, дараа нь гүйцэтгэлийн бууралт хүсэлтийг хэд хэдэн баг дараалан сайжруулж, тус бүр нь зөвхөн сайн санааны үүднээс ажилласан.
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: 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.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;
Маш сайн - ангилах нь ажилладаг бөгөөд нөөцийн хэрэглээ "микроскоп" хэвээр байна. "цэвэр" 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 болон gin аль аль нь байгаа, гэхдээ статистикийн хувьд энэ нь харагдаж байна. Хүсэлтийн 10-аас бага хувь нь хоёр дахь блокийн гүйцэтгэлд хүрдэг. Өөрөөр хэлбэл, даалгаварт урьдчилан мэдэгдэж байсан ийм ердийн хязгаарлалтын тусламжтайгаар бид серверийн нөөцийн нийт хэрэглээг бараг мянга дахин бууруулж чадсан юм!
1.5*: бид файлгүйгээр хийх боломжтой
Дээрээс LIKE Бид буруу эрэмбэлэхээс сэргийлсэн. Гэхдээ үүнийг USING операторыг зааж өгснөөр "зөв зам дээр" тавьж болно:
Анхдагчаар үүнийг таамаглаж байна
ASC. Нэмж дурдахад та тодорхой ангилах операторын нэрийг өгүүлбэрт зааж өгч болноUSING. Ангилах оператор нь B-tree операторын зарим гэр бүлийн бага буюу түүнээс их гишүүн байх ёстой.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>;Өөрөөр хэлбэл, үр дүн нь яг ижил байх нь тодорхой боловч CTE-ийн 2-р дэд асуулга руу "нисэх" боломж хамаагүй өндөр болсон бөгөөд үүнгүйгээр ч гэсэн илүү уншихад ойлгомжтой.
Гэхдээ энэ бол хамгийн гунигтай зүйл биш юм. Хөгжүүлэгч сонгохыг хүссэн тул DISTINCT тодорхой биш, харин бүх талбарт нэг дор бичлэгүүд, дараа нь дэд асуулгын талбар - дэд асуулгын үр дүн - тэнд автоматаар орсон. Одоо, гүйцэтгэх DISTINCT, мэдээллийн баазыг аль хэдийн ажиллуулах ёстой байсан 10 дэд асуулга биш, харин бүгд <2 * N> + 10!
2.4: хамтын ажиллагаа юун түрүүнд!
Тиймээс, дараагийн "хуудас" бүрийг хүлээн авах нь архаг удааширч, бүртгэлийг чухал N утгатай болгоход хэрэглэгч хангалттай тэвчээргүй байсан тул хөгжүүлэгчид төвөг удсангүй.
Өөр хэлтсийн хөгжүүлэгчид тэдэн дээр ирж, ийм тохиромжтой аргыг ашиглахыг хүсэх хүртэл давталттай хайлтын хувьд - өөрөөр хэлбэл бид зарим дээжээс хэсэг авч, нэмэлт нөхцлөөр шүүж, үр дүнг зурж, дараа нь дараагийн хэсгийг (бидний тохиолдолд N-ийг нэмэгдүүлэх замаар олж авдаг) дэлгэцийг дүүргэх хүртэл үргэлжилнэ.
Ерөнхийдөө баригдсан сорьцонд N бараг 17К утсанд хүрсэн, мөн нэг өдрийн дотор дор хаяж 4K ийм хүсэлтийг "гинжин хэлхээний дагуу" гүйцэтгэсэн. Тэдний сүүлчийнх нь зоригтойгоор сканнердсан Давталт бүрт 1 ГБ санах ой...
Нийт
Эх сурвалж: www.habr.com