۱۵ راهکار بهینه‌سازی کوئری‌های SQL

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

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

راهکارهایی برای بهینه‌سازی کوئری‌های SQL

برای بهینه‌سازی کوئری‌های SQL راه‌های مختلفی وجود دارد که شما می‌توانید به کمک آن‌ها فرایند بهینه‌سازی را انجام دهید. در ادامه مهم‌ترین و اصلی‌ترین راهکارهای بهینه‌سازی کوئری‌های SQL را بیان می‌کنیم:

۱. به‌درستی از ایندکس‌ها استفاده کنید

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

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

انواع ایندکس‌ها در پایگاه داده

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

ایندکس خوشه‌ای (Clustered Index): داده‌ها را به‌صورت فیزیکی بر اساس مقدار ستون مرتب می‌کند. این نوع ایندکس برای داده‌هایی که به‌صورت ترتیبی استفاده می‌شوند یا مقدار تکراری ندارند، مانند کلیدهای اصلی (Primary Key)، بسیار مناسب است.
ایندکس غیرخوشه‌ای (Non-Clustered Index): یک ساختار جداگانه برای نگه‌داری ایندکس می‌سازد. ایندکس غیرخوشه‌ای برای جدول‌هایی مناسب است که به‌نوعی مانند دیکشنری یا نگاشت هستند.
ایندکس متن کامل (Full-Text Index): برای جست‌وجو در متون بلند مانند مقاله‌ها یا ایمیل‌ها کاربرد دارد. این نوع از ایندکس، با ذخیره‌سازی موقعیت کلمات در متن، سرعت جست‌وجوی متنی را بالا می‌برد.

بهترین روش‌ها برای استفاده از ایندکس

ستون‌هایی که زیاد در کوئری‌ها استفاده می‌شوند را ایندکس کنید. برای مثال، اگر همیشه در حال جست‌وجو با customer_id هستید، ایندکس‌کردن این ستون تاثیر زیادی در سرعت دارد:

CREATE INDEX index_customer_id ON customers (customer_id);

1	CREATE INDEX index_customer_id ON customers (customer_id);

یکی دیگر از روش‌ها، اجتناب از ایندکس‌های غیرضروری است. اگرچه ایندکس‌ها در کوئری‌های SELECT فوق‌العاده مفید هستند، اما در عملیات‌هایی مانند INSERT, UPDATE و DELETE باعث کندی می‌شوند. دلیل کند شدن هم این است که باید همراه با تغییر داده‌ها، ایندکس‌ها هم به‌روزرسانی شوند.

در نهایت باید بتوانید نوع مناسب ایندکس را انتخاب کنید. برای مثال، اگر به‌طور معمول کوئری‌های شما را در بازه‌هایی از مقادیر جست‌وجو می‌کنند، ایندکس از نوع B-tree انتخاب مناسبی است.

۲. از SELECT * استفاده نکنید

یکی از اشتباه‌های رایج در نوشتن کوئری‌ها، استفاده از SELECT * است. شاید در نگاه اول انجام این کار به‌نظر ساده و راحت بیاید، اما پشت این راحتی، کلی هزینه پنهان شده است. در واقع وقتی از SELECT * استفاده می‌کنید، پایگاه داده مجبور می‌شود تمام ستون‌های جدول (حتی ستون‌هایی که به آن نیاز ندارید) را بخواند و انتقال دهد.

خواندن تمام ستون‌های جدول توسط دیتابیس و انتقال آن‌ها (مخصوصا زمانی که جدول بزرگ باشد یا کوئری‌های زیادی همزمان اجرا شوند) باعث مصرف بیشتر حافظه و کند شدن پاسخ‌گویی سیستم می‌شود. بهترین کار این است که فقط ستون‌هایی که واقعا نیاز داریم را انتخاب کنیم. این کار باعث افزایش سرعت اجرای کوئری می‌شود و حتی کد SQL ما را تمیزتر و قابل‌فهم‌تر هم می‌کند.

برای مثال، به‌جای این:

SELECT * 
FROM products;

1 2	SELECT * FROM products;

باید بنویسید:

SELECT product_id, product_name, product_price 
FROM products;

1 2	SELECT product_id, product_name, product_price FROM products;

۳. از بازیابی داده‌های اضافی یا غیرضروری پرهیز کنید

