SQL

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

بهینه ساز پرس و جوی سرویس دهنده SQL می‌تواند برای اجرای پیوندها از میان 3 استراتژی یکی را انتخاب کند: پیوند حلقه‌ای تودرتو (nested-loop)، پیوند ادغامی (merge) و پیوند hash، در این مقاله به شرح دو استراتژی اول یعنی حلقه‌ای تودرتو و ادغامی می‌پردازیم. در هر روش، جدولهایی را که می‌خواهید پیوند دهید (یا زیر مجموعه‌هایی از آنها که با شرط WHERE محدود کرده‌اید) ورودی‌های پیوند هستند. اگر پرس و جوی شما علاوه بر پیوند، شرط WHERE را نیز شامل شود، سرویس دهنده SQL ممکن است قبل از یافتن سطرهای موردنظر در دومین جدول شرط WHERE را بکار ببرد. بعنوان مثال پرس و جوی 1 از بانک اطلاعاتی Northwind را در نظر بگیرید.

-- Query 1:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
    ON e.EmployeeID = o.EmployeeID

این پیوند تمام سفارشات را از جدول سفارشها باز می‌گرداند و همچنین برای هر یک از آنها، FirstName، LastName کارمندی را که به آن OrderID و OrderDate مربوط می‌شود را نیز باز می‌گرداند.

در پرس و جوی 1 سرویس دهنده SQL تمام سطرها را در جدول کارمندان و سفارشات امتحان می‌کند و ورودی‌های پیوند تمام سطرهای جدولها هستند. بهرحال، طبق آنچه که پرس و جوی 2 نشان می‌دهد، اگر شما عبارت SELECT را با دو شرط WHERE تعریف کنید، ورودیهای پیوند دیگر تمام سطرهای جدولها نیستند.

-- Query 2:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
    ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < '1996-12-01' AND LastName < 'D'

ورودیهای پیوند در پرس و جوی 2، بسیار کوچکتر از پرس و جوی 1 هستند. به جای پیوند 9 سطر از جدول کارمندان با 830 سطر از جدول سفارشات، سرویس دهنده SQL باید تنها 2 سطر از جدول کارمندان را با 121 سطر از جدول سفارشات پیوند دهد. بهینه ساز پرس و جو با وجود تعداد محدودی از سطرهای ورودی غالباً استراتژی پیوند متفاوتی را در مقایسه با زمانی که ورودیهای پیوند بیشتر هستند برمی‌گزیند و همچنین ممکن است جدولها را به سبک متفاوتی پیوند دهد. به همان اندازه که تصمیمات استراتژی بهینه ساز مهم هستند اندازه جدولها نیز حائز اهمیت است.

حلقه‌های تودرتو

حتی اگر پرس وجوی شما بیش از 2 جدول را پیوند دهد، سرویس دهنده SQL عمل پیوند را از طریق پیوند تنها دو ورودی در یک زمان اجرا می‌کند و هر پیوند در یک پرس و جو ممکن است از استراتژی پیوند متفاوتی استفاده کند. آسانترین نوع پیوند – و نوعی از پیوند که اکثر افراد هنگام عملیات پیوند بدان فکر می‌کنند – پیوند حلقه تودرتوست  می‌توانید تصور کنید که سرویس دهنده SQL روی دو ورودی عمل می‌کند حتی اگر آنها آرایه‌هایی در یک زبان پیشرفته مانند C یا Basic باشند. سرویس دهنده SQL هر سطر یک ورودی را با تمام سطرهای ورودی دیگر مقایسه می‌کند تا تطابق بین سطرها را بیابد.

پرس و جوی 1 سعی دریافتن سطرهایی دارد که با ستون EmployeeID تطبیق داشته باشد. بنابراین با استراتژی پیوند حلقه تودرتو، سرویس دهنده SQL باید تمام مقادیر EmployeeID در یک جدول را با تمام مقادیر EmployeeID در جدول دیگر مقایسه کند.

