چگونه در MySQL از ایندکس‌ها استفاده کنیم؟

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

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

در ادامه خواهید خواند:

• پیش نیاز
• اتصال به MySQL و راه‌اندازی یک پایگاه داده نمونه
• مقدمه‌ای بر شاخص‌ها (Indexes)
• استفاده از ایندکس‌های تک‌ستونی
• استفاده از ایندکس‌های یکتا برای جلوگیری از داده‌های تکراری
• استفاده از ایندکس‌ها روی چند ستون
• فهرست‌کردن و حذف ایندکس‌های موجود
• جمع بندی

پیش نیاز

برای دنبال کردن این راهنما، شما به یک کامپیوتر با سیستم مدیریت پایگاه داده رابطه‌ای (RDBMS) مبتنی بر SQL نیاز دارید. دستورالعمل‌ها و مثال‌های این راهنما در محیط زیر بررسی و تایید شده‌اند:

• یک سرور با سیستم عامل Ubuntu که یک کاربر غیر روت با دسترسی مدیریتی دارد و فایروال با UFW پیکربندی شده است.
• MySQL روی سرور نصب و ایمن‌سازی شده است، مطابق با راهنمای نصب MySQL روی Ubuntu.
• آشنایی پایه با اجرای SELECT برای بازیابی داده‌ها از پایگاه داده.

با داشتن این پیش‌نیازها، شما آماده‌اید تا شاخص‌ها (Indexes) را در MySQL ایجاد و مدیریت کنید و عملکرد پرس‌وجوهای خود را بهینه سازید.

توجه: لازم است بدانید که بسیاری از سیستم‌های مدیریت پایگاه داده رابطه‌ای پیاده‌سازی‌های خاص خود از SQL را دارند. اگرچه دستورات ارائه شده در این آموزش در اکثر RDBMS ها کار خواهند کرد، ایندکس‌ها بخشی از دستور استاندارد SQL نیستند و بنابراین نحو دقیق یا خروجی آن‌ها ممکن است در سیستمی به جز MySQL متفاوت باشد.

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

با سرور مجازی ابری لیارا، زیرساخت پروژه‌های خود را با انعطاف‌پذیری کامل مدیریت کنید.
✅ منابع اختصاصی✅ مقیاس‌پذیری سریع✅ دسترسی Root
خرید و راه‌اندازی سرور مجازی ابری

اتصال به MySQL و راه‌اندازی یک پایگاه داده نمونه

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

مرحله 1: اتصال به سرور از طریق SSH

اگر سرور SQL شما روی سرور راه دور قرار دارد، ابتدا از طریق SSH وارد شوید:

ssh sammy@your_server_ip

در اینجا، sammy نام کابر شما است، در صورت متفاوت بودن آن را جایگزین کنید.

مرحله 2: ورود به پرامپت MySQL

برای ورود به خط فرمان MySQL، دستور زیر را وارد کنید:

mysql -u sammy -p

مرحله 3: ایجاد پایگاه داده نمونه

یک پایگاه داده جدید به نام indexes ایجاد کنید:

CREATE DATABASE indexes;

در صورت موفقیت، خروجی شبیه به دستوری که در ادامه آماده است خواهد بود.

Query OK, 1 row affected (0.01 sec)

مرحله 4: انتخاب پایگاه داده جدید

برای استفاده از آن پایگاه داده، دستور زیر را وارد کنید:

USE indexes;

و خروجی به صورت زیر خواهد بود:

Database changed

مرحله 5: تعریف جدول نمونه

در این راهنما از جدول employees استفاده می‌شود تا اطلاعات فرضی کارکنان و دستگاه‌های مرتبط با آن‌ها نگهداری شود. ستون‌ها به شرح زیر هستند:

• employee_id (int): شناسه‌ی یکتا برای هر کارمند (کلید اصلی).
• first_name (varchar(50)): نام کوچک.
• last_name (varchar(50)): نام خانوادگی.
• device_serial (varchar(15)): شماره سریال دستگاه اختصاص یافته.
• salary (int): حقوق کارمند.

برای ایجاد جدول، این دستور را اجرا کنید:

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

