آموزش جامع SQL Server (جلسه 20: ایندکس‌ها - بخش ۲)
1398/03/26 15:25 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه 20: ایندکس‌ها - بخش ۲)

در جلسه قبل وارد مبحث ایندکس‌ها (Index) شدیم و راجه به انواع ایندکس‌ها، چگونگی ایجاد، تغییر نام، فعال و غیر فعال کردن آن‌ها صحبت کردیم. در این جلسه به توضیح بقیه قسمت‌های این مبحث می‌پردازیم و بحث ایندکس‌ها را به پایان می‌رسانیم. با ما همراه باشید!

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

دستور DROP INDEX در SQL Server

دستور DROP INDEX یک یا چند ایندکس را از پایگاه داده فعلی حذف می‌کند. در زیر syntax دستور DROP INDEX را مشاهده می‌کند:

DROP INDEX [IF EXISTS] index_name
ON table_name;

در این syntax:

  • ابتدا، نام ایندکسی که می‌خواهید حذف شود پس از دستور DROP INDEX می‌آید.

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

حذف یک ایندکس ناموجود باعث بروز ارور می‌شود. به هر حال، می‌توانید از آپشن IF EXISTS برای حذف شرطی ایندکس و پرهیز از بروز ارور استفاده کنید.

توجه کنید که آپشن IF EXISTS از زمان انتشار SQL Server 2016 (13.x) در دسترس کاربران قرار گرفته است.

دستور DROP INDEX ایندکس‌های ایجاد شده توسط ویژگی‌های PRIMARY KEY یا UNIQUE را حذف نمی‌کند. برای حذف ایندکس‌های مرتبط با این ویژگی‌ها، می‌توانید از دستور ALTER TABLE DROP CONSTRAINT استفاده کنید.

برای حذف چند ایندکس از یک یا چند جدول به صورت هم‌زمان، می‌توانید لیستی از نام ایندکس‌های متناظر با نام‌های جدول (که با ویرگول از هم جدا شده‌اند) را پس از دستور DROP INDEX قرار دهید که در query زیر مشاهده می‌کنید:

DROP INDEX [IF EXISTS] 
    index_name1 ON table_name1,
    index_name2 ON table_name2,
    ...;

مثال‌های دستور DROP INDEX در SQL Server

از جدول Sales.customers از پایگاه داده نمونه Bikestores برای مثال‌ها استفاده می‌کنیم.

جدول Sales.customers

تصویر زیر ایندکس‌های جدول sales.customers را نشان می‌دهد:

یندکس‌های جدول sales.customers

الف) مثال استفاده از دستور DROP INDEX برای حذف یک ایندکس

این کد از دستور DROP INDEX برای حذف ایندکس ix_cust_email از جدول sales.customers استفاده می‌کند:

DROP INDEX IF EXISTS ix_cust_email
ON sales.customers;

اگر ایندکس‌های جدول Sales.customers را بررسی کنید، متوجه خواهید شد که ایندکس ix_cust_email حذف شده است.

 ایندکس ix_cust_email حذف شده است

ب) مثال استفاده از دستور DROP INDEX برای حذف چند ایندکس

مثال زیر از دستور DROP INDEX برای حذف ایندکس‌های ix_cust_city و ix_cust_fullname از جدول sales.customers استفاده می‌کند:

DROP INDEX 
    ix_cust_city ON sales.customers,
    ix_cust_fullname ON sales.customers;

حالا جدول Sales.customers هیچ ایندکس غیر خوشه‌ای ندارد:

حالا جدول Sales.customers هیچ ایندکس غیر خوشه‌ای ندارد

در نتیجه، چگونگی استفاده از دستور DROP INDEX برای حذف یک یا چند ایندکس از جداول را نیز آموختید.

ایندکس‌های دارای ستون در SQL Server

برای توضیحات این قسمت از جدول sales.customers موجود در پایگاه داده نمونه Bikestores استفاده می‌کنیم.

جدول sales.customers

کد زیر یک ایندکس یکتا برای ستون email ایجاد می‌کند:

CREATE UNIQUE INDEX ix_cust_email 
ON sales.customers(email);