یکی دیگر از راهکارهای بهینه‌سازی کوئری‌های SQL، پرهیز از بازیابی داده‌های اضافی یا غیرضروری است. همان‌طور که گفتیم، انتخاب ستون‌های مورد نیاز یک روش موثر برای بهینه‌سازی کوئری‌ها است اما فقط به ستون‌ها محدود نشوید. در واقع تعداد ردیف‌هایی که بازیابی می‌کنید هم مهم است. هرچه تعداد ردیف‌های بازگشتی بیشتر باشد، کوئری کندتر اجرا می‌شود و منابع بیشتری را درگیر می‌کند.

برای جلوگیری از این مشکل، می‌توانید از دستور LIMIT استفاده کنید. این دستور تعداد ردیف‌هایی که می‌خواهید دریافت کنید را محدود می‌کند. همچنین این کار در کوئری‌هایی که برای تست، اعتبارسنجی یا بررسی سریع نتایج نوشته می‌شوند، بسیار کاربردی است.

برای مثال:

SELECT name 
FROM customers 
ORDER BY customer_group DESC 
LIMIT 100;

SELECT name

FROM customers

ORDER BY customer_group DESC

LIMIT 100;

این کوئری حتی اگر جدول مشتری میلیون‌ها ردیف داشته باشد، فقط ۱۰۰ ردیف از جدول را بازمی‌گرداند. نکته مهم این است که در مدل‌های داده‌ای خودکار یا گزارش‌های نهایی که نیاز به همه داده‌ها داریم، استفاده از LIMIT مناسب نیست. البته باید بدانید که برای توسعه، تست و دیباگ بسیار مفید است.

۴. استفاده بهینه از Joinها

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

انواع Join

Join انواع مختلفی دارد و شما باید نحوه استفاده از آن‌ها را یاد بگیرد. در ادامه مدل‌های مختلف جوین را بیشتر معرفی می‌کنیم تا این راهکار را بهتر و دقیق‌تر یاد بگیرید:

Inner Join: فقط ردیف‌هایی را بازمی‌گرداند که در هر دو جدول تطابق دارند.

SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

SELECT o.order_id, c.name

FROM orders o

INNER JOIN customers c ON o.customer_id = c.customer_id;

Full Outer Join: همه ردیف‌های هر دو جدول را بازمی‌گرداند. اگر تطابقی وجود نداشته باشد، مقدار NULL نمایش داده می‌شود.

SELECT o.order_id, c.name
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

SELECT o.order_id, c.name

FROM orders o

FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

Left Join / Right Join: مدل Left Join همه ردیف‌های جدول سمت چپ و مقادیر تطابق‌دار جدول راست را بازمی‌گرداند. اگر تطابقی نباشد، مقادیر NULL برای ستون‌های جدول سمت راست برگردانده می شود. Right Join هم به همین صورت، اما برعکس عمل می‌کند.

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

SELECT c.name, o.order_id

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id;

نکات کاربردی برای بهینه‌سازی Join

برای اینکه راهکار Join را بهتر پیاده‌سازی کنید، باید به نکات زیر توجه داشته باشید:

رعایت ترتیب مناسب جدول‌ها: از جدول‌هایی شروع کنید که ردیف‌های کمتری برمی‌گردانند. این کار حجم پردازش را در مراحل بعدی کاهش می‌دهد.
ایندکس‌گذاری روی ستون‌های Join را فراموش نکنید: ایندکس‌ها به دیتابیس کمک می‌کنند تا سریع‌تر ردیف‌های مرتبط را پیدا کند.

در کوئری‌های پیچیده از زیرکوئری یا CTE استفاده کنید: استفاده از CTE (عبارت‌های جدول مشترک) باعث خوانایی بیشتر و عملکرد بهتر می‌شود. در ادامه یک مثال ساده برای استفاده از CTE را مشاهده می‌کنید:

WITH RecentOrders AS)
    SELECT customer_id, order_id
    FROM orders
    WHERE order_date >= DATE(‘now’, ‘-30 days’) 
)
SELECT c.customer_name, ro.order_id
FROM customers c
INNER JOIN RecentOrders ro ON c.customer_id = ro.customer_id;

WITH RecentOrders AS)

SELECT customer_id, order_id

FROM orders

WHERE order_date >= DATE(‘now’, ‘-30 days’)

)

SELECT c.customer_name, ro.order_id