خروجی در صورت موفقیت:

Query OK, 0 rows affected (0.00 sec)

مرحله 6: درج داده‌های نمونه

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

INSERT INTO employees VALUES
    (1, 'John', 'Smith', 'ABC123', 60000),
    (2, 'Jane', 'Doe', 'DEF456', 65000),
    (3, 'Bob', 'Johnson', 'GHI789', 70000),
    (4, 'Sally', 'Fields', 'JKL012', 75000),
    (5, 'Michael', 'Smith', 'MNO345', 80000),
    (6, 'Emily', 'Jones', 'PQR678', 85000),
    (7, 'David', 'Williams', 'STU901', 90000),
    (8, 'Sarah', 'Johnson', 'VWX234', 95000),
    (9, 'James', 'Brown', 'YZA567', 100000),
    (10, 'Emma', 'Miller', 'BCD890', 105000),
    (11, 'William', 'Davis', 'EFG123', 110000),
    (12, 'Olivia', 'Garcia', 'HIJ456', 115000),
    (13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
    (14, 'Isabella', 'Wilson', 'NOP012', 125000),
    (15, 'Matthew', 'Martinez', 'QRS345', 130000),
    (16, 'Sophia', 'Anderson', 'TUV678', 135000),
    (17, 'Daniel', 'Smith', 'WXY901', 140000),
    (18, 'Mia', 'Thomas', 'ZAB234', 145000),
    (19, 'Joseph', 'Hernandez', 'CDE567', 150000),
    (20, 'Abigail', 'Smith', 'FGH890', 155000);

خروجی مورد انتظار:

Query OK, 20 rows affected (0.010 sec)
Records: 20  Duplicates: 0  Warnings: 0

توجه: حجم داده‌ها برای نشان دادن بهره‌وری ایندکس‌ها کافی نیست، اما ساختار پرس‌وجو و نحوه استفاده MySQL از ایندکس‌ها را به خوبی نشان خواهد داد.

نحوه نصب MySQL در اوبونتو (راهنمای گام به گام)
نصب MySQL در اوبونتو

مقدمه‌ای بر شاخص‌ها (Indexes)

وقتی در MySQL یک کوئری اجرا می‌کنید، پایگاه داده به‌طور پیش‌فرض تمام رکوردهای جدوا را یکی یکی بررسی می‌کند. مثلا فرض کنید می‌خواهید کارمندانی را پیدا کنید که نام خانوادگی‌شان Smith است یا حقوقی بیشتر از 100,000 دلار دارند. در این حالت، MySQL هر ردیف جدول را بررسی می‌کند و اگر شرط درست بود آن را به نتایج اضافه می‌کند، در غیر این صورت به سراغ ردیف بعدی می‌رود.

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

برای حل این مشکل، ایندکس‌‍‌ها (Indexes) معرفی شدند. ایندکس‌ها ساختارهای داده‌ای خاصی هستند که داده‌ها را به‌صورت مرتب و جدا از ردیف‌های جدول ذخیره می‌کنند. با استفاده از ایندکس، موتور پایگاه داده می‌تواند سریع‌تر و بهینه‌تر داده‌ها را جستجو یا مرتب کند.

مثال ساده

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

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

مزایای ایندکس در MySQL

• سرعت بیشتر در جستجوهای شرطی با WHERE (هم تطبیق دقیق هم مقایسه).
• اجرای سریع‌تر مرتب‌سازی‌ها با ORDER BY.
• امکان تضمین یونیک بودن داده‌ها.

معایب ایندکس‌ها

البته ایندکس همیشه خوب نیست! چون هر بار که داده‌ای INSERT, UPDATE یا DELETE می‌شود، ایندکس هم باید به‌روزرسانی شود. همین موضوع می‌تواند عملکرد دیتابیس را در زمان نوشتن داده‌ها کند کند.

بنابراین:

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

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

• فقط رقتی ایندکس بسازید که نیاز واقعی وجود داشته باشد (مثلا وقتی اپلیکیشن کند شده).
• روی ستون‌هایی ایندکس بگذارید که در کوئری‌های پرتکرار و سنگین استفاده می‌شوند.
• برای هر ایندکس بهینه، الگوی کوئری‌ها را بررسی کنید.

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

با هاست ابری MySQL لیارا، دیتابیس خودت را سریع و امن راه‌اندازی کن.
✅ منابع اختصاصی ✅ شبکه خصوصی ✅ پشتیبان‌گیری خودکار
خرید و راه‌اندازی هاست ابری MySQL

استفاده از ایندکس‌های تک‌ستونی

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

در پایگاه داده نمونه‌ای که در مراحل قبل ایجاد کرده‌اید، هیچچ ایندکسی وجود ندارد. پیش‌ از ایجاد ایندکس، ابتدا بررسی می‌کنیم که پایگاه داده چگونه کوئری‌های SELECT روی جدول employees را اجرا می‌کند وقتی از عبارت WHERE برای بازیابی بخشی از داده‌ها استفاده می‌شود.

فرض کنید قصد دارید کارمندانی را پیدا کنید که دقیقا حقوق آن‌ها برابر یا 100000 است. کوئری زیر را اجرا کنید.

SELECT * FROM employees WHERE salary = 100000;

در این کوئری، عبارت WHERE درخواست می‌کند فقط کارمندانی بازیابی شوند که حقوق آن‌ها دقیقا با مقدار مورد نظر برابر است و خرجی پایگاه داده مشابه زیر خواهد بود.

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

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

برای درک نحوه اجرای کوئری‌ها توسط موتور MySQL می‌توانید از دستور EXPLAIN استفاده کنید. این دستور به جای نمایش نتایج کوئری‌، طرح اجرای آن را ارائه می‌دهد:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

خروجی شبیه به جدول زیر خواهد بود:

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• ستون possible_keys: ایندکس‌هایی که MySQL در نظر گرفته است. در اینجا مقدار NULL است یعنی هیچ ایندکسی وجود ندارد.
• ستون key: ایندکسی که در نهایت استفاده شده است. در این مثال، هیچ ایندکسی استفاده نشده (NULL).
• ستون rows: تعداد ردیف‌هایی که MySQL برای یافتن نتیجه باید بررسی کند. اینجا 20 ردیف یعنی تمام رکوردهای جدول.
• ستون Extra: اطلاعات تکمیلی درباره طرح اجرا. در اینجا عبارت Using where نشان می‌دهد که پایگاه داده رکوردها را مستقیماً با شرط WHERE فیلتر کرده است.

برای بهبود عملکرد یک ایندکس روی ستون salary ایجاد می‌کنیم:

CREATE INDEX salary ON employees(salary);

نکات دستور بالا:

• نام ایندکس: salary (باید در هر جدول یکتا باشد).
• نام جدول: employees.
• ستونی که ایندکس روی آن ساخته می‌شود: salary.

اگر کاربر MySQL شما مجوز ایجاد ایندکس را نداشته باشد، ممکن است خطای زیر نمایش داده شود.

ERROR 1142 (42000): INDEX command denied to user 'user'@'host' for table 'employees'

در این صورت، با کاربر root وارد شوید و دستورات زیر را اجرا کنید (نام کاربر و هاست را متناسب تغییر دهید):

GRANT INDEX on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

پس از ایجاد ایندکس، اجرای مجدد همان کوئری:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

این با خروجی نشان خواهد داد که ایندکس salary مورد استفاده قرار گرفته و تعداد ردیف‌های بررسی شده به 1 کاهش یافته است.

به طور مشابه، برای کوئری شرطی:

SELECT * FROM employees WHERE salary < 70000;
EXPLAIN SELECT * FROM employees WHERE salary < 70000;

خروجی نشان می‌دهد که ایندکس استفاده شده فقط ردیف‌های مرتبط بررسی شده‌اند.

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

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

استفاده از ایندکس‌های یکتا برای جلوگیری از داده‌های تکراری

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

تضمین جلوگیری از مقادیر تکراری برای حفظ یکپارچگی داده‌ها ضروری است. به طور مثال:

• دونفر نباید یک شماره ملی یکسان داشته باشند.
• در یک وب‌سایت، دو کاربر نباید با یک نام کاربری یا ایمیل یکسان ثبت‌نام کنند.

در مثال جدول employees، ستون device_serial (شماره سریال دستگاه اختصاصی کارمند) نباید تکراری باشد. اگر این اتفاق ببفتد، به این معنا است که دو کارمند یک دستگاه مشترک دارند.

اما در حال حاضر، پایگاه داده این محدودیت را اعمال نکرده است. برای نمونه:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

خروجی:

Query OK, 1 row affected (0.009 sec)

یعنی ردیف جدید با موفقیت اضافه شد، حتی اگر شماره سریال ABC123 قبلا وجود داشته باشد.

حالا اگر کوئری زیر را اجرا کنید:

SELECT * FROM employees WHERE device_serial = 'ABC123';

خروجی:

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|          21 | Sammy      | Smith     | ABC123        |  65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)

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

