Database Query Optimization در وردپرس چطور انجام می‌شود؟

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

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

سه سطح اصلی بهینه‌سازی وجود دارد: سطح کوئری، سطح اسکیما و سطح معماری داده.

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

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

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

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

Database Query Optimization (Query optimization) فرآیندی است که در آن، یک کوئری ناکارآمد به نسخه‌ای کارآمد تبدیل می‌شود. کارایی در این زمینه، ترکیبی از زمان اجرا، مصرف حافظه، تعداد ردیف‌های بررسی‌شده و بار تحمیلی روی منابع سرور است.

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

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

هدف بهینه‌سازی، کاهش هزینه هر یک از این چهار نوع است. هر کوئری که بهینه می‌شود، به‌طور مستقیم روی ظرفیت سرور اثر می‌گذارد. این اثر تجمعی است و در بازه‌های بلند به بهبود محسوس در معیارهایی مانند TTFB (Time To First Byte) منجر می‌شود که جزئیات آن در کاهش زمان TTFB آمده است.

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

نحوه کار بهینه‌ساز MySQL و تصمیم‌های داخلی

MySQL یک مؤلفه داخلی به نام بهینه‌ساز (Optimizer) دارد که وظیفه‌اش انتخاب مسیر اجرای هر کوئری است. این مؤلفه، از میان روش‌های مختلف دسترسی به داده، کارآمدترین گزینه را انتخاب می‌کند.

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

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

SHOW VARIABLES LIKE "optimizer_switch";

این دستور، وضعیت فعال بودن بهینه‌سازی‌های مختلف را نشان می‌دهد. پارامترهایی مانند index_merge، mrr و batched_key_access تعیین می‌کنند که بهینه‌ساز کدام تکنیک‌ها را فعال کند.

در بهینه‌سازی کوئری، درک رفتار بهینه‌ساز ضروری است. اگر کوئری به‌درستی نوشته شده اما بهینه‌ساز مسیر ناکارآمد را انتخاب می‌کند، ممکن است راه‌حل در بازنویسی کوئری یا در افزودن یک راهنما (Hint) باشد.

EXPLAIN FORMAT=JSON SELECT ... ;

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

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

EXPLAIN و خواندن دقیق برنامه اجرا

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

EXPLAIN SELECT p.ID, p.post_title
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = "featured"
  AND p.post_status = "publish"
ORDER BY p.post_date DESC
LIMIT 10;

خروجی این دستور، چند ستون کلیدی دارد. ستون id ترتیب مراحل اجرا را نشان می‌دهد. ستون select_type نوع هر مرحله (ساده، زیرکوئری، JOIN) را مشخص می‌کند. ستون table جدولی است که در آن مرحله بررسی می‌شود.

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

ستون possible_keys ایندکس‌های کاندید را نشان می‌دهد. ستون key ایندکس واقعاً استفاده‌شده را مشخص می‌کند. ستون key_len تعداد بایت‌های استفاده‌شده از ایندکس را نشان می‌دهد.

ستون rows تخمین بهینه‌ساز از تعداد ردیف‌های بررسی‌شده است. هدف بهینه‌سازی، کاهش این عدد است. ستون Extra اطلاعات تکمیلی ارائه می‌دهد. مقادیر Using index، Using where، Using temporary و Using filesort هر یک معنای مشخصی دارند.

مقدار Using filesort نشانه مرتب‌سازی خارج از ایندکس است که هزینه‌ای اضافه تحمیل می‌کند. اگر این مقدار در کوئری‌های پرتکرار ظاهر شود، افزودن ایندکس مناسب روی ستون مرتب‌سازی می‌تواند آن را حذف کند.

مقدار Using temporary نشانه ساخت جدول موقت است که در کوئری‌های GROUP BY و UNION رخ می‌دهد. این ساخت، هزینه قابل توجهی دارد و باید در حجم بالا اجتناب شود.

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

