چه اشتباهاتی باعث می‌شوند که ایندکس‌ها در MySQL کار نکنند؟

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

در این پست به چند اشتباه رایج در نگارش کوئری‌ها می‌پردازم که باعث نادیده گرفته شدن ایندکس‌ها (Index Obfuscation) می‌شوند و راهکارهایی برای رفع آنها ارائه می‌دهم.

❌ ۱. استفاده نادرست از Wildcardها در LIKE

🔍 مشکل: وقتی از عملگر LIKE همراه با علامت % در ابتدای رشته استفاده کنید، ایندکس‌های MySQL کار نمی‌کنند. به مثال زیر توجه کنید:

SELECT * FROM people WHERE email LIKE '%matin@example.com%';

ایندکس روی ستون email نادیده گرفته می‌شود، زیرا % در ابتدای رشته مانع از استفاده از جستجوی مبتنی بر ایندکس (Index Seek) شده و MySQL مجبور میشود کل جدول را اسکن کند (Full Table Scan).

✅ راه حل: ۱️⃣ اگر امکانپذیر است، از % فقط در انتهای مقدار جستجو استفاده کنید: ; این کار به MySQL اجازه می‌دهد از ایندکس استفاده کند.

SELECT * FROM people WHERE email LIKE  'matin%'

۲️⃣ در برخی موارد، می‌توانید از ستون های تولیدشده (Generated Columns) برای بهینه‌سازی جستجو استفاده کنید. به عنوان مثال، اگر بخواهید کاربران را بر اساس دامنه ایمیل فیلتر کنید:

ALTER TABLE people ADD COLUMN email_domain VARCHAR(255) AS (SUBSTRING_INDEX(email, '@', -1)); 

ALTER TABLE people ADD INDEX (email_domain); 

SELECT * FROM people WHERE email_domain = 'example.com';

🔹 در این روش، MySQL می‌تواند از ایندکس روی email_domain استفاده کند و جستجو سریعتر انجام شود.

❌ ۲. اشتباه در استفاده از ایندکس‌های ترکیبی (Composite Indexes)

ایندکس‌های ترکیبی (Multi-column یا Composite Indexes) یکی از ابزارهای قدرتمند در بهینه‌سازی کوئری‌ها هستند، اما اگر به درستی طراحی نشوند، ممکن است عملکردشان آن‌طور که انتظار دارید نباشد!

❌ ۱. ترتیب ستون‌ها در ایندکس مهم است!

🔹 MySQL ایندکس‌های ترکیبی را از چپ به راست بررسی می‌کند. اگر اولین ستون ایندکس در شرط WHERE نباشد، MySQL نمی‌تواند از ایندکس بهینه استفاده کند.

📌 مثال: فرض کنید ایندکس ترکیبی زیر را داریم:

ALTER TABLE people ADD INDEX multi (first_name, last_name, birthday);

ایندکس بالا ستون‌ها را به این ترتیب در خود نگه می‌دارد: first_name -> last_name -> birthday

🔹 حالا این دو کوئری را بررسی کنیم:

✅ کوئری زیر از ایندکس استفاده می‌کند، چون شامل اولین ستون ایندکس (first_name) است:

EXPLAIN SELECT * FROM people WHERE first_name = 'Matin';

❌ اما این کوئری ایندکس را نادیده می‌گیرد، چون first_name در شرط وجود ندارد:

EXPLAIN SELECT * FROM people WHERE last_name = 'Ebrahimi' AND birthday = '1999-02-21';

📌 نکته: اگر فقط قسمت اول ایندکس در شرط باشد، ایندکس کار می‌کند. اما اگر قسمت‌های میانی یا انتهایی بدون قسمت اول استفاده شوند، ایندکس نادیده گرفته می‌شود!

✅ راه‌حل: همیشه ترتیب ستون‌ها را بر اساس نحوه استفاده در شرط‌های WHERE انتخاب کنید.

❌ ۲. توقف در اولین شرط رنج (<, >, BETWEEN)

🔹 اگر در یک ایندکس ترکیبی، یک شرط رنج (<, >, BETWEEN) روی یک ستون اعمال شود، MySQL فقط تا همان ستون از ایندکس استفاده می‌کند و ستون‌های بعدی ایندکس نادیده گرفته می‌شوند.

