Index Optimization در وردپرس چرا این‌قدر مهم است؟

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

بدون ایندکس مناسب، هر کوئری باید جدول را خط به خط اسکن کند و همین اسکن در حجم بالا به گلوگاه اصلی تبدیل می‌شود.

وردپرس به‌طور پیش‌فرض مجموعه‌ای از ایندکس‌های پایه دارد، اما این ایندکس‌ها برای نیازهای افزونه‌ها و کوئری‌های سفارشی کافی نیستند.

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

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

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

ایندکس در پایگاه داده دقیقاً چه چیزی است

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

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

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

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

ایندکس یک معاوضه است: زمان نوشتن در برابر زمان خواندن؛ تصمیم درست بستگی به نسبت این دو در الگوی واقعی سایت دارد.

ایندکس‌های پیش‌فرض وردپرس و مرزهای آن‌ها

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

در جدول wp_posts چند ایندکس کلیدی وجود دارد. ایندکس روی ستون post_name که برای جستجوی نوشته بر اساس slug استفاده می‌شود. ایندکس مرکب روی post_type و post_status و post_date که برای آرشیوهای نوشته به‌کار می‌رود. ایندکس روی post_author که برای آرشیو نویسنده استفاده می‌شود.

SHOW INDEX FROM wp_posts;

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

در جدول wp_postmeta، تنها یک ایندکس مرکب روی post_id و meta_key وجود دارد. این ایندکس برای بازیابی متادیتای یک نوشته مشخص کارآمد است، اما برای کوئری‌هایی که بر اساس meta_value فیلتر می‌کنند، کارایی محدودی دارد. این محدودیت، یکی از پرتکرارترین علل کندی سایت‌های وردپرسی است.

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

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

EXPLAIN و خواندن درست برنامه اجرای کوئری

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

EXPLAIN SELECT ID, post_title
FROM wp_posts
WHERE post_type = "post"
  AND post_status = "publish"
ORDER BY post_date DESC
LIMIT 10;

خروجی این دستور، ستون‌های متعددی دارد که هر یک اطلاعات مشخصی ارائه می‌دهد. ستون type روش دسترسی به داده را نشان می‌دهد. مقادیر ALL، index، range، ref، eq_ref و const به ترتیب از کم‌کارایی‌ترین به کارآمدترین مرتب شده‌اند.

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

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

ستون Extra اطلاعات تکمیلی ارائه می‌دهد. مقدار Using index نشان می‌دهد که کوئری صرفاً از ایندکس پاسخ گرفته و به جدول اصلی مراجعه نکرده است. مقدار Using filesort نشان می‌دهد موتور پایگاه داده مجبور به مرتب‌سازی خارج از ایندکس شده است که یک نقطه ضعف است. مقدار Using temporary نشانه ساخت جدول موقت است که در کوئری‌های پیچیده رخ می‌دهد.

ترکیب این ستون‌ها تصویر روشنی از عملکرد کوئری ارائه می‌دهد. تمرین مستمر با EXPLAIN روی کوئری‌های پرتکرار، یکی از مؤثرترین راه‌ها برای شناسایی فرصت‌های بهینه‌سازی است. اصول تفصیلی این موضوع در بهینه‌سازی کوئری‌های MySQL آمده است.

EXPLAIN FORMAT=JSON SELECT ... ;

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

بدون تحلیل EXPLAIN، هر تصمیم ایندکس‌گذاری یک حدس است؛ و حدس در حجم بالا هزینه دارد.

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

چند نوع ایندکس در MySQL وجود دارد که هر یک برای سناریوی مشخصی طراحی شده است. انتخاب نوع درست، بخشی از تصمیم بهینه‌سازی است.

ایندکس اصلی (PRIMARY KEY) روی ستون یکتا تعریف می‌شود و ردیف‌ها بر اساس آن مرتب می‌شوند. در وردپرس، جدول wp_posts ایندکس اصلی روی ID دارد. ایندکس اصلی در موتور InnoDB به‌صورت خوشه‌ای پیاده‌سازی می‌شود، یعنی داده اصلی در برگ‌های همین ایندکس ذخیره می‌گردد.

