Mamlakat bo'ylab savdo ofislarining minglab menejerlari rekord o'rnatadilar har kuni o'n minglab aloqalar — potentsial yoki mavjud mijozlar bilan aloqa faktlari. Va buning uchun siz birinchi navbatda mijozni topishingiz kerak, va eng yaxshisi juda tez. Va bu ko'pincha ism bilan sodir bo'ladi.
Shuning uchun, eng ko'p yuklangan ma'lumotlar bazalaridan biri - o'zimizning "og'ir" so'rovlarni yana bir bor tahlil qilish ajablanarli emas. , men "yuqorida" topdim nomi bo'yicha "tezkor" qidiruvni so'rang tashkilot kartalari uchun.
Bundan tashqari, keyingi tergov qiziqarli misolni aniqladi birinchi optimallashtirish, keyin esa ishlashning pasayishi har biri faqat eng yaxshi niyat bilan harakat qilgan bir nechta jamoalar tomonidan ketma-ket takomillashtirish bilan so'rov.
0: foydalanuvchi nimani xohladi?
[KDPV ]
Foydalanuvchi nomi bo'yicha "tezkor" qidiruv haqida gapirganda, odatda nimani anglatadi? Bu kabi substring uchun "halol" qidiruv deyarli hech qachon chiqmaydi ... LIKE '%роза%' - chunki u holda natija nafaqat o'z ichiga oladi 'Розалия' и 'Магазин Роза'lekin 'Гроза' va hatto 'Дом Деда Мороза'.
Foydalanuvchi kundalik darajada siz uni ta'minlaysiz deb o'ylaydi so'z boshi bo'yicha qidirish sarlavhaga kiriting va uni tegishliroq qiling da boshlanadi kirgan. Va siz buni qilasiz deyarli bir zumda - interlinear kiritish uchun.
1: vazifani cheklash
Va bundan ham ko'proq, odam maxsus kirmaydi 'роз магаз', shuning uchun siz har bir so'zni prefiks bo'yicha qidirishingiz kerak. Yo'q, foydalanuvchi uchun oldingi so'zlarni maqsadli ravishda "aniqlashdan" ko'ra oxirgi so'z uchun tezkor maslahatga javob berish osonroq - har qanday qidiruv tizimi buni qanday hal qilishiga qarang.
Odatda, o'ng muammoga qo'yiladigan talablarni shakllantirish yechimning yarmidan ko'pini tashkil qiladi. Ba'zan ehtiyotkorlik bilan foydalanish holatlarini tahlil qilish .
Mavhum ishlab chiquvchi nima qiladi?
1.0: tashqi qidiruv tizimi
Oh, qidiruv qiyin, men umuman hech narsa qilishni xohlamayman - keling, uni devoplarga beraylik! Ularga ma'lumotlar bazasidan tashqarida qidiruv tizimini o'rnatishga ruxsat bering: Sphinx, ElasticSearch,...
Sinxronizatsiya va o'zgarishlar tezligi jihatidan ko'p mehnat talab qiladigan ish varianti. Ammo bizning holatlarimizda emas, chunki qidiruv har bir mijoz uchun faqat uning hisob ma'lumotlari doirasida amalga oshiriladi. Va ma'lumotlar juda yuqori o'zgaruvchanlikka ega - va agar menejer hozir kartaga kirgan bo'lsa 'Магазин Роза', keyin 5-10 soniyadan so'ng u o'z elektron pochtasini u erda ko'rsatishni unutganini va uni topib, tuzatishni xohlayotganini eslashi mumkin.
Shuning uchun - keling "to'g'ridan-to'g'ri ma'lumotlar bazasida" qidirish. Yaxshiyamki, PostgreSQL bizga bitta variantni emas, balki buni amalga oshirishga imkon beradi - biz ularni ko'rib chiqamiz.
1.1: "halol" pastki qator
Biz "substring" so'ziga yopishib olamiz. Ammo pastki satr bo'yicha (va hatto oddiy iboralar bo'yicha!) indekslarni qidirish uchun juda yaxshi ! Shundan keyingina to'g'ri tartiblash kerak bo'ladi.
Modelni soddalashtirish uchun quyidagi plastinani olishga harakat qilaylik:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Biz u erda haqiqiy tashkilotlarning 7.8 million yozuvlarini yuklaymiz va ularni indekslaymiz:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Interlinear qidiruv uchun dastlabki 10 ta yozuvni qidiramiz:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Xo'sh, bu ... 26 ms, 31 MB ma'lumotlarni o'qish va 1.7K dan ortiq filtrlangan yozuvlar - 10 ta qidirilganlar uchun. Qo'shimcha xarajatlar juda yuqori, undan samaraliroq narsa yo'qmi?
1.2: matn bo'yicha qidirish? Bu FTS!
Darhaqiqat, PostgreSQL juda kuchli (To'liq matnli qidiruv), shu jumladan prefiks qidiruvi qobiliyati. Ajoyib variant, siz kengaytmalarni o'rnatishingiz shart emas! Keling urinib koramiz:
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;
Bu erda so'rovlarni bajarishni parallellashtirish bizga vaqtni yarmiga qisqartirish uchun biroz yordam berdi 11 ms. Va biz 1.5 baravar kamroq o'qishimiz kerak edi - jami 20MB. Ammo bu erda qanchalik kam bo'lsa, shuncha yaxshi, chunki biz o'qigan hajm qanchalik katta bo'lsa, keshni o'tkazib yuborish ehtimoli shunchalik yuqori bo'ladi va diskdan o'qilgan ma'lumotlarning har bir qo'shimcha sahifasi so'rov uchun potentsial "tormoz" dir.
1.3: hali ham YOQDIMI?
Oldingi iltimos hamma uchun yaxshi, lekin kuniga yuz ming marta tortsangiz, keladi 2TB ma'lumotlarni o'qish. Eng yaxshi holatda, xotiradan, lekin agar omadingiz bo'lmasa, diskdan. Shunday qilib, keling, uni kichikroq qilishga harakat qilaylik.
Keling, foydalanuvchi nimani ko'rishni xohlayotganini eslaylik birinchi "bilan boshlanadi ...". Demak, bu sof shaklda yordamida text_pattern_ops! Va agar biz izlayotgan 10 tagacha yozuv "etarli bo'lmasa", FTS qidiruvi yordamida ularni o'qishni tugatishimiz kerak bo'ladi:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Zo'r ishlash - jami 0.05 ms va 100 KB dan bir oz ko'proq o'qing! Faqat biz unutdik nomi bo'yicha tartiblashfoydalanuvchi natijalarda adashib qolmasligi uchun:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oh, endi nimadir go'zal emas - ko'rsatkich borga o'xshaydi, lekin saralash uning yonidan o'tib ketadi ... Bu, albatta, avvalgi variantdan ko'ra ko'p marta samaraliroq, lekin...
1.4: "fayl bilan tugatish"
Ammo diapazon bo'yicha qidirish va tartiblashdan odatdagidek foydalanish imkonini beruvchi indeks mavjud - oddiy btree!
CREATE INDEX ON firms(lower(name));Buning uchun faqat so'rov "qo'lda to'planishi" kerak bo'ladi:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Zo'r - saralash ishlaydi va resurslar iste'moli "mikroskopik" bo'lib qoladi "sof" FTSga qaraganda minglab marta samaraliroq! Qolgan narsa uni bitta so'rovga qo'yishdir:
(
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;
E'tibor bering, ikkinchi pastki so'rov bajariladi faqat birinchisi kutilganidan kamroq qaytsa oxirgi LIMIT qatorlar soni. Men so'rovlarni optimallashtirishning ushbu usuli haqida gapiryapman .
Ha, bizda stolda btree ham, jin ham bor, ammo statistik ma'lum bo'lishicha, so'rovlarning 10% dan kamrog'i ikkinchi blokning bajarilishiga etadi. Ya'ni, vazifa uchun oldindan ma'lum bo'lgan bunday odatiy cheklovlar bilan biz server resurslarining umumiy iste'molini deyarli ming baravar kamaytirishga muvaffaq bo'ldik!
1.5*: biz faylsiz ham qila olamiz
Yuqorida LIKE Noto'g'ri saralashdan foydalanishning oldini oldik. Ammo USING operatorini ko'rsatib, uni "to'g'ri yo'lga qo'yish" mumkin:
Odatiy bo'lib, u taxmin qilinadi
ASC. Bundan tashqari, siz bandda ma'lum bir tartiblash operatorining nomini belgilashingiz mumkinUSING. Saralash operatori B-daraxt operatorlarining ayrim oilasining kichik yoki kattaroq a'zosi bo'lishi kerak.ASCodatda ekvivalentUSING <иDESCodatda ekvivalentUSING >.
Bizning holatda, "kamroq" ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: so'rovlar qanday yomonlashadi
Endi biz so'rovimizni olti oy yoki bir yil davomida "qaynatish" uchun qoldiramiz va uni xotiraning kunlik "nasosi" ko'rsatkichlari bilan yana "yuqorida" topib hayratda qoldiramiz (buferlar umumiy zarba) 5.5TB - ya'ni dastlab bo'lganidan ham ko'proq.
Yo'q, albatta, bizning biznesimiz o'sdi va ish yukimiz oshdi, lekin bir xil miqdorda emas! Bu shuni anglatadiki, bu erda nimadir baliq bor - keling, buni aniqlaylik.
2.1: peyjingning tug'ilishi
Bir nuqtada, boshqa ishlab chiqish guruhi bir xil, ammo kengaytirilgan natijalarga ega bo'lgan tezkor qidiruvdan ro'yxatga olish kitobiga "sakrash" imkonini yaratmoqchi edi. Sahifani navigatsiya qilmasdan registr nima? Keling, uni buzamiz!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Endi ishlab chiquvchi uchun hech qanday stresssiz qidiruv natijalari reestrini "sahifama-sahifa" yuklash bilan ko'rsatish mumkin edi.
Albatta, aslida, ma'lumotlarning har bir keyingi sahifasi uchun ko'proq va ko'proq o'qiladi (oldingi vaqtdan boshlab, biz tashlab yuboramiz, shuningdek, kerakli "dum") - ya'ni bu aniq antipattern. Ammo keyingi iteratsiyada qidiruvni interfeysda saqlangan kalitdan boshlash to'g'riroq bo'ladi, lekin bu haqda boshqa safar.
2.2: Men ekzotik narsani xohlayman
Bir nuqtada ishlab chiquvchi xohladi olingan namunani ma'lumotlar bilan diversifikatsiya qilish oldingi so'rov CTE ga yuborilgan boshqa jadvaldan:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Va shunga qaramay, bu yomon emas, chunki quyi so'rov faqat qaytarilgan 10 ta yozuv uchun baholanadi, agar bo'lmasa ...
2.3: DISTINCT ma'nosiz va shafqatsiz
2-kichik so'rovdan bunday evolyutsiya jarayonida bir joyda adashib qoldi NOT LIKE modda. Bundan keyin aniq UNION ALL qaytishni boshladi ba'zi yozuvlar ikki marta - avval satr boshida, keyin yana - bu qatorning birinchi so'zining boshida topilgan. Limitda 2-kichik so'rovning barcha yozuvlari birinchisining yozuvlariga mos kelishi mumkin.
Ishlab chiquvchi sabab izlash o'rniga nima qiladi?.. Savol yo'q!
- ikki barobar katta original namunalar
- DISTINCT ni qo'llashhar bir satrning faqat bitta nusxasini olish uchun
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>;Ya'ni, natija, oxir-oqibat, xuddi shunday ekanligi aniq, ammo 2-CTE pastki so'roviga "uchib ketish" imkoniyati ancha yuqori bo'ldi va hatto bu holda ham, aniqroq o'qilishi mumkin.
Lekin bu eng achinarlisi emas. Ishlab chiquvchi tanlashni so'raganligi sababli DISTINCT aniq bo'lganlar uchun emas, balki bir vaqtning o'zida barcha sohalar uchun yozuvlar, keyin sub_so'rov maydoni - pastki so'rov natijasi - u erda avtomatik ravishda kiritilgan. Endi, bajarish uchun DISTINCT, ma'lumotlar bazasi allaqachon bajarilishi kerak edi 10 ta pastki so'rov emas, balki barcha <2 * N> + 10!
2.4: birinchi navbatda hamkorlik!
Shunday qilib, ishlab chiquvchilar yashashdi - ular bezovta qilmadilar, chunki foydalanuvchi har bir keyingi "sahifa" ni olishda surunkali sekinlashuv bilan reestrni muhim N qiymatlariga "sozlash" uchun etarli sabr-toqatga ega emas edi.
Ularga boshqa bo'limdan ishlab chiquvchilar kelib, shunday qulay usuldan foydalanishni xohlamaguncha iterativ qidiruv uchun - ya'ni biz qandaydir namunadan parcha olamiz, uni qo'shimcha shartlar bo'yicha filtrlaymiz, natijani chizamiz, keyin keyingi qismni (bizning holatlarimizda N ni oshirish orqali erishiladi) va biz ekranni to'ldirgunimizcha davom etamiz.
Umuman olganda, ushlangan namunada N deyarli 17K qiymatlarga erishdi, va faqat bir kun ichida bunday so'rovlarning kamida 4K "zanjir bo'ylab" bajarildi. Ularning oxirgisi jasorat bilan skanerdan o'tkazildi Har bir iteratsiya uchun 1 Gb xotira...
jami
Manba: www.habr.com