Səhifələnmiş sorğularda OFFSET və LIMIT istifadə etməyin

Verilənlər bazası performansını optimallaşdırmaqdan narahat olmadığınız günlər geridə qaldı. Zaman bir yerdə dayanmır. Hər bir yeni texnoloji sahibkar əlinə keçə biləcək bütün məlumatları toplamağa çalışarkən növbəti Facebook-u yaratmaq istəyir. Bizneslərə pul qazanmağa kömək edən modelləri daha yaxşı hazırlamaq üçün bu məlumatlar lazımdır. Belə şəraitdə proqramçılar böyük həcmli məlumatlarla tez və etibarlı işləməyə imkan verən API yaratmalıdırlar.

Əgər siz hər hansı bir müddət ərzində proqram və ya verilənlər bazası arxa uçlarını dizayn edirsinizsə, yəqin ki, səhifələnmiş sorğuları yerinə yetirmək üçün kod yazmısınız. Məsələn, bu kimi:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Bu necədir?

Ancaq səhifələşdirməni belə etdinizsə, üzr istəyirəm ki, bunu ən səmərəli şəkildə etmədiniz.

Mənə etiraz etmək istəyirsən? Siz edə bilərsiniz heç bir xərcləmək vaxt. Süstlük, Shopify и mixmax Bu gün haqqında danışmaq istədiyim texnikalardan artıq istifadə edirlər.

Heç vaxt istifadə etməyən ən azı bir backend developer adını verin OFFSET и LIMIT səhifələnmiş sorğuları yerinə yetirmək üçün. MVP (Minimum Viable Product) və kiçik həcmli məlumatların istifadə olunduğu layihələrdə bu yanaşma olduqca uyğundur. Bu, belə demək mümkünsə, "sadəcə işləyir".

Ancaq sıfırdan etibarlı və səmərəli sistemlər yaratmaq lazımdırsa, bu cür sistemlərdə istifadə olunan verilənlər bazalarının sorğulanmasının effektivliyinə əvvəlcədən diqqət yetirməlisiniz.

Bu gün biz səhifələnmiş sorğu mühərriklərinin tez-tez istifadə olunan (çox pis) tətbiqləri ilə bağlı problemlər və bu cür sorğuları yerinə yetirərkən yüksək performansa necə nail olmaq barədə danışacağıq.

OFFSET və LIMIT ilə nə səhvdir?

Artıq deyildiyi kimi OFFSET и LIMIT Böyük həcmli məlumatlarla işləməyə ehtiyac olmayan layihələrdə yaxşı performans göstərirlər.

Problem verilənlər bazası elə ölçüdə böyüdükdə yaranır ki, o, artıq serverin yaddaşına sığmır. Lakin bu verilənlər bazası ilə işləyərkən səhifələnmiş sorğulardan istifadə etməlisiniz.

Bu problemin özünü büruzə verməsi üçün DBMS-nin hər bir səhifələnmiş sorğuda səmərəsiz Tam Cədvəl Skanı əməliyyatına müraciət etdiyi bir vəziyyət olmalıdır (daxiletmə və silmə əməliyyatları baş verə bilər və bizə köhnəlmiş məlumat lazım deyil!).

“Tam cədvəl skanı” (və ya “ardıcıl cədvəl skanı”, Ardıcıl Skan) nədir? Bu, DBMS-nin cədvəlin hər bir sətirini, yəni içindəki məlumatları ardıcıl olaraq oxuduğu və verilmiş şərtə uyğunluğunu yoxladığı əməliyyatdır. Bu növ cədvəl taramasının ən yavaş olduğu bilinir. Fakt budur ki, icra edildikdə, serverin disk alt sistemini əhatə edən bir çox giriş/çıxış əməliyyatları yerinə yetirilir. Vəziyyəti disklərdə saxlanılan məlumatlarla işləmə ilə bağlı gecikmə və məlumatların diskdən yaddaşa ötürülməsinin resurs tutumlu əməliyyat olması ilə daha da pisləşir.

Məsələn, 100000000 istifadəçi qeydiniz var və siz konstruksiya ilə sorğu icra edirsiniz OFFSET 50000000. Bu o deməkdir ki, DBMS bütün bu qeydləri yükləməli (və onlara ehtiyacımız da yoxdur!), onları yaddaşa qoymalı və bundan sonra, məsələn, 20 nəticəni götürməli olacaq. LIMIT.

Deyək ki, belə görünə bilər: "50000-dən 50020-dən 100000-yə qədər sətirləri seçin". Yəni, sorğunu tamamlamaq üçün sistem əvvəlcə 50000 sıra yükləməli olacaq. Görürsünüzmü nə qədər lazımsız iş görəcək?

Mənə inanmırsınızsa, funksiyalardan istifadə edərək yaratdığım nümunəyə nəzər salın db-fiddle.com. 

db-fiddle.com-da nümunə