شکل کوئری و اثر آن بر عملکرد

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

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

-- Slow: function applied to indexed column
SELECT ID FROM wp_posts
WHERE DATE(post_date) = "2024-09-15";

-- Fast: range condition directly on indexed column
SELECT ID FROM wp_posts
WHERE post_date >= "2024-09-15 00:00:00"
  AND post_date < "2024-09-16 00:00:00";

در نمونه اول، تابع DATE() روی ستون post_date اعمال شده است. این کار، ایندکس را از کار می‌اندازد چون بهینه‌ساز نمی‌تواند مقادیر پیش‌محاسبه‌شده را با ایندکس مقایسه کند. در نمونه دوم، شرط بازه مستقیم روی ستون اعمال شده و ایندکس به‌طور کامل استفاده می‌شود.

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

-- Better: select only what you need
SELECT ID, post_title, post_date FROM wp_posts
WHERE post_status = "publish"
ORDER BY post_date DESC
LIMIT 10;

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

اصل سوم، پرهیز از عملگر LIKE با کاراکتر ابتدایی است. الگویی مانند LIKE "%keyword" مانع استفاده از ایندکس می‌شود، چون موتور باید همه ردیف‌ها را بررسی کند. الگوی LIKE "keyword%" می‌تواند از ایندکس استفاده کند.

JOIN ها و قواعد چیدمان آن‌ها

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

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

-- Starting from a small filtered set
SELECT p.ID, p.post_title, u.user_login
FROM wp_users u
INNER JOIN wp_posts p ON u.ID = p.post_author
WHERE u.user_login = "admin"
  AND p.post_status = "publish"
ORDER BY p.post_date DESC
LIMIT 10;

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

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

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

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

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

زیرکوئری در برابر JOIN در وردپرس

زیرکوئری یکی از ابزارهای کوئری نویسی است که در برخی سناریوها کارآمد و در برخی دیگر ناکارآمد است. تفاوت میان یک زیرکوئری همبسته و یک زیرکوئری غیرهمبسته، تفاوت میان یک کوئری سریع و یک کوئری کند است.

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

-- Non-correlated subquery (executed once)
SELECT ID, post_title FROM wp_posts
WHERE post_author IN (
    SELECT ID FROM wp_users WHERE user_status = 0
);

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

-- Correlated subquery (executed per row)
SELECT ID, post_title FROM wp_posts p
WHERE EXISTS (
    SELECT 1 FROM wp_postmeta pm
    WHERE pm.post_id = p.ID AND pm.meta_key = "featured"
);

در این نمونه، برای هر ردیف جدول wp_posts، یک بار جدول wp_postmeta بررسی می‌شود. در جدول با هزاران ردیف، این عملیات می‌تواند چند ثانیه طول بکشد.

تبدیل زیرکوئری همبسته به JOIN، معمولاً زمان اجرا را کاهش می‌دهد.

-- Rewritten as JOIN
SELECT DISTINCT p.ID, p.post_title
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = "featured";

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

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

-- Slow: subquery in SELECT clause
SELECT p.ID,
       (SELECT COUNT(*) FROM wp_comments c WHERE c.comment_post_ID = p.ID) AS comment_count
FROM wp_posts p
WHERE p.post_status = "publish";

راه‌حل استاندارد، تبدیل این نوع زیرکوئری به یک JOIN با GROUP BY است.

-- Faster: rewrite as JOIN with GROUP BY
SELECT p.ID, COUNT(c.comment_ID) AS comment_count
FROM wp_posts p
LEFT JOIN wp_comments c ON c.comment_post_ID = p.ID
WHERE p.post_status = "publish"
GROUP BY p.ID;

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

WP_Query و کوئری‌های تولیدشده توسط وردپرس

