ایندکس گذاری در mysql

یادم می‌آید یک شب، ساعت دو بامداد، با یک سایت فروشگاهی طرف بودم که صفحه‌ی گزارش سفارش‌هایش هفتاد ثانیه طول می‌کشید تا بالا بیاید. صاحب سایت با لحن عصبانی می‌گفت هاست را عوض کرده، افزونه‌ها را کم کرده، حتی قالب را عوض کرده، ولی مشکل باقی است. وقتی نشستم پشت سیستم و کوئری اصلی را دیدم، فقط یک چیز توجه‌ام را جلب کرد: ستون user_id در جدول orders که در WHERE استفاده می‌شد، هیچ ایندکسی نداشت. یک ایندکس روی آن ستون، زمان آن گزارش را از هفتاد ثانیه به کمتر از یک ثانیه رساند — بدون تغییر هاست، بدون تغییر کد، و بدون خاموش‌کردن هیچ افزونه‌ای. آن شب برایم روشن شد که ایندکس گذاری در MySQL نه یک نکته‌ی پیشرفته است و نه یک بهینه‌سازی دلبخواه؛ پایه‌ای‌ترین مهارتی است که تفاوت بین یک دیتابیس معمولی و یک دیتابیس حرفه‌ای را می‌سازد. در این مقاله، همان مسیری را می‌روم که در پروژه‌های واقعی طی می‌کنم: از مفهوم B-Tree و انواع ایندکس تا ایندکس ترکیبی، ایندکس پوششی، تحلیل EXPLAIN و اشتباهاتی که بارها دیده‌ام.

چرا ایندکس، تفاوت بین سرعت و فاجعه است؟

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

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

در ترم‌های فنی، وقتی MySQL بدون ایندکس به‌دنبال رکوردی می‌گردد، از یک full table scan استفاده می‌کند — یعنی تمام رکوردهای جدول را بررسی می‌کند تا به رکورد موردنظر برسد. پیچیدگی این عملیات، از مرتبه‌ی O(n) است. با ایندکس، این پیچیدگی به O(log n) کاهش می‌یابد. تفاوت این دو در جدولی با ده‌هزار رکورد محسوس نیست، ولی در جدولی با ده میلیون رکورد، می‌تواند تفاوت بین نیم‌ثانیه و ده‌ثانیه باشد.

تجربه‌ی چندساله‌ی من در پروژه‌های واقعی یک قاعده‌ی ساده به من داده است: در بیش از نیمی از پرونده‌های «سایت کند است»، مشکل از هاست یا قالب نبوده؛ از نبودِ ایندکس مناسب روی یکی دو ستون کلیدی بوده است. اگر با تحلیل کوئری‌های کند آشنا نیستید، بهینه‌سازی کوئری‌های MySQL مسیر تشخیص را کامل توضیح می‌دهد — این مقاله، دقیقاً همان پایه‌ای است که در آن به آن تکیه می‌کنم.

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

ایندکس در MySQL چطور کار می‌کند؟

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

در موتور InnoDB (که پیش‌فرض امروز است)، ساختار داخلی ایندکس، دو نوع دارد:

• Clustered Index: خودِ جدول، به ترتیب کلید اصلی روی دیسک ذخیره می‌شود. یعنی رکوردهای یک جدول، فیزیکی نزدیک به هم قرار می‌گیرند. به همین دلیل است که PRIMARY KEY در InnoDB نقش مرکزی بازی می‌کند.
• Secondary Index: ایندکس‌های دیگر، به کلید اصلی اشاره می‌کنند نه به رکورد مستقیم. یعنی وقتی با یک ایندکس ثانویه رکوردی را پیدا می‌کنید، MySQL اول کلید اصلی را می‌خواند و بعد رکورد را.

