بهینه سازی پرس و جوهای SQL: یک مثال با استفاده از MSSQL

عملکرد پرس و جوی SQL را با MSSQL تقویت کنید. در این راهنمای آموزنده بیاموزید که چگونه پرس و جوهای خود را برای کارایی و سرعت بهینه کنید.

درخواست‌های SQL بهینه‌نشده Microsoft SQL Server (MSSQL) می‌تواند منجر به عملکرد پایین‌تر، استفاده بیش از حد از منابع، داده‌های ناسازگار، نقص‌های امنیتی و مشکلات نگهداری شود. این مشکلات می تواند بر عملکرد، قابلیت اطمینان و امنیت برنامه شما تأثیر بگذارد، که ممکن است کاربران را ناامید کند و هزینه بیشتری برای شما به همراه داشته باشد.

بهینه سازی پرس و جوهای SQL در MSSQL برای دستیابی به عملکرد کارآمد و سریع بسیار مهم است. با پیاده سازی تکنیک هایی مانند نمایه سازی، ساده سازی پرس و جو و رویه های ذخیره سازی، کاربران می توانند عملکرد پرس و جو و عملکرد اصلاح داده ها را متعادل کنند و در نهایت عملکرد کلی پایگاه داده را بهبود بخشند.

بهینه سازی پرس و جو از طریق نمایه سازی

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

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

ایجاد ایندکس در MSSQL

ایجاد ایندکس در پایگاه داده های رابطه ای آسان است و MSSQL نادیده گرفته نمی شود. می توانید از دستور CREATE INDEX برای ایجاد یک شاخص در MSSQL استفاده کنید.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

در کد SQL بالا، index_name نام ایندکس، table_name نام جدول، و ستون 1، ستون 2 و غیره نام ستون هایی است که قرار است ایندکس شوند.

در اینجا نحوه ایجاد یک نمایه غیر خوشه ای در ستون LastName جدول مشتریان با یک عبارت SQL آورده شده است.

CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON Customers (LastName);

این عبارت یک نمایه غیر خوشه ای به نام IX_Customers_LastName در ستون LastName جدول مشتریان ایجاد می کند.

هزینه نمایه سازی

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

بنابراین، تعادل عملکرد پرس و جو و عملکرد اصلاح داده ها ضروری است. شما باید فقط در ستون هایی که به طور مکرر جستجو می کنید، فهرست ایجاد کنید. همچنین نظارت منظم بر استفاده از منابع فهرست و حذف نمایه های غیر ضروری ضروری است.

بهینه سازی پرس و جو از طریق ساده سازی پرس و جو

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

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

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

در اینجا یک نمونه پرس و جو پیچیده است که در MSSQL برای جدولی از سفارشات مشتری اجرا می شود که هدف آن شناسایی روندها و الگوها در داده ها است:

SELECT
  customer_name,
  COUNT(order_id) AS total_orders,
  AVG(order_amount) AS average_order_amount,
  SUM(order_amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status = 'completed'
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  total_sales DESC;

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

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

شما می توانید با شکستن پرس و جو به پرس و جوهای کوچکتر که یکی یکی اجرا می شوند، پرس و جو را ساده کنید.

-- Get a list of customer names and the total number of orders they have placed
SELECT
  customer_name,
  COUNT(order_id) AS total_orders
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status = 'completed'
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5;

-- Get the average order amount for each customer
SELECT
  customer_name,
  AVG(order_amount) AS average_order_amount
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status = 'completed'
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5;

-- Get the total sales for each customer
SELECT
  customer_name,
  SUM(order_amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status = 'completed'
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  total_sales DESC;

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

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

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

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

بهینه سازی پرس و جو از طریق رویه های ذخیره شده

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

در اینجا نمونه ای از ایجاد یک رویه ذخیره شده برای MSSQL است که میانگین حقوق کارمندان یک بخش را برمی گرداند:

CREATE PROCEDURE [dbo].[GetAverageSalary]
@DepartmentName VARCHAR(50)
AS
BEGIN
SELECT AVG(Salary) as AverageSalary
FROM Employees
WHERE Department = @DepartmentName
END

در روش ذخیره شده، پارامتری به نام @DepartmentName را تعریف کردید و از عبارت WHERE برای فیلتر کردن نتایج توسط بخش استفاده کردید. شما همچنین از تابع AVG برای محاسبه میانگین حقوق کارمندان در بخش استفاده کردید.

شما می توانید رویه های ذخیره شده را در MSSQL با دستور EXEC اجرا کنید.

در اینجا نحوه اجرای رویه ذخیره شده در بالا آمده است:

EXEC [dbo].[GetAverageSalary] @DepartmentName = 'Sales'

در بیانیه اجرای رویه ذخیره شده، مقدار Sales را به عنوان نام بخش ارسال می کنید. این بیانیه میانگین حقوق کارمندان در بخش فروش را برمی گرداند.

چگونه رویه های ذخیره شده عملکرد پرس و جو را بهبود می بخشند؟

رویه های ذخیره شده می توانند به طور قابل توجهی عملکرد پرس و جو را بهبود بخشند. اولاً، رویه‌های ذخیره‌شده با اجرای دستورات SQL در سمت سرور به جای انتقال داده‌ها بین مشتری و سرور، ترافیک شبکه را کاهش می‌دهند، در نتیجه میزان داده ارسال شده از طریق شبکه را کاهش می‌دهند و زمان پاسخ پرس و جو را کاهش می‌دهند.

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

شما می توانید MSSQL را در اوبونتو راه اندازی کنید

MSSQL در پشتیبانی از اوبونتو و سایر توزیع‌های لینوکس پیشرفت‌های چشمگیری داشته است. مایکروسافت به محبوبیت رو به رشد لینوکس در شرکت پی برد و تصمیم گرفت در دسترس بودن سرور پایگاه داده پرچمدار خود را به پلتفرم های لینوکس گسترش دهد.

منبع مقاله