FROM customers c

INNER JOIN RecentOrders ro ON c.customer_id = ro.customer_id;

۵. بررسی برنامه اجرای کوئری (Query Execution Plan)

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

برای همین منظور، بیشتر پایگاه‌های داده ابزاری مانند EXPLAIN یا EXPLAIN PLAN دارند. این ابزارها به شما یک نقشه از مراحل اجرای کوئری نشان می‌دهند. داشتن این نقشه‌ مانند این است که یک سیستم موقعیت‌یاب جهانی (GPS) در اختیار داشته باشید و به شما نشان دهد که یک پرس و جو از کجا آغاز می‌شود، در کدام نقاط تغییر مسیر می‌دهد و در چه موقعیت‌هایی با مانع مواجه می‌شود. برای درک بیشتر موضوع به مثال زیر توجه کنید:

EXPLAIN 
SELECT f.title, a.actor_name
FROM film f, film_actor fa, actor a
WHERE f.film_id = fa.film_id AND fa.actor_id = a.id;

EXPLAIN

SELECT f.title, a.actor_name

FROM film f, film_actor fa, actor a

WHERE f.film_id = fa.film_id AND fa.actor_id = a.id;

سپس می‌توانیم نتایج را بررسی کنیم:

چگونه خروجی EXPLAIN را تفسیر کنیم؟

برای درک بهتر موضوع، در اینجا یک راهنما درباره نحوه تفسیر نتایج را برایتان آورده‌ایم:

Full Table Scan: اگر خروجی نشان دهد که دیتابیس کل جدول را خط‌به‌خط اسکن می‌کند، یعنی یک جای کار ایراد دارد. در چنین شرایطی معمولا یا ایندکس ندارید یا شرط WHERE بهینه نیست.
استراتژی‌های Join ضعیف: ممکن است دیتابیس از الگوریتم‌های ترکیب ناکارآمد استفاده کند. با EXPLAIN می‌توانیم بفهمیم که دقیقا چه اتفاقی می‌افتد.
مشکلات دیگر: Explain plans می‌توانند مشکلات دیگری مانند هزینه‌های بالای مرتب‌سازی (Sort) یا استفاده بیش‌ازحد از جدول‌های موقت را نیز نشان دهند.

۶. بهینه‌سازی شرط WHERE

بهینه‌سازی شرط WHERE، یکی دیگر از راهکارهای بهینه‌سازی کوئری‌های SQL است. در واقع عبارت WHERE قلب تپنده بسیاری از کوئری‌های SQL به شمار می‌آید. این بخش به شما اجازه می‌دهد تا داده‌ها را براساس شرایط خاصی فیلتر کنید. در نتیجه، فقط اطلاعات مرتبط بازیابی می‌شود و حجم پردازش (مخصوصا وقتی با دیتاست‌های بزرگ کار می‌کنید) کاهش پیدا می‌کند.

البته فقط داشتن WHERE کافی نیست. شما باید بتوانید به‌صورت هوشمندانه از آن استفاده کنید. در اینجا چند تکنیک برای بهینه‌سازی WHERE معرفی می‌کنیم:

اعمال سریع شرط‌ها: هرچه زودتر ردیف‌های غیرمرتبط را حذف کنید، دیتابیس سریع‌تر به نتیجه می‌رسد. فیلترها باید در ابتدای شرط‌ها باشند تا هرچه زودتر بار داده کاهش پیدا کند.
اجتناب از توابع روی ستون‌ها: اگر در شرط WHERE از توابعی مانند YEAR() یا MONTH() روی ستون‌ها استفاده کنید، دیتابیس نمی‌تواند از ایندکس‌ها استفاده کند و باید برای هر ردیف تابع را محاسبه کنید. در واقع این موضوع باعث کاهش شدید کارایی می‌شود.

برای مثال، به جای این عبارت:

SELECT * 
FROM employees 
WHERE YEAR(hire_date) = 2020;

SELECT *

FROM employees

WHERE YEAR(hire_date) = 2020;

باید بنویسید:

SELECT * 
FROM employees 
WHERE hire_date >= ‘2020-01-01’ AND hire_date < ‘2021-01-01’;

SELECT *

FROM employees

WHERE hire_date >= ‘2020-01-01’ AND hire_date < ‘2021-01-01’;