بازگردانی تغییر اشتباه

برای بازگرداندن وضعیت، رکورد جدید را حذف کنید:

DELETE FROM employees WHERE employee_id = 21;

حالا دوباره بررسی کنید:

SELECT * FROM employees WHERE device_serial = 'ABC123';

خروجی:

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

اکنون فقط یک کارمند (John Smith) از دستگاه با شماره سریال ABC123 استفاده می‌کند.

ایجاد ایندکس یکتا روی ستون device_serial

برای جلوگیری از این مشکل، می‌توانیم روی ستون device_serial یک اسندکس یکتا ایجاد کنیم:

CREATE UNIQUE INDEX device_serial ON employees(device_serial);

خروجی:

Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

از این به بعد، اگر تلاش کنید رکوردی با شماره سریال تکراری اضافه کنید:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

خروجی:

ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'

بررسی استفاده از ایندکس‌ در کوئری‌ها

علاوه بر جلوگیری از داده‌های تکراری، ایندکس‌های یکتا مانند سایر ایندکس‌ها باعث افزایش سرعت کوئری‌ها نیز می‌شوند. می‌توان این را با دستور EXPLAIN بررسی کرد:

EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';

خروجی:

+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ستون‌های possible_keys و key نشان می‌دهند که ایندکس device_serial واقعا در اجرای کوئری استفاده شده است.

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