ایندکس یکتا (UNIQUE) مشابه ایندکس اصلی است، اما محدودیت یکتایی را روی ستون اعمال می‌کند. در وردپرس، ستون user_login در جدول wp_users یکتا است.

ایندکس معمولی (INDEX یا KEY) امکان جستجو سریع را بدون محدودیت یکتایی فراهم می‌کند. بیشتر ایندکس‌های وردپرس از این نوع هستند.

ایندکس مرکب (COMPOSITE) روی ترکیب دو یا چند ستون تعریف می‌شود. این نوع ایندکس، برای کوئری‌هایی که همزمان بر اساس چند ستون فیلتر می‌کنند، کارآمد است. ترتیب ستون‌ها در این نوع ایندکس اهمیت حیاتی دارد.

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

ایندکس متن کامل (FULLTEXT) برای جستجوی متنی طراحی شده است. این نوع ایندکس، امکان جستجوی کلمه یا عبارت در ستون‌های متنی را فراهم می‌کند و از عملگر MATCH ... AGAINST برای جستجو استفاده می‌شود.

ایندکس فضایی (SPATIAL) برای داده‌های جغرافیایی طراحی شده است. در وردپرس، اگر افزونه‌ای از داده‌های مکانی استفاده کند، این نوع ایندکس کاربرد دارد.

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

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

ایندکس مرکب یکی از مؤثرترین ابزارهای بهینه‌سازی کوئری‌های چندویژگی است، اما تنها در صورت انتخاب درست ترتیب ستون‌ها کارآمد می‌ماند.

قاعده اصلی در ایندکس مرکب، قاعده پیشوند چپ (Left-Prefix Rule) است. بر اساس این قاعده، یک ایندکس مرکب روی (A, B, C) می‌تواند برای کوئری‌هایی که بر اساس A، ترکیب (A, B) یا ترکیب (A, B, C) فیلتر می‌کنند، استفاده شود. اما برای کوئری‌هایی که تنها بر اساس B یا C یا ترکیب (B, C) فیلتر می‌کنند، این ایندکس بی‌استفاده است.

-- Composite index on (post_type, post_status, post_date)
CREATE INDEX idx_type_status_date ON wp_posts (post_type, post_status, post_date);

-- This query uses the index efficiently
SELECT ID FROM wp_posts
WHERE post_type = "post" AND post_status = "publish"
ORDER BY post_date DESC;

-- This query does NOT use the index efficiently
SELECT ID FROM wp_posts
WHERE post_status = "publish";

در طراحی ایندکس مرکب، سه قاعده عملی کمک‌کننده است. قاعده اول، ستون‌های با بالاترین Selectivity را در ابتدا قرار دهید. قاعده دوم، ستون‌هایی که در شرط تساوی (=) استفاده می‌شوند را پیش از ستون‌های شرط بازه (>، <) قرار دهید. قاعده سوم، ستون‌های استفاده‌شده در مرتب‌سازی (ORDER BY) را در انتهای ایندکس قرار دهید.

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

در وردپرس، ایندکس مرکب روی جدول wp_posts یکی از پرتکرارترین نیازها است. جدول wp_posts با ایندکس پیش‌فرض روی (post_type, post_status, post_date) ساخته می‌شود، اما در برخی افزونه‌ها نیاز به ایندکس‌های اضافی روی post_author یا post_parent وجود دارد.

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

ایندکس‌گذاری روی postmeta و usermeta

جدول wp_postmeta یکی از پرکاربردترین و در عین حال چالش‌برانگیزترین جداول در وردپرس است. ساختار key-value این جدول، ایندکس‌گذاری را پیچیده می‌کند.

ایندکس پیش‌فرض این جدول روی ترکیب (post_id, meta_key) است. این ایندکس برای بازیابی متادیتای یک نوشته مشخص کارآمد است، اما برای کوئری‌هایی که بر اساس meta_key یا meta_value فیلتر می‌کنند، محدودیت دارد.

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