این کد مشتری‌ای را می‌یابد که ایمیلش ‘aide.franco@msn.com’ باشد:

SELECT    
    customer_id, 
    email
FROM    
    sales.customers
WHERE 
    email = 'aide.franco@msn.com';

اگر طرح اجرای تخمینی برای query بالا را نمایش دهید، متوجه خواهید شد که بهینه‌ساز query از عملیات جستجوی ایندکس در ایندکس غیر خوشه‌ای استفاده می‌کند.

طرح اجرای تخمینی

به هر حال، مثال زیر را در نظر بگیرید:

SELECT    
 first_name,
 last_name, 
 email
FROM    
 sales.customers
WHERE email = 'aide.franco@msn.com';

طرح اجرایی آن به این شکل است:

طرح اجرای تخمینی

در این طرح اجرایی:

ابتدا، بهینه‌ساز query از روش جستجوی ایندکس در ایندکس غیر خوشه‌ای ix_cust_email برای یافتن email و customer_id استفاده می‌کند.

از روش جستجوی ایندکس در ایندکس غیر خوشه‌ای ix_cust_email برای یافتن email و customer_id استفاده می‌کند.

دوم، بهینه‌ساز query از key lookup در ایندکس خوشه‌ای از جدول Salec.customers برای یافتن نام و نام خانوادگی مشتری با استفاده از customer id استفاده می‌کند.

از key lookup در ایندکس خوشه‌ای از جدول Salec.customers برای یافتن نام و نام خانوادگی مشتری با استفاده از customer id استفاده می‌کند

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

همان‌طور که مشاهده می‌کنید هزینه برای key lookup حدود ۵۰ درصد از query است، که خب خیلی گران است.

برای کمک به کاهش این هزینه key lookup، سیستم SQL Server به شما اجازه می‌دهد تا با به کارگیری ستون‌های غیر کلید، کارایی یک ایندکس غیر خوشه‌ای را افزایش دهید.

با به کارگیری ستون‌های غیر کلید در ایندکس‌های غیر خوشه‌ای، می‌توانید ایندکس‌های غیر خوشه‌ای ایجاد کنید که queryهای بیشتری را پوشش دهند.

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

ابتدا، ایندکس ix_cust_email را از جدول sales.customers حذف کنید:

DROP INDEX ix_cust_email 
ON sales.customers;

سپس، یک ایندکس جدید بهنام ix_cust_email_inc ایجاد کنید که شامل دو ستون نام و نام خانوادگی باشد:

CREATE UNIQUE INDEX ix_cust_email_inc
ON sales.customers(email)
INCLUDE(first_name,last_name);

حالا، بهینه‌ساز query به تنهایی از ایندکس غیر خوشه‌ای برای بازگرداندند داده‌های درخواستی از query استفاده می‌کند:

طرح اجرای تخمینی

یک ایندکس دارای ستون می‌تواند به شدت عملکرد query را بهبود بخشد، چون تمام ستون‌ها در query، در داخل ایندکس درج شده‌اند.

بهینه‌ساز Query می‌تواند تمام مقادیر ستون‌ها را در داخل ایندکس بیابد، بدون اینکه به جدول یا ایندکس خوشه‌ای دسترسی یابد و این امر باعث کاهش عملیات‌های disk I/O می‌شود.

Syntax برای ایجاد یک ایندکس با ستون

کد زیر syntax برای ایجاد یک ایندکس غیر خوشه‌ای با ستون را نشان می‌دهد:

CREATE [UNIQUE] INDEX index_name
ON table_name(key_column_list)
INCLUDE(included_column_list);

در این syntax:

  • اول، نام ایندکس پس از دستور CREATE INDEX مشخص شده است. اگر ایندکس یکتا باشد، باید کلیدواژه UNIQUE را اضافه کنید.

  • دوم، نام جدولی و لیست ستون‌های کلیدی باری ایندکس پس از دستور ON مشخص شده‌اند.

  • سوم، لیستی از ستون‌های مجزا شده با ویرگول در دستور INCLUDE مشخص شده است.

