ایندکس‌ها و پلن کوئری در SQL: سریع کردن کوئری‌های کُند

کوئری‌ای که روی لپ‌تاپ شما در یک چشم به هم زدن جواب می‌دهد، ممکن است روی سرور اصلی چند ثانیه طول بکشد. معمولاً خودِ کوئری ایرادی ندارد: فقط جدول از هزار ردیف به چند میلیون ردیف رسیده و پایگاه داده برای پیدا کردن همان چند ردیفی که خواسته‌اید، تک‌تکِ ردیف‌ها را می‌خواند.

ایندکس‌ها این مشکل را حل می‌کنند؛ اما فقط ایندکسِ درست، و فقط اگر کوئری شما اجازه بدهد پایگاه داده از آن استفاده کند. در این مقاله یک جدول یک‌میلیون‌ردیفی می‌سازید، می‌بینید یک کوئری از ۱۰۰ میلی‌ثانیه به کمتر از یک میلی‌ثانیه می‌رسد، و یاد می‌گیرید پلن کوئری را بخوانید تا بفهمید چرا.

از SQLite استفاده می‌کنیم، چون تقریباً همه‌جا نصب است و سرور نمی‌خواهد. همین ایده‌ها مستقیم در PostgreSQL و MySQL هم صدق می‌کنند؛ آخر مقاله جدولی برای تطبیق اصطلاحات آمده است. اگر JOINها هنوز برایتان تازه‌اند، از JOINها در SQL با مثال‌های ساده شروع کنید.

ساختن یک میلیون ردیف#

یک پایگاه دادهٔ تازه با sqlite3 shop.db باز کنید و این را در آن بچسبانید. این کد جدول orders را می‌سازد و آن را با یک میلیون سفارش تصادفی از ۵۰ هزار مشتری پر می‌کند:

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status      TEXT    NOT NULL,
  total       REAL    NOT NULL,
  created_at  TEXT    NOT NULL
);

WITH RECURSIVE n(i) AS (
  SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000000
)
INSERT INTO orders (customer_id, status, total, created_at)
SELECT abs(random()) % 50000 + 1,
       CASE abs(random()) % 10 WHEN 0 THEN 'pending' WHEN 1 THEN 'cancelled' ELSE 'shipped' END,
       (abs(random()) % 50000) / 100.0,
       datetime('2024-01-01', '+' || (abs(random()) % 86400000) || ' seconds')
FROM n;

بعد با .timer on زمان‌سنجی را روشن کنید. از این به بعد هر کوئری گزارش می‌دهد چقدر طول کشیده است.

خواندن پلن کوئری#

اگر قبل از هر کوئری EXPLAIN QUERY PLAN بنویسید، SQLite به‌جای اجرای آن، راهبردش را نشان می‌دهد:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 4242;