این تفکیک، در پروژه‌های واقعی اهمیت دارد: اگر کلید اصلی شما بزرگ باشد (مثلاً VARCHAR(255) به‌جای INT)، همه‌ی ایندکس‌های ثانویه هم بزرگ‌تر می‌شوند چون همان کلید را تکرار می‌کنند. این یک دلیل مهم برای انتخاب INT یا BIGINT به‌عنوان کلید اصلی در جدول‌های بزرگ است. اگر با تفاوت موتورهای ذخیره‌سازی درگیرید، تفاوت InnoDB و MyISAM این تفاوت‌ها را با جزئیات بیشتر توضیح می‌دهد.

انواع ایندکس که در پروژه‌ها استفاده می‌کنم

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

PRIMARY KEY: اهمیت انتخاب درست

کلید اصلی، هم ایندکس یکتاست و هم در InnoDB، خودِ ترتیب فیزیکی جدول را تعیین می‌کند. در پروژه‌های واقعی، انتخاب کلید اصلی را جدی می‌گیرم چون همه‌ی ایندکس‌های بعدی، به آن اشاره می‌کنند. اگر داده‌ی شما نرخ درج بالایی دارد و کلید اصلی تصادفی است (مثل UUID)، درج‌های تصادفی می‌توانند بازدهی نوشتن را پایین بیاورند. برای این نوع بار، کلید خودافزا یا UUIDهای مرتب‌شدنی (ULID) انتخاب بهتری هستند.

UNIQUE INDEX: جدا از PRIMARY KEY

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    username VARCHAR(50) NOT NULL,
    UNIQUE KEY uk_users_email (email),
    UNIQUE KEY uk_users_username (username)
);

در اکثر پروژه‌ها، ایمیل و نام کاربری هر دو باید یکتا باشند. UNIQUE این تضمین را در سطح دیتابیس می‌دهد، نه در کد. تجربه‌ی من: هر بار که این تضمین را در کد گذاشته‌ام و در دیتابیس نه، ماه‌ها بعد داده‌ی تکراری در پروژه پیدا کرده‌ام.

INDEX عادی

پرکاربردترین نوع برای تسریع جستجو. بدون قید یکتایی. مثال:

CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created ON orders (created_at);

یک نکته‌ی مهم که در طراحی دیتابیس در MySQL هم رویش تأکید کرده‌ام: در MySQL، ستون‌های کلید خارجی به‌طور خودکار ایندکس نمی‌شوند (برخلاف بعضی دیتابیس‌های دیگر). اگر روی user_id در جدول orders ایندکس نگذارید، هر JOIN تبدیل به یک full scan می‌شود.

FULLTEXT: داستان جداگانه

اگر جستجوی متنی روی متن‌های بلند دارید، FULLTEXT انتخاب درستی است:

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    FULLTEXT KEY ft_articles (title, body)
);

-- جستجو
SELECT * FROM articles WHERE MATCH(title, body) AGAINST("mysql index" IN NATURAL LANGUAGE MODE);

FULLTEXT برای LIKE "%text%" جایگزین نیست — این دو، مکانیزم‌های متفاوتی دارند. LIKE با % در ابتدا نمی‌تواند از ایندکس B-Tree استفاده کند، ولی FULLTEXT با ساختار معکوس، جستجوی متنی سریع روی متن‌های بزرگ ممکن می‌کند.

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

ایندکس ترکیبی (Composite Index) روی چند ستون با هم ساخته می‌شود. مثال:

CREATE INDEX idx_orders_user_status_date
    ON orders (user_id, status, created_at);

نکته‌ی حیاتی در ایندکس ترکیبی، ترتیب ستون‌ها است. این ایندکس، برای کوئری‌های زیر کارا است:

• WHERE user_id = 5
• WHERE user_id = 5 AND status = "paid"
• WHERE user_id = 5 AND status = "paid" AND created_at > "2026-01-01"

ولی برای کوئری‌های زیر، بی‌اثر است:

• WHERE status = "paid" (چون user_id ستون اول است)
• WHERE created_at > "2026-01-01" (چون created_at ستون سوم است)

