📞 02166056460

دستور Create Index در sql

10 / 10
از 4 کاربر
بازگشت به لیست مقالات » | یکشنبه 3 آبان 1405 در ساعت 11 و 1 دقیقه | نظرات کاربران ( 1 )
آموزش SQL

دستور CREATE INDEX در SQL چیست؟

آموزش ساخت اندیس، انتخاب ستون مناسب و بهبود سرعت جستجو در پایگاه داده
۰۱

آشنایی با دستور CREATE INDEX در SQL

دستور CREATE INDEX در SQL برای ساخت اندیس روی یک یا چند ستون جدول به کار می‌رود. اندیس به پایگاه داده کمک می‌کند هنگام جستجو، مرتب‌سازی یا اتصال جدول‌ها، داده‌های موردنیاز را سریع‌تر پیدا کند. به همین دلیل، ایجاد اندیس مناسب می‌تواند در جدول‌های بزرگ زمان اجرای بعضی پرس‌وجوها را کاهش دهد.

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

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

۵ نکته مهم درباره ساخت اندیس در SQL

۰۲

۱. ساختار پایه دستور CREATE INDEX

برای ساخت اندیس معمولی، نام اندیس، نام جدول و نام ستون را مشخص می‌کنیم. شکل کلی دستور در بسیاری از سامانه‌های SQL به این صورت است:

CREATE INDEX index_name
ON table_name (column_name);

در این الگو، index_name نامی است که برای شناسایی اندیس انتخاب می‌کنید، table_name نام جدول و column_name نام ستونی است که قرار است در اندیس قرار بگیرد. نام‌گذاری روشن، نگهداری پایگاه داده را ساده‌تر می‌کند؛ برای نمونه، نامی مانند IX_Persons_LastName می‌تواند جدول و ستون هدف را مشخص کند.

برای نمونه، اگر جدول Persons شامل ستون LastName باشد، دستور زیر اندیسی با نام PIndex می‌سازد:

CREATE INDEX PIndex
ON Persons (LastName);

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

۰۳

۲. انتخاب ستون بر اساس نوع جستجو

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

تعداد مقادیر متفاوت در ستون نیز اهمیت دارد. ستونی با مقدارهای متنوع ممکن است در برخی جستجوها انتخاب‌پذیری خوبی داشته باشد؛ ستونی که فقط چند مقدار تکراری دارد، بسته به نوع پرس‌وجو و توزیع رکوردها ممکن است برای اندیس مستقل مناسب نباشد. این نکته یک قانون مطلق نیست و باید با داده واقعی و برنامه اجرایی سنجیده شود.

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

۰۴

۳. ساخت اندیس یکتا با CREATE UNIQUE INDEX

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

شکل کلی دستور برای یک ستون چنین است:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

برای مثال، اگر مقدار ستون Email باید در جدول کاربران یکتا باشد، می‌توان چنین اندیسی تعریف کرد:

CREATE UNIQUE INDEX UQ_Users_Email
ON Users (Email);

رفتار مقدارهای تهی و جزئیات محدودیت یکتا ممکن است بین سامانه‌های پایگاه داده تفاوت داشته باشد. اگر یکتایی بخشی از قواعد اصلی داده‌های برنامه است، بررسی امکان تعریف قید UNIQUE در کنار یا به جای اندیس‌گذاری مستقیم نیز ارزش دارد. انتخاب روش به طراحی جدول و قابلیت‌های سامانه مورد استفاده بستگی دارد.

۰۵

۴. ساخت اندیس ترکیبی برای چند ستون

برای جستجوهایی که معمولاً با چند ستون انجام می‌شوند، می‌توان یک اندیس ترکیبی ساخت. نام ستون‌ها در پرانتز و به ترتیب موردنظر نوشته می‌شود. برای نمونه، اگر جستجوی اشخاص اغلب با نام خانوادگی و سپس نام انجام شود، دستور زیر یک اندیس ترکیبی می‌سازد:

CREATE INDEX PIndex
ON Persons (LastName, FirstName);

