فصل ۵: ایندکس و کارایی — از B-Tree تا EXPLAIN ANALYZE

Slow Query Log و دام‌های رایجی که ایندکس را بی‌اثر می‌کنند

اول پیدا کنید کدام کوئری کند است

بهینه‌سازی بدون اندازه‌گیری، حدس است. Slow query log همه‌ی کوئری‌هایی را که بیش از یک آستانه طول کشیده‌اند ثبت می‌کند.

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;                 -- نیم ثانیه؛ اعشار مجاز است
SET PERSIST log_slow_extra = ON;                   -- 8.0.14+: ستون‌های اضافه مثل Rows_examined دقیق
SHOW VARIABLES LIKE 'slow_query_log_file';
# خلاصه‌سازی: کوئری‌های مشابه با پارامتر متفاوت یکی می‌شوند
sudo mysqldumpslow -s t -t 10 /var/lib/mysql/server-slow.log
# ابزار حرفه‌ای‌تر (Percona Toolkit)
pt-query-digest /var/lib/mysql/server-slow.log > digest.txt

بدون فایل لاگ هم می‌توانید از performance_schema استفاده کنید که آمار همه‌ی کوئری‌ها را از زمان راه‌اندازی جمع کرده است:

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

مرتب‌سازی بر اساس زمان کل مهم است: کوئری ۵۰ میلی‌ثانیه‌ای که روزی یک میلیون بار اجرا می‌شود از کوئری ۱۰ ثانیه‌ای روزی یک بار مهم‌تر است.

دام‌هایی که ایندکس را خاموش می‌کنند

الگوی بدچرابازنویسی
WHERE DATE(created_at) = '2025-03-21'تابع روی ستون؛ مقدار ایندکس‌شده دیگر مستقیم مقایسه نمی‌شودcreated_at >= '2025-03-21' AND created_at < '2025-03-22'
WHERE YEAR(created_at) = 2025همانبازه‌ی نیمه‌باز سال
WHERE mobile = 9121234567ستون VARCHAR با عدد مقایسه می‌شود؛ هر ردیف به عدد تبدیل می‌شودmobile = '09121234567'
WHERE price * 1.1 > 1000000محاسبه روی ستونprice > 1000000 / 1.1
WHERE title LIKE '%کاشان%'پیشوند نامعلومFULLTEXT (درس بعد)
WHERE a = 1 OR b = 2دو ستون مختلف؛ گاهی index_merge، اغلب ALLدو SELECT با UNION
ORDER BY RAND() LIMIT 5کل جدول مرتب می‌شودانتخاب تصادفی id در برنامه
-- OR روی دو ستون، بازنویسی با UNION (هر بخش ایندکس خودش را دارد)
SELECT id FROM customers WHERE mobile = '09121234567'
UNION
SELECT id FROM customers WHERE national_id = '1234567890';

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

  • عکس حالت تبدیل ضمنی بی‌خطر است: ستون عددی با رشته (id = '42') مشکلی ندارد، چون ثابت یک بار تبدیل می‌شود. مشکل فقط ستون متنی با ثابت عددی است — رایج در کد PHP و پایتونی که شماره موبایل را int می‌کند.
  • JOIN بین دو ستون با collation متفاوت (مثلاً یک جدول قدیمی utf8mb3_general_ci و جدول جدید utf8mb4_0900_ai_ci) همان اثر تابع روی ستون را دارد؛ EXPLAIN با type=ALL لو می‌دهد.
  • به‌جای تغییر کوئری‌های قدیمی با DATE(col) می‌توانید ایندکس تابعی ((DATE(created_at))) بسازید؛ عبارت کوئری باید دقیقاً با عبارت ایندکس یکی باشد.
  • log_queries_not_using_indexes را همراه با log_throttle_queries_not_using_indexes روشن کنید، وگرنه جدول‌های کوچکی که همیشه اسکن کامل می‌شوند لاگ را پر می‌کنند.
  • long_query_time = 0 برای چند دقیقه در زمان اوج، تصویری کامل از بار واقعی می‌دهد (همه‌ی کوئری‌ها لاگ می‌شوند)؛ فقط فضای دیسک را زیر نظر داشته باشید و فوراً برگردانید.
درس قبلی درس بعدی

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