وردپرس از کلاس WP_Query برای تولید کوئری‌های بازیابی نوشته استفاده می‌کند. این کلاس، کوئری‌های پیچیده SQL را از پارامترهای ساده می‌سازد. شناخت نحوه کار این کلاس، پیش‌نیاز بهینه‌سازی کوئری‌های وردپرسی است.

هر نمونه از WP_Query، یک کوئری SQL تولید می‌کند که معمولاً شامل چند JOIN است. جدول اصلی wp_posts است و در کنار آن، جدول wp_postmeta و جداول تاکسونومی قرار می‌گیرند.

$args = array(
    "post_type"      => "post",
    "post_status"    => "publish",
    "posts_per_page" => 10,
    "meta_query"     => array(
        array(
            "key"     => "featured",
            "value"   => "yes",
            "compare" => "=",
        ),
    ),
);
$query = new WP_Query( $args );

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

سه نکته برای بهینه‌سازی WP_Query وجود دارد. نکته اول، استفاده از no_found_rows است وقتی نیازی به شمارش کل نتایج نیست. این پارامتر، کوئری گران SQL_CALC_FOUND_ROWS را حذف می‌کند.

$args = array(
    "post_type"      => "post",
    "posts_per_page" => 10,
    "no_found_rows"  => true,
);

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

$args = array(
    "post_type"      => "post",
    "posts_per_page" => 10,
    "fields"         => "ids",
);

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

نکته تکمیلی، فعال‌سازی حالت SAVEQUERIES در وردپرس است. این حالت، همه کوئری‌های اجراشده در طول درخواست را ثبت می‌کند. با تحلیل این داده، می‌توان کوئری‌های پرتکرار یا کند را شناسایی کرد. اصول تفصیلی این تحلیل در Slow Query Log در وردپرس آمده است.

هر پارامتر اضافه به WP_Query، یک JOIN یا شرط اضافه در SQL است؛ هزینه‌ای که در حجم بالا محسوس می‌شود.

تله postmeta و بازنویسی کوئری‌های چندویژگی

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

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

SELECT p.ID
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id AND pm1.meta_key = "price"    AND pm1.meta_value > 100
INNER JOIN wp_postmeta pm2 ON p.ID = pm2.post_id AND pm2.meta_key = "in_stock" AND pm2.meta_value = "yes"
INNER JOIN wp_postmeta pm3 ON p.ID = pm3.post_id AND pm3.meta_key = "brand"    AND pm3.meta_value = "acme"
WHERE p.post_type = "product"
ORDER BY p.post_date DESC
LIMIT 20;

در این کوئری، سه JOIN روی جدول wp_postmeta انجام می‌شود. در جدول با میلیون‌ها ردیف، این کوئری می‌تواند چند ثانیه طول بکشد.

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

SELECT p.ID FROM wp_posts p
WHERE p.post_type = "product"
  AND EXISTS (SELECT 1 FROM wp_postmeta pm1 WHERE pm1.post_id = p.ID AND pm1.meta_key = "price" AND pm1.meta_value > 100)
  AND EXISTS (SELECT 1 FROM wp_postmeta pm2 WHERE pm2.post_id = p.ID AND pm2.meta_key = "in_stock" AND pm2.meta_value = "yes")
  AND EXISTS (SELECT 1 FROM wp_postmeta pm3 WHERE pm3.post_id = p.ID AND pm3.meta_key = "brand" AND pm3.meta_value = "acme")
ORDER BY p.post_date DESC
LIMIT 20;

رویکرد دوم، افزودن ایندکس مرکب مناسب روی جدول wp_postmeta است. ایندکس روی (meta_key, meta_value(191)) می‌تواند زمان اجرای کوئری‌های تک‌ویژگی را کاهش دهد.

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

SELECT id, title FROM wp_wpk_products
WHERE price > 100 AND in_stock = 1 AND brand = "acme"
ORDER BY created_at DESC
LIMIT 20;

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

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

کوئری‌های ووکامرس و بار تحلیل‌محور

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

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