CREATE INDEX idx_meta_key ON wp_postmeta (meta_key);

راهبرد دوم، افزودن ایندکس مرکب روی (meta_key, meta_value) است. این ایندکس برای کوئری‌هایی که بر اساس یک ویژگی و مقدار آن فیلتر می‌کنند، کارآمد است. اما باید توجه داشت که نوع ستون meta_value از نوع LONGTEXT است و ایندکس‌گذاری روی آن فضای قابل توجهی مصرف می‌کند.

CREATE INDEX idx_meta_kv ON wp_postmeta (meta_key, meta_value(191));

در این نمونه، طول ایندکس به ۱۹۱ کاراکتر محدود شده است که یک رویه استاندارد برای ستون‌های متنی طولانی است. این محدودیت، از مصرف بی‌رویه فضا جلوگیری می‌کند.

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

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

تعادل درست، ایندکس‌گذاری هدفمند است. تنها ویژگی‌هایی که به‌طور مکرر در کوئری‌ها فیلتر می‌شوند، ارزش ایندکس‌گذاری دارند. ویژگی‌هایی که به‌ندرت استفاده می‌شوند، بدون ایندکس باقی می‌مانند.

ایندکس‌گذاری در جداول اختصاصی

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

سه دسته ایندکس در جدول اختصاصی وجود دارد. دسته اول، ایندکس‌های فیلتر که روی ستون‌های پرتکرار در شرط WHERE تعریف می‌شوند. دسته دوم، ایندکس‌های مرتب‌سازی که روی ستون‌های ORDER BY تعریف می‌شوند. دسته سوم، ایندکس‌های مرکب که ترکیب دو یا چند ستون را پوشش می‌دهند.

CREATE TABLE wp_wpk_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    event_type VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_user_created (user_id, created_at),
    KEY idx_type_created (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

مسئله دیگر، ایندکس‌گذاری روی ستون‌های با Selectivity پایین است. اگر ستونی تنها دو یا سه مقدار ممکن داشته باشد (مانند ستون in_stock با مقادیر صفر و یک)، ایندکس اختصاصی روی آن کارآمد نیست. در چنین حالتی، ترکیب آن ستون با ستونی با Selectivity بالا، ایندکس مرکب معنا داری ایجاد می‌کند.

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

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

Cardinality، Selectivity و انتخاب ترتیب ستون‌ها

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

ستونی با Cardinality بالا، مقادیر یگانه زیادی دارد. مثلاً ستون ID در جدول کاربران، Cardinality برابر با تعداد کاربران است. ستونی با Cardinality پایین، مقادیر تکراری زیادی دارد. مثلاً ستون post_status در جدول نوشته‌ها معمولاً تنها چند مقدار ممکن دارد.

Selectivity یک ستون، در تصمیم ایندکس‌گذاری بسیار مهم است. ستون با Selectivity بالا (نزدیک به ۱)، کاندید خوبی برای ایندکس است. ستون با Selectivity پایین (نزدیک به صفر)، کاندید ضعیفی است و ایندکس روی آن کارآمد نیست.

SELECT
    COUNT(DISTINCT post_status) AS status_cardinality,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT post_status) / COUNT(*) AS status_selectivity
FROM wp_posts;

این کوئری، Cardinality و Selectivity یک ستون را محاسبه می‌کند. مقدار نزدیک به ۱ نشان‌دهنده Selectivity بالا است.

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

قاعده دیگر، استفاده از ستون‌های شرط تساوی پیش از ستون‌های شرط بازه است. اگر کوئری همزمان روی ستون user_id با شرط تساوی و ستون created_at با شرط بازه فیلتر می‌کند، ترتیب درست (user_id, created_at) است، نه برعکس.

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

