მოერიდეთ OFFSET-ისა და LIMIT-ის გამოყენებას ფურცირებულ შეკითხვებში

წავიდა ის დრო, როდესაც თქვენ არ გჭირდებათ ფიქრი მონაცემთა ბაზის მუშაობის ოპტიმიზაციაზე. დრო არ დგას. ყველა ახალ ტექნოლოგიურ მეწარმეს სურს შექმნას შემდეგი Facebook, იმავდროულად, ცდილობს შეაგროვოს ყველა ის მონაცემი, რაც მათ ხელში მოხვდება. ბიზნესებს ესაჭიროებათ ეს მონაცემები, რათა უკეთ მოამზადონ მოდელები, რომლებიც მათ ფულის გამომუშავებაში დაეხმარება. ასეთ პირობებში, პროგრამისტებმა უნდა შექმნან API-ები, რომლებიც მათ საშუალებას მისცემს სწრაფად და საიმედოდ იმუშაონ უზარმაზარი რაოდენობით ინფორმაციასთან.

თუ თქვენ აპლიკაციის ან მონაცემთა ბაზის სარეზერვო ასლების დიზაინს აწარმოებდით გარკვეული დროის მანძილზე, თქვენ ალბათ დაწერეთ კოდი გვერდითი მოთხოვნების გასაშვებად. მაგალითად, ასე:

SELECT * FROM table_name LIMIT 10 OFFSET 40

როგორია?

მაგრამ თუ ასე გააკეთეთ თქვენი პაგინაცია, ვწუხვარ, რომ ეს არ გაგიკეთებიათ ყველაზე ეფექტური გზით.

გინდა გამიპროტესტო? თქვენ შეგიძლიათ არარის დახარჯვა დრო. Slack, Shopify и მიქსაქსი ისინი უკვე იყენებენ იმ ტექნიკას, რომელზეც დღეს მინდა ვისაუბრო.

დაასახელეთ მინიმუმ ერთი დეველოპერი, რომელსაც არასოდეს გამოუყენებია OFFSET и LIMIT ფურცირებული მოთხოვნების შესასრულებლად. MVP-ში (მინიმალური სიცოცხლისუნარიანი პროდუქტი) და პროექტებში, სადაც მცირე რაოდენობის მონაცემები გამოიყენება, ეს მიდგომა საკმაოდ გამოსაყენებელია. ასე ვთქვათ, "უბრალოდ მუშაობს".

მაგრამ თუ თქვენ გჭირდებათ ნულიდან საიმედო და ეფექტური სისტემების შექმნა, წინასწარ უნდა იზრუნოთ ასეთ სისტემებში გამოყენებული მონაცემთა ბაზების შეკითხვის ეფექტურობაზე.

დღეს ჩვენ ვისაუბრებთ პრობლემების შესახებ, რომლებიც ხშირად გამოიყენება (ძალიან ცუდი) გვერდითი მოთხოვნის ძრავების განხორციელების შესახებ და როგორ მივაღწიოთ მაღალ შესრულებას ასეთი მოთხოვნების შესრულებისას.

რისი ბრალია OFFSET და LIMIT?

როგორც უკვე ითქვა, OFFSET и LIMIT ისინი კარგად მუშაობენ პროექტებში, რომლებსაც არ სჭირდებათ დიდი რაოდენობით მონაცემებით მუშაობა.

პრობლემა ჩნდება მაშინ, როდესაც მონაცემთა ბაზა იზრდება ისეთ ზომამდე, რომ ის აღარ ჯდება სერვერის მეხსიერებაში. თუმცა, ამ მონაცემთა ბაზასთან მუშაობისას, თქვენ უნდა გამოიყენოთ ფაგინირებული მოთხოვნები.

იმისათვის, რომ ეს პრობლემა გამოვლინდეს, უნდა არსებობდეს სიტუაცია, როდესაც DBMS მიმართავს არაეფექტურ Full Table Scan ოპერაციას თითოეულ პაგინირებულ მოთხოვნაზე (მაშინ როდესაც შეიძლება მოხდეს ჩასმის და წაშლის ოპერაციები და ჩვენ არ გვჭირდება მოძველებული მონაცემები!).