بدترین قسمت سناریو برای یک پیوند حلقه تودرتو زمانی است که هیچ شاخصی نتواند به سرویس دهنده SQL دریافتن سطرهای منطبق در بین ورودیها و همچنین یافتن سطرهایی که در هر شرط WHERE صدق می‌کند، کمک نماید. در ین حالت، ورودیها کل سطرهای جدولها هستند. بهینه ساز پرس و جو جدولی را بعنوان جدول خارجی انتخاب می‌کند و در ابتدا به سطرهای آن دستیابی می‌یابد. بیایید فرض کنیم که جدول خارجی دارای P1 صفحه و R1 سطر باشد. دومین جدول که جدول داخلی است P2 صفحه دارد. سرویس دهنده SQL باید تمام صفحات را از جدول خارجی بخواند؛ و برای هر سطر تعریف شده در هر صفحه باید تمام صفحات را از جدول داخلی بخواند. برای یافتن تعداد صفحاتی که سرویس دهنده SQL برای خواندن و ارائه نتیجه نیاز دارد، می‌توانید از فرمول زیر استفاده کنید:

P1 + R1 * P2

حتی اگر جدولها نسبتاً کوچک باشند، عدد حاصله از صفحات خوانده شده به سرعت بزرگ می‌شود. در مورد یک جدول خارجی با تنها 200 صفحه و 4000 سطر (برای مثال 20 سطر برای هر صفحه) و یک جدول داخلی با 100 صفحه، نتیجه رقمی کاملاً بزرگ است. جدولهایی با 100 یا 200 صفحه جدولهایی نیستند که بطور غیرمعمول بزرگ باشند، اما برای پردازش پیوند در صورتی که جدولها شاخصهای مفیدی نداشته باشند، سرویس دهنده SQL نیاز به دستیابی به بیش از 400,000 صفحه خواهد داشت.

شاخصها می‌توانند در بهبود عملکرد یک پیوند حلقه تودرتو به طرق مختلف نقش داشته باشند. بزرگترین حسن این شاخصها اغلب زمانی است که شما یک شاخص کلاستری روی ستون پیوند یکی از جدولها داشته باشید. وجود یک شاخص کلاستری روی ستون پیوند غالباً مشخص می‌کند که سرویس دهنده SQL چه جدولی را بعنوان جدول داخلی انتخاب می‌کند. اگر جدول داخلی دارای شاخص کلاستری باشد، سرویس دهنده SQL نیاز به جستجو در میان کل سطرهای آن جدول را ندارد. شاخص کلاستری، سرویس دهنده SQL را مستقیماً به سوی سطرهایی در جدول داخلی هدایت می‌کند که دارای مقدار ستون پیوند بوده که سطرهای جاری در جدول خارجی را تطبیق می‌دهد. بنابراین در آن فرمول، به جای عبارت R1 ´ P2 که نشان می‌دهد سرویس دهنده SQL به تمام P2 صفحه دستیابی پیدا می‌کند، می‌توانید P2 را با دستیابی 2 یا 3 صفحه‌ای جایگزین کنید بسته به اینکه شاخص کلاستری چند Level دارد. بنابراین در مورد مثالی با 200 صفحه و 400 سطر در جدول خارجی و 100 صفحه در جدول داخلی نتیجه 3*4000+200 یا 200،12 صفحه خوانده شده است – یک پیشرفت بزرگ بالای 400,000 صفحه می‌باشد.

هنوز هم آن 4000 سطر در محاسبه نتیجه را بزرگتر از حد انتظار خواهد کرد. در این حالت، تمامی 4000 سطر در جدول خارجی بخشی از نتیجه هستند که موجب 4000 بار رجوع به جدول داخلی می‌شود. یک راه دیگر برای کاهش تعداد صفحات بدست آمده کاهش اندازه ورودیهای خارجی است. علاوه بر کنترل شاخص کلاستری روی ستون پیوند، ابتدا بهینه ساز سعی می‌کند جدولها را با ورودیهای کوچکتر پیوند دهد. در پرس و جوی 1، جدول کارمندان دارای یک شاخص کلاستری روی ستون پیوند یعنی EmployeeID هستند اما این جدول نیز بطور نمایشی کوچکتر از جدول سفارشات است. جدول کارمندان تنها 9 سطر دارد و جدول سفارشات 830 سطر. در پرس و جوی 1، اگر بهینه ساز یک پیوند حلقه تودرتو را انتخاب کند، از جدول کوچکتر کارمندان به عنوان ورودی خارجی استفاده می‌کند بگونه‌ای که تنها 9 بار به جدول سفارشات رجوع خواهد داشت.