از عملگرهای مناسب استفاده کنید: معمولا = سریع‌تر از LIKE است. استفاده از بازه‌های زمانی مشخص، به‌نسبت توابع روی تاریخ‌ها انتخاب بهتری به شمار می‌آید. در اینجا یک مثال غیربهینه را مشاهده می‌کنید:

SELECT * 
FROM orders 
WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

SELECT *

FROM orders

WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

به جای مثال بالا، باید بنویسید:

SELECT * 
FROM orders 
WHERE order_date >= ‘2023-12-01’ AND order_date < ‘2024-01-01’;

SELECT *

FROM orders

WHERE order_date >= ‘2023-12-01’ AND order_date < ‘2024-01-01’;

۷. بهینه‌سازی زیرکوئری‌ها (Subqueries)

در ادامه معرفی راهکارهای بهینه‌سازی کوئری‌های SQL، به بهینه‌سازی زیرکوئری‌ها می‌رسیم. در بسیاری از مواقع، نیاز دارید که در دل یک کوئری، فیلترسازی، تجمیع یا الحاق داده‌ها را به‌صورت پویا انجام دهید. در واقع به‌جای اجرای چند کوئری مجزا، می‌توانید از زیرکوئری استفاده کنید.

زیرکوئری‌ها، به کوئری‌هایی گفته می‌شود که درون کوئری دیگر قرار می‌گیرند و معمولا در عبارات SELECT ،INSERT ،UPDATE یا DELETE استفاده می‌شوند. با اینکه استفاده از آن‌ها در نگاه اول جذاب به‌نظر می‌رسد، اما در صورتی‌که به‌درستی بهینه نشوند، می‌توانند موجب افت عملکرد شوند. در ادامه، برخی نکات مهم برای بهینه‌سازی زیرکوئری‌ها را معرفی می‌کنیم:

در صورت امکان، از JOIN به‌جای Subquery استفاده کنید. در بیشتر موارد، JOIN‌ها سریع‌تر و کارآمدتر از زیرکوئری‌ها عمل می‌کنند.
استفاده از عبارت‌های CTE (عبارات جدول مشترک) پیشنهاد مناسبی برای جایگزینی زیرکوئری‌ها است. CTEها کد را به بخش‌های کوچک‌تر تقسیم می‌کنند و باعث خوانایی بیشتر و نگهداری آسان‌تر کوئری می‌شوند. برای مثال:

WITH SalesCTE AS ( 
    SELECT salesperson_id, SUM(sales_amount) AS total_sales 
    FROM sales 
    GROUP BY salesperson_id 
)
SELECT salesperson_id, total_sales 
FROM SalesCTE 
WHERE total_sales > 5000;

WITH SalesCTE AS (

SELECT salesperson_id, SUM(sales_amount) AS total_sales

FROM sales

GROUP BY salesperson_id

)

SELECT salesperson_id, total_sales

FROM SalesCTE

WHERE total_sales > 5000;

از زیرکوئری‌های غیرهمبسته (Uncorrelated Subqueries) استفاده کنید. این نوع زیرکوئری‌ها مستقل از کوئری بیرونی اجرا می‌شوند و پردازش آن‌ها فقط یک‌بار انجام می‌شود. در مقابل، زیرکوئری‌های همبسته (Correlated) برای هر ردیف از کوئری بیرونی اجرا می‌شوند و زمان اجرای بیشتری نیاز دارند.

۸. استفاده از EXISTS به‌جای IN

در بیشتر اوقات، هنگام کار با زیرکوئری‌ها باید بررسی کنید که آیا مقدار مشخصی در مجموعه‌ای از نتایج وجود دارد؟ معمولا این کار با استفاده از IN یا EXISTS انجام می‌شود. با این حال، EXISTS در بسیاری از موارد (به‌ویژه در دیتاست‌های بزرگ) از نظر عملکردی انتخاب بهتری است.

عبارت IN ابتدا کل نتایج زیرکوئری را در حافظه بارگذاری می‌کند و سپس مقایسه را انجام می‌دهد. البته EXISTS برای بهینه‌سازی بیشتر، به‌محض یافتن یک تطابق، فرآیند را متوقف می‌کند و خروجی را بازمی‌گرداند. برای مثال:

SELECT * 
FROM orders o
WHERE EXISTS (
    SELECT 1 
    FROM customers c 
    WHERE c.customer_id = o.customer_id 
      AND c.country = ‘USA’
);