რა არის "სრული ცხრილის სკანირება" (ან "თანმიმდევრული ცხრილის სკანირება", თანმიმდევრული სკანირება)? ეს არის ოპერაცია, რომლის დროსაც DBMS თანმიმდევრულად კითხულობს ცხრილის თითოეულ სტრიქონს, ანუ მასში არსებულ მონაცემებს და ამოწმებს მათ შესაბამისობას მოცემულ პირობასთან. ამ ტიპის ცხრილის სკანირება ცნობილია, როგორც ყველაზე ნელი. ფაქტია, რომ მისი შესრულებისას მრავალი შეყვანის/გამოსვლის ოპერაციები ხორციელდება, რომლებიც მოიცავს სერვერის დისკის ქვესისტემას. სიტუაციას ამძიმებს დისკებზე შენახულ მონაცემებთან მუშაობასთან დაკავშირებული შეყოვნება და ის ფაქტი, რომ მონაცემთა გადატანა დისკიდან მეხსიერებაში რესურსზე ინტენსიური ოპერაციაა.

მაგალითად, თქვენ გაქვთ 100000000 XNUMX XNUMX მომხმარებლის ჩანაწერი და აწარმოებთ შეკითხვას კონსტრუქციით OFFSET 50000000. ეს ნიშნავს, რომ DBMS-ს მოუწევს ჩატვირთოს ყველა ეს ჩანაწერი (და ჩვენ არც კი გვჭირდება!), ჩააყენოს ისინი მეხსიერებაში და ამის შემდეგ აიღოს, ვთქვათ, 20 შედეგი მოხსენებული. LIMIT.

ვთქვათ, შეიძლება ასე გამოიყურებოდეს: "აირჩიეთ რიგები 50000-დან 50020-მდე 100000-დან". ანუ, სისტემას ჯერ დასჭირდება 50000 მწკრივის ჩატვირთვა მოთხოვნის დასასრულებლად. ხედავ, რამდენი ზედმეტი სამუშაო მოუწევს მას?

თუ ჩემი არ გჯერათ, გადახედეთ ჩემს მიერ შექმნილი ფუნქციების მაგალითს db-fiddle.com. 

მაგალითი db-fiddle.com-ზე

იქ, მარცხნივ, მინდორში Schema SQL, არის კოდი, რომელიც 100000 სტრიქონს ათავსებს მონაცემთა ბაზაში და მარჯვნივ, ველში Query SQL, ნაჩვენებია ორი შეკითხვა. პირველი, ნელი, ასე გამოიყურება:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

და მეორე, რომელიც არის იგივე პრობლემის ეფექტური გადაწყვეტა, ასეთია:

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

ამ მოთხოვნების შესასრულებლად, უბრალოდ დააჭირეთ ღილაკს Run გვერდის ზედა ნაწილში. ამის შემდეგ ჩვენ შევადარებთ ინფორმაციას შეკითხვის შესრულების დროის შესახებ. გამოდის, რომ არაეფექტური მოთხოვნის შესრულებას მინიმუმ 30-ჯერ მეტი დრო სჭირდება, ვიდრე მეორეს (ეს დრო განსხვავდება გაშვებამდე; მაგალითად, სისტემამ შეიძლება შეატყობინოს, რომ პირველ მოთხოვნას დასჭირდა 37 ms, მაგრამ შესრულებას მეორე - 1 ms).

და თუ მეტი მონაცემი იქნება, მაშინ ყველაფერი კიდევ უფრო უარესად გამოიყურება (ამაში რომ დარწმუნდეთ, გადახედეთ ჩემს მაგალითად 10 მილიონი მწკრივით).

ის, რაც ჩვენ ახლა განვიხილეთ, უნდა მოგცეთ გარკვეული ინფორმაცია იმის შესახებ, თუ როგორ ხდება მონაცემთა ბაზის მოთხოვნების რეალურად დამუშავება.

გთხოვთ გაითვალისწინოთ, რომ რაც უფრო მაღალია მნიშვნელობა OFFSET - რაც უფრო მეტი დრო დასჭირდება მოთხოვნის შესრულებას.

რა გამოვიყენო OFFSET-ისა და LIMIT-ის კომბინაციის ნაცვლად?

კომბინაციის ნაცვლად OFFSET и LIMIT ღირს შემდეგი სქემის მიხედვით აგებული სტრუქტურის გამოყენება:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

ეს არის შეკითხვის შესრულება კურსორზე დაფუძნებული პაგინაციის საშუალებით.