در نتیجه، چگونگی استفاده از ایندکس‌های دارای ستون برای بهبود عملکرد quuery را نیز آموختید.

ایندکس‌های فیلتر شده در SQL Server

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

  • ابتدا، حافظه اضافی برای ذخیره کپی داده‌های ستون‌های کلیدی ایندکس نیاز دارند.

  • سپس، وقتی ردیف‌ها را در جدول وارد، به روز رسانی یا از آن حذف می‌کنید، SQL Server نیاز به به روز رسانی ایندکس غیر خوشه‌ای مرتبط دارد.

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

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

Syntax زیر چگونگی ایجاد یک ایندکس فیلتر شده را نشان می‌دهد:

CREATE INDEX index_name
ON table_name(column_list)
WHERE predicate;

 در این syntax:

  • ابتدا، نام ایندکس فیلتر شده پس از دستور CREATE INDEX آمده است.

  • دوم، نام جدول با لیستی از ستون‌های کلیدی که در ایندکس درج خواهند شد آمده‌اند.

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

مثال ایندکس فیلتر شده در SQL Server

از جدول sales.customers از پایگاه داده نمونه Bikestores برای مثال‌ استفاده می‌کنیم.

جدول sales.customers

جدول sales.customers دارای ستون phone است که حاوی مقادیر NULL بسیاری است:

SELECT 
    SUM(CASE
            WHEN phone IS NULL
            THEN 1
            ELSE 0
        END) AS [Has Phone], 
    SUM(CASE
            WHEN phone IS NULL
            THEN 0
            ELSE 1
        END) AS [No Phone]
FROM 
    sales.customers;
Has Phone   No Phone
----------- -----------
1267        178
 
(1 row affected)

این ستون phone یک کاندید خوب برای ایندکس فیلتر شده است.

این کد یک ایندکس فیلتر شده برای ستون phone از جدول Sales.customers است:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
WHERE phone IS NOT NULL;

Query زیر مشتری که شماره تلفن آن 3309-363 (281) است را می‌یابد:

SELECT    
    first_name,
    last_name, 
    phone
FROM    
    sales.customers
WHERE phone = '(281) 363-3309';

 طرح اجرای تخمینی آن به این شکل است:

طرح اجرای تخمینی

حالا بهینه‌ساز query می‌تواند از مزیت ایندکس فیلتر شده ix_cust_phone برای جستجو استفاده کند.

توجه کنید که برای بهبود key lookup، می‌توانید از یک ایندکس دارای ستون استفاده کنید که شامل دو ستون first_name و last_name باشد:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
INCLUDE (first_name, last_name)
WHERE phone IS NOT NULL;

مزایای ایندکس‌های فیلتر شده

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

علاوه بر این، ایندکس‌های فیلتر شده هزینه نگهداری را کاهش می‌دهند، چون فقط بخشی از ردیف‌های داده (نه همه آن‌ها) باید وقتی داده‌های مربوط به جدول تغییر می‌کنند، به روز رسانی شوند.

در نتیجه، چگونگی استفاده از ایندکس‌های فیلتر شده برای ایجاد ایندکس‌های غیر خوشه‌ای بهینه‌سازی شده برای جداول را آموختید.

ایندکس‌ها در ستون‌های محاسبه‌ای در SQL Server

باز هم برای توضیحات این قسمت، از جدول Sales.customers استفاده می‌کنیم.

جدول Sales.customers

این query مشتری که بخش محلی آدرس ایمیل او ‘garry.esinoza’ است را می‌یابد:

SELECT    
    first_name,
    last_name,
    email
FROM    
    sales.customers
WHERE 
    SUBSTRING(email, 0, 
        CHARINDEX('@', email, 0)
    ) = 'garry.espinoza';

در تصویر زیر طرح اجرای تخمینی query بالا را مشاهده می‌کنید:

طرح اجرای تخمینی

همان‌طور که به وضوح در خروجی مشخص است، بهینه‌ساز query باید کل ایندکس خوشه‌ای را اسکن کند تا بتواند مشتری را بیابد، که این روش به صرفه‌ای نیست.