چگونه یک پایگاه داده ریموت را برای بهینه‌سازی عملکرد سایت با MySQL روی سرور مجازی اوبونتو راه‌اندازی کنیم؟
پایگاه داده ریموت MySQL روی سرور مجازی

استفاده از ایندکس‌ها روی چند ستون

تا اینجا، تمامی ایندکس‌هایی که در بخش‌های قبل ایجاد کردید، تنها بر اساس یک ستون تعریف شده بودند و به مقادیر همان ستون مربوط می‌شدند. بیشتر سیستم‌های پایگاه داده از ایندکس‌هایی پشتیبانی می‌کنند که بیش از یک ستون را در بر می‌گیرند. این ایندکس‌ها که به آن‌ها ایندکس چندستونه (Multi-Column Indexes) گفته می‌شود، روشی را برای ذخیره‌سازی مقادیر چند ستون در یک ایندکس فراهم می‌کنند و این امکان را به موتور پایگاه داده می‌دهند تا پرس‌وجوهایی که از مجموعه‌ای از ستون‌ها به صورت هم‌زمان استفاده می‌کنند، سریع‌تر و کارآمدتر اجرا شوند.

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

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

اولین راه‌حلی که ممکن است برای بهینه‌سازی چنین پرس‌وجویی به ذهن برسد، ایجاد دو ایندکس جداگانه است: یکی روی ستون last_name و دیگری روی ستون first_name. با این حال، این روش برای چنین وضعیتی بهترین انتخاب نیست.

اگر چنین ایندکس‌هایی ایجاد کنید، MySQL قادر خواهد بود تمام کارمندانی را که نام خانوادگی آن‌ها Smith است پیدا کند. همچنین می‌تواند تمامی کارمندانی را که نام آن‌ها John است شناسایی کند. اما در یافتن افرادی که دقیقاً John Smith هستند، مشکل خواهد داشت.