SELECT *

FROM orders o

WHERE EXISTS (

SELECT 1

FROM customers c

WHERE c.customer_id = o.customer_id

AND c.country = ‘USA’

);

۹. محدود کردن استفاده از DISTINCT

در شرایطی که نیاز دارید اطلاعات بدون تکرار بازیابی کنید، ممکن است گزینه‌ اولیه‌ شما استفاده از عبارت DISTINCT باشد. اگرچه این عبارت کاربردی است، اما استفاده‌ بیش‌ازحد یا بی‌رویه از آن می‌تواند باعث مصرف بالای منابع و کاهش سرعت شود. برای کاهش اتکا به DISTINCT، پیشنهادهای زیر را در نظر بگیرید:

حذف داده‌های تکراری در مراحل پاک‌سازی داده: این کار باعث می‌شود داده‌های واردشده به پایگاه داده از ابتدا تکراری نباشند.
استفاده از GROUP BY به‌جای DISTINCT: در بسیاری از موارد، GROUP BY می‌تواند با کارایی بهتر و امکان استفاده هم‌زمان از توابع تجمیعی، نتیجه‌ای مشابه یا بهتر ارائه دهد.

برای مثال، به نوشتن این عبارت:

SELECT DISTINCT city 
FROM customers;

1 2	SELECT DISTINCT city FROM customers;

می‌توانید این‌گونه بنویسید:

SELECT city 
FROM customers 
GROUP BY city;

SELECT city

FROM customers

GROUP BY city;

استفاده از توابع پنجره‌ای (Window Functions): توابعی مانند ROW_NUMBER() می‌توانند به شما کمک کنند تا بدون آن‌که نیاز به استفاده از DISTINCT باشد، ردیف‌های تکراری را شناسایی و حذف کنید.

۱۰. بهره‌گیری از قابلیت‌های خاص هر پایگاه داده

در فرایند تعامل با داده‌ها، شما باید دستورات SQL را از طریق سامانه مدیریت پایگاه داده (DBMS) اجرا می‌کنید. هر DBMS قابلیت‌های منحصربه‌فردی دارد که می‌توانند در بهینه‌سازی عملکرد کوئری‌ها نقش مهمی ایفا کنند.

Database Hints دستورالعمل‌هایی هستند که به کوئری اضافه می‌شوند تا بر تصمیمات بهینه‌ساز کوئری اثر بگذارند. استفاده از این دستورالعمل‌ها می‌تواند باعث بهبود عملکرد شود، اما استفاده از آن‌ها باید با احتیاط صورت بگیرد، زیرا ممکن است در برخی موارد باعث پیچیدگی یا کاهش کارایی شوند.

مثال در MySQL: استفاده از USE INDEX برای اجبار استفاده از یک ایندکس خاص

SELECT * 
FROM employees 
USE INDEX (idx_salary) 
WHERE salary > 50000;

SELECT *

FROM employees

USE INDEX (idx_salary)

WHERE salary > 50000;

مثال در SQL Server: استفاده از OPTION (LOOP JOIN) برای تعیین نوع الحاق

SELECT * 
FROM orders 
INNER JOIN customers 
    ON orders.customer_id = customers.id 
OPTION (LOOP JOIN);

SELECT *

FROM orders

INNER JOIN customers

ON orders.customer_id = customers.id

OPTION (LOOP JOIN);

معمولا این دستورات در موارد خاصی که کوئری پیچیده است یا تصمیمات پیش‌فرض بهینه‌ساز ناکارآمد هستند، به کار گرفته می‌شوند. همچنین باید بدانید که در سیستم‌های مبتنی بر ابر، برای مدیریت داده‌های بزرگ و توزیع بار پردازشی، از دو تکنیک زیر استفاده می‌شود:

پارتیشن‌بندی: این تکنیک شامل تقسیم یک جدول بزرگ به چند جدول کوچک‌تر است. البته این تقسیم‌بندی بر اساس یک کلید پارتیشن (مانند تاریخ ایجاد یا یک مقدار عددی) انجام می‌شود. در هنگام اجرای کوئری، سیستم به‌طور خودکار تنها به پارتیشن مرتبط مراجعه می‌کند. این امر باعث افزایش سرعت بازیابی داده‌ها می‌شود.
شاردینگ: در این روش، کل پایگاه داده به چند پایگاه داده‌ مستقل تقسیم می‌شود که هر یک از آن‌ها روی سروری جداگانه قرار دارند. در اینجا، یک کلید شاردینگ مسیر اجرای کوئری را به پایگاه داده مناسب هدایت می‌کند. این کار به توزیع بار بین سرورها و افزایش مقیاس‌پذیری کمک می‌کند.