ترتیب ستون‌ها مهم است. بسیاری از موتورهای پایگاه داده می‌توانند از ابتدای ترتیب تعریف‌شده در اندیس ترکیبی استفاده کنند؛ بنابراین اندیسی با ترتیب (LastName, FirstName) لزوماً برای پرس‌وجویی که فقط بر اساس FirstName فیلتر می‌کند، همان کارایی را ندارد. پیش از ساخت اندیس، شکل شرط‌های WHERE و ترتیب مرتب‌سازی را بررسی کنید.

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

۰۶

۵. سنجش هزینه و فایده اندیس

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

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

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

🔎

فیلتر کردن

اندیس می‌تواند به جستجوی رکوردها بر اساس ستون‌های پرتکرار کمک کند.

📊

مرتب‌سازی

برخی اندیس‌ها با مرتب‌سازی موردنیاز پرس‌وجو هماهنگ هستند.

🔄

اتصال جدول‌ها

ستون‌های مرتبط در شرط اتصال می‌توانند نامزد بررسی باشند.

راهنمای انتخاب و ساخت اندیس مناسب

۰۷

بررسی کیفیت و الگوی پرس‌وجو

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

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

۰۸

توجه به سامانه پایگاه داده

دستور پایه CREATE INDEX میان بسیاری از سامانه‌ها شباهت دارد، اما گزینه‌های پیشرفته و رفتار اجرایی یکسان نیستند. برای نمونه، شیوه ساخت اندیس خوشه‌ای، اندیس جزئی، اندیس بر پایه عبارت یا گزینه‌های هم‌زمان ممکن است در SQL Server، PostgreSQL، MySQL و SQLite تفاوت داشته باشد. پیش از استفاده از گزینه‌های اختصاصی، مستندات نسخه پایگاه داده خود را بخوانید.

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

۰۹

نام‌گذاری، مستندسازی و نگهداری

نام اندیس بهتر است قابل فهم و هماهنگ با شیوه نام‌گذاری پروژه باشد. استفاده از نام‌هایی که جدول و ستون یا هدف اندیس را نشان می‌دهند، هنگام بررسی طرح پایگاه داده به تیم کمک می‌کند. مستندسازی دلیل ایجاد اندیس نیز مفید است؛ به‌ویژه اگر اندیس برای یک گزارش پرتکرار یا نیاز مشخص برنامه ساخته شده باشد.

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

۱۰

اشتباهات رایج هنگام ساخت اندیس

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

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

مطالعه بیشتر

برای توسعه پروژه، انتخاب آگاهانه داشته باشید

در کنار بهینه‌سازی پایگاه داده، توجه به نیازهای فنی و تجربه کاربری در طراحی سایت نیز اهمیت دارد.

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

دسترسی کارآمدتر به داده‌ها

با انتخاب اندیس متناسب با نیاز برنامه
۰۱
جستجو

یافتن سریع‌تر داده

کمک به دسترسی هدفمند به رکوردها

۰۲
یکتایی

جلوگیری از تکرار

اعمال یکتایی برای ستون‌های انتخاب‌شده

۰۳
چندستونی

پشتیبانی از جستجوی ترکیبی

ترکیب ستون‌ها بر اساس الگوی پرس‌وجو

۰۴
پرس‌وجو

کمک به مرتب‌سازی

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

۰۵
نگهداری

طراحی هدفمندتر

اتکا به سنجش نیازهای واقعی سامانه

۱۱

نتیجه‌گیری

دستور CREATE INDEX در SQL روشی برای تعریف ساختار اندیس روی یک یا چند ستون است تا موتور پایگاه داده بتواند در برخی پرس‌وجوها به داده‌ها کارآمدتر دسترسی پیدا کند. انتخاب ستون مناسب، توجه به ترتیب ستون‌ها در اندیس ترکیبی و بررسی امکان استفاده از اندیس یکتا، بخش‌های مهم این کار هستند.

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

اندیس را بر پایه نیاز واقعی انتخاب کنید

پرس‌وجوها را بررسی کنید، اثر اندیس را بسنجید و تغییرهای پایگاه داده را پیش از اجرا در محیط مناسب آزمایش کنید.

آشنایی با طراحی سایت ←

سوالات متداول دستور CREATE INDEX در SQL

دستور CREATE INDEX در SQL چه کاری انجام می‌دهد؟

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

چگونه روی یک ستون اندیس بسازیم؟

