ایندکس‌گذاری پیشرفته در MySQL

۴.۱ چرا ایندکس مهم‌ترین موضوع MySQL است؟

اگر بخواهیم در یک جمله بگوییم، فرق بین یک Query که در ۱۰ میلی‌ثانیه تمام می‌شود و همان Query که ۳۰ ثانیه طول می‌کشد، در ۹۰٪ موارد ایندکس است. اما ایندکس شمشیر دو لبه است: ایندکس درست، Read را ۱۰۰ برابر سریع می‌کند؛ ایندکس غلط، Write را کند می‌کند و فضا تلف می‌کند.

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

۴.۲ ساختار B-Tree Index – چطور کار می‌کند؟

اکثر ایندکس‌های MySQL از ساختار B+Tree استفاده می‌کنند. تصور کنید کتاب راهنمای تلفن:

                    [50, 100]                  ← ریشه
                   /    |    
              [10,30] [60,80] [120,150]        ← نُود میانی
              /  |    ...
          [1-9][11-29][31-49] ...              ← برگ‌ها (داده‌ها)
    

هر جستجو با O(log n) به نتیجه می‌رسد. در یک جدول ۱۰۰ میلیون رکوردی، حداکثر ۴-۵ بار «خواندن صفحه» (page read) لازم است.

چه عملیاتی از B-Tree سود می‌برد؟

• Equality: WHERE id = 5
• Range: WHERE age BETWEEN 20 AND 30, WHERE date > '2026-01-01'
• Prefix LIKE: WHERE name LIKE 'علی%' (با درصد در آخر، نه اول!)
• ORDER BY: اگر روی ستون ایندکس باشد
• MIN/MAX: روی ستون ایندکس فوری انجام می‌شود

چه عملیاتی سود نمی‌برد؟

• WHERE name LIKE '%علی' (% در ابتدا)
• WHERE YEAR(created_at) = 2026 (تابع روی ستون)
• WHERE phone + 0 = 9121234567 (محاسبه)
• NOT EQUAL در بعضی موارد

۴.۳ Clustered Index در InnoDB

این مفهوم خاص InnoDB است: داده فیزیکی جدول، روی Primary Key مرتب است. به همین خاطر:

• یک جدول فقط یک Clustered Index دارد (همان PK)
• جستجو بر اساس PK سریع‌ترین حالت ممکن است
• ایندکس‌های ثانویه (Secondary Index) فقط PK را ذخیره می‌کنند، نه آدرس فیزیکی

Clustered Index (PK = id):
  id=1 → [name, email, age, ...]   (داده کامل)
  id=2 → [name, email, age, ...]
  id=3 → [name, email, age, ...]

Secondary Index (روی email):
  ali@x.com → id=2
  saeid@y.com → id=15
  reza@z.com → id=7

جستجو بر اساس email:
  1. در Secondary Index، email را پیدا کن → id بگیر
  2. در Clustered Index، با id داده کامل را بخوان
  این روش "Index Lookup + Bookmark Lookup" نام دارد
    

۴.۴ ساخت ایندکس – دستورات اصلی

SQL📋 کپی
-- در زمان CREATE TABLE
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(120),
    phone VARCHAR(15),
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    created_at TIMESTAMP,
    -- ایندکس‌ها در همان CREATE
    UNIQUE INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_name (last_name, first_name),  -- composite
    INDEX idx_created (created_at)
);

-- روی جدول موجود
CREATE INDEX idx_email ON users (email);
CREATE UNIQUE INDEX idx_phone ON users (phone);
ALTER TABLE users ADD INDEX idx_name (last_name, first_name);

-- حذف ایندکس
DROP INDEX idx_old ON users;
ALTER TABLE users DROP INDEX idx_old;

-- نمایش ایندکس‌های یک جدول
SHOW INDEX FROM users;

۴.۵ Composite Index – مهم‌ترین مفهوم پیشرفته

ایندکس روی چند ستون. ترتیب ستون‌ها بسیار مهم است.

SQL📋 کپی
CREATE INDEX idx_user_status_date ON orders (user_id, status, created_at);

این ایندکس برای کدام Query‌ها کار می‌کند؟

قانون «Leftmost Prefix»

یک composite index (a, b, c) برای این جستجوها کار می‌کند:

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?

اما نه برای: WHERE b = ? یا WHERE a = ? AND c = ?

چه ترتیبی انتخاب کنیم؟

1. ستون با Cardinality بالا (مقادیر متمایز زیاد) را اول بگذارید: user_id بهتر از status
2. ستون‌های Equality قبل از Range: (status, created_at) بهتر از (created_at, status) برای WHERE status = ? AND created_at > ?
3. ستون ORDER BY در آخر

۴.۶ Covering Index – ایندکس بدون Bookmark Lookup

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

SQL📋 کپی
-- جدول
CREATE TABLE orders (
    id INT UNSIGNED PRIMARY KEY,
    user_id INT UNSIGNED,
    status VARCHAR(20),
    total BIGINT UNSIGNED,
    created_at TIMESTAMP
);

-- Query پرتکرار:
SELECT user_id, status, total
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';

-- ❌ ایندکس معمولی: created_at را پیدا می‌کند، بعد از جدول اصلی user_id, status, total را می‌خواند
CREATE INDEX idx_date ON orders (created_at);

-- ✅ Covering Index: همه چیز در ایندکس هست
CREATE INDEX idx_date_covering ON orders (created_at, user_id, status, total);

در EXPLAIN، Covering Index با Using index در ستون Extra نشان داده می‌شود.

۴.۷ Prefix Index – برای ستون‌های متنی بلند

ایندکس روی ستون‌های متنی بلند فضای زیادی می‌گیرد. می‌توانید فقط چند کاراکتر اول را ایندکس کنید:

SQL📋 کپی
-- ایندکس روی ۱۰ کاراکتر اول email
CREATE INDEX idx_email_prefix ON users (email(10));

-- پیدا کردن طول مناسب با Selectivity Test
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;
-- هدف: عددی نزدیک sel_full با کمترین طول

۴.۸ Functional Index (MySQL 8.0+)

ایندکس روی نتیجه یک تابع. قبلاً با Generated Column انجام می‌شد، الان مستقیم:

SQL📋 کپی
-- MySQL 8.0+
CREATE INDEX idx_year ON orders ((YEAR(created_at)));

-- حالا این Query از ایندکس استفاده می‌کند:
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- در MySQL 5.7 و MariaDB، باید Generated Column بسازید:
ALTER TABLE orders
  ADD COLUMN order_year SMALLINT UNSIGNED AS (YEAR(created_at)) STORED,
  ADD INDEX idx_year (order_year);

۴.۹ Unique Index – تضمین یکتایی

SQL📋 کپی
-- یکتا تک‌ستونی
CREATE UNIQUE INDEX idx_phone ON users (phone);

-- یکتا چندستونی (ترکیب باید یکتا باشد)
CREATE UNIQUE INDEX idx_user_product ON wishlist (user_id, product_id);

-- استفاده در ON DUPLICATE KEY
INSERT INTO users (phone, name) VALUES ('09121234567', 'علی')
ON DUPLICATE KEY UPDATE name = VALUES(name);

۴.۱۰ Full-Text Index – برای جستجوی متنی

برای جستجوی کلمات در متن (مثل عنوان مقاله):

SQL📋 کپی
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content MEDIUMTEXT,
    FULLTEXT INDEX idx_search (title, content)
) ENGINE=InnoDB;

-- جستجوی Natural Language
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('فرش دستباف کاشان');

-- Boolean Mode (دقیق‌تر)
SELECT * FROM articles
WHERE MATCH(title, content)
      AGAINST('+فرش +کاشان -ماشینی' IN BOOLEAN MODE);

-- نمایش امتیاز ربط
SELECT id, title,
       MATCH(title, content) AGAINST('فرش دستباف') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('فرش دستباف')
ORDER BY relevance DESC;

برای فارسی، تنظیم ngram پارسر کمک می‌کند (در فصل ۹ کامل می‌بینیم).

۴.۱۱ Cardinality و Statistics

Cardinality یعنی تعداد مقادیر متمایز در ستون. هرچه بالاتر، ایندکس مفیدتر.

SQL📋 کپی
-- بررسی Cardinality
SHOW INDEX FROM orders;
-- ستون Cardinality نشان می‌دهد چند مقدار متمایز

-- مثال:
-- ایندکس روی gender (M/F) → cardinality = 2 → ضعیف
-- ایندکس روی email → cardinality = nearly تعداد ردیف‌ها → عالی

-- به‌روزرسانی statistics
ANALYZE TABLE orders;

-- محاسبه selectivity
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_sel,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_sel
FROM orders;
-- هرچه نزدیک‌تر به ۱، selectivity بیشتر، ایندکس مفیدتر

۴.۱۲ پیدا کردن ایندکس‌های بلااستفاده

ایندکس‌های اضافی Performance Insert/Update را پایین می‌آورند و فضا تلف می‌کنند:

SQL📋 کپی
-- MySQL 8.0+ / MariaDB 10.4+
SELECT
    object_schema AS db,
    object_name AS tbl,
    index_name AS idx
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;
-- این ایندکس‌ها هرگز استفاده نشده‌اند

-- سایز ایندکس‌ها
SELECT
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
  AND database_name = 'my_db'
ORDER BY size_mb DESC;

۴.۱۳ Anti-Pattern های رایج در ایندکس‌گذاری

۱. ایندکس روی هر ستون

«برای امان» همه ستون‌ها را ایندکس می‌کنند. نتیجه: Insert/Update فاجعه می‌شود.

۲. ایندکس روی ستون پرتکرار

SQL📋 کپی
-- ❌ بد: gender فقط 2 مقدار دارد
CREATE INDEX idx_gender ON users (gender);

-- ✅ خوب: composite با ستون پرکاردینالیتی
CREATE INDEX idx_gender_created ON users (gender, created_at);

۳. عدم استفاده از Composite

SQL📋 کپی
-- ❌ بد: دو ایندکس جدا
CREATE INDEX idx_user ON orders (user_id);
CREATE INDEX idx_status ON orders (status);

-- ✅ خوب: یک composite (اگر همیشه با هم استفاده می‌شوند)
CREATE INDEX idx_user_status ON orders (user_id, status);

۴. Function در WHERE

SQL📋 کپی
-- ❌ ایندکس بی‌فایده می‌شود
WHERE LOWER(email) = 'ali@x.com'
WHERE DATE(created_at) = '2026-05-01'
WHERE phone + 0 = 9121234567

-- ✅ بازنویسی
WHERE email = 'ali@x.com'  -- داده را lowercase ذخیره کنید
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'
WHERE phone = '09121234567'

۴.۱۴ خلاصه فصل

• B+Tree برای equality و range عالی است، برای LIKE با % در ابتدا بی‌فایده
• InnoDB از Clustered Index استفاده می‌کند؛ PK کوچک نگه دارید
• Composite Index قدرتمندترین ابزار است؛ Leftmost Prefix را بدانید
• Covering Index سریع‌ترین حالت را می‌دهد
• Cardinality بالا = ایندکس مفید
• ایندکس‌های بلااستفاده را با performance_schema پیدا و حذف کنید
• Function روی ستون در WHERE = مرگ ایندکس

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