SELECT
    DATE(date_created) AS day,
    COUNT(*) AS orders,
    SUM(total_sales) AS revenue
FROM wp_wc_order_stats
WHERE date_created >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND status IN ("wc-completed","wc-processing")
GROUP BY DATE(date_created);

این کوئری نمونه‌ای از گزارش‌گیری است. سه نقطه بهینه‌سازی در آن وجود دارد. نقطه اول، اعمال تابع DATE() روی ستون date_created که ایندکس را از کار می‌اندازد. نقطه دوم، فقدان ایندکس مناسب روی ترکیب date_created و status. نقطه سوم، محاسبات تجمعی روی همه ردیف‌های بازه.

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

SELECT
    DATE(date_created) AS day,
    COUNT(*) AS orders,
    SUM(total_sales) AS revenue
FROM wp_wc_order_stats
WHERE date_created >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND date_created < CURDATE()
  AND status IN ("wc-completed","wc-processing")
GROUP BY DATE(date_created);

در این نسخه، شرط بازه مستقیم روی ستون ایندکس‌شده اعمال می‌شود و تابع DATE() تنها در بخش SELECT و GROUP BY باقی می‌ماند.

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

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

لایه کش و کاهش بار پایگاه داده

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

سه نوع کش در این زمینه اهمیت دارند. کش شیء (Object Cache) که نتایج کوئری‌های تکراری را در حافظه نگه می‌دارد. کش صفحه (Page Cache) که کل خروجی HTML را ذخیره می‌کند. کش لایه وب‌سرور که پیش از رسیدن درخواست به PHP پاسخ می‌دهد.

function wpk_get_featured_products() {
    $cache_key = "wpk_featured_products";
    $results = wp_cache_get( $cache_key, "wpk_products" );
    if ( false !== $results ) {
        return $results;
    }
    global $wpdb;
    $results = $wpdb->get_results( "SELECT ..." );
    wp_cache_set( $cache_key, $results, "wpk_products", 600 );
    return $results;
}

این الگو، نتیجه کوئری را در کش شیء نگه می‌دارد و از اجرای مکرر آن جلوگیری می‌کند. زمان کش ۶۰۰ ثانیه در این نمونه، بر اساس فراوانی تغییر داده تعیین می‌شود.

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

در لایه کش صفحه، افزونه‌های تخصصی مانند WP Rocket یا W3 Total Cache عملکرد مناسبی دارند. انتخاب و پیکربندی این افزونه‌ها موضوعی است که در بهترین افزونه‌های کش وردپرس آمده است.

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

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

بازطراحی اسکیما و نقش جدول اختصاصی

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

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

