رابطه بین قیود جامعیت و بهینه سازی

مقدمه

شاید از دیدن و شنیدن عنوان مقاله تعجب کرده باشید؛ اما این یک حقیقت است. زبان SQL یک زبان اعلامی (Declarative) است. به این معنا که شما درخواست بازیابی داده های مورد نظر را تحویل RDBMS می دهید
بدون اینکه تعیین کنید چگونه این داده‌ها بدست آیند؛ در واقع شما اعلام می کنید چه می خواهید اما چگونگی آن را تعیین نمی کنید. اوست که تصمیم می گیرد از چه روش و الگوریتمی داده ها را از روی دیسک خوانده و تحویل شما دهد.
هر DBMS دارای یک نرم افزار به نام بهینه ساز (Optimizer) می‌باشد که وظیفه‌ی آن انتخاب بهترین نقشه اجرا جهت اجرای تقضا می باشد. بهینه سازی که در SQL Server بکار رفته است Cost Based می باشد. به این معنا که Plan ای که دارای کمترین هزینه باشد برای اجرا انتخاب می شود.

این بهینه ساز است که با توجه به Meta Data ها، Statistics و Catalog ای که در اختیار دارد تصمیم می گیرد که از کدام الگوریتم  جهت عمل پیوند، از کدام شاخص برای خواندن داده های استفاده کند… . شما لازم نیست جهت بازیابی داده ها خود را درگیر مباحث درونی و فیزیکی پایگاه داده ی خود کنید. کافیست که درخواست مورد نظر را با منطق و زبان SQL اعلام کرده و منتظر نتیجه خود باشید. اما این به این معنا نیست که Quey مورد نظر را به هر شکلی که می پسندید ایجاد کنید. چون همانطور که در ادامه مطرح خواهم نمود متاسفانه بهینه ساز هنوز تمام قوانین بهینه سازی را اعمال نمی کند.
 اگر بهینه ساز ها از لحاظ هوشمندی به بلوغ کافی رسیده بودند دیگر ما نیازی نداشتیم که نگران چگونگی ایجاد و خلق Query های خودمان می بودیم. فقط کافی بود به هر روشی که می دانستیم اعلام کنیم که چه می خواهیم؛ حال این روش می توانست استفاده از توابع Window باشد یا چندین Correlated Subqery پیچیده. چون در نهایت هر دو فرم با یک Execution Plan بهینه اجرا می شدند. اما این به دلایل گوناگونِ امکان پذیر نیست که دو دلیل اساسی آن یکی کامل نبودن بهینه ساز و دیگری بصرفه نبودن تولید بهترین نقشه اجرا است.

به خاطر محدود بودن زمان جهت پیدا کردن و بررسی انواع Plan های ممکنه نمی توان تمام مسیر را بررسی کرد، مخصوصا زمانی که Query ما از منطق پیچیده ای برخوردار باشد در این حالت ممکن است برای اجرای آن بیشمار مسیر و راه وجود داشته باشد که بررسی تمام آنها به صرفه نیست و می تواند زمانی بس طولانی صرف کند، زمانی که به مراتب از اجرای خود Query بیشتر است.
 در بحث بهینه سازی پرس و جو موضوعی بسیار گسترده مطرح است به نام قوانین تبدیل (Transformation Rules) که به دسته های مختلفی تقسیم بندی شده اند. این به این معنا نیست که لزوما بهینه سازها تمام این قوانین را اجرا می کنند. هستند قوانینی که هنوز اعمال نمی شوند.

یکی از قوانین تبدیل که خوشبختانه در بهینه سازی SQL Server نیز اعمال می شود، قانون تبدیل معنایی یا semantic transformation است. در واقع واژه semantic به این حقیقت اشاره می کند که در شرایط معینی این هم ارزی معنا و مفهوم پیدا می کند و این دو کوئری اولیه و ساده شده را هم ارز معنایی یا semantically equivalent می نامند (اگر مطلب برایتان گنگ است هیچ نگران نباشید در ادامه به خوبی متوجه موضوع خواهید شد).
فرض کنید دو جدول با نام های Customer و Order داریم که کدمشتری در جدول Order بایستی الزاما مقداری را به خود بگیرد که در جدول Customer وجود دارد؛ این الزام توسط تعریف یک قید کلید خارجی صورت می گیرد. حال اگر بخواهیم پرس و جویی با این مضمون تهیه کنیم: “از جدول Orders تمام سفارش هایی را انتخاب کنید که مربوط به مشتریانی باشد که در جدول Customer وجود داشته باشد” چون ما داده های مربوط به کد مشتری در جدول سفارشات را مقید کردیم، هیچ کدی خارج از داده های جدول مشتری درج نخواهد شد
به عبارت دیگر سفارش مشتری که قبلا کد آن در جدول Customers ثبت نشده است هیچ گاه پذیرفته نخواهد شد. در نتیجه هیچ ضرورتی ندارد که پیوندی بین این دو جدول جهت تطابق بین داده های دو کلید اولیه و خارجی صورت بگیرد تا داده های مطابقت یافته انتخاب شود. به بیان دیگر تمام سفارشات قطعا دارای یک مشتری معتبر می باشند.