۱۱. پایش و به‌روزرسانی آمار پایگاه داده

پایش و به‌روزرسانی آمار پایگاه داده هم یکی دیگر از راهکارهای بهینه‌سازی کوئری‌های SQL به‌حساب می‌آید. برای اینکه بهینه‌ساز کوئری بتواند بهترین مسیر اجرای کوئری را انتخاب کند، نیاز به اطلاعات دقیق از ساختار و پراکندگی داده‌ها دارد. این اطلاعات از طریق آمار پایگاه داده (Database Statistics) تامین می‌شود.

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

اغلب پایگاه‌های داده، مکانیزم‌هایی برای به‌روزرسانی خودکار آمار دارند. در SQL Server، آمار به‌طور پیش‌فرض هنگام تغییر قابل‌توجه داده‌ها به‌روزرسانی می‌شود. همچنین در PostgreSQL، قابلیت Auto-Analyze پس از عبور از یک آستانه مشخص از تغییرات، آمار را به‌روزرسانی می‌کند.

در شرایطی که این مکانیزم‌های خودکار کافی نباشند یا نیاز به دخالت دستی باشد. در PostgreSQL می‌توان از دستور ANALYZE برای این منظور استفاده کرد:

— Update statistics for all tables in the current database
ANALYZE;
— Update statistics for a specific table
ANALYZE my_table;

— Update statistics for all tables in the current database

ANALYZE;

— Update statistics for a specific table

ANALYZE my_table;

۱۲. استفاده از Stored Procedureها

Stored Procedure یا رویه‌ ذخیره‌شده مجموعه‌ای از دستورات SQL است که در پایگاه داده ذخیره می‌شود تا بتوان آن را چندین بار و بدون نیاز به نوشتن مجدد، اجرا کرد. این قابلیت که یکی از بهترین راهکارهای بهینه‌سازی کوئری‌های SQL است، به شما کمک می‌کند تا منطق‌های پرتکرار مانند درج یا به‌روزرسانی داده‌ها را در قالب یک رویه بسته‌بندی کنید و تنها با فراخوانی آن، عملیات مورد نظر را انجام دهید.

Stored Procedureها علاوه بر افزایش خوانایی کد، به دلیل پردازش اولیه و آماده‌سازی دستورات، از لحاظ عملکرد هم کارآمدتر هستند.

برای مثال، در PostgreSQL می‌توان به صورت زیر یک رویه برای درج اطلاعات کارمند تعریف کرد:

CREATE OR REPLACE PROCEDURE insert_employee(
    emp_id INT,
    emp_first_name VARCHAR,
    emp_last_name VARCHAR
)
LANGUAGE plpgsql
AS $
BEGIN
    — Insert a new employee into the employees table
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (emp_id, emp_first_name, emp_last_name);
END;
$;

— call the procedure
CALL insert_employee(101, ‘John’, ‘Doe’);

CREATE OR REPLACE PROCEDURE insert_employee(

emp_id INT,

emp_first_name VARCHAR,

emp_last_name VARCHAR

)

LANGUAGE plpgsql

AS $

BEGIN

— Insert a new employee into the employees table

INSERT INTO employees (employee_id, first_name, last_name)

VALUES (emp_id, emp_first_name, emp_last_name);

END;

— call the procedure

CALL insert_employee(101, ‘John’, ‘Doe’);

۱۳. پرهیز از مرتب‌سازی و گروه‌بندی غیرضروری

در تحلیل داده‌ها، مرتب‌سازی (ORDER BY) و گروه‌بندی (GROUP BY) نقش مهمی دارند اما در عین حال می‌توانند هنگام کار با مجموعه‌های بزرگ، از پرهزینه‌ترین عملیات‌ها (از نظر منابع پردازشی) به‌حساب بیایند.

اجرای این دستورات نیازمند اسکن کامل داده و انجام محاسبات برای مرتب‌سازی یا دسته‌بندی است که می‌تواند باعث افزایش زمان پاسخ‌گویی کوئری‌ها شود.

