View ایندکس شده(Indexed View) SQL Server افزایش عملکرد و بهینه‌سازی پایگاه داده

View ایندکس شده (Indexed View) در SQL Server: راهکاری برای افزایش چشمگیر عملکرد پایگاه داده

View ایندکس شده (Indexed View) یکی از قابلیت‌های قدرتمند و اغلب نادیده گرفته شده در SQL Server است که می‌تواند تأثیر شگرفی بر بهینه‌سازی عملکرد کوئری‌ها و کاهش بار پردازشی سرور داشته باشد. این Viewها با ذخیره‌سازی فیزیکی نتایج کوئری در پایگاه داده، مانند یک جدول معمولی، به شما این امکان را می‌دهند تا به سرعت به داده‌های پیچیده یا تجمیع شده دسترسی پیدا کنید. اگر به دنبال راهی برای افزایش سرعت گزارش‌گیری، بهبود پاسخ‌دهی برنامه‌ها و کاهش منابع مصرفی در محیط‌های پربار هستید، درک و پیاده‌سازی Views ایندکس شده ضروری است.

View ایندکس شده چیست؟

یک View استاندارد (Standard View) در SQL Server در واقع یک کوئری ذخیره شده است که هنگام فراخوانی، هر بار مجدداً اجرا می‌شود. اما یک View ایندکس شده (Indexed View) (که گاهی اوقات به آن View مادی شده یا Materialized View نیز گفته می‌شود)، فراتر از این است. این View داده‌های برگشتی از کوئری خود را به صورت فیزیکی روی دیسک ذخیره می‌کند، دقیقاً مانند یک جدول. این ذخیره‌سازی فیزیکی به این معنی است که SQL Server می‌تواند یک ایندکس کلاستر (Clustered Index) و سپس ایندکس‌های غیرکلاستر (Non-Clustered Indexes) را روی آن ایجاد کند. هدف اصلی از ایجاد یک View ایندکس شده، کاهش زمان اجرای کوئری‌ها برای محاسبات پیچیده و تجمیع داده‌ها است. هنگامی که یک ایندکس کلاستر منحصر به فرد (Unique Clustered Index) بر روی یک View ایجاد می‌شود، نتایج آن View محاسبه شده و در پایگاه داده ذخیره می‌گردند. هر زمان که داده‌های جداول پایه تغییر می‌کنند، این View ایندکس شده نیز به صورت خودکار به‌روزرسانی می‌شود تا همیشه با داده‌های اصلی سازگار باشد.

مزایای استفاده از View ایندکس شده

استفاده از View ایندکس شده مزایای قابل توجهی را به همراه دارد که می‌تواند به طور چشمگیری کارایی پایگاه داده شما را افزایش دهد:

• بهبود عملکرد کوئری: مهم‌ترین مزیت، افزایش سرعت اجرای کوئری‌ها است، به خصوص برای گزارش‌هایی که شامل تجمیع‌های پیچیده یا جوین‌های متعدد هستند. به جای محاسبه مجدد داده‌ها در هر بار اجرا، سیستم از نسخه از پیش محاسبه شده و ایندکس شده View استفاده می‌کند.
• کاهش ورودی/خروجی (I/O): با دسترسی به داده‌های از پیش محاسبه شده، نیاز به خواندن حجم زیادی از داده‌ها از جداول پایه و انجام محاسبات تکراری کاهش می‌یابد که به نوبه خود بار I/O دیسک را کاهش می‌دهد.
• بهینه‌سازی کوئری‌های پیچیده: برای کوئری‌هایی که شامل توابع تجمیعی (SUM, COUNT_BIG, AVG) و گروه‌بندی (GROUP BY) هستند، Views ایندکس شده می‌توانند عملکرد را به طرز چشمگیری بهبود بخشند.
• یکپارچگی و سازگاری داده‌ها: داده‌های موجود در View ایندکس شده همیشه با داده‌های جداول پایه سازگار و به‌روز هستند، زیرا هرگونه تغییر در جداول پایه به صورت خودکار در View اعمال می‌شود.

پیش‌نیازهای ایجاد View ایندکس شده

برای ایجاد یک View ایندکس شده در SQL Server، مجموعه‌ای از الزامات سختگیرانه وجود دارد که باید رعایت شوند. عدم رعایت هر یک از این موارد می‌تواند منجر به خطا در ایجاد ایندکس روی View شود. در ادامه به مهم‌ترین این پیش‌نیازها اشاره می‌کنیم:

• SCHEMA_BINDING: View شما باید با گزینه WITH SCHEMABINDING ایجاد شود. این گزینه تضمین می‌کند که تعاریف جداول پایه استفاده شده در View، بدون تغییر در View قابل تغییر نیستند. این ویژگی ثبات و یکپارچگی ساختار View را تضمین می‌کند.
• توابع و عملیات قطعی (Deterministic): تمام توابع (مانند UDF) و عملیات استفاده شده در View باید قطعی (Deterministic) باشند. به این معنی که با همان ورودی، همیشه خروجی یکسانی را تولید کنند. توابع غیرقطعی مانند GETDATE() یا NEWID() مجاز نیستند.
• توابع تجمیعی: در صورت استفاده از توابع تجمیعی، باید از COUNT_BIG به جای COUNT(*) استفاده شود. همچنین SUM فقط برای عباراتی مجاز است که امکان NULL شدن نداشته باشند. AVG، MAX، MIN، STDEV، VAR، APPROX_COUNT_DISTINCT، و GROUPING نیز با محدودیت‌های خاصی مجازند.
• عبارت JOIN: فقط INNER JOIN مجاز است. LEFT OUTER JOIN، RIGHT OUTER JOIN، FULL OUTER JOIN، و CROSS JOIN مجاز نیستند.
• عبارت FROM: فقط می‌تواند شامل جداول پایه باشد و نمی‌تواند شامل Views دیگر (حتی View ایندکس شده دیگر)، توابع جدولی (Table-Valued Functions) یا OPENROWSET باشد.
• عبارت GROUP BY: اگر از توابع تجمیعی استفاده می‌کنید، عبارت GROUP BY الزامی است. اگر فقط COUNT_BIG(*) استفاده شود و هیچ ستون دیگری در SELECT و GROUP BY نباشد، GROUP BY نیز لازم نیست.
• عدم وجود عبارات خاص: عباراتی مانند OUTER JOIN، UNION، TOP، ORDER BY، DISTINCT، COMPUTE/COMPUTE BY، ROWSET، TABLE HINTS، subqueries، full-text predicates، CONTAINS/FREETEXT، MIN/MAX (در صورت استفاده از نوع داده float یا real) و انواع داده TEXT، NTEXT، IMAGE، XML، VARBINARY(MAX)، VARCHAR(MAX)، NVARCHAR(MAX) در SELECT، GROUP BY، یا WHERE، مجاز نیستند. همچنین timestamp، datetime، smalldatetime، datetimeoffset، time، real، float و sql_variant نمی‌توانند به عنوان ستون‌های کلیدی در ایندکس کلاستر منحصر به فرد استفاده شوند.
• تنظیمات نشست (Session Options): این تنظیمات برای همه نشست‌هایی که View و ایندکس آن را ایجاد می‌کنند، باید به صورت زیر باشد:

  
  SET ANSI_NULLS ON;
  SET ANSI_PADDING ON;
  SET ANSI_WARNINGS ON;
  SET ARITHABORT ON;
  SET CONCAT_NULL_YIELDS_NULL ON;
  SET QUOTED_IDENTIFIER ON;
          

  این تنظیمات تضمین می‌کنند که نتایج کوئری همیشه به صورت قطعی و قابل پیش‌بینی محاسبه شوند.

• ایندکس کلاستر منحصر به فرد: اولین ایندکسی که روی View ایندکس شده ایجاد می‌شود، حتماً باید یک ایندکس کلاستر منحصر به فرد (Unique Clustered Index) باشد. این ایندکس است که باعث ذخیره فیزیکی داده‌های View می‌شود.
• نسخه SQL Server: استفاده خودکار از Views ایندکس شده توسط بهینه‌ساز کوئری (Query Optimizer) نیازمند نسخه Enterprise یا بالاتر SQL Server است. در نسخه‌های Standard یا Web، برای استفاده از Views ایندکس شده، باید از هینت WITH (NOEXPAND) در کوئری خود استفاده کنید.

ایجاد یک View ایندکس شده

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

ابتدا، تنظیمات نشست را همانطور که گفته شد، برقرار می‌کنیم:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

سپس، اگر View مورد نظر یا جداول پایه از قبل وجود دارند، آن‌ها را حذف می‌کنیم تا از تداخل جلوگیری شود:

IF OBJECT_ID('dbo.v_SalesSummary', 'V') IS NOT NULL
    DROP VIEW dbo.v_SalesSummary;
GO

IF OBJECT_ID('dbo.Sales', 'U') IS NOT NULL
    DROP TABLE dbo.Sales;
GO

IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL
    DROP TABLE dbo.Products;
GO

حالا جداول پایه Products و Sales را ایجاد می‌کنیم:

CREATE TABLE dbo.Products
(
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50)
);
GO

CREATE TABLE dbo.Sales
(
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleDate DATE,
    Quantity INT,
    Price DECIMAL(10, 2)
);
GO

در مرحله بعد، کمی داده نمونه را به جداول Products و Sales اضافه می‌کنیم:

INSERT INTO dbo.Products (ProductID, ProductName, Category) VALUES
(1, 'Laptop', 'Electronics'),
(2, 'Mouse', 'Electronics'),
(3, 'Keyboard', 'Electronics'),
(4, 'Desk', 'Furniture'),
(5, 'Chair', 'Furniture');
GO

INSERT INTO dbo.Sales (SaleID, ProductID, SaleDate, Quantity, Price) VALUES
(1, 1, '2023-01-01', 2, 1200.00),
(2, 2, '2023-01-01', 5, 25.00),
(3, 1, '2023-01-02', 1, 1150.00),
(4, 3, '2023-01-02', 3, 75.00),
(5, 4, '2023-01-03', 1, 300.00),
(6, 2, '2023-01-03', 10, 22.00);
GO

اکنون، View v_SalesSummary را با استفاده از WITH SCHEMABINDING ایجاد می‌کنیم. این View مجموع فروش هر محصول در هر دسته را محاسبه می‌کند:

CREATE VIEW dbo.v_SalesSummary
WITH SCHEMABINDING
AS
SELECT
    P.Category,
    P.ProductName,
    SUM(S.Quantity * S.Price) AS TotalSales,
    COUNT_BIG(*) AS NumberOfSales
FROM
    dbo.Sales AS S
INNER JOIN
    dbo.Products AS P ON S.ProductID = P.ProductID
GROUP BY
    P.Category,
    P.ProductName;
GO

و در نهایت، مهم‌ترین مرحله: ایجاد یک ایندکس کلاستر منحصر به فرد بر روی View. این کار باعث می‌شود که View ما به یک View ایندکس شده تبدیل شود و داده‌ها به صورت فیزیکی ذخیره شوند:

CREATE UNIQUE CLUSTERED INDEX IX_v_SalesSummary_Category_ProductName
ON dbo.v_SalesSummary (Category, ProductName);
GO

شما می‌توانید ایندکس‌های غیرکلاستر اضافی را نیز روی View ایندکس شده خود ایجاد کنید تا عملکرد کوئری‌های خاصی را بیشتر بهبود بخشید. به عنوان مثال:

-- Optional: Create a non-clustered index on TotalSales
CREATE NONCLUSTERED INDEX IX_v_SalesSummary_TotalSales
ON dbo.v_SalesSummary (TotalSales DESC);
GO

تایید View ایندکس شده

برای اطمینان از اینکه View شما به درستی ایندکس شده است، می‌توانید از چندین روش استفاده کنید:

1. بررسی در sys.views و sys.indexes:
شما می‌توانید با کوئری گرفتن از Views سیستمی، وجود ایندکس و ویژگی‌های آن را تایید کنید. ستون is_indexed در sys.views نشان می‌دهد که آیا View ایندکس شده است یا خیر:

SELECT
    v.name AS ViewName,
    v.object_id,
    v.is_indexed
FROM
    sys.views AS v
WHERE
    v.name = 'v_SalesSummary';

SELECT
    i.name AS IndexName,
    i.type_desc AS IndexType,
    OBJECT_NAME(i.object_id) AS ObjectName
FROM
    sys.indexes AS i
WHERE
    OBJECT_NAME(i.object_id) = 'v_SalesSummary'
    AND i.index_id > 0;
GO

2. بررسی پلن اجرای کوئری (Execution Plan):
مهمترین راه برای اطمینان از استفاده بهینه‌ساز کوئری از View ایندکس شده، بررسی پلن اجرای کوئری است. یک کوئری ساده بر روی View:

SELECT Category, ProductName, TotalSales
FROM dbo.v_SalesSummary
WHERE TotalSales > 1000;
GO