مسئله تکمیلی، رفتار ایندکس در زمان به‌روزرسانی داده است. ستون‌هایی که به‌سرعت تغییر می‌کنند (مانند updated_at)، ایندکس‌های بیشتری را نامعتبر می‌کنند و هزینه نگهداری را بالا می‌برند. اگر نیاز به ایندکس روی چنین ستونی نیست، بهتر است از آن صرف‌نظر شود.

Covering Index و حذف مراجعه به جدول اصلی

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

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

-- Query
SELECT user_id, created_at FROM wp_wpk_events
WHERE event_type = "login"
ORDER BY created_at DESC
LIMIT 20;

-- Covering index
CREATE INDEX idx_type_created_user ON wp_wpk_events (event_type, created_at, user_id);

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

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

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

راهبرد متعادل، ایندکس پوششی برای کوئری‌های بسیار پرتکرار و ایندکس معمولی برای بقیه کوئری‌ها است. این رویکرد، بهره‌وری را در نقاط بحرانی حفظ می‌کند و هزینه را در نقاط غیربحرانی کاهش می‌دهد.

FULLTEXT و جستجوی متنی در وردپرس

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

ALTER TABLE wp_posts ADD FULLTEXT INDEX idx_ft_content (post_title, post_content);

پس از افزودن این ایندکس، جستجو با عملگر MATCH ... AGAINST انجام می‌شود.

SELECT ID, post_title
FROM wp_posts
WHERE MATCH (post_title, post_content) AGAINST ("wordpress optimization" IN NATURAL LANGUAGE MODE)
  AND post_status = "publish"
LIMIT 20;

این کوئری، از ایندکس متن کامل برای جستجو استفاده می‌کند و زمان اجرای آن در حجم بالا چند مرتبه کمتر از جستجوی LIKE است.

دو حالت جستجو در ایندکس متن کامل وجود دارد. حالت اول، NATURAL LANGUAGE MODE است که جستجو را بر اساس قواعد زبان طبیعی انجام می‌دهد. حالت دوم، BOOLEAN MODE است که امکان جستجوی دقیق‌تر با عملگرهای بولین را فراهم می‌کند.

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

[mysqld]
ft_min_word_len = 3
innodb_ft_min_token_size = 3

تغییر این مقدار نیازمند بازسازی ایندکس‌های متن کامل موجود است. بدون بازسازی، تغییر تنظیمات اثر خود را نشان نمی‌دهد.

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

در برخی پروژه‌ها، استفاده از موتورهای جستجوی تخصصی مانند Elasticsearch یا Meilisearch، عملکرد بهتری برای جستجوی فارسی ارائه می‌دهد. اما این ابزارها پیچیدگی زیرساخت اضافه می‌کنند. اصول کلی این تصمیم‌گیری در پایگاه داده مناسب برای بک‌اند آمده است.

آمار جدول و اثر ANALYZE TABLE

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

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

ANALYZE TABLE wp_posts, wp_postmeta, wp_options;

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

در سایت‌های وردپرسی، جدول‌های wp_posts، wp_postmeta و wp_options بیشترین تغییرات را دارند و بیشترین نیاز به تحلیل دوره‌ای را دارند. جدول wp_users در سایت‌های معمولی تغییرات کمتری دارد.

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

در پیکربندی MySQL، پارامتر innodb_stats_persistent تعیین می‌کند که آمار به‌صورت پایدار ذخیره شود یا در هر بار راه‌اندازی موتور بازسازی شود. تنظیم این پارامتر روی مقدار ON توصیه می‌شود، چون از محاسبه مجدد آمار در زمان راه‌اندازی جلوگیری می‌کند و پایداری انتخاب ایندکس را بهبود می‌بخشد.

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

آمار کهنه، تصمیم‌های کهنه می‌سازد؛ و تصمیم کهنه در پایگاه داده، کوئری کند تولید می‌کند.

ایندکس‌گذاری در فروشگاه ووکامرس

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

جداول اصلی ووکامرس شامل wp_posts برای محصولات و سفارش‌ها، wp_postmeta برای ویژگی‌های اضافی و جداول wp_wc_* برای گزارش‌ها است. هر یک از این جداول نیاز ایندکس‌گذاری مختص خود را دارد.