برای روشن‌تر شدن موضوع، فرض کنید دو دفترچه تلفن جداگانه دارید: یکی بر اساس نام خانوادگی مرتب شده و دیگری بر اساس نام. این دو دفترچه شبیه به ایندکس‌های ایجاد شده روی ستون‌های last_name و first_name هستند. به عنوان یک کاربر دفترچه تلفن، برای پیدا کردن John Smith سه رویکرد در پیش دارید:

• استفاده از دفترچه مرتب‌شده بر اساس نام خانوادگی: برای یافتن تمام افراد با نام خانوادگی Smith، نادیده گرفتن دفترچه دوم، و سپس بررسی دستی تمامی افراد تا زمانی که John Smith را پیدا کنید.
• برعکس عمل کنید: از دفترچه بر اساس نام برای یافتن همه افراد به نام John استفاده کنید، دفترچه اول را نادیده بگیرید و سپس به صورت دستی بین Johnها جستجو کنید تا John Smith را بیابید.
• تلاش کنید از هر دو دفترچه استفاده کنید: همه افراد به نام John و جداگانه همه افراد با نام خانوادگی Smith را بیابید، نتایج میانی را یادداشت کنید و سپس به صورت دستی دو مجموعه داده را با یکدیگر مقایسه کنید تا فرد مشترک را بیابید.

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

راهکار بهتر استفاده از ایندکس‌هایی است که نه فقط یک ستون، بلکه چند ستون را هم‌زمان در نظر می‌گیرند. این وضعیت را می‌توان به دفترچه تلفنی تشبیه کرد که درون دفترچه‌ای دیگر قرار دارد: ابتدا نام خانوادگی Smith را پیدا می‌کنید، سپس به دفترچه دوم مخصوص همه افراد با نام خانوادگی Smith هدایت می‌شوید که بر اساس نام مرتب شده است و می‌توانید به سرعت John را بیابید.

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

برای ایجاد یک ایندکس چندستونه روی ستون‌های نام خانوادگی و نام در جدول employees، دستور زیر را اجرا کنید:

CREATE INDEX names ON employees(last_name, first_name);

در این دستور، بخش داخل پرانتز پس از نام جدول (employees) شامل دو ستون است: ابتدا last_name و سپس first_name. این کار یک ایندکس چندستونه روی هر دو ستون ایجاد می‌کند. ترتیب ستون‌ها در تعریف ایندکس بسیار مهم است که در ادامه روشن خواهد شد.

پایگاه داده پیام زیر را برای موفقیت در ایجاد ایندکس نمایش خواهد داد:

Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

اکنون پرس‌وجوی زیر را اجرا کنید تا رکوردهایی با نام خانوادگی Smith و نام John بازیابی شوند:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

نتیجه یک رکورد شامل کارمندی به نام John Smith خواهد بود:

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

اکنون با استفاده از EXPLAIN بررسی کنید که آیا ایندکس استفاده شده است یا خیر:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

خروجی مشابه زیر خواهد بود (ممکن است بسته به جدول شما اندکی متفاوت باشد):

+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

در اینجا پایگاه داده از ایندکس names استفاده کرده است. تنها یک رکورد اسکن شده و بنابراین جدول بیش از حد لازم پیمایش نشده است. ستون Extra مقدار Using index condition را نشان می‌دهد که بدین معناست MySQL توانسته است عمل فیلترینگ را به طور کامل با استفاده از ایندکس انجام دهد.

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

حال بررسی کنیم اگر تنها بر اساس نام خانوادگی Smith جستجو انجام شود، چه اتفاقی رخ می‌دهد:

SELECT * FROM employees WHERE last_name = 'Smith';

خروجی به شکل زیر خواهد بود:

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|          20 | Abigail    | Smith     | FGH890        | 155000 |
|          17 | Daniel     | Smith     | WXY901        | 140000 |
|           1 | John       | Smith     | ABC123        |  60000 |
|           5 | Michael    | Smith     | MNO345        |  80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)

چهار کارمند با نام خانوادگی Smith وجود دارند.