قاعده‌ی کلاسیک: ایندکس ترکیبی از «چپ به راست» فعال است. یعنی کوئری باید از ستون اول شروع کند تا ایندکس به‌کار بیاید. ولی یک نکته‌ی کمتر گفته‌شده هم وجود دارد که در پروژه‌های واقعی به آن رسیده‌ام: پرش از یک ستون هم مجاز است اگر شرط روی ستون‌های بعدی، از «برابری» استفاده کند. مثلاً برای ایندکس (a, b, c)، کوئری WHERE a = 1 AND c = 3 هم از این ایندکس استفاده می‌کند، ولی فقط برای بخش a — یعنی c مجبور است داخل نتیجه‌ی a فیلتر شود. این ظرافت، در تحلیل EXPLAIN دیده می‌شود.

در تجربه‌ی من، ترتیب درست ستون‌ها را با پاسخ به این سؤال پیدا می‌کنم: «کدام ستون، مقدارهای متمایز بیشتری دارد؟» آن ستون را اول می‌گذارم. مثلاً در جدول orders، user_id مقدارهای متمایز زیادی دارد، ولی status فقط چند مقدار محدود دارد. پس user_id انتخاب بهتری برای ستون اول است. اگر JOIN در MySQL را جدی می‌گیرید، ایندکس ترکیبی روی ستون‌های JOIN و WHERE، یکی از مؤثرترین بهینه‌سازی‌هاست.

در ایندکس ترکیبی، ترتیب ستون‌ها یک انتخاب سلیقه‌ای نیست؛ یک قرارداد با موتور دیتابیس است — هرچه ترتیب را بفهمید، دیتابیس سریع‌تر جواب می‌دهد.

ایندکس پوششی: بهینه‌سازی پنهان

یکی از تکنیک‌های کمتر شناخته‌شده ولی بسیار مؤثر، ایندکس پوششی (Covering Index) است. ایده ساده است: اگر ایندکسی همه‌ی ستون‌هایی که کوئری شما نیاز دارد را در خود داشته باشد، MySQL لازم نیست به جدول اصلی برگردد — فقط با ایندکس، پاسخ را کامل می‌سازد.

-- ایندکس ترکیبی که کل کوئری را پوشش می‌دهد
CREATE INDEX idx_orders_user_total ON orders (user_id, total);

-- این کوئری فقط از ایندکس پاسخ می‌گیرد
SELECT total FROM orders WHERE user_id = 5;

در خروجی EXPLAIN، اگر ستون Extra حاوی Using index باشد (بدون ذکر Using where یا Using filesort)، یعنی ایندکس پوششی به‌کار رفته. این وضعیت، در جداول بزرگ، تفاوت بین چند صد میلی‌ثانیه و چند میلی‌ثانیه است — چون MySQL از خواندن اضافی دیسک صرفه‌جویی می‌کند.

تجربه‌ی من در پروژه‌های واقعی: وقتی کوئری گزارش‌گیری پرتکرار دارید که فقط چند ستون را می‌خواند، ایندکس پوششی بگذارید. مثلاً گزارش «پرفروش‌ترین محصولات هر کاربر» می‌تواند با یک ایندکس روی (user_id, product_id, total) چند برابر سریع‌تر شود. همین تکنیک، در جدول‌های بزرگ با میلیون‌ها رکورد، گاهی تفاوت بین «گزارش کار می‌کند» و «سایت می‌خوابد» است.

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

قاعده‌ی ساده‌ای که در پروژه‌های واقعی به آن رسیده‌ام: ایندکس روی ستون‌هایی بگذارید که در WHERE، JOIN، ORDER BY و GROUP BY پرتکرار ظاهر می‌شوند. ولی تصمیم قطعی، بر اساس تحلیل صادقانه‌ی کوئری‌های واقعی گرفته می‌شود. سه سناریوی که در آن‌ها بدون تردید ایندکس می‌گذارم:

• کلید خارجی: هر ستونی که در شرط JOIN استفاده می‌شود، باید ایندکس داشته باشد. این قاعده، در MySQL نقش مهمی دارد چون به‌طور خودکار ایندکس نمی‌سازد.
• ستون‌های پرتکرار در WHERE: اگر جدول شما میلیون‌ها رکورد دارد و کوئری‌هایتان مرتباً روی یک ستون خاص فیلتر می‌کنند (مثل status، user_id، created_at)، ایندکس نگذارید یعنی به کاربر خود ظلم کرده‌اید.
• ستون‌های ORDER BY در کوئری‌های صفحه‌بندی‌شده: اگر مرتب «آخرین نوشته‌ها» را می‌گیرید، ایندکس روی created_at DESC سرعت را چند برابر می‌کند.

برای انتخاب دقیق، همیشه از EXPLAIN و لاگ کوئری‌های کند کمک می‌گیرم — این دو ابزار، به من می‌گویند کدام کوئری کند است و چرا. اگر تازه با دستورات MySQL آشنا می‌شوید، دستورات پرکاربرد MySQL فهرستی از همین ابزارها را در کنار بقیه‌ی دستورات کاربردی دارد.

چه زمانی ایندکس نگذاریم؟

ایندکس، رایگان نیست. هر ایندکس، هزینه‌ی نوشتن دارد و اگر بی‌دلیل ساخته شود، پروژه را کند می‌کند. چهار سناریو که در آن‌ها عمداً ایندکس نمی‌گذارم:

• جدول‌های کوچک: اگر جدول شما کمتر از چند هزار رکورد دارد، ایندکس معمولاً بی‌اثر است — MySQL می‌تواند بدون ایندکس هم سریع پاسخ بدهد. تفاوت این حالت با جدول بزرگ این است که یک full scan روی هزار رکورد، از خواندن ساختار ایندکس هم سریع‌تر است.
• ستون‌های کم‌تنوع (Low Cardinality): اگر ستونی فقط چند مقدار ممکن دارد (مثل gender یا active)، ایندکس روی آن به‌ندرت مؤثر است. MySQL در چنین حالتی خودش ترجیح می‌دهد full scan کند و از ایندکس استفاده نکند.
• جدول‌هایی با نوشتن بسیار زیاد: اگر جدولی روزانه میلیون‌ها رکورد می‌گیرد و خواندنش کم است، هر ایندکس اضافی، سرعت نوشتن را کم می‌کند. در این جدول‌ها فقط ایندکس‌های حیاتی را نگه می‌دارم.
• ستون‌هایی که در WHERE استفاده نمی‌شوند: اگر ستونی فقط در SELECT ظاهر می‌شود و هرگز در WHERE، JOIN یا ORDER BY نمی‌آید، ایندکس روی آن بی‌فایده است.

EXPLAIN: ابزار تحلیل ایندکس

بدون EXPLAIN، ایندکس‌گذاری شبیه حدس‌زدن است. EXPLAIN به شما می‌گوید MySQL چگونه یک کوئری خاص را اجرا می‌کند:

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = "paid";

خروجی EXPLAIN چند ستون دارد که در پروژه‌های واقعی، بیشتر از همه به آن‌ها نگاه می‌کنم:

سه وضعیتی که در خروجی EXPLAIN به من هشدار می‌دهد:

• type: ALL: یعنی MySQL کل جدول را اسکن می‌کند. این سیگنال قرمز است که یا ایندکس مناسب وجود ندارد، یا کوئری طوری نوشته شده که MySQL نمی‌تواند از ایندکس استفاده کند.
• key: NULL: ایندکسی وجود دارد ولی MySQL انتخاب کرده از آن استفاده نکند — معمولاً به دلیل کم‌تنوع بودن مقدارها، یا آمار قدیمی. در این حالت ANALYZE TABLE می‌تواند کمک کند.
• Extra: Using filesort: یعنی MySQL برای مرتب‌سازی، داده را در حافظه‌ی موقت مرتب می‌کند. اگر ORDER BY روی ستونی باشد که ایندکس ندارد، این اتفاق می‌افتد و در جدول‌های بزرگ، کند است.

در نسخه‌های جدید MySQL، EXPLAIN ANALYZE هم اضافه شده که کوئری را واقعاً اجرا می‌کند و زمان دقیق هر مرحله را نشان می‌دهد. برای کوئری‌های پیچیده، این ابزار تفاوت بین «حدس» و «دانستن» است. اصول کامل این تحلیل را در بهینه‌سازی کوئری‌های MySQL باز کرده‌ام.