QUERY PLAN
`--SCAN orders

SCAN یعنی پیمایش کامل جدول: SQLite هر یک میلیون ردیف را می‌خواند و تک‌تک بررسی می‌کند. خودِ کوئری را اجرا کنید؛ در آزمایش ما حدود ۱۰۰ میلی‌ثانیه طول کشید تا چند ردیف انگشت‌شمار را برگرداند. شاید زیاد به نظر نرسد، تا وقتی که یک صفحه آن را بیست بار اجرا کند، یا هزار کاربر هم‌زمان آن صفحه را باز کنند.

ایندکس B-tree چطور کار می‌کند#

ایندکس یک نسخهٔ جداگانه و مرتب‌شده از یک یا چند ستون است که هر ورودی‌اش به ردیف اصلی اشاره می‌کند. پایگاه‌های داده آن را به شکل یک B-tree نگه می‌دارند: درختی کم‌عمق از صفحه‌ها (page) که هر کدام صدها کلید مرتب را در خود دارند. برای پیدا کردن 4242، پایگاه داده صفحهٔ ریشه را می‌خواند، اشاره‌گرِ بازه‌ای را که 4242 در آن است دنبال می‌کند و همین را تکرار می‌کند تا به یک صفحهٔ برگ برسد. چون هر صفحه به صدها فرزند منشعب می‌شود، یک میلیون ردیف فقط سه یا چهار سطح لازم دارد.

یک ایندکس بسازید و دوباره به پلن نگاه کنید:

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 4242;

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

SEARCH ... USING INDEX یعنی SQLite در ایندکس تا ورودی‌های منطبق پیش می‌رود و بعد فقط همان ردیف‌ها را از جدول برمی‌دارد. حالا همان کوئری در کمتر از یک میلی‌ثانیه اجرا می‌شود، یعنی تقریباً صد برابر سریع‌تر؛ و این فاصله با بزرگ‌تر شدن جدول بیشتر هم می‌شود: هزینهٔ پیمایش کامل با دو برابر شدن جدول دو برابر می‌شود، اما جست‌وجو در ایندکس تقریباً تغییری حس نمی‌کند.

ایندکس‌های ترکیبی: ترتیب ستون‌ها همه‌چیز است#

کوئری‌های واقعی روی بیش از یک ستون فیلتر می‌کنند. این یک نمونهٔ رایج است: سفارش‌های اخیر یک مشتری، به ترتیب تاریخ.

SELECT * FROM orders
WHERE customer_id = 4242 AND created_at >= '2025-01-01'
ORDER BY created_at;

اگر فقط idx_orders_customer وجود داشته باشد، پلن یک خط دوم هم دارد:

QUERY PLAN
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

USE TEMP B-TREE FOR ORDER BY یعنی ردیف‌ها نامرتب برمی‌گردند و SQLite باید خودش آن‌ها را مرتب کند. یک ایندکس ترکیبی روی هر دو ستون، فیلتر و مرتب‌سازی را با هم حل می‌کند:

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_created (customer_id=? AND created_at>?)

ایندکس ترکیبی را مثل دفترچهٔ تلفنی تصور کنید که اول بر اساس نام خانوادگی و بعد بر اساس نام مرتب شده است. داخل بخش «4242»، ورودی‌ها از قبل بر اساس created_at مرتب‌اند؛ پس پایگاه داده مستقیم به اولین تاریخی که لازم دارد می‌پرد و از آنجا جلو می‌رود. بدون هیچ مرحلهٔ مرتب‌سازی.

حالا ستون‌ها را برعکس امتحان کنید. اول دو ایندکسِ مشتری را حذف کنید تا SQLite نتواند آن‌ها را انتخاب کند:

DROP INDEX idx_orders_customer;
DROP INDEX idx_orders_customer_created;
CREATE INDEX idx_orders_created_customer ON orders (created_at, customer_id);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_created_customer (created_at>?)

هنوز SEARCH نوشته، پس به نظر درست می‌آید. اما در آزمایش ما ۹۵ میلی‌ثانیه طول کشید؛ تقریباً به کندیِ حالتی که اصلاً ایندکس نداشتیم. داخل پرانتز را نگاه کنید: فقط created_at>? از ایندکس استفاده می‌کند. این بازه حدود دو سوم همهٔ سفارش‌ها را می‌پوشاند و SQLite باید تک‌تک آن ورودی‌ها را برای customer_id = 4242 بررسی کند. قبل از ادامه، این ایندکس را حذف کنید (DROP INDEX idx_orders_created_customer;).

ایندکس‌های پوشا: اصلاً سراغ جدول نروید#

حتی با یک ایندکس عالی، هر ردیف منطبق یک پرشِ اضافه از ایندکس به جدول لازم دارد تا بقیهٔ ستون‌ها خوانده شود. اگر ایندکس از قبل همهٔ ستون‌هایی که کوئری لازم دارد را داشته باشد، پایگاه داده می‌تواند کلاً از جدول بگذرد:

CREATE INDEX idx_orders_customer_created_total
  ON orders (customer_id, created_at, total);

EXPLAIN QUERY PLAN
SELECT created_at, total FROM orders WHERE customer_id = 4242;

QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_customer_created_total (customer_id=?)

COVERING INDEX (ایندکس پوشا) بهترین پلنی است که برای یک جست‌وجو می‌توانید بگیرید. بیشترین اثرش روی کوئری‌هایی است که ردیف‌های زیادی برمی‌گردانند، مثل گزارش‌ها و داشبوردها. این هم یک دلیل خوب دیگر برای پرهیز از SELECT * است: وقتی ستون‌های کمتری بخواهید، ایندکس پوشا ممکن می‌شود.

اشتباه‌هایی که بی‌صدا ایندکس را از کار می‌اندازند#

ایندکس فقط وقتی کمک می‌کند که پایگاه داده بتواند شرط شما را مستقیم با مقدارهای مرتب‌شده مقایسه کند. اگر ستون را داخل یک تابع یا عبارت بگذارید، دیگر نمی‌تواند. فرض کنید یک ایندکس ساده روی created_at اضافه کرده‌ایم و سفارش‌های یک روز را می‌خواهیم:

CREATE INDEX idx_orders_created ON orders (created_at);

-- ✗ a full SCAN: date() has to be computed for every one of the million entries (≈600 ms)
SELECT count(*) FROM orders WHERE date(created_at) = '2025-03-14';

-- ✓ a SEARCH on idx_orders_created (created_at>? AND created_at<?) (<1 ms)
SELECT count(*) FROM orders
WHERE created_at >= '2025-03-14' AND created_at < '2025-03-15';

هر دو همان تعداد را برمی‌گردانند. به شکل دوم sargable می‌گویند (از Search ARGument ABLE): ستون به‌تنهایی در یک طرف مقایسه آمده است. چند قاتل رایج دیگرِ ایندکس:

وقتی واقعاً باید روی یک عبارت فیلتر کنید، روی خودِ همان عبارت ایندکس بسازید. SQLite، PostgreSQL و MySQL (از نسخهٔ ۸) همه از این پشتیبانی می‌کنند:

CREATE INDEX idx_orders_day ON orders (date(created_at));
-- now WHERE date(created_at) = '2025-03-14' uses: SEARCH orders USING INDEX idx_orders_day (<expr>=?)

ایندکس‌های جزئی: فقط ردیف‌هایی را ایندکس کنید که کوئری می‌گیرید#

فرض کنید یک کار پس‌زمینه مدام سفارش‌های در انتظار (pending) را می‌خواهد که فقط ۱۰ درصد جدول‌اند. یک ایندکس جزئی (partial index) فقط همین ردیف‌ها را در خود دارد:

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE status = 'pending' AND created_at < '2024-02-01';

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_pending (created_at<?)

در پایگاه دادهٔ ما این ایندکس ۲٫۷ مگابایت جا می‌گیرد، در برابر ۲۷ مگابایت برای یک ایندکس کامل روی created_at. ایندکس‌های کوچک‌تر سریع‌تر جست‌وجو می‌شوند، راحت‌تر در حافظه جا می‌گیرند و به‌روزرسانی‌شان ارزان‌تر است. PostgreSQL هم ایندکس جزئی دارد؛ MySQL ندارد.

ایندکس‌ها رایگان نیستند#

هر ایندکس یک ساختار دادهٔ دوم است که پایگاه داده باید مرتب نگهش دارد. هر INSERT، هر UPDATE روی یک ستونِ ایندکس‌شده و هر DELETE باید همهٔ ایندکس‌های جدول را به‌روز کند. در آزمایش ما، درج ۲۰۰ هزار ردیفِ دیگر در جدول یک‌میلیونی بدون ایندکس ثانویه حدود نیم ثانیه و با سه ایندکس ثانویه بیش از هفت ثانیه طول کشید.

پس هر ستونی را «محض احتیاط» ایندکس نکنید:

• ستون‌هایی را ایندکس کنید که در WHERE، JOIN و ORDER BYِ کوئری‌های پرتکرار می‌آیند.
• یک ایندکس ترکیبیِ خوب‌طراحی‌شده را به چند ایندکس تک‌ستونی ترجیح دهید.
• دنبال ایندکس‌های تکراری بگردید: وقتی (customer_id, created_at) وجود دارد، (customer_id) به خاطر قانون پیشوند سمت چپ زائد است.
• ستون‌هایی که فقط چند مقدار متمایز دارند (مثل status) به‌تنهایی به‌ندرت ایندکس خوبی می‌شوند. از آن‌ها در یک ایندکس جزئی یا به‌عنوان ستون‌های بعدیِ یک ایندکس ترکیبی استفاده کنید.
• بعد از تغییرات بزرگ در داده‌ها ANALYZE را اجرا کنید تا برنامه‌ریز کوئری آمار به‌روز برای انتخاب بین ایندکس‌ها داشته باشد.

خواندن پلن در PostgreSQL و MySQL#

واژه‌ها فرق می‌کنند، اما سؤال‌ها همان‌اند: پیمایش کامل است یا جست‌وجو، از کدام ایندکس استفاده می‌شود و آیا مرحلهٔ مرتب‌سازیِ اضافه‌ای هست؟

در PostgreSQL از EXPLAIN ANALYZE استفاده کنید تا کوئری واقعاً اجرا شود و تعداد واقعیِ ردیف‌ها و زمان‌ها را کنار تخمین‌های برنامه‌ریز ببینید. وقتی تخمینی با تعداد واقعی فاصلهٔ زیادی دارد، معمولاً مقصر آمارِ قدیمی است.

جمع‌بندی#

• SCAN همهٔ ردیف‌ها را می‌خواند؛ SEARCH ... USING INDEX از طریق یک B-tree مرتب مستقیم سراغ ردیف‌های منطبق می‌رود.
• در ایندکس‌های ترکیبی ستون‌های تساوی را اول بگذارید و بعد یک ستون برای بازه یا مرتب‌سازی. ایندکس‌ها فقط به پیشوند سمت چپ خودشان کمک می‌کنند.
• ایندکس پوشا بدون دست زدن به جدول به کوئری جواب می‌دهد.
• توابع و محاسبات روی ستونِ ایندکس‌شده ایندکس را از کار می‌اندازند. شرط را بازنویسی کنید یا روی عبارت ایندکس بسازید.
• ایندکس‌های جزئی وقتی فقط زیرمجموعه‌ای از ردیف‌ها را کوئری می‌گیرید، ایندکس را کوچک نگه می‌دارند.
• هر ایندکس نوشتن را کُندتر می‌کند. برای کوئری‌های واقعی ایندکس بسازید و پلن را قبل و بعد از آن بررسی کنید.

مهم‌ترین عادت: قبل از اضافه کردن ایندکس EXPLAIN بگیرید، و بعد از اضافه کردنش دوباره EXPLAIN بگیرید.