از بهینه‌ساز بپرسید چه نقشه‌ای دارد
EXPLAIN نقشه‌ی اجرای کوئری را بدون اجرای آن نشان می‌دهد. EXPLAIN ANALYZE (از 8.0.18) کوئری را واقعاً اجرا می‌کند و زمان و تعداد ردیف واقعی هر مرحله را کنار تخمین‌ها می‌گذارد. هر بهینه‌سازی باید با یکی از این دو شروع و با آن تأیید شود؛ حدس زدن کافی نیست.
EXPLAIN
SELECT o.id, o.total_amount, c.full_name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid' AND o.created_at >= '2025-03-21'
ORDER BY o.created_at DESC LIMIT 50;ستون‌های مهم
| ستون | معنا |
|---|---|
type | روش دسترسی؛ مهم‌ترین ستون (جدول بعدی) |
possible_keys / key | ایندکس‌های قابل استفاده و ایندکسی که انتخاب شد |
key_len | چند بایت از ایندکس استفاده شد؛ نشان می‌دهد چند ستون از ایندکس ترکیبی به کار رفته |
rows | تخمین ردیف‌هایی که باید بررسی شوند |
filtered | درصد تخمینی ردیف‌هایی که از شرط‌های باقی‌مانده عبور می‌کنند |
Extra | جزئیات: Using index، Using where، Using filesort، Using temporary |
نردبان type، از بهترین تا بدترین
| type | یعنی |
|---|---|
const | حداکثر یک ردیف با کلید اصلی یا UNIQUE |
eq_ref | در JOIN، برای هر ردیف یک ردیف با کلید یکتا |
ref | جست‌وجوی برابری روی ایندکس غیریکتا |
range | پیمایش بازه‌ای از ایندکس |
index | پیمایش کل ایندکس (بهتر از ALL ولی باز هم کامل) |
ALL | پیمایش کل جدول؛ روی جدول بزرگ زنگ خطر |
در Extra، Using filesort یعنی مرتب‌سازی جداگانه (نه لزوماً روی دیسک) و Using temporary یعنی جدول موقت؛ هر دو روی نتیجه‌های بزرگ گران‌اند و معمولاً با ایندکسی که ترتیب مورد نیاز را دارد حذف می‌شوند.
EXPLAIN ANALYZE و قالب درختی
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*) FROM orders
WHERE created_at >= '2025-01-01' GROUP BY customer_id\G
-- خروجی (خلاصه):
-- -> Table scan on <temporary> (actual time=45.2..46.0 rows=2870 loops=1)
-- -> Aggregate using temporary table (actual time=45.1..45.1 rows=2870 loops=1)
-- -> Index range scan on orders using ix_created
-- (cost=6010 rows=30020) (actual time=0.1..35.7 rows=28750 loops=1)درخت را از داخلی‌ترین (پایین‌ترین) سطر به بیرون بخوانید. actual time=a..b زمان اولین ردیف و همه‌ی ردیف‌ها به میلی‌ثانیه است و loops تعداد دفعات اجرای آن گره؛ زمان کل گره تقریباً b × loops است. اختلاف بزرگ بین rows تخمینی و واقعی یعنی آمار غلط است و بهینه‌ساز تصمیم اشتباه گرفته.
نکته‌هایی که کمتر کسی می‌داند
- EXPLAIN ANALYZE کوئری را کامل اجرا می‌کند؛ روی کوئری ده‌دقیقه‌ای، ده دقیقه منتظر می‌مانید. روی سرور تولید در ساعت شلوغ مراقب باشید.
- بعد از
EXPLAINمعمولی،SHOW WARNINGS;کوئری بازنویسی‌شده توسط بهینه‌ساز را نشان می‌دهد؛ می‌بینید زیرکوئری شما به semi-join تبدیل شده یا نه. EXPLAIN FOR CONNECTION 1234;نقشه‌ی کوئری در حال اجرای یک اتصال دیگر را (شماره از SHOW PROCESSLIST) نشان می‌دهد؛ برای کوئری گیرکرده‌ی تولید بی‌نظیر است.- برای فهم «چرا این ایندکس انتخاب نشد»، optimizer trace را روشن کنید:
SET optimizer_trace='enabled=on';، کوئری را اجرا وSELECT * FROM information_schema.optimizer_trace\Gرا بخوانید. - اگر آمار ستون‌های بدون ایندکس غلط است، هیستوگرام بسازید:
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;؛ تخمین filtered دقیق‌تر می‌شود بدون هزینه‌ی نگه‌داری ایندکس.