بهینه‌سازی Query و EXPLAIN

۵.۱ مقدمه: چرا Query‌های شما کند هستند؟

بعد از طراحی صحیح Schema و ایندکس، گام بعد تحلیل و بهینه‌سازی Query‌های واقعی است. در این فصل ابزارهای لازم را یاد می‌گیریم: EXPLAIN، Slow Query Log، Profiling و الگوهای رایج بازنویسی.

۵.۲ EXPLAIN – مهم‌ترین ابزار شما

EXPLAIN به شما نشان می‌دهد MySQL چگونه قرار است Query را اجرا کند. استفاده‌اش ساده است:

SQL📋 کپی
EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'paid';

-- نسخه گرافیکی JSON (دقیق‌تر)
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 5;

-- MySQL 8.0+: اجرای واقعی + اندازه‌گیری
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;

ستون‌های مهم خروجی EXPLAIN

ستون type – از بهترین به بدترین

ستون Extra – علائم خطر

• Using index: ✅ Covering Index (عالی!)
• Using where: 👌 فیلتر بعد از خواندن
• Using filesort: ⚠️ مرتب‌سازی در حافظه/دیسک (کند)
• Using temporary: ⚠️ نیاز به جدول موقت
• Using join buffer: 🚨 JOIN بدون ایندکس

۵.۳ مثال‌های عملی EXPLAIN

SQL📋 کپی
-- مثال ۱: Full Scan فاجعه‌بار
EXPLAIN SELECT * FROM orders WHERE total > 1000000;
-- type: ALL, rows: 5000000  ❌
-- راه‌حل: CREATE INDEX idx_total ON orders (total);

-- مثال ۲: ایندکس کار می‌کند
EXPLAIN SELECT * FROM orders WHERE user_id = 5;
-- type: ref, rows: 12  ✅

-- مثال ۳: ORDER BY بدون ایندکس
EXPLAIN SELECT * FROM orders ORDER BY total DESC LIMIT 10;
-- Extra: Using filesort  ⚠️
-- راه‌حل: CREATE INDEX idx_total ON orders (total);

-- مثال ۴: Covering Index
EXPLAIN SELECT user_id, status FROM orders WHERE created_at > '2026-01-01';
-- اگر INDEX idx_date_covering(created_at, user_id, status) باشد:
-- Extra: Using index ✅✅✅

-- مثال ۵: JOIN بدون ایندکس روی FK
EXPLAIN SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id;
-- اگر u.id ایندکس داشته باشد (که PK دارد): eq_ref ✅
-- اگر o.user_id ایندکس نداشته باشد در WHERE: مشکلی نیست

۵.۴ Slow Query Log – کشف Query‌های کند

قبل از بهینه‌سازی، باید بدانید کدام Query کند است:

my.cnf📋 کپی
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0                  # query‌های بیش از 1 ثانیه
log_queries_not_using_indexes = 1       # query‌های بدون ایندکس
log_slow_admin_statements = 1
min_examined_row_limit = 100

SQL - فعال‌سازی runtime📋 کپی
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log_file = '/tmp/slow.log';

تحلیل با pt-query-digest

Bash📋 کپی
# نصب Percona Toolkit
sudo apt install percona-toolkit -y

# تحلیل
pt-query-digest /var/log/mysql/slow.log

# خروجی نمونه:
# Rank Query ID    Response time  Calls  R/Call  Item
# # 1  0xABCD...   45.2s   38.5%   100    0.452   SELECT orders WHERE...
# # 2  0xEF12...   23.1s   19.7%   500    0.046   SELECT products...

# مرتب‌سازی بر اساس بدترین‌ها (Top 10)
pt-query-digest --limit 10 /var/log/mysql/slow.log

۵.۵ JOIN‌های پیشرفته

انواع JOIN

SQL📋 کپی
-- INNER JOIN: فقط ردیف‌های منطبق
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN: همه از جدول چپ + منطبق از راست
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- کاربری که سفارش ندارد، با NULL در total می‌آید

-- RIGHT JOIN: عکس LEFT (به‌ندرت استفاده می‌شود؛ بهتر است LEFT بنویسید)

-- CROSS JOIN: حاصل ضرب کارتزین (هر ردیف A با هر ردیف B)
SELECT * FROM colors CROSS JOIN sizes;
-- ۵ رنگ × ۴ سایز = ۲۰ ردیف

-- SELF JOIN: روی همان جدول
SELECT u1.name AS user, u2.name AS referred_by
FROM users u1
LEFT JOIN users u2 ON u1.referrer_id = u2.id;

JOIN Algorithms

• Nested Loop Join: پیش‌فرض. سریع وقتی ایندکس داریم
• Block Nested Loop (BNL): وقتی ایندکس نیست، با buffer
• Hash Join (MySQL 8.0.18+): برای JOIN بدون ایندکس، خیلی سریع

۵.۶ Subquery vs JOIN vs CTE

سه روش برای کارهای مشابه. کدام بهتر است؟