با اینکه پرهیز از مرتب‌سازی و گروه‌بندی غیرضروری یکی از راهکارهای بهینه‌سازی کوئری‌های SQL به شمار می‌آید اما برای بهینه‌سازی عملکرد کوئری‌ها در این زمینه، باید نکات زیر را رعایت کنید:

کاهش مرتب‌سازی غیرضروری: تنها زمانی از ORDER BY استفاده کنید که واقعا به ترتیب خاصی نیاز دارید.
استفاده از ایندکس: اطمینان حاصل کنید که ستون‌های درگیر در عملیات ORDER BY یا GROUP BY، دارای ایندکس مناسب باشند.
انتقال مرتب‌سازی به لایه اپلیکیشن: در صورت امکان، مرتب‌سازی را به اپلیکیشن منتقل کنید تا بار آن از روی پایگاه داده برداشته شود.
پیش‌تجمیع داده‌ها: در سناریوهای پیچیده که GROUP BY با محاسبات ترکیب می‌شود، می‌توان داده‌ها را پیش‌تجمیع کرد یا از Viewهای مادی (Materialized Views) بهره برد.

۱۴. استفاده از UNION ALL به جای UNION

در مواقعی که نیاز داریم نتایج حاصل از چند کوئری را در یک لیست ترکیب کنیم، از عبارت‌های UNION یا UNION ALL استفاده می‌کنیم. هر دوی این دستورات، نتایج چندین دستور SELECT را که ساختاری مشابه دارند (یعنی تعداد و نوع ستون‌ها یکسان است) با هم ترکیب می‌کنند. البته تفاوت مهمی بین این دو وجود دارد که آن‌ها را برای سناریوهای متفاوت مناسب می‌سازد.

عبارت UNION، مقادیر تکراری را حذف می‌کند، یعنی پایگاه داده باید ابتدا نتایج را مرتب‌سازی کند و بعد از شناسایی مقادیر تکراری، تنها مقادیر یکتا را بازگرداند. این عملیات اضافی (خصوصا در داده‌های بزرگ) می‌تواند زمان‌بر و پردازشی باشد.

در طرف مقابل، UNION ALL همه ردیف‌ها از هر کوئری را بدون حذف تکراری‌ها بازمی‌گرداند، به همین دلیل در شرایطی که نیازی به حذف مقادیر تکراری نداریم، استفاده از UNION ALL به‌طور قابل‌توجهی کارایی بالاتری دارد.در مثال زیر، عملکرد دو روش مقایسه شده است:

— Potentially slower
SELECT product_id FROM products WHERE category = ‘Electronics’
UNION
SELECT product_id FROM products WHERE category = ‘Books’;

— Potentially faster
SELECT product_id FROM products WHERE category = ‘Electronics’
UNION ALL
SELECT product_id FROM products WHERE category = ‘Books’;

— Potentially slower

SELECT product_id FROM products WHERE category = ‘Electronics’

UNION

SELECT product_id FROM products WHERE category = ‘Books’;

— Potentially faster

SELECT product_id FROM products WHERE category = ‘Electronics’

UNION ALL

SELECT product_id FROM products WHERE category = ‘Books’;

۱۵. تجزیه کوئری‌های پیچیده (Break Down Complex Queries)

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

یکی از رویکردهای موثر برای مواجهه با این نوع کوئری‌ها، شکستن آن‌ها به بخش‌های کوچکتر و ساده‌تر است. این کار باعث می‌شود بتوانید تکنیک‌های بهینه‌سازی را هدفمندتر به کار بگیرید.

یکی از روش‌های رایج در این زمینه، استفاده از نماهای مادی (Materialized Views) است. این نماها نتایج از پیش‌محاسبه‌شده یک کوئری را ذخیره می‌کنند و هنگام اجرای کوئری، به جای محاسبه مجدد، به‌طور مستقیم از داده‌های ذخیره‌شده استفاده می‌شود. این موضوع باعث افزایش سرعت اجرای کوئری‌های تکراری می‌شود.

در صورتی که داده‌های زیربنایی تغییر کنند، نمای مادی هم باید به‌صورت دستی یا خودکار به‌روزرسانی شود. در ادامه نمونه‌ای از ساخت و استفاده از نمای مادی را مشاهده می‌کنید:

— Create a materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id;

— Query the materialized view
SELECT * FROM daily_sales;

— Create a materialized view

CREATE MATERIALIZED VIEW daily_sales AS

SELECT product_id, SUM(quantity) AS total_quantity

FROM order_items

GROUP BY product_id;

— Query the materialized view

SELECT * FROM daily_sales;

کلام آخر

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

منابع

datacamp.com

سوالات متداول

منظور مجموعه‌ای از تکنیک‌ها برای کاهش زمان اجرا و مصرف منابع کوئری‌هاست، بدون تغییر در خروجی داده.

ایندکس‌گذاری نامناسب، استفاده نادرست از JOINها، کوئری‌های پیچیده و حجم بالای داده از دلایل اصلی هستند.

ایندکس‌ها می‌توانند سرعت جستجو را به‌طور چشمگیری افزایش دهند، اما استفاده بیش‌ازحد آن‌ها ممکن است عملکرد نوشتن را کاهش دهد.

بله، جزئیات پیاده‌سازی در MySQL، PostgreSQL، SQL Server و Oracle متفاوت است، اما اصول کلی مشترک‌اند.

زمانی که حجم داده زیاد می‌شود، تعداد کاربران بالا می‌رود یا سیستم با محدودیت منابع مواجه است.

۱۵ راهکار بهینه‌سازی کوئری‌های SQL

راهکارهایی برای بهینه‌سازی کوئری‌های SQL

۱. به‌درستی از ایندکس‌ها استفاده کنید

انواع ایندکس‌ها در پایگاه داده

بهترین روش‌ها برای استفاده از ایندکس

۲. از SELECT * استفاده نکنید

۳. از بازیابی داده‌های اضافی یا غیرضروری پرهیز کنید

۴. استفاده بهینه از Joinها

انواع Join

نکات کاربردی برای بهینه‌سازی Join

۵. بررسی برنامه اجرای کوئری (Query Execution Plan)

چگونه خروجی EXPLAIN را تفسیر کنیم؟

۶. بهینه‌سازی شرط WHERE

۷. بهینه‌سازی زیرکوئری‌ها (Subqueries)

۸. استفاده از EXISTS به‌جای IN

۹. محدود کردن استفاده از DISTINCT

۱۰. بهره‌گیری از قابلیت‌های خاص هر پایگاه داده

۱۱. پایش و به‌روزرسانی آمار پایگاه داده

۱۲. استفاده از Stored Procedureها

۱۳. پرهیز از مرتب‌سازی و گروه‌بندی غیرضروری

۱۴. استفاده از UNION ALL به جای UNION

۱۵. تجزیه کوئری‌های پیچیده (Break Down Complex Queries)

سوالات متداول

دیدگاه‌ها

دیدگاهتان را بنویسید لغو پاسخ

۱۵ راهکار بهینه‌سازی کوئری‌های SQL

راهکارهایی برای بهینه‌سازی کوئری‌های SQL

۱. به‌درستی از ایندکس‌ها استفاده کنید

انواع ایندکس‌ها در پایگاه داده

بهترین روش‌ها برای استفاده از ایندکس

۲. از SELECT * استفاده نکنید

۳. از بازیابی داده‌های اضافی یا غیرضروری پرهیز کنید

۴. استفاده بهینه از Joinها

انواع Join

نکات کاربردی برای بهینه‌سازی Join

۵. بررسی برنامه اجرای کوئری (Query Execution Plan)

چگونه خروجی EXPLAIN را تفسیر کنیم؟

۶. بهینه‌سازی شرط WHERE

۷. بهینه‌سازی زیرکوئری‌ها (Subqueries)

۸. استفاده از EXISTS به‌جای IN

۹. محدود کردن استفاده از DISTINCT

۱۰. بهره‌گیری از قابلیت‌های خاص هر پایگاه داده

۱۱. پایش و به‌روزرسانی آمار پایگاه داده

۱۲. استفاده از Stored Procedureها

۱۳. پرهیز از مرتب‌سازی و گروه‌بندی غیرضروری

۱۴. استفاده از UNION ALL به جای UNION

۱۵. تجزیه کوئری‌های پیچیده (Break Down Complex Queries)

سوالات متداول

مطالب مرتبط

دیدگاه‌ها

دیدگاهتان را بنویسید لغو پاسخ