اگر با Oracle یا PostgerSQL کار کرده باشید، پس احتمالاً می‌دانید که Oracle از ایندکس‌های تابع محور پشتیبانی می‌کند و PostgerSQL دارای ایندکس‌های عبارت محور است.

این نوع ایندکس‌ها به شما اجازه می‌دهند تا نتیجه یک تابع یا یک عبارت را ایندکس کنید، که این امر باعث بهبود عملکرد queryهایی می‌شود، که دستور WHERE آن‌ها شامل تابع و عبارت باشد.

در SQL Server، می‌توانید از یک ایندکس در یک ستون محاسبه‌ای استفاده کنید تا به تأثیر مشابهی با یک شاخص تابع محور دست یابید:

  • ابتدا، یک ستون محاسبه‌ای بر اساس عبارت موجود در دستور WHERE ایجاد کنید.

  • سپس، یک ایندکس غیر خوشه‌ای برای ستون محاسبه‌ای ایجاد کنید.

برای مثال، برای جستجوی مشتریان بر اساس بخش‌های محلی و شخصی آدرس‌های ایمیلشان، باید این مراحل را طی کنید:

ابتدا، یک ستون محاسبه‌ای جدید به جدول sales.customers اضافه کنید:

ALTER TABLE sales.customers
ADD 
    email_local_part AS 
        SUBSTRING(email, 
            0, 
            CHARINDEX('@', email, 0)
        );

سپس، یک ایندکس در ستون email_local_part ایجاد کنید:

CREATE INDEX ix_cust_email_local_part
ON sales.customers(email_local_part);

حالا، می‌توانید از ستون email_local_part به جای عبارت داخل دستور WHERE برای یافتن مشتریان بر اساس بخش محلی و خصوصی آدرس ایمیلشان استفاده کنید:

SELECT    
    first_name,
    last_name,
    email
FROM    
    sales.customers
WHERE 
    email_local_part = 'garry.espinoza';

بهینه‌ساز query از عملیات جستجوی ایندکس در ایندکس ix_cust_email_local_part به صورتی که در تصویر زیر نمایش داده شده است، استفاده می‌کند:

طرح اجرای تخمینی

ملزومات برای ایندکس‌های ستون‌های محاسبه‌ای

برای ایجاد یک ایندکس در یک ستون محاسبه‌ای، باید ملزومات زیر را فراهم کنید:

  • توابع دخیل در عبارت ستون محاسبه‌ای باید دارای مالک (یا همان جدول) یکسان باشند.

  • عبارت ستون محاسبه‌ای باید قطعی و ثابت باشد. یعنی اینکه عبارت همیشه نتیجه یکسانی برای یک مجموعه خاص از ورودی‌ها بازمی‌گرداند.

  • ستون محاسبه‌ای باید دقیق باشد، که یعنی عبارت آن نباید شامل هیچ نوع داده FLOAT یا REAL باشد.

  • نتیجه عبارت ستون محاسبه‌ای نباید به صورت نوع‌های داده‌ای NTEXT، TEXT یا IMAGE ارزیابی شوند.

  • آپشن ANSI_NULLS در زمانی که ستون محاسبه‌ای با استفاده از دستور CREATE TABLE یا ALTER TABLE تعریف می‌شود، باید روی ON تنظیم شود.

    علاوه بر این، آپشن‌های QUOTED_IDENTIFIER، ARITHABORT، ANSI_WARNINGS، ANSI_PADDING و CONCAT_NULL_YIELDS_NULL نیز باید روی ON تنظیم شوند و NUMERIC_ROUNDABORT باید روی OFF تنظیم شود.

به این شکل، چگونگی استفاده از ایندکس‌ها در ستون‌های محاسبه‌ای برای بهبود سرعت queryهای دارای عبارت‌ها را آموختید.

خلاصه

یکی دیگر از مهم‌ترین و پر کاربرد‌ترین بخش‌های SQL Server یعنی ایندکس‌ها (Index) نیز به پایان رسید.

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

در جلسه بعدی راجع به موضوع بسیار گره گشا و پر کاربرد Stored Procedureها صحبت خواهیم کرد.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات