خانه SQL Server رابطه بین قیود جامعیت و بهینه سازی SQL Server مبانی SQL Server نوشته شده توسط: محمد سلیم آبادی تاریخ انتشار: ۱۲ آبان ۱۳۹۳ آخرین بروزرسانی: ۲۱ شهریور ۱۴۰۲ زمان مطالعه: 18 دقیقه ۵ (۱) مقدمه شاید از دیدن و شنیدن عنوان مقاله تعجب کرده باشید؛ اما این یک حقیقت است. زبان 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. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش معرفی نویسنده مقالات 4 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده محمد سلیم آبادی معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 2.190.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ محمدحسین فخرآوری ۲۵ / ۰۴ / ۹۵ - ۰۹:۳۲ عالی پاسخ به دیدگاه مجتبی ۰۸ / ۰۶ / ۹۴ - ۱۰:۱۲ لطفا مطالب پیچیده را در قالب ویدیو آموزشی توضیح دهید با فیلم آمزشی فهم آن راحتر است تا مقاله پاسخ به دیدگاه مسعود طاهری ۰۸ / ۰۶ / ۹۴ - ۱۰:۲۷ مجتبی جان سلام واقعیت این است که به علت مشغله کاری امکان تولید فیلم های آموزشی مقدور نیست اما خوشحال می شوم شما همین موضوع را فیلم آموزشی کنید تا با نام خودتون در سایت ارائه دهیم. اگر تمایل به همکاری داشتید اعلام کنید موفق باشید پاسخ به دیدگاه