اندیس ها در MySQL — راهنمای جامع

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

هنگام ایجاد اندیس باید این نکته را در نظر داشت که کدام ستون‌ها برای ایجاد کوئری‌های SQL بیشتر مورد استفاده قرار می‌گیرند و یک یا چند اندیس روی این ستون‌ها تشکیل داد.

از لحاظ عملی اندیس‌ها نیز نوعی جدول محسوب می‌شوند که کلید ابتدایی (primary key) یا فیلد اندیس را در خود جای داده‌اند و به هر یک از رکوردهای داخل جدول واقعی اشاره می‌کنند.

کاربران نمی‌توانند اندیس‌ها را ببینند. اندیس‌ها از سوی موتور جستجوی پایگاه داده صرفاً برای افزایش سرعت اجرای کوئری‌ها و یافتن بسیار سریع‌تر رکوردها استفاده می‌شوند.

عبارت‌های INSERT و UPDATE روی جدول‌هایی که حاوی اندیس هستند، زمان بیشتری طول می‌کشند؛ در حالی که عبارت SELECT روی این جدول‌ها سریع‌تر اجرا می‌شود. دلیل این مسئله آن است که هنگام درج یا به‌روزرسانی فیلدها، پایگاه داده باید مقادیر اندیس را نیز درج یا به‌روزرسانی کند.

اندیس ساده و یکتا

شما می‌توانید یک اندیس یکتا روی یک جدول ایجاد کنید. منظور از اندیس یکتا این است که دو ردیف نمی‌توانند مقدار اندیس مشابهی داشته باشند. در ادامه طرز ایجاد یک اندیس روی جدول را می‌بینید:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2,...);

شما می‌توانید از یک یا چند ستون برای ایجاد یک اندیس استفاده کنید.

برای مثال ما یک اندیس را روی tutorials_tbl با استفاده از tutorial_author ایجاد می‌کنیم:

CREATE UNIQUE INDEX AUTHOR_INDEX

ON tutorials_tbl (tutorial_author)

شما می‌توانید یک اندیس ساده را روی یک جدول ایجاد کنید. کافی است کلیدواژه UNIQUE را از کوئری حذف کنید تا یک اندیس ساده ایجاد شود. یک اندی ساده امکان کپی کردن مقادیر یک جدول را ایجاد می‌کند.

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

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX

ON tutorials_tbl (tutorial_author DESC)

دستور ALTER برای افزودن یا حذف یک اندیس

چهار نوع عبارت برای افزودن اندیس به یک جدول وجود دارد:

• (ALTER TABLE tbl_name ADD PRIMARY KEY (column_list – این عبارت یک PRIMARY KEY اضافه می‌کند که به این معنی است که مقادیر اندیس شده باید یکتا و غیر NULL باشند.
• (ALTER TABLE tbl_name ADD UNIQUE index_name (column_list – این عبارت یک اندیس ایجاد می‌کند که مقادیر آن باید یکتا باشند (به استثنای مقادیر NULL که می‌توانند تکرار شده باشند)
• (ALTER TABLE tbl_name ADD INDEX index_name (column_list – این عبارت یک اندیس معمولی اضافه می‌کند که در آن مقادیر مختلف می‌توانند بیش از یک بار وجود داشته باشند.
• (ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list – این عبارت یک اندیس FULLTEXT خاص ایجاد می‌کند که به منظور جستجوهای متنی مورد استفاده قرار می‌گیرد.

در ادامه قطعه کدی ارائه شده است که مثالی برای افزودن اندیس به یک جدول موجود است:

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

شما می‌توانید هر اندیس را با استفاده از بند DROP به همراه دستور ALTER حذف کنید. مثال زیر برای حذف اندیسی استفاده می‌شود که در بخش قبل ایجاد کردیم.

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

شما می‌توانید هر اندیس را با استفاده از بند DROP به همراه دستور ALTER حذف نمایید

دستور ALTER برای افزودن یا حذف PRIMARY KEY

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

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

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

شما می‌توانید از دستور ALTER برای حذف کلید ابتدایی به صورت زیر استفاده کنید:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

برای حذف اندیسی که کلید ابتدایی نیست، باید نام اندیس را ذکر کنید.

نمایش اطلاعات اندیس

از دستور SHOW INDEX می‌توان برای نمایش فهرستی از همه اندیس‌های مرتبط با یک جدول استفاده کرد. خروجی با قالب‌بندی عمودی (که با G تعیین می‌شود) غالباً در این عبارت مفید است، چون از چندپاره شدن خطوط جلوگیری می‌کند. مثال زیر را بررسی کنید:

mysql> SHOW INDEX FROM table_name\G

........

منبع: فرادرس