CREATE TABLE wp_wpk_products (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    in_stock TINYINT(1) NOT NULL DEFAULT 0,
    brand VARCHAR(100) NOT NULL DEFAULT "",
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_price_stock_brand (price, in_stock, brand)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

پایش، سنجش و اعتبارسنجی تغییرات

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

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

SET profiling = 1;
SELECT ... ;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

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

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

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

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

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

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

جدول تصمیم‌گیری الگوی بهینه‌سازی

الگوی کوئری	مشکل رایج	راه‌حل پیشنهادی
فیلتر تابعی روی ستون ایندکس‌شده	ایندکس از کار افتاده	تبدیل به شرط بازه مستقیم
زیرکوئری همبسته در SELECT	اجرای مکرر برای هر ردیف	تبدیل به JOIN با GROUP BY
چند JOIN متوالی روی postmeta	هزینه نمایی با افزایش ویژگی‌ها	انتقال به جدول اختصاصی
کوئری تحلیلی با GROUP BY روی حجم بالا	مصرف حافظه و زمان	کش نتیجه یا انتقال به پس‌زمینه
کوئری بدون ایندکس	اسکن کامل جدول	افزودن ایندکس مناسب
کوئری پرتکرار با نتیجه ثابت	بار تکراری پایگاه داده	کش کردن نتیجه
کوئری با SELECT *	انتقال داده اضافی	محدودسازی به ستون‌های لازم
کوئری با ORDER BY بدون ایندکس	مرتب‌سازی خارج از ایندکس	افزودن ایندکس روی ستون مرتب‌سازی

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

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

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

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

چهارمین اشتباه، بازنویسی کوئری بدون اندازه‌گیری پس از تغییر است. تفاوت میان یک بازنویسی مؤثر و یک بازنویسی خوش‌شانس، تنها با اندازه‌گیری مشخص می‌شود.

پنجمین اشتباه، استفاده بی‌مورد از SELECT * در کوئری‌های داخلی است. محدودسازی ستون‌ها، به‌ویژه در کوئری‌های پرتکرار، اثر مستقیم بر ظرفیت سرور دارد.

ششمین اشتباه، نادیده گرفتن ترتیب JOIN ها است. در برخی سناریوها، ترتیب JOIN انتخابی بهینه‌ساز کارآمد نیست و باید با راهنماهای (Hints) تغییر کند.

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

هشتمین اشتباه، نبود پاک‌سازی دوره‌ای داده‌های بی‌استفاده است. جدول‌هایی مانند wp_postmeta، wp_options و wp_comments در طول زمان با داده‌های بی‌استفاده پر می‌شوند و کارایی کوئری‌ها را کاهش می‌دهند. اصول این پاک‌سازی در پاک‌سازی اسپم و ترنزینت‌های وردپرس آمده است.

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

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

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

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

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

بهینه‌سازی کوئری در وردپرس از کجا باید شروع شود؟

از تحلیل کوئری‌های کند با EXPLAIN و از لاگ کوئری کند MySQL. بدون شناخت کوئری‌های مشکل‌دار، هر تغییری می‌تواند بی‌اثر یا حتی مضر باشد.

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

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

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

با فعال‌سازی Slow Query Log و تعیین آستانه زمانی مناسب. کوئری‌هایی که از آستانه عبور می‌کنند، در فایل لاگ ثبت می‌شوند. تحلیل این لاگ، کوئری‌های مشکل‌دار را مشخص می‌کند.

EXPLAIN چه اطلاعاتی ارائه می‌دهد؟

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

چرا برخی کوئری‌ها در وردپرس به‌طور ذاتی کند هستند؟

سه علت اصلی وجود دارد. ناکارآمدی ساختار key-value جدول wp_postmeta. پیچیدگی کوئری‌های تولیدشده توسط WP_Query با پارامترهای متعدد. نبود ایندکس مناسب روی جداول با حجم بالا.

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

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

چگونه کوئری‌های تولیدشده توسط WP_Query را بهینه کنم؟

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

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

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

چگونه اثر بهینه‌سازی را اندازه بگیرم؟

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

آیا بهینه‌سازی کوئری روی سئو اثر دارد؟

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

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

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

چگونه کوئری‌های postmeta را بازنویسی کنم؟

سه رویکرد اصلی وجود دارد. تبدیل JOIN به EXISTS در برخی نسخه‌های MySQL. افزودن ایندکس مرکب روی (meta_key, meta_value). انتقال ویژگی‌های پرتکرار به جدول اختصاصی با ایندکس مناسب.

آیا MySQL 8 بهینه‌سازی بهتری ارائه می‌دهد؟

بله، نسخه‌های جدیدتر MySQL بهینه‌سازهای پیشرفته‌تری دارند و از قابلیت‌هایی مانند Common Table Expressions پشتیبانی می‌کنند. به‌روزرسانی نسخه، بخشی از مسیر بهینه‌سازی است.

آیا بهینه‌سازی کوئری برای سایت‌های کوچک هم لازم است؟

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

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

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

آیا بهینه‌سازی کوئری روی سرعت نوشتن اثر دارد؟

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

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

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

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