تکنیک های بهینه سازی پرس و جوی SQL

بازنشر افلاطون

دنبال: 0

تکنیک های بهینه سازی پرس و جوی SQL
تصویر توسط نویسنده

در سطح مبتدی، ما فقط بر روی نوشتن و اجرای پرس و جوهای SQL تمرکز می کنیم. ما در مورد اینکه اجرای آن چقدر زمان می برد یا اینکه می تواند میلیون ها رکورد را مدیریت کند، کاری نداریم. اما در سطح متوسط، مردم انتظار دارند که پرس و جو شما بهینه شود و حداقل زمان برای اجرا نیاز دارد.

نوشتن یک پرس و جو بهینه شده در برنامه های کاربردی بزرگ با میلیون ها رکورد، مانند پلت فرم های تجارت الکترونیک یا سیستم های بانکی، ضروری است. فرض کنید صاحب یک شرکت تجارت الکترونیک با بیش از یک میلیون محصول هستید و مشتری می خواهد محصولی را جستجو کند. اگر درخواستی که در باطن نوشتید بیش از یک دقیقه طول بکشد تا آن محصول از پایگاه داده واکشی شود چه؟ آیا فکر می کنید مشتریان محصولاتی را از وب سایت شما خریداری می کنند؟

شما باید اهمیت بهینه سازی پرس و جوی SQL را درک کنید. در این آموزش، نکات و ترفندهایی را به شما نشان خواهم داد تا پرس و جوهای SQL خود را بهینه کنید و آنها را سریعتر اجرا کنید. پیش نیاز اولیه این است که شما باید دانش اولیه SQL را داشته باشید.

برای بررسی اینکه آیا عنصر خاصی در جدول وجود دارد یا خیر، از عبارت استفاده کنید EXIST() کلمه کلیدی به جای COUNT() پرس و جو را به روشی بهینه تر اجرا می کند.

با استفاده از COUNT()، پرس و جو نیاز به شمارش تمام رخدادهای آن عنصر خاص دارد که ممکن است زمانی که پایگاه داده گسترده است ناکارآمد باشد. از سوی دیگر، EXIST() فقط اولین رخداد آن عنصر را بررسی می کند و پس از یافتن اولین رخداد متوقف می شود. این باعث صرفه جویی در زمان زیادی می شود.

همچنین، شما فقط علاقه مند به یافتن وجود یا عدم وجود یک عنصر خاص هستید. شما علاقه ای به یافتن تعداد رخدادها ندارید. به همین دلیل نیز EXIST() بهتر است.

SELECT EXISTS( SELECT * FROM table WHERE myColumn = 'val' );

پرس و جوی بالا بازخواهد گشت 1 اگر حداقل یک ردیف جدول حاوی ورودی باشد که در آن ستونی به نام myColumn دارای مقدار برابر است وال. در غیر این صورت باز خواهد گشت 0.

هر دو char و varchar از انواع داده ها برای ذخیره رشته های کاراکتر در جدول استفاده می شود. ولی varchar بسیار کارآمدتر از حافظه است char.

نوع داده char فقط می تواند رشته کاراکتری با طول ثابت تعریف شده را ذخیره کند. اگر طول رشته کمتر از طول ثابت باشد، آنگاه فضاهای خالی را ضمیمه می کند تا طول آن برابر با طول تنظیم شده باشد. این باعث هدر رفتن بی مورد حافظه در padding می شود. مثلا،CHAR(100) حتی اگر یک کاراکتر ذخیره شود، 100 بایت از حافظه می گیرد.

از طرف دیگر، نوع داده varchar رشته کاراکتری با طول متغیر را با طول کمتر از حداکثر طول مشخص شده ذخیره می کند. فضاهای خالی را خالی نمی کند و فقط حافظه را برابر با طول واقعی رشته می گیرد. مثلا، VARCHAR(100) هنگام ذخیره یک کاراکتر تنها 1 بایت حافظه می گیرد.

CREATE TABLE myTable ( id INT PRIMARY KEY, charCol CHAR(10), varcharCol VARCHAR(10)
);

در مثال بالا، یک جدول myTable با دو ستون ایجاد می شود، charCol و varcharCol دارای انواع داده char و varchar به ترتیب. charCol همیشه 10 بایت حافظه می گیرد. متقابلا، varcharCol حافظه را برابر با اندازه واقعی رشته کاراکتر ذخیره شده در آن می گیرد.

ما باید از استفاده از پرس و جوهای فرعی در داخل عبارت WHERE برای بهینه سازی پرس و جوی SQL اجتناب کنیم. از آنجایی که پرس‌و‌جوهای فرعی زمانی که تعداد زیادی ردیف را برمی‌گردانند می‌توانند گران و دشوار باشند.

به جای استفاده از پرس و جوی فرعی، می توانید با استفاده از عملیات پیوستن یا نوشتن یک زیرپرسوجوی همبسته، به همان نتیجه برسید. پرسش فرعی همبسته، پرسش فرعی است که در آن پرس و جو درونی به پرس و جوی بیرونی بستگی دارد. و در مقایسه با پرس و جوهای فرعی غیر همبسته بسیار کارآمد هستند.

در زیر یک مثال برای درک تفاوت بین این دو آورده شده است.