اگر شما دارای شرط WHERE باشید که جدول خارجی را شامل می‌شود، تعداد سطرهای تعریف شده پایین می‌آید و سرویس دهنده SQL کمتر نیاز به مراجعه به جدول داخلی را خواهد داشت. اگر پرس و جوی 1 را طوری تغییر دهید که شامل یک شرط WHERE در جدول سفارشات باشد، همانگونه که در پرس و جوی 3 نشان داده شده، طرح پرس و جو تغییر می‌کند.

-- Query 3:
SELECT LastName, FirstName, OrderID, OrderDate
FROM Employees e JOIN Orders o
    ON e.EmployeeID = o.EmployeeID
WHERE OrderDate < '1996-12-01'

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

FIGURE 1: Query plan for Query 3
|..Nested Loops(Inner Join, OUTER REFERENCES:([o].[EmployeeID]))
    |..Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]
     AS [o]),
         WHERE:([o].[OrderDate] < 'Dec 1 1996 12:00AM'))
    |..Clusterd Index
Seek(OBJECT:([northwind].[dbo].[Employees].[PK_Employees] AS [e]),
         SEEK:([e].[EmployeeID]=[o].[EmployeeID]) ORDERED FORWARD)

شکل 1 طرح پرس و جو را در مورد پرس و جوی 3 نشان می‌دهد. اولین خط این طرح نوع پیوند (حلقه تودرتو) را نشان می‌دهد و مشخص می‌کند که جدول خارجی ستون EmployeeID را ارجاع خواهد داد.

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

چنانچه قبلاً ذکر شد شاخص در ستون OrderDate چیز خوبی است اما عملکرد پرس و جو را تقریباً به یک شاخص کلاستری روی ستون پیوند بهبود نخواهد بخشید. یک شاخص مفید در پارامتر جستجو در جدول خارجی بدین معناست که سرویس دهنده SQL نباید به تمام صفحات جدول خارجی رجوع نماید، بنابراین،‌ مقدار P1 کاهش می‌یابد. باتوجه به اینکه مقدار P1 نسبت به مقدار دومین عبارت،‌ P2 R1، کوچکتر است، بنابراین کاهش مقدار P1 فقط موجب بهبودی کمتر عملکرد می‌گردد. شاخص جدول خارجی تعداد دفعاتی که سرویس دهنده SQL باید به جدول داخلی رجوع کند را کاهش نمی‌دهد زیرا سرویس دهنده SQL هنوز باید بازای هر سطر تعریف شده در جدول خارجی به جدول داخلی رجوع کند. شما می‌توانید انتخاب بهینه‌ساز از پیوند حلقه تودرتو را اینگونه تعمیم دهید: در صورتی که یکی از ورودیهای پیوند بسیار کوچکتر از دیگری و ورودی بزرگتر دارای یک شاخص کلاستری روی ستون پیوند باشد، بهینه ساز اغلب پیوند حلقه تودرتو را برمی‌گزیند.

ادغام

در پیوند حلقه تودرتو، شاخص ستون پیوند در مورد جدول خارجی بی‌فایده است. بهرحال، زمانی که شما پرس و جوها و جداول را تنظیم می‌کنید، ممکن است همیشه ندانید که کدام جدول داخلی و کدام خارجی است، بنابراین باید در هر دو جدول ورودی شاخصهای کلاستری را روی ستونهای پیوند ایجاد کرد. زمانی که هر دو ورودیهای پیوند روی ستون پیوند مرتب سازی می‌شوند، سرویس دهنده SQL می‌تواند از پیوند ادغامی استفاده کند، درست مانند موردی که هر دو جدول دارای شاخصهای کلاستری روی ستون پیوند باشند.پیوند ادغامی را می‌توان همچون ترکیب دویست مرتب سازی شده از مقادیر تصور کرد. فرض کنید دارای دو سری از اطلاعات پیمانکاری هستید. یک سری شامل قراردادهای مهم می‌باشد که هر پیمانکاری آن را امضا کرده است و دومین سری توصیف هریک از پروژه‌هایی است که پیمانکار بر روی آن کار می‌کند بنابراین، شما اساساً نیاز به یک پل ارتباطی میان این دو سری اطلاعات دارید.پرس و جوی 1 را در نظر بگیرید: اگر جدول کارمندان و جدول سفارشات در ستون EmployeeID شاخصهای کلاستری داشته باشند، سرویس دهنده SQL می‌تواند پیوند ادغامی را اجرا کند. شبه کد مربوطه در مورد اجرای ادغامی سرویس دهنده SQL چیزی شبیه این عبارات خواهد بود:

GET one Orders row and one Employees row
DO (until one input is empty);
      IF EmployeeID values are equal
           Return values from both rows
           GET next Orders row
      ELSE IF Orders.EmployeeID <> Employees.EmployeeID
           GET next Employees row
      ELSE GET next Orders row

بهینه ساز پرس و جو معمولاً استراتژی پیوند ادغامی را زمانی انتخاب می کند که هر دو ورودیها قبلاً در ستون پیوند مرتب شده باشند. اگر هر دو ورودی قبلاً مرتب شده باشند، در صورتی که پیوند یک به چند باشد استفاده از I/O کمتری برای پردازش پیوند ادغامی ضروریست. پیوند ادغامی چند به چند (M:N) به جای کنار گذاشتن سطرها که معمولاً انجام می‌دهد، آنها را در یک جدول موقتی ذخیره می‌کند. اگر داده‌ها شامل مقادیر تکراری از هر دو ورودی باشند، هنگامی که سرویس دهنده SQL هر مقدار تکراری را از اولین ورودی پردازش می‌کند، دومین ورودی باید به ابتدای مقادیر تکراری در جدول موقت بازگردد. بهرحال، در اکثر موارد، سرویس دهنده SQL از پیوند ادغامی استفاده نخواهد کرد مگر اینکه حداقل یکی از ستونهای پیوند Unique باشد.در اینجا مثالی از پیوند دو جدول یکی با شاخص و دیگری بدون شاخص Unique آورده شده است.

LISTING 1: Joins Two Tables With and Without Unique Index -- Copy the two tables SELECT * INTO o1 FROM orders SELECT * INTO od1 FROM [order details] -- Create the indexes. CREATE CLUSTERED INDEX orders_index ON o1(orderID) CREATE CLUSTERED INDEX OD_index ON od1(orderID) -- Look at the query plan for the query. SELECT * FROM o1 JOIN od1 ON o1.orderID = od1.orderID -- Now recreate the clustered index on o1 as unique. CREATE UNIQUE CLUSTERED INDEX orders_index ON o1(orderID) WITH DROP_EXISTING -- Now Look at the query plan for the query. SELECT * FROM o1 JOIN od1 ON o1.orderID = od1.orderID

LISTING 2: Modified Listing 1 SELECT * INTO o2 FROM orders SELECT * INTO od2 FROM [order details] CREATE UNIQUE CLUSTERED INDEX orders_index ON o2(orderID) CREATE INDEX OD_index ON od2(orderID) -- Check the query plan to see the sort operation before -- the merge join: SELECT * FROM o2 JOIN od2 ON o2.orderID = od2.orderID

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

جدولهایتان را بشناسید

معمولاً، بهینه ساز پرس و جو تعیین می‌کند که سرویس دهنده SQL چه نوع پیوندی را استفاده خواهد کرد. شما می‌توانید پرس و جوی پیوند خود را نوشته و انتخاب استراتژی را به عهده SQL بگذارید. بهرحال، دانستن نحوه اجرای پیوندها در سرویس دهنده SQL می‌تواند انتخاب شما در مورد شاخصهای مفیدتر کمک کند. بهینه ساز معمولاً در صورتی که یکی از ورودیهای پیوند در مقایسه با ورودی دیگری کوچکتر و ورودی بزرگتر دارای یک شاخص کلاستری روی ستون پیوند باشد، از پیوند حلقه تودرتو استفاده می‌کند. اگر هر دو ورودی در ستون پیوند مرتب شده باشند و بخصوص یکی از ورودیها دارای شاخص کلاستری unique باشد نوع پیوند به احتمال زیاد ادغامی خواهد بود.

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