Ma'lumotlar bazasi ish faoliyatini optimallashtirish haqida qayg'urmasligingiz kerak bo'lgan kunlar o'tdi. Vaqt bir joyda turmaydi. Har bir yangi texnologik tadbirkor o'z qo'llariga olishi mumkin bo'lgan barcha ma'lumotlarni yig'ishga harakat qilib, keyingi Facebook-ni yaratishni xohlaydi. Korxonalarga pul ishlashga yordam beradigan modellarni yaxshiroq tayyorlash uchun bu maʼlumotlar kerak. Bunday sharoitda dasturchilar katta hajmdagi ma'lumotlar bilan tez va ishonchli ishlash imkonini beruvchi API yaratishlari kerak.
Agar siz uzoq vaqt davomida dastur yoki ma'lumotlar bazasini loyihalashtirgan bo'lsangiz, siz sahifalangan so'rovlarni bajarish uchun kod yozgan bo'lishingiz mumkin. Masalan, bu kabi:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Bu qanday?
Ammo agar siz sahifalashni shunday qilgan bo'lsangiz, afsuski, siz buni eng samarali tarzda qilmadingiz.
Menga e'tiroz bildirmoqchimisiz? . , и Ular men bugun gaplashmoqchi bo'lgan usullardan allaqachon foydalanishmoqda.
Hech qachon foydalanmagan kamida bitta dasturchini nomlang OFFSET и LIMIT sahifalangan so'rovlarni bajarish uchun. MVP (Minimum Viable Product) va kichik hajmdagi ma'lumotlardan foydalanadigan loyihalarda bu yondashuv juda qo'llaniladi. Bu, ta'bir joiz bo'lsa, "ishlaydi".
Ammo agar siz noldan ishonchli va samarali tizimlarni yaratishingiz kerak bo'lsa, bunday tizimlarda ishlatiladigan ma'lumotlar bazalarini so'rash samaradorligi haqida oldindan g'amxo'rlik qilishingiz kerak.
Bugun biz sahifalangan so'rovlar mexanizmlarini keng qo'llaniladigan (juda yomon) amalga oshirish bilan bog'liq muammolar va bunday so'rovlarni bajarishda qanday qilib yuqori ko'rsatkichlarga erishish haqida gaplashamiz.
OFFSET va LIMIT bilan nima xato?
Yuqorida aytilganidek, OFFSET и LIMIT Ular katta hajmdagi ma'lumotlar bilan ishlashni talab qilmaydigan loyihalarda yaxshi ishlaydi.
Muammo ma'lumotlar bazasi server xotirasiga sig'maydigan darajada kattalashganda paydo bo'ladi. Biroq, ushbu ma'lumotlar bazasi bilan ishlashda siz sahifalangan so'rovlardan foydalanishingiz kerak.
Ushbu muammoning paydo bo'lishi uchun ma'lumotlar bazasi ma'lumotlar bazasi har bir sahifalangan so'rovda to'liq jadvalni skanerlash samarasiz operatsiyaga murojaat qiladigan vaziyat bo'lishi kerak (qo'shish va o'chirish operatsiyalari sodir bo'lishi mumkin va bizga eskirgan ma'lumotlar kerak emas!).
"To'liq jadvalni skanerlash" (yoki "jadvalni ketma-ket skanerlash", ketma-ket skanerlash) nima? Bu ma'lumotlar bazasi ma'lumotlar bazasi jadvalning har bir satrini, ya'ni undagi ma'lumotlarni ketma-ket o'qiydi va ularni berilgan shartga muvofiqligini tekshiradi. Ushbu turdagi jadvalni skanerlash eng sekin ekanligi ma'lum. Gap shundaki, u bajarilganda serverning disk quyi tizimi bilan bog'liq ko'plab kiritish/chiqarish operatsiyalari bajariladi. Vaziyat disklarda saqlangan ma'lumotlar bilan ishlash bilan bog'liq kechikishlar va ma'lumotlarni diskdan xotiraga o'tkazish resurslarni ko'p talab qiladigan operatsiya ekanligi bilan yomonlashadi.
Masalan, sizda 100000000 XNUMX XNUMX foydalanuvchi yozuvlari bor va siz konstruksiya bilan so'rovni bajarasiz. OFFSET 50000000. Bu shuni anglatadiki, ma'lumotlar bazasi ma'lumotlar bazasi ushbu barcha yozuvlarni yuklashi kerak (va biz ularga kerak emas!), ularni xotiraga qo'yishi va shundan so'ng, masalan, 20 ta natijani olishi kerak. LIMIT.
Aytaylik, u shunday ko'rinishi mumkin: "50000 dan 50020 dan 100000 gacha qatorlarni tanlang". Ya'ni, so'rovni bajarish uchun tizim birinchi navbatda 50000 XNUMX qatorni yuklashi kerak bo'ladi. Ko'ryapsizmi, u qancha keraksiz ishlarni bajarishi kerak?
Agar menga ishonmasangiz, xususiyatlardan foydalangan holda men yaratgan misolni ko'rib chiqing .
Misol uchun db-fiddle.com
U erda, chapda, dalada Schema SQL, ma'lumotlar bazasiga 100000 XNUMX qator qo'shadigan kod mavjud va o'ng tomonda maydonda Query SQL, ikkita so'rov ko'rsatiladi. Birinchi, sekin, shunday ko'rinadi:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Xuddi shu muammoning samarali yechimi bo'lgan ikkinchisi esa quyidagicha:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Ushbu so'rovlarni bajarish uchun tugmani bosish kifoya Run sahifaning yuqori qismida. Buni amalga oshirib, so'rovni bajarish vaqti haqidagi ma'lumotlarni solishtiramiz. Ma'lum bo'lishicha, samarasiz so'rovni bajarish ikkinchisini bajarishdan kamida 30 baravar ko'proq vaqt oladi (bu vaqt ishga tushirishdan boshqasiga farq qiladi; masalan, tizim birinchi so'rovni bajarish uchun 37 milodiy vaqt sarflagani haqida xabar berishi mumkin, ammo so'rovning bajarilishi ikkinchi - 1 ms).
Va agar ko'proq ma'lumot bo'lsa, unda hamma narsa bundan ham yomonroq ko'rinadi (buga ishonch hosil qilish uchun mening ko'rsatmalarimga qarang. 10 million qator bilan).
Biz hozirgina muhokama qilgan narsa sizga ma'lumotlar bazasi so'rovlari aslida qanday qayta ishlanishi haqida ma'lumot berishi kerak.
E'tibor bering, qiymat qanchalik baland bo'lsa OFFSET — so‘rovni bajarish uchun qancha vaqt kerak bo‘ladi.
OFFSET va LIMIT kombinatsiyasi o'rniga nimani ishlatishim kerak?
Kombinatsiya o'rniga OFFSET и LIMIT Quyidagi sxema bo'yicha qurilgan tuzilmadan foydalanishga arziydi:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Bu kursorga asoslangan sahifalash bilan so'rovni bajarish.
Joriylarni mahalliy saqlash o'rniga OFFSET и LIMIT va ularni har bir so'rov bilan uzatish uchun siz oxirgi olingan asosiy kalitni saqlashingiz kerak (odatda bu ID) va LIMIT, natijada yuqoridagiga o'xshash so'rovlar olinadi.
Nega? Gap shundaki, oxirgi o'qilgan qatorning identifikatorini aniq ko'rsatib, siz o'zingizning DBMSga kerakli ma'lumotlarni qidirishni qaerdan boshlash kerakligini aytasiz. Bundan tashqari, kalitdan foydalanish tufayli qidiruv samarali amalga oshiriladi, tizimni belgilangan diapazondan tashqaridagi chiziqlar chalg'itishi shart emas.
Keling, turli so'rovlarning quyidagi ishlashini taqqoslashni ko'rib chiqaylik. Mana samarasiz so'rov.
Sekin so'rov
Va bu so'rovning optimallashtirilgan versiyasi.
Tez so'rov
Ikkala so'rov ham bir xil miqdordagi ma'lumotlarni qaytaradi. Lekin birinchisini bajarish uchun 12,80 soniya, ikkinchisiga esa 0,01 soniya kerak bo'ladi. Farqni his qilyapsizmi?
Mumkin bo'lgan muammolar
Taklif etilgan so'rov usuli samarali ishlashi uchun jadvalda butun son identifikatori kabi noyob, ketma-ket indekslarni o'z ichiga olgan ustun (yoki ustunlar) bo'lishi kerak. Ba'zi o'ziga xos holatlarda, bu ma'lumotlar bazasi bilan ishlash tezligini oshirish uchun bunday so'rovlardan foydalanish muvaffaqiyatini aniqlashi mumkin.
Tabiiyki, so'rovlarni tuzishda siz jadvallarning o'ziga xos arxitekturasini hisobga olishingiz va mavjud jadvallarda eng yaxshi ishlaydigan mexanizmlarni tanlashingiz kerak. Misol uchun, agar siz katta hajmdagi tegishli ma'lumotlarga ega bo'lgan so'rovlarda ishlashingiz kerak bo'lsa, u sizga qiziqarli bo'lishi mumkin maqola.
Agar biz asosiy kalitni yo'qotish muammosiga duch kelsak, masalan, agar bizda ko'p-ko'p munosabatlariga ega bo'lgan jadval mavjud bo'lsa, unda an'anaviy foydalanish usuli OFFSET и LIMIT, bizga mos kelishi kafolatlangan. Ammo undan foydalanish potentsial sekin so'rovlarga olib kelishi mumkin. Bunday hollarda, men avtomatik ravishda ko'paytiriladigan asosiy kalitdan foydalanishni tavsiya qilaman, hatto u faqat sahifalangan so'rovlarni bajarish uchun kerak bo'lsa ham.
Agar siz ushbu mavzuga qiziqsangiz - , и - bir nechta foydali materiallar.
natijalar
Biz chiqarishimiz mumkin bo'lgan asosiy xulosa shundan iboratki, biz ma'lumotlar bazalarining hajmi qanday bo'lishidan qat'iy nazar, har doim so'rovni bajarish tezligini tahlil qilish kerak. Hozirgi vaqtda echimlarning kengayishi juda muhim va agar hamma narsa ma'lum bir tizimda ishlashning boshidanoq to'g'ri tuzilgan bo'lsa, bu kelajakda ishlab chiquvchini ko'plab muammolardan qutqarishi mumkin.
Ma'lumotlar bazasi so'rovlarini qanday tahlil qilasiz va optimallashtirasiz?
Manba: www.habr.com