📌 مثال:

EXPLAIN SELECT * FROM people

WHERE first_name = 'Matin' AND last_name < 'Ebrahimi' AND birthday = '1999-02-21';

در این کوئری:

✅ MySQL از first_name استفاده می‌کند.

✅ MySQL از last_name هم استفاده می‌کند، ولی به دلیل شرط رنج (<)، پردازش ایندکس متوقف می‌شود.

❌ birthday نادیده گرفته می‌شود!

✅ راه‌حل: اگر مرتباً از شرط‌های > و < استفاده می‌کنید، ممکن است نیاز باشد ایندکس‌های جداگانه‌ای برای برخی ستون‌ها تعریف کنید تا عملکرد بهتری داشته باشید.

🔥 چگونه ایندکس ترکیبی را بهینه طراحی کنیم؟

1️⃣ اولویت با ستون‌هایی است که بیشتر در WHERE با = استفاده می‌شوند.

2️⃣ از شرایط رنج (<, >, BETWEEN) در انتهای ترتیب ایندکس استفاده کنید.

3️⃣ اگر کوئری‌های شما نیاز دارند که روی ستون‌های میانی ایندکس جستجو انجام شود، ممکن است نیاز به ایندکس‌های جداگانه‌ای برای آن‌ها داشته باشید.

4️⃣ از EXPLAIN برای بررسی عملکرد کوئری‌ها و استفاده از ایندکس کمک بگیرید.

۳. استفاده از توابع یا اعمال تغییرات روی ستون‌هایی است که ایندکس دارند

❌ مشکل: تغییر مقدار ستون در شرط WHERE

🔹 اگر در قسمت WHERE یک عملیات ریاضی، متنی یا تابعی روی یک ستون ایندکس‌شده انجام دهید، MySQL دیگر نمی‌تواند از ایندکس استفاده کند.

📌 مثال:

SELECT * FROM film WHERE length / 60 < 2;

در اینجا:
❌ ستون length دارای ایندکس است، اما به دلیل تقسیم (/ 60)، ایندکس نادیده گرفته می‌شود.
❌ MySQL مجبور می‌شود همه سطرها را بررسی کند (Full Table Scan).

✅ راه‌حل: عملیات را به مقدار ثابت منتقل کنید تا ستون دست‌نخورده باقی بماند:

SELECT * FROM film WHERE length < 2 * 60;

🔹 در این روش، مقدار length بدون تغییر باقی می‌ماند، و MySQL می‌تواند از ایندکس استفاده کند.

📌 خطاهای مشابه در استفاده از توابع و نحوه رفع آن‌ها

🛑 استفاده از توابع متنی روی ستون ایندکس‌شده

❌ این کوئری ایندکس را نادیده می‌گیرد:

SELECT * FROM users WHERE LOWER(username) = 'john_doe';

✅ راه‌حل: مقدار ورودی را تبدیل کنید، نه مقدار ستون:

SELECT * FROM users WHERE username = BINARY 'john_doe';

یا اگر case-insensitive search نیاز دارید، از COLLATE utf8mb4_general_ci استفاده کنید.

🛑 استفاده از DATE() روی ستون‌های تاریخ

❌ این کوئری باعث می‌شود که MySQL نتواند از ایندکس استفاده کند:

SELECT * FROM orders WHERE DATE(order_date) = '2024-02-15';

✅ راه‌حل: بازه‌ی تاریخ را در شرط قرار دهید:

SELECT * FROM orders

WHERE order_date >= '2024-02-15 00:00:00'

AND order_date < '2024-02-16 00:00:00';

🔥 چگونه از این اشتباهات جلوگیری کنیم؟

✅ همیشه مقدار ستون ایندکس‌شده را تغییر ندهید، بلکه مقدار مقایسه‌ای را تنظیم کنید.
✅ اگر نیاز به پردازش روی مقادیر دارید، از Generated Columns استفاده کنید.
✅ با استفاده از EXPLAIN بررسی کنید که آیا MySQL از ایندکس استفاده می‌کند یا نه.