شناسایی کوئری‌های کند

قبل از این‌که ایندکس بسازید، باید بدانید کدام کوئری کند است. در MySQL، دو ابزار برای این کار وجود دارد:

۱) Slow Query Log

-- فعال‌سازی لاگ کوئری‌های کند
SET GLOBAL slow_query_log = "ON";
SET GLOBAL long_query_time = 1;  -- کوئری‌های بیش از ۱ ثانیه
SET GLOBAL slow_query_log_file = "/var/log/mysql/slow.log";

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

۲) Performance Schema

در MySQL ۸.۰ به بعد، performance_schema فعال است و می‌توانید کوئری‌های پرتکرار و کند را مستقیماً از جدول‌های داخلی بپرسید:

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT / 1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

این کوئری، ده کوئری کندِ سرور شما را با میانگین زمان اجرا نشان می‌دهد. تجربه‌ی من: بعد از پاک‌سازی این فهرست، تقریباً همیشه دو یا سه کوئری وجود دارند که کل بار را سنگین می‌کنند — و برای هرکدام، ایندکس مناسب یا بازنویسی، تفاوت محسوسی می‌سازد.

هزینه‌ی پنهان ایندکس‌ها

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

• فضای دیسک: هر ایندکس، یک ساختار داده‌ی جداگانه است که باید روی دیسک نگه داشته شود. در جدول‌های حجیم، ایندکس‌ها می‌توانند به اندازه‌ی خودِ داده فضا بگیرند. اگر فضای دیسک محدود است، این یک هزینه‌ی واقعی است.
• کندی نوشتن: هر INSERT، UPDATE یا DELETE، باعث به‌روزرسانی همه‌ی ایندکس‌های مربوطه می‌شود. جدولی با ده ایندکس، سرعت نوشتنش می‌تواند ده برابر کندتر از جدولی با یک ایندکس باشد.
• زمان ساخت و بازسازی: ALTER TABLE ... ADD INDEX روی جدول بزرگ، ممکن است دقیقه‌ها یا ساعت‌ها طول بکشد و در این مدت، جدول قفل باشد. اگر با خطای Lock wait timeout در MySQL روبرو شده‌اید، معمولاً همین دلیل است.

به این دلایل، در پروژه‌های واقعی، قاعده‌ی من ساده است: ایندکس‌ها را مثل کد، مدیریت کنید — هر کدام باید دلیل وجودی داشته باشد. اگر ایندکسی در چند ماه گذشته استفاده نشده، احتمالاً باید حذف شود. پاک‌سازی دوره‌ای ایندکس‌های بلااستفاده، بخشی از همان اصولی است که در بهینه‌سازی جداول MySQL برای سرعت بیشتر توضیح داده‌ام.

ایندکس، مثل دارو است؛ دوزِ درست، درمان است و دوزِ زیاد، خودش بیماری. ایندکس‌گذاری بدون اندازه‌گیری، به تجویز بدون تشخیص شبیه است.

شرایطی که ایندکس را بی‌اثر می‌کنند

گاهی اوقات ایندکس وجود دارد ولی MySQL از آن استفاده نمی‌کند. سه سناریوی رایج که در پروژه‌های واقعی دیده‌ام:

۱) LIKE با % در ابتدا

-- ایندکس روی name استفاده نمی‌شود
SELECT * FROM users WHERE name LIKE "%Ali%";

-- ایندکس استفاده می‌شود
SELECT * FROM users WHERE name LIKE "Ali%";

اگر الگو با % شروع شود، MySQL نمی‌تواند از ایندکس B-Tree استفاده کند چون باید از ابتدای درخت، جستجو را آغاز کند. برای جستجوی متنیِ داخلی، FULLTEXT ایندکس یا ابزارهای تخصصی‌تر را در نظر بگیرید.

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