اکنون طرح اجرای پرس‌وجو را بررسی کنید:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

خروجی مشابه زیر خواهد بود:

+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 203     | const |    4 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

در اینجا نیز پایگاه داده از ایندکس چندستونه names استفاده کرده است. چهار رکورد اسکن شده که دقیقاً برابر با تعداد رکوردهای بازیابی شده است.

اکنون اگر تنها بر اساس ستون دوم ایندکس یعنی first_name جستجو انجام شود، وضعیت متفاوت خواهد بود:

SELECT * FROM employees WHERE first_name = 'John';

خروجی:

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

اکنون طرح اجرای پرس‌وجو را مشاهده کنید:

EXPLAIN SELECT * FROM employees WHERE first_name = 'John';

خروجی:

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

هرچند تنها یک رکورد بازگردانده شد، اما هیچ ایندکسی استفاده نشده است. پایگاه داده کل جدول (20رکورد) را اسکن کرده است. در ستون Extra مقدار Using where نشان‌دهنده همین موضوع است.

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

به طور کلی، یک ایندکس چندستونه شامل ستون‌های (a, b, c) می‌تواند برای پرس‌وجوهایی که هر سه ستون را شامل می‌شوند یا فقط دو ستون اول (a, b) یا تنها ستون اول (a) استفاده شود. اما این ایندکس برای پرس‌وجوهایی که فقط شامل ستون آخر (c) یا ستون‌های (b, c) هستند، سودی نخواهد داشت.

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

با سرور مجازی Ubuntu لیارا، زیرساخت پروژه‌های خود را سریع و امن راه‌اندازی کن.
✅ نصب آسان و آماده استفاده✅ منابع اختصاصی و پایدار✅ دسترسی کامل SSH
خرید و راه‌اندازی سرور Ubuntu

فهرست‌کردن و حذف ایندکس‌های موجود

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

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

SHOW INDEXES FROM employees;

خروجی مشابه نمونه زیر خواهد بود:

+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | device_serial |            1 | device_serial | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | salary        |            1 | salary        | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            1 | last_name     | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            2 | first_name    | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

بسته به نسخه‌ی MySQL شما، خروجی ممکن است اندکی متفاوت باشد، اما شامل تمامی ایندکس‌‌ها خواهد بود؛ از جمله نام‌ آن‌ها، ستون‌هایی که ایندکس بر اساس آن‌ها تعریف شده است، اطلاعات مربوط به یکتا بودن ایندکس و سایر جزئیات مربوط به تعریف ایندکس.

برای حذف ایندکس‌های موجود، می‌توانید از دستور DROP INDEX در SQL استفاده نمایید. فرض کنید دیگر نمی‌خواهید یکتایی بر روی ستون device_serial اعمال شود؛ در این صورت ایندکس device_serial دیگر مورد نیاز نخواهد بود. دستور زیر را اجرا کنید:

DROP INDEX device_serial ON employees;

در اینجا، device_serial نام ایندکس و employees نام جدولی است که ایندکس بر روی آن تعریف شده است. پایگاه داده حذف ایندکس را تأیید خواهد کرد:

Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

گاهی الگوهای کوئری‌های متداول در طول زمان تغییر می‌کنند یا انواع جدیدی از کوئری‌ها برجسته می‌شوند. در چنین شرایطی، ممکن است نیاز باشد ایندکس‌های مورد استفاده را بازبینی کنید، ایندکس‌های جدیدی ایجاد نمایید یا ایندکس‌های بلااستفاده را حذف کنید تا از کاهش عملکرد پایگاه داده جلوگیری شود و آن را به‌روز نگه دارید. با استفاده از دستورات CREATE INDEX و DROP INDEX می‌توانید ایندکس‌ها را در یک پایگاه داده‌ی موجود مدیریت کنید و بر اساس بهترین رویه‌ها، ایندکس‌هایی را تنها زمانی ایجاد نمایید که واقعاً مورد نیاز و سودمند باشند.

آموزش بکاپ‌گیری از دیتابیس MySQL در سرور مجازی اوبونتو 24.04
بکاپ‌گیری از دیتابیس MySQL

جمع بندی

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