# Using a subquery
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'INDIA' ); # Using a join operation
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'INDIA';

در مثال اول، پرس و جوی فرعی ابتدا تمام شناسه های مشتری را که به هند تعلق دارند جمع آوری می کند و سپس کوئری بیرونی تمام سفارشات شناسه های مشتری انتخاب شده را دریافت می کند. و در مثال دوم با پیوستن به به همین نتیجه رسیده ایم customers و orders جداول و سپس انتخاب فقط سفارشاتی که مشتریان از هند تعلق دارند.

به این ترتیب، می‌توانیم با اجتناب از استفاده از زیرپرس و جوهای درون عبارت WHERE و آسان‌تر کردن خواندن و درک آن‌ها، کوئری را بهینه کنیم.

درخواست JOIN عملیات از یک جدول بزرگتر به یک جدول کوچکتر یک تکنیک رایج بهینه سازی SQL است. زیرا پیوستن از یک جدول بزرگتر به یک جدول کوچکتر باعث می شود درخواست شما سریعتر اجرا شود. اگر الف را اعمال کنیم JOIN عملیات از یک جدول کوچکتر به یک جدول بزرگتر، موتور SQL ما باید در یک جدول بزرگتر برای ردیف های مطابقت جستجو کند. این کار بیشتر منابع فشرده و زمان بر است. اما از سوی دیگر، اگر JOIN از یک جدول بزرگتر به یک جدول کوچکتر اعمال می شود، سپس موتور SQL باید در یک جدول کوچکتر برای ردیف های مطابقت جستجو کند.

در اینجا یک مثال برای درک بهتر شما آورده شده است.

# Order table is larger than the Customer table # Join from a larger table to a smaller table
SELECT * FROM Order JOIN Customer ON Customer.id = Order.id # Join from a smaller table to a larger table
SELECT * FROM Customer JOIN Order ON Customer.id = Order.id

بر خلاف LIKE عبارت، regexp_like همچنین برای جستجوی الگو استفاده می شود. را LIKE clause یک عملگر اصلی تطبیق الگو است که فقط می تواند عملیات اساسی مانند انجام دهد _ or %، که به ترتیب برای مطابقت با یک کاراکتر یا هر تعداد کاراکتر استفاده می شوند. را LIKE بند باید پایگاه داده کامل را اسکن کند تا الگوی خاص را پیدا کند که برای جداول بزرگ کند است.

از سوی دیگر، regexp_like یک تکنیک جستجوی الگوی کارآمدتر، بهینه تر و قدرتمندتر است. از عبارات منظم پیچیده تری برای یافتن الگوهای خاص در یک رشته کاراکتر استفاده می کند. این عبارات منظم نسبت به تطبیق ساده عبارات خاص تر هستند زیرا به شما امکان می دهند الگوی دقیقی را که ما پیدا می کنیم جستجو کنید. به همین دلیل، حجم داده هایی که باید جستجو شوند کاهش می یابد و کوئری سریعتر اجرا می شود.

لطفا توجه داشته باشید که regexp_like ممکن است در همه سیستم های مدیریت پایگاه داده وجود نداشته باشد. نحو و عملکرد آن ممکن است در سیستم های دیگر متفاوت باشد.

در اینجا یک مثال برای درک بهتر شما آورده شده است.

# Query using the LIKE clause
SELECT * FROM mytable WHERE ( name LIKE 'A%' OR name LIKE 'B%' ); # Query using regexp_like clause
SELECT * FROM mytable WHERE regexp_like(name, '^[AB].*');

پرس و جوهای بالا برای یافتن عناصری که نام آنها با A یا B شروع می شود استفاده می شود. در مثال اول، LIKE برای جستجوی تمام نام هایی که با A یا B شروع می شوند استفاده می شود. A% به این معنی است که کاراکتر اول A است. پس از آن، هر تعداد کاراکتر می تواند وجود داشته باشد. در مثال دوم، regexp_like استفاده می شود. داخل ^[AB], ^ نشان می دهد که نماد در ابتدای رشته مطابقت دارد، [AB] نشان می دهد که کاراکتر آغازین می تواند A یا B باشد و .* نشان دهنده تمام شخصیت های بعد از آن است.

با استفاده از regexp_like، پایگاه داده می تواند به سرعت ردیف هایی را که با الگو مطابقت ندارند فیلتر کند و عملکرد را بهبود بخشد و استفاده از منابع را کاهش دهد.

در این مقاله روش ها و نکات مختلفی را برای بهینه سازی پرس و جو SQL مورد بحث قرار داده ایم. این مقاله به شما درک روشنی از نحوه نوشتن پرس و جوهای SQL کارآمد و اهمیت بهینه سازی آنها می دهد. راه‌های زیادی برای بهینه‌سازی کوئری‌ها وجود دارد، مانند ترجیح استفاده از مقادیر صحیح به جای کاراکترها یا استفاده از Union All به جای Union زمانی که جدول شما حاوی موارد تکراری نیست و غیره.

آریایی گرگ B.Tech است. دانشجوی مهندسی برق، در حال حاضر در سال آخر کارشناسی. علاقه او در زمینه توسعه وب و یادگیری ماشین است. او این علاقه را دنبال کرده و مشتاق است در این مسیرها بیشتر کار کند.