იმის ნაცვლად, რომ შეინახოთ მიმდინარეები ადგილობრივად OFFSET и LIMIT და გადასცეთ ისინი ყოველი მოთხოვნით, თქვენ უნდა შეინახოთ ბოლო მიღებული პირველადი გასაღები (ჩვეულებრივ ასეა ID) და LIMIT, შედეგად, მიიღება ზემოაღნიშნულის მსგავსი მოთხოვნები.

რატომ? საქმე იმაშია, რომ ბოლო წაკითხული მწკრივის იდენტიფიკატორის მკაფიოდ მითითებით, თქვენ ეუბნებით თქვენს DBMS-ს, სად უნდა დაიწყოს საჭირო მონაცემების ძებნა. უფრო მეტიც, ძებნა, გასაღების გამოყენების წყალობით, განხორციელდება ეფექტურად, სისტემას არ მოუწევს ყურადღება მიიპყროს მითითებულ დიაპაზონს გარეთ ხაზებით.

მოდით შევხედოთ სხვადასხვა მოთხოვნების შესრულების შემდეგ შედარებას. აქ არის არაეფექტური შეკითხვა.

ნელი მოთხოვნა

და აქ არის ამ მოთხოვნის ოპტიმიზებული ვერსია.

სწრაფი მოთხოვნა

ორივე მოთხოვნა აბრუნებს ზუსტად იგივე რაოდენობის მონაცემებს. მაგრამ პირველს 12,80 წამი სჭირდება, ხოლო მეორეს 0,01 წამი. გრძნობ განსხვავებას?

შესაძლო პრობლემები

იმისათვის, რომ შემოთავაზებული შეკითხვის მეთოდი ეფექტურად იმუშაოს, ცხრილს უნდა ჰქონდეს სვეტი (ან სვეტები), რომელიც შეიცავს უნიკალურ, თანმიმდევრულ ინდექსებს, როგორიცაა მთელი რიცხვის იდენტიფიკატორი. ზოგიერთ კონკრეტულ შემთხვევაში, ამან შეიძლება განსაზღვროს ასეთი მოთხოვნების გამოყენების წარმატება მონაცემთა ბაზასთან მუშაობის სიჩქარის გასაზრდელად.

ბუნებრივია, მოთხოვნების აგებისას, თქვენ უნდა გაითვალისწინოთ ცხრილების სპეციფიკური არქიტექტურა და აირჩიოთ ის მექანიზმები, რომლებიც საუკეთესოდ იმუშავებს არსებულ ცხრილებზე. მაგალითად, თუ თქვენ გჭირდებათ მუშაობა შეკითხვებზე დიდი მოცულობის დაკავშირებული მონაცემებით, შეიძლება თქვენთვის საინტერესო იყოს ამ სტატია.

თუ ჩვენ წინაშე ვდგავართ პირველადი გასაღების გამოტოვების პრობლემის წინაშე, მაგალითად, თუ გვაქვს ცხრილი მრავალი-მრავალთან ურთიერთობით, მაშინ გამოყენების ტრადიციული მიდგომა OFFSET и LIMIT, გარანტირებულია ჩვენთან მორგება. მაგრამ მისმა გამოყენებამ შეიძლება გამოიწვიოს პოტენციურად ნელი მოთხოვნები. ასეთ შემთხვევებში, მე გირჩევთ გამოიყენოთ ავტომატური გაზრდის პირველადი გასაღები, მაშინაც კი, თუ ის საჭიროა მხოლოდ პაგინირებული მოთხოვნების დასამუშავებლად.

თუ გაინტერესებთ ეს თემა - აქ, აქ и აქ - რამდენიმე სასარგებლო მასალა.

შედეგები

მთავარი დასკვნა, რომლის გაკეთებაც შეგვიძლია, არის ის, რომ არ აქვს მნიშვნელობა რა ზომის მონაცემთა ბაზებზეა საუბარი, ყოველთვის აუცილებელია შეკითხვის შესრულების სიჩქარის ანალიზი. დღესდღეობით, გადაწყვეტილებების მასშტაბურობა ძალზე მნიშვნელოვანია და თუ ყველაფერი სწორად არის შემუშავებული გარკვეულ სისტემაზე მუშაობის თავიდანვე, ამან, მომავალში, შეიძლება გადაარჩინოს დეველოპერი მრავალი პრობლემისგან.

როგორ აანალიზებთ და ოპტიმიზაციას უკეთებთ მონაცემთა ბაზის შეკითხვებს?

წყარო: www.habr.com