SQL📋 کپی
-- نیاز: کاربرانی که در ماه گذشته سفارش داشته‌اند

-- روش ۱: Subquery (IN)
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
    WHERE created_at > NOW() - INTERVAL 30 DAY
);

-- روش ۲: JOIN با DISTINCT
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL 30 DAY;

-- روش ۳: EXISTS (معمولاً بهترین برای این الگو)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at > NOW() - INTERVAL 30 DAY
);

-- روش ۴: CTE (MySQL 8.0+ / MariaDB 10.2+)
WITH active_users AS (
    SELECT DISTINCT user_id
    FROM orders
    WHERE created_at > NOW() - INTERVAL 30 DAY
)
SELECT u.*
FROM users u
INNER JOIN active_users a ON a.user_id = u.id;

۵.۷ الگوهای رایج بهینه‌سازی

۱. حذف SELECT *

SQL📋 کپی
-- ❌ بد
SELECT * FROM products WHERE category_id = 5;

-- ✅ خوب: فقط ستون‌های مورد نیاز
SELECT id, title, price FROM products WHERE category_id = 5;
-- این می‌تواند Covering Index را فعال کند

۲. Pagination حرفه‌ای

صفحه‌بندی با OFFSET برای صفحات اول خوب است، اما در صفحه ۱۰۰۰ فاجعه می‌شود:

SQL📋 کپی
-- ❌ کند روی صفحات بزرگ (MySQL باید 100,000 ردیف بخواند و دور بریزد)
SELECT * FROM products ORDER BY id LIMIT 100000, 20;

-- ✅ Cursor-based: از آخرین id صفحه قبل استفاده کن
SELECT * FROM products
WHERE id > 100000
ORDER BY id
LIMIT 20;

-- ✅ یا با مقدار آخر مرتب‌سازی:
-- صفحه قبل با id=12345 و price=50000 تمام شد:
SELECT * FROM products
WHERE (price, id) > (50000, 12345)
ORDER BY price, id
LIMIT 20;

۳. اجتناب از OR روی ستون‌های مختلف

SQL📋 کپی
-- ❌ Optimizer نمی‌تواند هر دو ایندکس را همزمان استفاده کند
SELECT * FROM users WHERE email = 'x@y.com' OR phone = '09121234567';

-- ✅ UNION
SELECT * FROM users WHERE email = 'x@y.com'
UNION
SELECT * FROM users WHERE phone = '09121234567';

۴. Index Hint

SQL📋 کپی
-- اجبار به استفاده از ایندکس خاص
SELECT * FROM orders USE INDEX (idx_user_status)
WHERE user_id = 5 AND status = 'paid';

-- جلوگیری از استفاده از ایندکس
SELECT * FROM orders IGNORE INDEX (idx_old)
WHERE created_at > '2026-01-01';

-- اجبار قطعی (به ندرت استفاده شود)
SELECT * FROM orders FORCE INDEX (idx_date)
WHERE created_at > '2026-01-01';

۵.۸ Window Functions (MySQL 8.0+ / MariaDB 10.2+)

یکی از قدرتمندترین قابلیت‌های مدرن SQL:

SQL📋 کپی
-- شماره ردیف، رتبه‌بندی
SELECT
    id, title, price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK() OVER (ORDER BY price DESC) AS rank_pos,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank_pos
FROM products;

-- پارتیشن‌بندی: رتبه در هر دسته
SELECT
    category_id, title, price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_in_cat
FROM products;

-- محاسبه running total
SELECT
    DATE(created_at) AS day,
    SUM(total) AS daily,
    SUM(SUM(total)) OVER (ORDER BY DATE(created_at)) AS running_total
FROM orders
GROUP BY DATE(created_at);

-- LAG و LEAD: مقایسه با ردیف قبل/بعد
SELECT
    DATE(created_at) AS day,
    SUM(total) AS today_total,
    LAG(SUM(total)) OVER (ORDER BY DATE(created_at)) AS yesterday_total,
    SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE(created_at)) AS diff
FROM orders
GROUP BY DATE(created_at);

۵.۹ Profiling – اندازه‌گیری دقیق

SQL📋 کپی
-- روش قدیمی (هنوز در MariaDB کار می‌کند)
SET profiling = 1;

SELECT * FROM orders WHERE user_id = 5;
SELECT title, price FROM products LIMIT 100;

SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
-- نمایش زمان هر مرحله: parsing, optimizing, sending data, ...

-- روش مدرن: Performance Schema
SELECT
    EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

۵.۱۰ خلاصه فصل

• EXPLAIN اولین ابزار شما برای هر Query کند است
• type=ALL یا Extra=filesort علامت قرمز است
• Slow Query Log را در Production فعال کنید (long_query_time = 1)
• pt-query-digest بهترین ابزار تحلیل است
• SELECT * را حذف، Pagination را Cursor-based کنید
• Window Functions راه‌حل بسیاری از کوئری‌های پیچیده است