ایندکس دیتابیس: کِی بسازیم و کِی نسازیم

کوئری‌تان روی جدولی با دو میلیون ردیف، هشت ثانیه طول می‌کشد و صفحه قبل از اینکه چیزی نشان دهد تایم‌اوت می‌شود. لاگ MySQL را باز می‌کنید، کوئری را در EXPLAIN می‌اندازید و در ستون type می‌بینید ALL؛ یعنی موتور دارد کل جدول را می‌خواند. حالا سؤال واقعی این است: روی کدام ستون ایندکس بسازید، و آیا اصلاً باید بسازید؟

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

چرا EXPLAIN با type=ALL نشان می‌دهد جدول اسکن می‌شود

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

EXPLAIN SELECT id, title FROM posts WHERE author_id = 42;
-- type: ALL, rows: 1843200, key: NULL

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

CREATE INDEX idx_author ON posts (author_id);
-- type: ref, rows: 312, key: idx_author

اختلاف بین ۱٫۸ میلیون ردیف و ۳۱۲ ردیف، همان چیزی است که کاربر به‌صورت بارگذاری سریع صفحه حس می‌کند. اگر روی وردپرس کار می‌کنید و صفحه‌های آرشیو نویسنده کند است، احتمالاً دقیقاً همین ایندکس را کم دارید.

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

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

CREATE INDEX idx_status_date ON orders (status, created_at);

این ایندکس برای WHERE status = 'paid' و برای WHERE status = 'paid' AND created_at > '2024-01-01' کار می‌کند. اما برای WHERE created_at > '2024-01-01' به‌تنهایی بی‌فایده است، چون ستون اول در شرط نیست. این‌جا اشتباه می‌کنند: ایندکس ترکیبی می‌سازند، کوئری را تست می‌کنند، می‌بینند کند است، و نتیجه می‌گیرند «ایندکس فایده ندارد». در واقع ایندکس هست، ترتیبش غلط است.

قاعده انتخاب ترتیب: ستونی که در شرط تساوی (=) می‌آید اول، ستونی که در بازه (>، <، BETWEEN) می‌آید بعد از آن. ستون‌های ORDER BY را هم می‌توان در انتها اضافه کرد تا مرتب‌سازی از روی ایندکس انجام شود و filesort حذف شود.

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

هر ایندکس اضافه، هر INSERT را کندتر می‌کند. روی یک جدول با پنج ایندکس، درج یک ردیف یعنی شش نوشتن روی دیسک به‌جای یکی. اگر جدول شما عمدتاً خوانده می‌شود (مثل posts یا products) این هزینه ناچیز است. اگر جدول نوشتن‌محور است (لاگ، نشست، صف پیام، جدول wp_options پرترافیک) هر ایندکس اضافه یک بدهی است.

در عمل روی یک جدول لاگ با ۲۰۰۰ درج در ثانیه، اضافه‌کردن یک ایندکس بی‌مصرف باعث شد نرخ درج به حدود ۱۴۰۰ برسد و صف بن‌بست بخورد. مشکل از کوئری خواندن نبود؛ از ایندکسی بود که کسی یادش رفته بود چرا ساخته شده.

پس تصمیم درست این است: ایندکس را برای کوئری‌هایی بسازید که واقعاً اجرا می‌شوند، نه برای هر ستونی که «شاید روزی لازم شود». ایندکس‌های بی‌استفاده را با sys.schema_unused_indexes پیدا کنید:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql','sys');

کِی ایندکس نسازیم

• روی ستون‌هایی با کاردینالیتی پایین مثل is_active که فقط دو مقدار دارد. ایندکس روی چنین ستونی معمولاً رد می‌شود چون بهینه‌ساز می‌فهمد خواندن ایندکس گران‌تر از اسکن است.
• روی جدول‌های کوچک زیر چند هزار ردیف. اسکن کامل این‌ها در حد میلی‌ثانیه است.
• روی ستونی که در WHERE داخل تابع پیچیده شده: WHERE YEAR(created_at) = 2024 ایندکس روی created_at را بی‌اثر می‌کند. شرط را به بازه بازنویسی کنید.
• روی جدول‌هایی که عملیات نوشتن غالب است و کوئری خواندنشان نادر است.

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

ایندکس پوششی (covering index) همه ستون‌های موردنیاز کوئری را در خود دارد، پس MySQL اصلاً به جدول اصلی مراجعه نمی‌کند. در EXPLAIN این را با Using index در ستون Extra می‌بینید. تفاوتش با حالت عادی می‌تواند چند برابر باشد، چون هر مراجعه به جدول یک خواندن تصادفی روی دیسک است.

CREATE INDEX idx_cover ON orders (status, created_at, total);
SELECT status, created_at, total FROM orders WHERE status = 'paid';

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

روش عملی: قبل و بعد را اندازه بگیرید

1. کوئری کند را از slow_query_log بردارید. مقدار long_query_time را روی ۱ ثانیه بگذارید و یک روز صبر کنید.
2. با EXPLAIN ببینید کدام مرحله گران است. اگر type=ALL بود، ایندکس کم است.
3. ایندکس را بسازید و همان کوئری را دوباره با EXPLAIN و EXPLAIN ANALYZE بسنجید.
4. زمان اجرا را با SHOW PROFILES یا لاگ کند مقایسه کنید. عدد قبل و بعد را یادداشت کنید؛ حافظه آدم ضعیف است.
5. یک هفته بعد با schema_unused_indexes چک کنید که ایندکس واقعاً استفاده شده باشد.

روی سرورهای اشتراکی، ساخت ایندکس روی جدول بزرگ قفل می‌گیرد و سایت را برای دقایقی از دسترس خارج می‌کند. اگر MySQL 5.6 یا بالاتر دارید، از ALTER TABLE ... ADD INDEX ... , ALGORITHM=INPLACE, LOCK=NONE استفاده کنید تا نوشتن متوقف نشود. نسخه‌های قدیمی‌تر این گزینه را ندارند و باید در بازه کم‌ترافیک کار کنید.

اگر روی وردپرس کار می‌کنید و نمی‌خواهید در ساعات اوج با قفل جدول درگیر شوید، اجرای این تغییرات روی هاست وردپرس با کنترل پنل و دسترسی مستقیم به MySQL کار را ساده‌تر می‌کند. برای کوئری‌های تکراری وردپرس، قبل از دست‌زدن به ساختار جدول، نگاهی به wp-cron وردپرس؛ چرا اجرا نمی‌شود و چطور با کرون واقعی جایگزینش کنیم بیندازید؛ گاهی کندی از کوئری نیست، از وظایف زمان‌بندی‌شده‌ای است که پشت سر هم اجرا می‌شوند.

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

پرسش‌های پرتکرار

آیا ایندکس دیتابیس سرعت نوشتن را کم می‌کند؟

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

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

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

چطور بفهمم یک ایندکس بی‌استفاده است؟

در MySQL 5.7 و بالاتر، جدول sys.schema_unused_indexes ایندکس‌هایی را نشان می‌دهد که از آخرین ری‌استارت سرور استفاده نشده‌اند. اگر ایندکسی هفته‌ها در این لیست بماند، احتمالاً فقط هزینه نوشتن دارد و می‌توانید حذفش کنید.

آیا ایندکس روی ستون‌های متنی بلند فایده دارد؟

روی VARCHAR بلند یا TEXT، ایندکس کامل حجیم و کند می‌شود. راه‌حل، ایندکس پیشوندی است: CREATE INDEX idx_title ON posts (title(20));. این روش برای جست‌وجوی LIKE 'foo%' کار می‌کند اما برای LIKE '%foo%' نه.

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