Orada, solda, tarlada Schema SQL, verilənlər bazasına 100000 sətir daxil edən kod var və sağda sahədə Query SQL, iki sorğu göstərilir. Birinci, yavaş, belə görünür:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Eyni problemin effektiv həlli olan ikincisi isə belədir:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Bu sorğuları yerinə yetirmək üçün düyməni sıxmağınız kifayətdir Run səhifənin yuxarısında. Bunu etdikdən sonra sorğunun icra müddəti haqqında məlumatları müqayisə edirik. Məlum olub ki, səmərəsiz sorğunun icrası ikincinin yerinə yetirilməsi ilə müqayisədə ən azı 30 dəfə uzun çəkir (bu müddət qaçışdan işə dəyişir; məsələn, sistem birinci sorğunun tamamlanmasının 37 ms çəkdiyini bildirə bilər, lakin sorğunun icrası ikinci - 1 ms).

Və daha çox məlumat varsa, hər şey daha da pis görünəcək (buna əmin olmaq üçün mənim misal 10 milyon sıra ilə).

İndicə müzakirə etdiyimiz şey verilənlər bazası sorğularının əslində necə işləndiyi barədə sizə bir az fikir verməlidir.

Nəzərə alın ki, dəyər nə qədər yüksəkdir OFFSET — sorğunun tamamlanması nə qədər uzun çəkəcək.

OFFSET və LIMIT birləşməsinin əvəzinə nə istifadə etməliyəm?

Birləşmə əvəzinə OFFSET и LIMIT Aşağıdakı sxemə uyğun olaraq qurulmuş bir quruluşdan istifadə etməyə dəyər:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Bu kursor əsaslı səhifələşdirmə ilə sorğunun icrasıdır.

Mövcudları yerli olaraq saxlamaq əvəzinə OFFSET и LIMIT və onları hər sorğu ilə ötürmək üçün, son alınan əsas açarı saxlamalısınız (adətən bu ID) Və LIMIT, nəticədə yuxarıdakılara bənzər sorğular alınacaq.

Niyə? Məsələ burasındadır ki, oxunan sonuncu sətirin identifikatorunu açıq şəkildə göstərərək, siz DBMS-ə lazımi məlumatların axtarışına harada başlamalı olduğunu bildirirsiniz. Üstəlik, açarın istifadəsi sayəsində axtarış səmərəli aparılacaq, sistemin müəyyən edilmiş diapazondan kənar xətlərlə yayındırılmasına ehtiyac olmayacaq.

Müxtəlif sorğuların aşağıdakı performans müqayisəsinə nəzər salaq. Budur səmərəsiz sorğu.

Yavaş sorğu

Və burada bu sorğunun optimallaşdırılmış versiyası var.

Sürətli sorğu

Hər iki sorğu eyni miqdarda məlumat qaytarır. Amma birincisi 12,80 saniyə, ikincisi isə 0,01 saniyə çəkir. Fərqi hiss edirsiniz?

Mümkün problemlər

Təklif olunan sorğu metodunun effektiv işləməsi üçün cədvəldə tam ədəd identifikatoru kimi unikal, ardıcıl indekslərdən ibarət sütun (və ya sütunlar) olmalıdır. Bəzi spesifik hallarda bu, verilənlər bazası ilə işləmə sürətini artırmaq üçün bu cür sorğulardan istifadənin uğurunu müəyyən edə bilər.

Təbii ki, sorğular qurarkən, cədvəllərin xüsusi arxitekturasını nəzərə almalı və mövcud cədvəllərdə ən yaxşı işləyəcək mexanizmləri seçməlisiniz. Məsələn, böyük həcmdə əlaqəli məlumatlarla sorğularda işləmək lazımdırsa, sizə maraqlı gələ bilər bu məqalə.

Əgər əsas açarı əldən vermə problemi ilə qarşılaşırıqsa, məsələn, çoxdan çoxa münasibəti olan bir cədvəlimiz varsa, o zaman ənənəvi istifadə yanaşması OFFSET и LIMIT, bizə uyğun olacağına zəmanət verilir. Lakin onun istifadəsi potensial olaraq yavaş sorğularla nəticələnə bilər. Belə hallarda, yalnız səhifələnmiş sorğuları idarə etmək üçün lazım olsa belə, avtomatik artan əsas açardan istifadə etməyi tövsiyə edərdim.

Bu mövzu ilə maraqlanırsınızsa - burada, burada и burada - bir neçə faydalı material.

Nəticələri

Çıxara biləcəyimiz əsas nəticə ondan ibarətdir ki, məlumat bazalarının ölçüsündən asılı olmayaraq, həmişə sorğunun icra sürətini təhlil etmək lazımdır. İndiki vaxtda həllərin miqyası son dərəcə vacibdir və hər şey müəyyən bir sistem üzərində işləməyin əvvəlindən düzgün tərtib olunarsa, bu, gələcəkdə tərtibatçını bir çox problemdən xilas edə bilər.

Verilənlər bazası sorğularını necə təhlil edir və optimallaşdırırsınız?

Mənbə: www.habr.com