از الگوی CREATE INDEX index_name ON table_name (column_name); استفاده کنید و نام اندیس، جدول و ستون را جایگزین کنید. بهتر است پیش از ساخت، کاربرد ستون را در پرس‌وجوهای برنامه بررسی کنید.

تفاوت CREATE INDEX و CREATE UNIQUE INDEX چیست؟

CREATE INDEX اندیس معمولی ایجاد می‌کند، اما CREATE UNIQUE INDEX علاوه بر ایجاد اندیس، از تکراری بودن ترکیب مقدارهای ستون یا ستون‌های مشخص‌شده جلوگیری می‌کند. جزئیات مقدارهای تهی ممکن است به سامانه پایگاه داده وابسته باشد.

آیا ساخت اندیس همیشه سرعت پایگاه داده را افزایش می‌دهد؟

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

در اندیس ترکیبی، ترتیب ستون‌ها اهمیت دارد؟

بله. ترتیب ستون‌ها می‌تواند روی نوع پرس‌وجوهایی که از اندیس بهره می‌برند اثر بگذارد. ترتیب را با شرط‌های پرتکرار و الگوی جستجوی برنامه هماهنگ کنید و رفتار دقیق را در سامانه پایگاه داده خود آزمایش کنید.

ارسال نظر و نظرات ثبت شده
:
5 0
khoda kheyretun bede khaili b man komak kard matalebetun
سایر مقالات
آموزش View در sql آموزش view در sql آموزش پایگاه داده ساخت، استفاده و مدیریت جدول‌های مجازی در SQL ...
فیلد identity در Sql فیلد identity در SQL چیست و چگونه از آن استفاده کنیم؟ ⭐ فیلد identity در SQL فیلد identity د ...
محدودیت Unique در sql SQL UNIQUE Constraint برای جلوگیری از ثبت مقدارهای تکراری در ستون‌های جدول به کار می‌رود. ...
محدودیت NOT NULL در sql آموزش پایگاه داده محدودیت NOT NULL در SQL چیست؟ آموزش کاربردی و مثال با تعریف این محدودیت، ...
محدودیت Check در sql آموزش پایگاه داده محدودیت CHECK در SQL؛ راهنمای کامل تعریف و مدیریت اعتبارسنجی داده‌ه ...
محدودیت Default در اسکیوال آموزش SQL محدودیت SQL DEFAULT؛ مقدار پیش فرض در SQL نحوه تعریف، تغییر و حذف مقدار پیش فرض ...
محدودیت Foreign Key در sql آموزش SQL کلید خارجی در SQL چیست؟ راهنمای کامل Foreign Key تعریف، کاربرد، ساخت و حذف محدود ...
محدودیت Primary Key در sql آموزش پایگاه داده محدودیت Primary Key در SQL؛ تعریف و کاربرد کلید اصلی راهنمای تعریف، استف ...
ایجاد محدودیت برای فیلد ها (Constraints) آموزش پایگاه داده محدودیت های SQL چیست و چگونه از آنها استفاده کنیم؟ راهنمای کاربردی آشنای ...
دستور alter table در sql آموزش پایگاه داده دستور ALTER TABLE در SQL؛ راهنمای افزودن، حذف و تغییر ستون آشنایی با ساخ ...
تغییر نام فیلد در یک جدول با استفاده از دستور sql آموزش SQL Server تغییر نام ستون در SQL Server با دستور sp_rename نحوه تغییر نام فیلد، مثال ...
دستور Create Index در sql آموزش SQL دستور CREATE INDEX در SQL چیست؟ آموزش ساخت اندیس، انتخاب ستون مناسب و بهبود سرعت ...
دستور Drop در sql دستور DROP در SQL؛ آموزش حذف جدول، اندیس و پایگاه داده 💡آشنایی با حذف ساختارها در SQL دستور ...
دستور Create Table در sql دستور CREATE TABLE در SQL؛ راهنمای ایجاد جدول در پایگاه داده 💡آشنایی با ساخت جدول دستور CRE ...
دستور CREATE DATABASE در sql دستور CREATE DATABASE در SQL چیست؟ 💡آشنایی با ساخت پایگاه داده برای ایجاد یک پایگاه داده ...
دسته بندی
جستجو
⏳ در حال پردازش...
لطفا صبر کنید