برای ملموس شدن مطلب فوق به مثال زیر توجه کنید. ابتدا دو جدول Customes و Orders را به این صورت ایجاد می‌کنیم

CREATE TABLE Customers
(
custid INT IDENTITY
PRIMARY KEY,
custname VARCHAR(5) NOT NULL
);
GO
CREATE TABLE Orders
(
orderid INT IDENTITY
PRIMARY KEY,
custid INT NOT NULL
);
GO

همانطور که ملاحظه می کنید هنوز قید FK را روی ستون custid جدول Orders ایجاد نکردیم تا داده های این ستون را مقید کنیم.
حال می خواهیم سفارشاتی از جدول Orders را بازیابی کنیم که مشتریان آن سفارشات در جدول Customers نیز وجود داشته باشند، کد به زبان SQL:

SELECT *
FROM Orders AS O
WHERE EXISTS
(SELECT *
FROM Customers AS C
WHERE C.custid = O.custid);

همانطور که مشاهده می شود چون هیچگونه قیدی وجود ندارد که تضمین کند که تنها سفارشات مشتریان معتبر که از قبل در جدول Customers ثبت شده اند مجاز به دریافت هستند، DBMS ناگزیر به ایجاد پیوند بین این دو جدول جهت استخراج سطرهای تطابق یافته شده است.
حال با کد DDL زیر یک قید FK ایجاد می کنیم.
ALTER TABLE Orders ADD CONSTRAINT FK_custid FOREIGN KEY (custid) REFERENCES Customers (custid);

سپس مجدد تقاضای مورد نظر را اجرا می‌کنیم یعنی این:

SELECT *
FROM Orders AS O
WHERE EXISTS
(SELECT *
FROM Customers AS C
WHERE C.custid = O.custid);

ملاحظه کنید؛ این بار دیگر خبری از جدول Customers در Plan اجرایی نیست:
در واقع Query مذکور با Query زیر از نظر معنایی در صورتی که قید مورد نظر در پایگاه داده پابرجا باشد هم ارز خواهد بود:

--Semantically Equivalent:
SELECT *
FROM Orders AS O

نکته‌ای که شایان ذکر است مربوط به غیر فعال کردن قید می باشد. فرض کنید تصمیم گرفته اید این قید را بطور موقت غیر فعال کنید تا مجاز به درج هر گونه مقداری باشید؛ با کد DDL زیر اینکار صورت می گیرد:

ALTER TABLE Orders
NOCHECK CONSTRAINT FK_custid;

مجدد تقاضا را اجرا کنید؛ Plan چگونه است؟ آیا مشابه زمانی ایست که قید هنوز تعریف نشده بود؟

بله؛ به دلیل Disable شدن قید دیگر هیچ گونه گارانتی جهت جامع بودن داده های custid جدول Orders وجود ندارد (چون معلوم نیست بعد از غیر فعال کردن این قید چه داده هایی درج شده اند)؛ بنابراین DBMS ملزم به بررسی می شود. حتی اگر داده های جدول Orders دست خوش تغییر قرار نگرفته باشند و طبیعتا هیچ گونه نقض قیدی هم صورت نگرفته است باز داده های ما مورد اعتماد نیستند.
قیدکلید خارجی ویژگی دارد به نام is trusted به معنای قابل اعتماد بودن. زمانی که قید را از حال CHECK خارجی میکنید و به DBMS اجازه می دهید که هر مقدار دلخواهی در این ستون درج یا بروزرسانی شود؛ در این حالت دیگر داده های این جدول قابل اعتماد نیست.
توسط Query زیر می توان این موضوع را بررسی نمود، مقدار ۱ به معنای عدم اعتماد و مقدار ۰ به معنای اعتماد است.

SELECT name, is_not_trusted
FROM sys.foreign_keys

حالا سعی کنید با کد زیر مجدد آن قید را فعال کنید تا مانع از درج داده های غیر معتبر شود:

ALTER TABLE Orders
CHECK CONSTRAINT FK_custid;

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

ALTER TABLE Orders
WITH CHECK
CHECK CONSTRAINT FK_custid;

اگر خصیصه is not trusted مربوط به قید را بررسی کنید این بار با رقم ۰ به معنای False مواجه خواهید شد و همچنین با یک Plan ساده نیز روبرو خواهید بود.

به عنوان جمع بندی

این قانونِ تبدیل معنایی تنها مربوط به قیود Referential Integrity یعنی FK نمی باشد. اما با بررسی های که انجام دادم در مورد CHECK این موضوع در بهینه ساز منظور اعمال نشده است. و ضمنا تا جایی که میدانم این ساده سازی معنایی تنها مربوط به قیود اعلامی یعنی CONSTRAINT ها میشود نه روش های رویه ای مثل Trigger.