-- ایندکس روی created_at استفاده نمی‌شود
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- ایندکس استفاده می‌شود
SELECT * FROM orders
WHERE created_at >= "2026-01-01" AND created_at < "2027-01-01";

هر تابعی که روی ستون ایندکس‌شده اعمال کنید، ایندکس را بی‌اثر می‌کند. در پروژه‌های واقعی، این نوع نوشتن را زیاد دیده‌ام — و راه‌حل، بازنویسی کوئری با بازه به‌جای تابع است.

۳) تبدیل نوع پنهان

-- فرض کنید user_id از نوع INT است
-- این کوئری ایندکس را بی‌اثر می‌کند چون رشته به INT تبدیل می‌شود
SELECT * FROM orders WHERE user_id = "5";

-- درست: مقدار با نوع درست
SELECT * FROM orders WHERE user_id = 5;

هر بار که نوع مقداری که پاس می‌دهید با نوع ستون مطابقت نداشته باشد، MySQL تبدیل انجام می‌دهد و این تبدیل می‌تواند ایندکس را بی‌اثر کند. در پروژه‌های چندزبانه (مثل پایتون + MySQL)، این اشتباه رایج است — چون پایتون رشته را بدون تمایز پاس می‌دهد. نمونه‌های عملی این تعامل در اتصال پایتون به MySQL آمده است.

اشتباهاتی که در پروژه‌های واقعی دیده‌ام

در بازبینی دیتابیس پروژه‌های مختلف، این اشتباهات را زیاد دیده‌ام:

• ایندکس روی همه ستون‌ها: بعضی تیم‌ها برای احتیاط، روی همه چیز ایندکس می‌گذارند. نتیجه: سرعت نوشتن به‌شدت پایین می‌آید و فضای دیسک هدر می‌رود. ایندکس، ابزاری هدفمند است، نه یک بیمه‌ی سراسری.
• فراموش کردن ایندکس روی کلیدهای خارجی: همان‌طور که در طراحی دیتابیس در MySQL گفتم، MySQL به‌طور خودکار ایندکس نمی‌سازد. اگر یادتان برود، JOINها تبدیل به full scan می‌شوند.
• ترتیب اشتباه در ایندکس ترکیبی: ایندکس روی (status, user_id) که برای کوئری WHERE user_id = 5 استفاده نمی‌شود، چون user_id ستون دوم است. این اشتباه، به‌طور خاموش، کارایی را نابود می‌کند.
• نادیده‌گرفتن EXPLAIN: ایندکس می‌گذارند ولی هیچ‌وقت چک نمی‌کنند که واقعاً استفاده می‌شود. خیلی وقت‌ها، ایندکس وجود دارد ولی MySQL انتخاب می‌کند از آن استفاده نکند — چون آمار قدیمی است یا ایندکس مناسب کوئری نیست.
• ساخت ایندکس در ساعت شلوغی: ALTER TABLE ... ADD INDEX روی جدول بزرگ، جدول را قفل می‌کند. انجام این کار در ساعت شلوغی، به تجربه‌ی کاربران ضربه می‌زند.
• ایندکس روی ستون‌های کم‌تنوع: ایندکس روی status با سه مقدار ممکن، تقریباً همیشه استفاده نمی‌شود چون سود آن کمتر از هزینه‌ی پیمایش ایندکس است.
• نادیده‌گرفتن ایندکس پوششی: بسیاری از تیم‌ها نمی‌دانند اگر ایندکسی همه‌ی ستون‌های کوئری را داشته باشد، MySQL دیگر به جدول برنمی‌گردد. این تکنیک در گزارش‌های پرتکرار، تفاوت بین کند و سریع را می‌سازد.
• نداشتن سیاست برای ایندکس‌های قدیمی: ایندکس‌ها انباشته می‌شوند و هیچ‌کس مسئول حذفشان نیست. نتیجه: جدول با ده ایندکس که نیمی‌شان استفاده نمی‌شوند و همه سرعت نوشتن را می‌خورند.
• تغییر ستون‌ها بدون بازبینی ایندکس‌ها: وقتی ستونی را تغییر می‌دهید یا نامش را عوض می‌کنید، ایندکس‌های مرتبط ممکن است بی‌استفاده شوند. در migrationها، این نکته را جداگانه چک می‌کنم.
• ایندکس روی ستون‌های طولانی بدون پیشوند: ایندکس روی VARCHAR(500) می‌تواند خیلی بزرگ شود. راه‌حل: ایندکس پیشوندی روی بخش اول ستون (مثل name(50)) — مناسب برای جستجوهایی که با بخش اول کار می‌کنند.
• نداشتن مستندات ایندکس‌ها: دو سال بعد، هیچ‌کس نمی‌داند چرا یک ایندکس خاص ساخته شده و چه کوئری‌ای به آن نیاز دارد. یک فایل INDEXES.md ساده، این ابهام را از بین می‌برد.