در جدول wp_posts، ایندکس پیش‌فرض وردپرس برای بیشتر کوئری‌های ووکامرس کافی است. اما برخی افزونه‌ها ممکن است نیاز به ایندکس اضافی روی post_parent (برای محصولات متغیر) یا post_excerpt داشته باشند.

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

CREATE INDEX idx_meta_price ON wp_postmeta (meta_key, meta_value(20))
WHERE meta_key = "_price";

این نوع ایندکس که با نام Partial Index شناخته می‌شود، تنها بخشی از ردیف‌های جدول را ایندکس می‌کند. مزیت آن، حجم کم و کارایی بالا در مقایسه با ایندکس کامل است. متأسفانه MySQL از Partial Index پشتیبانی نمی‌کند و این تکنیک تنها در موتورهایی مانند PostgreSQL در دسترس است. در MySQL، معادل نزدیک این تکنیک، ایندکس مرکب با meta_key در ابتدا است.

در جداول wp_wc_*، ایندکس‌گذاری بخشی از طراحی اصلی ووکامرس است. جدول wp_wc_order_stats ایندکس روی date_created و status دارد. جدول wp_wc_order_product_lookup ایندکس‌های متعدد برای گزارش‌گیری دارد.

مسئله مهم در فروشگاه‌های بزرگ، حجم جدول wp_postmeta است. در فروشگاهی با صد هزار محصول و بیست ویژگی برای هر محصول، این جدول می‌تواند دو میلیون ردیف داشته باشد. در این حجم، ایندکس‌گذاری دقیق ضروری است.

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

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

پایش و اندازه‌گیری عملکرد ایندکس

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

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

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = "wordpress"
ORDER BY COUNT_READ DESC;

این کوئری، آمار استفاده از هر ایندکس را نشان می‌دهد. ایندکس‌هایی که COUNT_READ آن‌ها پایین است، کاندید حذف هستند. ایندکس‌هایی که COUNT_INSERT یا COUNT_UPDATE بالایی دارند اما COUNT_READ پایین، هزینه‌ای هستند بدون بازدهی.

ابزار دوم، slow_query_log است که کوئری‌های کند را ثبت می‌کند. تحلیل این لاگ، کوئری‌هایی که به بهینه‌سازی نیاز دارند را نشان می‌دهد.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

پارامتر log_queries_not_using_indexes به‌طور خاص مفید است، چون کوئری‌هایی که از ایندکس استفاده نمی‌کنند را ثبت می‌کند. این کوئری‌ها کاندید اصلی بهینه‌سازی هستند.

ابزار سوم، ابزارهای پایش سطح سرور مانند Percona Toolkit است که مجموعه‌ای از دستورات برای تحلیل عملکرد پایگاه داده فراهم می‌کند. دستور pt-index-usage یکی از این ابزارهاست که تحلیل عمیقی از استفاده از ایندکس‌ها ارائه می‌دهد.

pt-index-usage /var/log/mysql/slow.log --host=localhost --user=root

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

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

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

جدول تصمیم‌گیری: ایندکس یا بدون ایندکس

اشتباهات رایج

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

دومین اشتباه، نادیده گرفتن ترتیب ستون‌ها در ایندکس مرکب است. ایندکس مرکب با ترتیب اشتباه، عملاً بی‌استفاده می‌شود و تنها هزینه نگهداری تحمیل می‌کند.

سومین اشتباه، ایندکس‌گذاری روی ستون‌های با Selectivity پایین است. ستون‌هایی که تعداد مقادیر یگانه آن‌ها پایین است، از ایندکس سود کمی می‌برند. در چنین حالتی، ترکیب آن‌ها با ستون‌های با Selectivity بالا معنا دارد.

چهارمین اشتباه، فراموش‌کردن ANALYZE TABLE دوره‌ای است. آمار قدیمی جدول، به انتخاب نادرست ایندکس منجر می‌شود و در نتیجه کوئری‌ها کندتر اجرا می‌شوند.

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

