পেজিনেটেড ক্যোয়ারীতে অফসেট এবং লিমিট ব্যবহার করা এড়িয়ে চলুন

সেই দিনগুলি চলে গেছে যখন আপনাকে ডাটাবেস কর্মক্ষমতা অপ্টিমাইজ করার বিষয়ে চিন্তা করতে হবে না। সময় স্থির থাকে না। প্রতিটি নতুন প্রযুক্তি উদ্যোক্তা পরবর্তী Facebook তৈরি করতে চায়, যখন তারা তাদের হাত পেতে পারে এমন সমস্ত ডেটা সংগ্রহ করার চেষ্টা করে। ব্যবসায়িকদের এই ডেটার প্রয়োজন হয় আরও ভাল মডেলের প্রশিক্ষণের জন্য যা তাদের অর্থ উপার্জনে সহায়তা করে। এই ধরনের পরিস্থিতিতে, প্রোগ্রামারদের এমন API তৈরি করতে হবে যা তাদের বিপুল পরিমাণ তথ্যের সাথে দ্রুত এবং নির্ভরযোগ্যভাবে কাজ করতে দেয়।

আপনি যদি কোনো দৈর্ঘ্যের জন্য অ্যাপ্লিকেশন বা ডাটাবেস ব্যাকএন্ড ডিজাইন করে থাকেন, তাহলে আপনি সম্ভবত পৃষ্ঠাযুক্ত প্রশ্নগুলি চালানোর জন্য কোড লিখেছেন। উদাহরণস্বরূপ, এই মত:

SELECT * FROM table_name LIMIT 10 OFFSET 40

এটা উপায়?

কিন্তু আপনি যদি এইভাবে আপনার পেজিনেশন করেছেন, আমি বলতে দুঃখিত যে আপনি এটি সবচেয়ে কার্যকর উপায়ে করেননি।

তুমি কি আমাকে আপত্তি করতে চাও? আপনি করতে পারেন না ব্যয় করা время. ঢিলা, বিষয়শ্রেণী и মিক্সম্যাক্স তারা ইতিমধ্যেই কৌশলগুলি ব্যবহার করছে যা আমি আজকে বলতে চাই।

অন্তত একজন ব্যাকএন্ড ডেভেলপারের নাম দিন যিনি কখনো ব্যবহার করেননি OFFSET и LIMIT পৃষ্ঠাযুক্ত প্রশ্নগুলি সম্পাদন করতে। MVP (ন্যূনতম কার্যকর পণ্য) এবং প্রকল্পগুলিতে যেখানে অল্প পরিমাণে ডেটা ব্যবহার করা হয়, এই পদ্ধতিটি বেশ প্রযোজ্য। এটা "শুধু কাজ করে," তাই কথা বলতে.

কিন্তু আপনার যদি স্ক্র্যাচ থেকে নির্ভরযোগ্য এবং দক্ষ সিস্টেম তৈরি করতে হয়, তাহলে এই ধরনের সিস্টেমে ব্যবহৃত ডাটাবেসগুলি অনুসন্ধান করার দক্ষতা সম্পর্কে আপনার আগে থেকেই যত্ন নেওয়া উচিত।

আজ আমরা পেজিনেটেড ক্যোয়ারী ইঞ্জিনগুলির সাধারণভাবে ব্যবহৃত (খুব খারাপ) বাস্তবায়নের সমস্যাগুলি এবং এই জাতীয় প্রশ্নগুলি চালানোর সময় কীভাবে উচ্চ কার্যকারিতা অর্জন করা যায় সে সম্পর্কে কথা বলব।

OFFSET এবং LIMIT এর সাথে কি ভুল?

যেমন ইতিমধ্যে বলা হয়েছে, OFFSET и LIMIT তারা এমন প্রকল্পগুলিতে ভাল পারফরম্যান্স করে যেগুলিকে প্রচুর পরিমাণে ডেটা নিয়ে কাজ করার প্রয়োজন নেই।

সমস্যা দেখা দেয় যখন ডাটাবেসটি এমন আকারে বৃদ্ধি পায় যে এটি আর সার্ভারের মেমরিতে ফিট করে না। যাইহোক, এই ডাটাবেসের সাথে কাজ করার সময়, আপনাকে পৃষ্ঠাযুক্ত প্রশ্নগুলি ব্যবহার করতে হবে।

এই সমস্যাটি নিজেকে প্রকাশ করার জন্য, এমন একটি পরিস্থিতি থাকতে হবে যেখানে ডিবিএমএস প্রতিটি পেজিনেটেড ক্যোয়ারীতে একটি অদক্ষ পূর্ণ টেবিল স্ক্যান অপারেশনের অবলম্বন করে (যদিও সন্নিবেশ এবং মুছে ফেলার ক্রিয়াকলাপ ঘটতে পারে, এবং আমাদের পুরানো ডেটার প্রয়োজন নেই!)

একটি "সম্পূর্ণ টেবিল স্ক্যান" (বা "অনুক্রমিক টেবিল স্ক্যান", অনুক্রমিক স্ক্যান) কি? এটি এমন একটি অপারেশন যার সময় DBMS ক্রমানুসারে টেবিলের প্রতিটি সারি পড়ে, অর্থাৎ এতে থাকা ডেটা, এবং একটি প্রদত্ত শর্তের সাথে সম্মতির জন্য সেগুলি পরীক্ষা করে। এই ধরনের টেবিল স্ক্যান সবচেয়ে ধীর বলে পরিচিত। আসল বিষয়টি হল যখন এটি কার্যকর করা হয়, তখন অনেক ইনপুট/আউটপুট অপারেশন করা হয় যা সার্ভারের ডিস্ক সাবসিস্টেমকে জড়িত করে। ডিস্কে সংরক্ষিত ডেটার সাথে কাজ করার সাথে যুক্ত লেটেন্সি এবং ডিস্ক থেকে মেমরিতে ডেটা স্থানান্তর করা একটি সম্পদ-নিবিড় ক্রিয়াকলাপের কারণে পরিস্থিতি আরও খারাপ হয়েছে।

উদাহরণস্বরূপ, আপনার কাছে 100000000 ব্যবহারকারীর রেকর্ড রয়েছে এবং আপনি নির্মাণের সাথে একটি ক্যোয়ারী চালান OFFSET 50000000. এর মানে হল যে ডিবিএমএসকে এই সমস্ত রেকর্ডগুলি লোড করতে হবে (এবং আমাদের তাদের প্রয়োজনও নেই!), সেগুলিকে মেমরিতে রাখতে হবে এবং তার পরে, বলুন, 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 এমএস সময় নিয়েছে, কিন্তু দ্বিতীয় - 1 এমএস)।

এবং যদি আরও ডেটা থাকে তবে সবকিছু আরও খারাপ দেখাবে (এটি নিশ্চিত হওয়ার জন্য, আমার দেখুন উদাহরণ 10 মিলিয়ন সারি সহ)।

আমরা যা আলোচনা করেছি তা আপনাকে ডাটাবেস কোয়েরিগুলি কীভাবে প্রক্রিয়া করা হয় সে সম্পর্কে কিছুটা অন্তর্দৃষ্টি দেওয়া উচিত।

দয়া করে নোট করুন যে মান যত বেশি OFFSET - অনুরোধটি সম্পূর্ণ হতে যত বেশি সময় লাগবে।

অফসেট এবং সীমার সংমিশ্রণের পরিবর্তে আমার কী ব্যবহার করা উচিত?

একটি সমন্বয়ের পরিবর্তে 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