یک توصیه‌ی عملی از تجربه: قبل از ساخت هر ایندکس، سه سؤال از خودتان بپرسید. اول، «کدام کوئری‌ها از این ایندکس بهره می‌برند؟» دوم، «آیا این ایندکس، روی نرخ نوشتن جدول تأثیر قابل‌قبولی می‌گذارد؟» سوم، «چطور می‌فهمم که ایندکس استفاده می‌شود؟» اگر جواب سؤال سوم را ندارید، احتمالاً هنوز آماده نیستید ایندکس بسازید. کار کردن با دیتابیس، مثل کار با زیرساخت است — بدون اندازه‌گیری، هر تصمیمی حدس است، و حدس گران‌ترین چیزی است که می‌توانید به پروژه بدهید. اگر با اصول امنیت دیتابیس هم درگیر هستید، امنیت دیتابیس چیست و امنیت دیتابیس وردپرس لایه‌های مکمل را نشان می‌دهند؛ و اگر با تراکنش‌ها کار می‌کنید، تراکنش‌ها در MySQL توضیح می‌دهد که قفل‌ها و ایندکس‌ها چطور با هم تعامل دارند.

سخن آخر

ایندکس گذاری در MySQL، از یک CREATE INDEX ساده شروع می‌شود ولی در پروژه‌های واقعی، به یک تصمیم راهبردی تبدیل می‌شود. سه نکته‌ی اصلی که در این مقاله به آن‌ها رسیدیم: اول، ایندکس، ساختار داده‌ای است که تفاوت بین O(n) و O(log n) را می‌سازد — این تفاوت، در جدول‌های بزرگ می‌تواند بین نیم‌ثانیه و ده‌ثانیه باشد؛ دوم، ایندکس رایگان نیست — هر ایندکس، هزینه‌ی فضای دیسک و سرعت نوشتن دارد. انتخاب ایندکس درست، به اندازه‌ی نبودنش مهم است؛ سوم، بدون EXPLAIN، ایندکس‌گذاری حدس است — هر ایندکسی که می‌سازید، باید با تحلیل خروجی EXPLAIN تأیید شود که واقعاً استفاده می‌شود.

اگر امروز می‌خواهید شروع کنید، سه کار کوچک پیشنهاد می‌کنم: در یک دیتابیس تستی، یک جدول با میلیون رکورد بسازید (می‌توانید با یک اسکریپت پایتونی تولید کنید)، یک کوئری فیلتر روی یکی از ستون‌ها بزنید و با EXPLAIN ببینید type چه مقدار است. سپس ایندکس روی همان ستون بسازید و EXPLAIN را دوباره اجرا کنید. تغییر مقدار type از ALL به ref، یک درس عملی است که هیچ کتابی نمی‌تواند جایگزینش کند. اگر تجربه‌ای از ایندکس‌گذاری در پروژه‌های خودتان دارید — مخصوصاً اگر با یک کوئری گیر کرده‌اید یا ایندکسی ساخته‌اید که انتظار داشتید استفاده شود و نشد — در دیدگاه‌ها بنویسید؛ همین نکته‌های میدانی، برای خواننده‌ی بعدی از هر مستند رسمی ارزشمندتر است. ⚡