ششمین اشتباه، نبود پایش استفاده از ایندکس‌هاست. اگر ایندکسی هرگز استفاده نشود، نگه‌داشتن آن هزینه‌ای است بدون بازدهی. پایش دوره‌ای، این ایندکس‌های بی‌استفاده را شناسایی می‌کند.

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

هشتمین اشتباه، نبود استراتژی برای ایندکس‌های جدول wp_postmeta است. این جدول با ساختار key-value، ایندکس‌گذاری متفاوتی نیاز دارد. ایندکس‌گذاری سطحی، اثر محدودی دارد.

نهمین اشتباه، نادیده گرفتن تفاوت موتورهای ذخیره‌سازی است. ایندکس در InnoDB و MyISAM رفتار متفاوتی دارد. اصول این تفاوت در تفاوت InnoDB و MyISAM آمده است.

دهمین اشتباه، نبود تحلیل EXPLAIN پیش از افزودن ایندکس است. بدون تحلیل، ممکن است ایندکسی اضافه شود که کوئری‌ها از آن استفاده نمی‌کنند.

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

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

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

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

ایندکس چیست و چه تفاوتی با کلید اصلی دارد؟

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

چرا وردپرس به‌طور پیش‌فرض ایندکس‌های کافی ندارد؟

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

چگونه بفهمم ایندکس به کوئری کمک می‌کند؟

با تحلیل EXPLAIN. اگر در خروجی، ستون type مقدار ref، range یا eq_ref داشته باشد و ستون rows عدد کوچکی باشد، ایندکس کارآمد است. اگر مقدار type برابر ALL باشد، ایندکس استفاده نشده است.

آیا ایندکس‌گذاری روی همه ستون‌ها ایده خوبی است؟

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

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

ایندکس مرکب روی ترکیب دو یا چند ستون تعریف می‌شود و برای کوئری‌هایی که همزمان بر اساس چند ستون فیلتر می‌کنند، کارآمد است. ترتیب ستون‌ها در این ایندکس اهمیت حیاتی دارد.

چرا ایندکس روی postmeta پیچیده است؟

ساختار key-value این جدول، ایندکس‌گذاری را پیچیده می‌کند. ایندکس پیش‌فرض روی (post_id, meta_key) برای بازیابی متادیتای یک نوشته کارآمد است، اما برای فیلتر بر اساس meta_value محدودیت دارد. راه‌حل، ایندکس مرکب روی (meta_key, meta_value) با محدودیت طول است.

ANALYZE TABLE چه زمانی باید اجرا شود؟

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

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

با کوئری از جدول performance_schema.table_io_waits_summary_by_index_usage. ایندکس‌هایی که COUNT_READ پایینی دارند و در عین حال COUNT_INSERT و COUNT_UPDATE بالایی دارند، کاندید حذف هستند.

آیا ایندکس روی سئو اثر دارد؟

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

آیا افزودن ایندکس به جدول کند است؟

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

آیا FULLTEXT INDEX برای جستجوی فارسی مناسب است؟

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

آیا ایندکس‌گذاری روی ستون‌های با Selectivity پایین مفید است؟

خیر، در اکثر موارد. اگر ستونی تنها چند مقدار ممکن داشته باشد، ایندکس اختصاصی کارایی کمی دارد. راه‌حل، ترکیب آن ستون با ستون دیگر در ایندکس مرکب است.

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

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

آیا حذف ایندکس بی‌استفاده به سرعت سایت کمک می‌کند؟

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

آیا بهینه‌سازی ایندکس جایگزین بهینه‌سازی کوئری است؟

خیر. ایندکس‌گذاری و بهینه‌سازی کوئری دو رویکرد مکمل هستند. یک کوئری ناکارآمد حتی با بهترین ایندکس هم کند می‌ماند. بهترین نتیجه با ترکیب این دو رویکرد حاصل می‌شود.

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

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

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