در نسخه‌های Enterprise، بهینه‌ساز ممکن است به صورت خودکار از View ایندکس شده استفاده کند، حتی اگر شما به طور مستقیم از آن کوئری نگیرید یا جداول پایه را فراخوانی کنید. اما برای اطمینان و در نسخه‌های Standard یا Web، باید از هینت WITH (NOEXPAND) استفاده کنید تا صراحتاً به SQL Server بگویید که از View ایندکس شده استفاده کند:

SELECT Category, ProductName, TotalSales
FROM dbo.v_SalesSummary WITH (NOEXPAND)
WHERE TotalSales > 1000;
GO

در پلن اجرا، باید یک عملگر Clustered Index Scan یا Clustered Index Seek بر روی ایندکس View ایندکس شده (مثلاً IX_v_SalesSummary_Category_ProductName) مشاهده کنید، به جای اینکه SQL Server به جداول پایه دسترسی پیدا کند.

حذف یک View ایندکس شده

برای حذف یک View ایندکس شده، ابتدا باید تمام ایندکس‌های موجود بر روی آن را حذف کنید و سپس خود View را حذف کنید. این ترتیب ضروری است زیرا تا زمانی که ایندکس‌ها وجود دارند، View نمی‌تواند حذف شود:

IF OBJECT_ID('dbo.v_SalesSummary', 'V') IS NOT NULL
BEGIN
    -- Drop non-clustered indexes first, if any
    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_v_SalesSummary_TotalSales' AND object_id = OBJECT_ID('dbo.v_SalesSummary'))
        DROP INDEX IX_v_SalesSummary_TotalSales ON dbo.v_SalesSummary;

    -- Drop the unique clustered index
    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_v_SalesSummary_Category_ProductName' AND object_id = OBJECT_ID('dbo.v_SalesSummary'))
        DROP INDEX IX_v_SalesSummary_Category_ProductName ON dbo.v_SalesSummary;

    -- Then drop the view itself
    DROP VIEW dbo.v_SalesSummary;
END
GO

ملاحظات و بهترین شیوه‌ها

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

• سربار نگهداری (Maintenance Overhead): هر زمان که داده‌های جداول پایه تغییر می‌کنند (INSERT، UPDATE، DELETE)، View ایندکس شده نیز باید به‌روزرسانی شود. این عملیات سرباری را به تراکنش‌ها اضافه می‌کند. بنابراین، Views ایندکس شده برای جداولی که تغییرات داده‌ای کمی دارند یا جداولی که عملیات خواندن (Read) بسیار بیشتر از نوشتن (Write) است، مناسب‌تر هستند.
• تاثیر بر عملکرد نوشتن: اگر جداول پایه حجم بالایی از عملیات INSERT، UPDATE یا DELETE را تجربه می‌کنند، سربار نگهداری View ایندکس شده می‌تواند عملکرد عملیات نوشتن را کاهش دهد. این یک معاوضه (Trade-off) است: بهبود سرعت خواندن در مقابل کاهش سرعت نوشتن.
• استفاده از WITH (NOEXPAND): همانطور که قبلاً ذکر شد، در نسخه‌های Standard و Web، بهینه‌ساز کوئری به صورت خودکار از View ایندکس شده استفاده نمی‌کند. بنابراین، توسعه‌دهندگان باید به صراحت از هینت WITH (NOEXPAND) در کوئری‌های خود استفاده کنند تا از مزایای آن بهره‌مند شوند. در نسخه‌های Enterprise، بهینه‌ساز هوشمندتر است و ممکن است بدون این هینت نیز از View استفاده کند.
• کوئری‌نویسی بر روی View یا جداول پایه: شما می‌توانید کوئری‌های خود را به طور مستقیم روی View ایندکس شده اجرا کنید یا حتی کوئری‌هایی را بنویسید که مستقیماً به جداول پایه اشاره دارند اما شامل همان منطق تجمیع و جوین موجود در View باشند. در حالت دوم، بهینه‌ساز می‌تواند تشخیص دهد که View ایندکس شده می‌تواند برای پاسخ به آن کوئری مفید باشد.
• طراحی دقیق: به دلیل الزامات سختگیرانه، طراحی View ایندکس شده نیازمند دقت و برنامه‌ریزی است. مطمئن شوید که تمام پیش‌نیازها را قبل از ایجاد رعایت کرده‌اید.

نتیجه‌گیری

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