Index ডিজাইন — composite, covering আর EXPLAIN

১ · প্রথম অভ্যাস — কোয়েরির plan পড়া

পর্ব ৪০-এ দেখেছেন index-এর ভেতরের গঠন: B+ tree, hash আর bitmap. এই পর্বটি হাতে-কলমে। প্রশ্ন তিনটি: কোন কলামে index দেবেন, একাধিক কলাম হলে কোন ক্রমে, আর DBMS সত্যিই সেটি ব্যবহার করছে কি না। তৃতীয় প্রশ্নের উত্তর অনুমানে মেলে না। DBMS-এর query optimizer নিজে ঠিক করে কোয়েরিটি কোন পথে চলবে, আর সেই পথ — query plan — দেখার কমান্ড হলো EXPLAIN.

ছোট টেবিলে সব কোয়েরিই দ্রুত, তাই এই পর্বে আমরা একটি বড় টেবিল বানাব। প্রতিটি এডিটরে আগে থেকে তৈরি থাকে customers (customer_id, name, phone — ২,০০০ জন গ্রাহক; phone UNIQUE, তাই এতে স্বয়ংক্রিয় একটি index আছে)। প্রথম এডিটরে আপনি নিজে orders বানাবেন: order_id, customer_id, status, order_date আর amount (টাকা)। পর্ব ২৫-এর recursive CTE ১ থেকে ২০,০০০ পর্যন্ত সংখ্যা i বানায়, আর ভাগশেষের হিসাব (modulo) থেকে এলোমেলো-দেখতে মান আসে:

• i * 7919 % 2000 + 1 — customer_id. 7919 আর 2000-এর কোনো সাধারণ উৎপাদক নেই, তাই প্রতিটি গ্রাহক ঠিক ১০টি করে অর্ডার পান, কিন্তু ছড়ানোভাবে।
• i % 9 — status: ৯ ভাগের ১ ভাগ cancelled, ২ ভাগ pending, ৩ ভাগ shipped, ৩ ভাগ delivered.
• i * 37 % 731 — ২০২৪-০১-০১ থেকে কত দিন পরে; ফলে তারিখ ২০২৪ আর ২০২৫ জুড়ে, ৭৩১টি দিনে ছড়ানো।

CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  status TEXT NOT NULL, order_date TEXT NOT NULL, amount INTEGER NOT NULL);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO orders
SELECT i, i * 7919 % 2000 + 1,
       CASE WHEN i % 9 = 0 THEN 'cancelled' WHEN i % 9 < 3 THEN 'pending'
            WHEN i % 9 < 6 THEN 'shipped' ELSE 'delivered' END,
       date('2024-01-01', '+' || (i * 37 % 731) || ' days'),
       100 + i * 131 % 4900
FROM n;
SELECT status, COUNT(*) AS n FROM orders GROUP BY status;
SELECT * FROM orders WHERE order_id <= 3;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;

SQLite-এর EXPLAIN QUERY PLAN-এর detail-এ যে শব্দগুলো বারবার দেখবেন:

এবার customer_id-এ একটি index বানিয়ে একই প্রশ্ন আবার করুন। এখান থেকে প্রতিটি এডিটরে orders টেবিলও আগে থেকে তৈরি থাকে (ঠিক ওপরের কোড দিয়ে):

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE order_id = 42;
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM orders WHERE customer_id = 42;
SELECT order_id, status, order_date, amount
FROM orders WHERE customer_id = 42;

খরচের হিসাবটি নির্দিষ্ট করে বলা যায়। SCAN-এ n সারির প্রতিটি পড়তে হয়: খরচ n-এর সমানুপাতিক, এখানে ২০,০০০টি সারি। SEARCH-এ B+ tree-এর মূল থেকে পাতা পর্যন্ত h-টি page (h = গাছের উচ্চতা, প্রায় log_f n, যেখানে f = এক page-এ কতগুলো key ধরে), তারপর মেলা k-টি entry, আর প্রতিটির জন্য টেবিলে একটি খোঁজ। একটি page-এ কয়েকশো key ধরলে কোটি সারির টেবিলেও h সাধারণত ৩–৪. তাই SEARCH-এর খরচ প্রায় h + k·(টেবিলে একটি খোঁজ), আর SCAN-এর n — k ছোট হলে পার্থক্য হাজারো গুণ।

২ · PostgreSQL আর MySQL-এর EXPLAIN

প্রতিটি DBMS-এর EXPLAIN আছে, ভাষা আলাদা। PostgreSQL-এ দুটি রূপ: EXPLAIN শুধু পরিকল্পনা দেখায় আর অনুমান করা খরচ; EXPLAIN ANALYZE কোয়েরিটি সত্যিই চালায়, তারপর অনুমানের পাশে আসল সময় আর আসল সারির সংখ্যা বসায়। সত্যিই চালায় বলে UPDATE বা DELETE-এ সাবধান — BEGIN; EXPLAIN ANALYZE …; ROLLBACK; লিখুন। নিচের ফলগুলো এই পর্বের মতো ২০,০০০ সারির একটি orders টেবিলে PostgreSQL 16-এর আদলে লেখা উদাহরণ; চলে না, আর আপনার মেশিনে সংখ্যা আলাদা হবে। ট্যাব বদলে চারটি প্রধান পথ দেখুন:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

 Seq Scan on orders  (cost=0.00..379.00 rows=10 width=27)
                     (actual time=0.031..1.874 rows=10 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 19990
 Planning Time: 0.092 ms
 Execution Time: 1.893 ms

 Index Scan using idx_orders_customer on orders
     (cost=0.29..39.44 rows=10 width=27)
     (actual time=0.024..0.041 rows=10 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.118 ms
 Execution Time: 0.058 ms

EXPLAIN ANALYZE SELECT order_date, amount FROM orders WHERE customer_id = 42;

 Index Only Scan using idx_cust_date_amt on orders
     (cost=0.29..4.46 rows=10 width=8)
     (actual time=0.019..0.022 rows=10 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0

EXPLAIN ANALYZE SELECT * FROM orders
WHERE order_date BETWEEN '2025-12-01' AND '2025-12-31';

 Bitmap Heap Scan on orders  (cost=19.01..206.53 rows=850 width=27)
                             (actual time=0.110..0.512 rows=850 loops=1)
   Recheck Cond: ((order_date >= '2025-12-01'::date) AND (order_date <= '2025-12-31'::date))
   Heap Blocks: exact=24
   ->  Bitmap Index Scan on idx_orders_date  (cost=0.00..18.80 rows=850 width=0)
                                             (actual time=0.081..0.081 rows=850 loops=1)
         Index Cond: ((order_date >= '2025-12-01'::date) AND (order_date <= '2025-12-31'::date))

প্রতিটি লাইনের সংখ্যাগুলোর মানে:

• cost=শুরু..মোট — অনুমিত খরচ, একক হলো “একটি page ক্রমানুসারে পড়া = 1.0”. প্রথম সংখ্যা প্রথম সারি দেওয়ার আগের খরচ, দ্বিতীয়টি সব সারির। এটি মিলিসেকেন্ড নয়।
• rows, width — optimizer-এর অনুমান: কতগুলো সারি আসবে, প্রতিটি গড়ে কত বাইট।
• actual time=শুরু..শেষ — আসল মিলিসেকেন্ড, প্রতি loop-এ; loops — অংশটি কতবার চলেছে (nested loop JOIN-এর ভেতরে অনেকবার)। মোট সময় ≈ actual × loops.
• সবচেয়ে কাজের তুলনা: অনুমিত rows বনাম actual rows. ১০ অনুমান করে ১ লাখ পেলে plan-টি ভুল তথ্যে বানানো — সাধারণত পুরোনো statistics (অংশ ৯)।

MySQL-এর EXPLAIN ফল দেয় একটি টেবিল হিসেবে, প্রতিটি টেবিলের জন্য একটি সারি। চারটি কলাম সবচেয়ে জরুরি:

mysql> EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY amount;
+-------+------+---------------------+------+-----------------------------+
| table | type | key                 | rows | Extra                       |
+-------+------+---------------------+------+-----------------------------+
| orders| ref  | idx_orders_customer |   10 | Using filesort              |
+-------+------+---------------------+------+-----------------------------+
(পরিষ্কার রাখতে id, select_type, possible_keys, key_len, ref, filtered কলাম বাদ)

MySQL 8.0-এর নতুন সংস্করণে EXPLAIN ANALYZE আর গাছ-আকারের EXPLAIN FORMAT=TREE-ও আছে, PostgreSQL-এর মতো। Oracle-এ EXPLAIN PLAN FOR …, SQL Server-এ graphical execution plan — নাম আলাদা, পড়ার প্রশ্ন একই: পুরো টেবিল না index, কতগুলো সারি, আলাদা sort আছে কি না।

৩ · Composite index আর leftmost prefix নিয়ম

একাধিক কলাম মিলে বানানো index-কে বলে composite index: CREATE INDEX … ON orders(customer_id, order_date). এটি দুটি আলাদা index নয় — একটিই সাজানো তালিকা, যেখানে entry-গুলো আগে প্রথম কলাম দিয়ে সাজানো, প্রথম কলাম সমান হলে দ্বিতীয় কলাম দিয়ে। ঠিক টেলিফোন ডিরেক্টরির মতো: আগে পদবি, একই পদবির ভেতরে নাম। এই সাজানোর ধরন থেকেই সবচেয়ে জরুরি নিয়মটি আসে — leftmost prefix. ধাপে ধাপে দেখুন:

এবার orders-এ একই নিয়ম পরীক্ষা করুন — (customer_id, order_date) index, তিন রকম প্রশ্ন:

CREATE INDEX idx_cust_date ON orders(customer_id, order_date);
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE customer_id = 42 AND order_date >= '2025-01-01';
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE order_date = '2025-12-01';
SELECT order_id, order_date, amount FROM orders
WHERE customer_id = 42 AND order_date >= '2025-01-01';

একটি ফল: (customer_id, order_date) থাকলে আলাদা (customer_id) index প্রায় সবসময় অপ্রয়োজনীয় — বড়টির prefix-ই সেই কাজ করে। অংশ ৮-এ এমন দ্বৈত index খুঁজে মোছার কথা আসবে।

৪ · কলামের ক্রম — আগে সমতা, পরে range

প্রশ্ন: “গ্রাহক ৪২-এর ২০২৫ সালের অর্ডার”। শর্ত দুটি — customer_id-এ সমতা (=), order_date-এ range (BETWEEN). index দুইভাবে বানানো যায়: (order_date, customer_id) অথবা (customer_id, order_date). দুটিতেই দুই কলাম আছে, তবু কাজের পরিমাণে আকাশ-পাতাল তফাত:

লাইভ যাচাই। SQLite-এর INDEXED BY দিয়ে নির্দিষ্ট index ব্যবহার করতে বাধ্য করা যায় — শুধু পরীক্ষার জন্য, বাস্তব কোডে নয়:

CREATE INDEX idx_cust_date ON orders(customer_id, order_date);
CREATE INDEX idx_date_cust ON orders(order_date, customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders INDEXED BY idx_date_cust
WHERE customer_id = 42 AND order_date BETWEEN '2025-01-01' AND '2025-12-31';
EXPLAIN QUERY PLAN SELECT * FROM orders INDEXED BY idx_cust_date
WHERE customer_id = 42 AND order_date BETWEEN '2025-01-01' AND '2025-12-31';
SELECT COUNT(*) AS in_2025 FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
SELECT COUNT(*) AS cust42_2025 FROM orders
WHERE customer_id = 42 AND order_date BETWEEN '2025-01-01' AND '2025-12-31';

৫ · Covering index — টেবিলে না গিয়েই উত্তর

সাধারণ index-এ entry থাকে (index-এর কলাম, সারির ঠিকানা)। SQLite-এ ঠিকানা হলো rowid; orders-এ order_id INTEGER PRIMARY KEY নিজেই rowid. কোয়েরি এমন কলাম চাইলে যা index-এ নেই, প্রতিটি মেলা entry-র জন্য টেবিলের B+ tree-তে আলাদা একটি খোঁজ লাগে — table lookup. আর কোয়েরির দরকারি সব কলাম index-এই থাকলে সেটি covering index: টেবিলে যেতেই হয় না। কতটা বাঁচে, একটি বড় টেবিল ধরে হিসাব করুন (ধরা যাক দুই গাছেরই উচ্চতা ৩, আর ক্যাশে কিছু নেই — সবচেয়ে খারাপ অবস্থা):

CREATE INDEX idx_cust_date ON orders(customer_id, order_date);
EXPLAIN QUERY PLAN SELECT order_date, amount FROM orders WHERE customer_id = 42;
DROP INDEX idx_cust_date;
CREATE INDEX idx_cust_date_amt ON orders(customer_id, order_date, amount);
EXPLAIN QUERY PLAN SELECT order_date, amount FROM orders WHERE customer_id = 42;
EXPLAIN QUERY PLAN SELECT order_id, amount FROM orders WHERE customer_id = 42;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;

PostgreSQL (11 থেকে) আর SQL Server-এ আরও পরিষ্কার উপায় আছে — INCLUDE. INCLUDE-এর কলাম শুধু index-এর পাতায় বসে, খোঁজা বা সাজানোর key-তে নয়; তাই গাছের মাঝের স্তর ছোট থাকে, আর UNIQUE index-এর অনন্যতার নিয়মেও এগুলো গোনা হয় না:

-- খোঁজা হয় customer_id আর order_date দিয়ে; amount শুধু বহন করা হয়
CREATE INDEX idx_cust_date_inc ON orders (customer_id, order_date) INCLUDE (amount);

-- অনন্যতা শুধু email-এ, name পাতায় বহন করা
CREATE UNIQUE INDEX uq_email ON users (email) INCLUDE (name);

MySQL InnoDB-তে INCLUDE নেই, কিন্তু সেখানে টেবিল নিজেই primary key দিয়ে সাজানো (clustered), আর প্রতিটি secondary index-এর entry-তে primary key-র কলামগুলো থাকে। তাই InnoDB-তে primary key কলাম চাওয়া কোয়েরি অনেক সময় এমনিই covering হয় — SQLite-এর rowid-এর মতোই।

৬ · ORDER BY আর GROUP BY-এর জন্য index

Index সাজানো তালিকা — তাই শুধু খোঁজায় নয়, সাজানোতেও কাজে লাগে। “গ্রাহক ৪২-এর সর্বশেষ ৩টি অর্ডার” প্রশ্নে index যদি (customer_id, order_date) হয়, তবে গ্রাহক ৪২-এর entry-গুলো আগে থেকেই তারিখের ক্রমে; শেষ দিক থেকে ৩টি পড়লেই উত্তর। index শুধু (customer_id) হলে ১০টি সারি পড়ে আলাদা করে সাজাতে হয় — temp B-tree. GROUP BY-ও তাই: সাজানো index-এ একই status-এর সব entry পাশাপাশি, তাই দল বানাতে আলাদা sort লাগে না।

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT order_id, order_date FROM orders
WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 3;
EXPLAIN QUERY PLAN SELECT status, COUNT(*) FROM orders GROUP BY status;
CREATE INDEX idx_cust_date ON orders(customer_id, order_date);
CREATE INDEX idx_status ON orders(status);
EXPLAIN QUERY PLAN SELECT order_id, order_date FROM orders
WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 3;
EXPLAIN QUERY PLAN SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT order_id, order_date FROM orders
WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 3;

LIMIT থাকলে লাভ আরও বড়: সাজানো index থেকে প্রথম ৩টি পেয়েই থামা যায়, সব মেলা সারি পড়তে হয় না। দোকানের অ্যাপে “সর্বশেষ অর্ডার”, ফেসবুক-ধাঁচের “নতুন পোস্ট আগে” — এ ধরনের পাতা প্রায় সবসময় (filter-এর কলাম, sort-এর কলাম) index-এ চলে, আর পর্ব ১৫-এর keyset pagination-ও এর ওপরই দাঁড়িয়ে। দুটি সতর্কতা: ORDER BY a ASC, b DESC-এর মতো মিশ্র দিক হলে index-ও সেভাবে বানাতে হয় ((a, b DESC)); আর filter-এ range থাকলে (যেমন customer_id IN (…) বা >) তার পরের কলামের ক্রম আর একটানা থাকে না, তাই sort লাগতে পারে।

৭ · Selectivity, cardinality — আর যখন index উপেক্ষিত হয়

দুটি শব্দ আলাদা করে বুঝুন। একটি কলামের cardinality হলো তাতে কতগুলো ভিন্ন মান: orders.status-এ ৪টি, customers.phone-এ ২,০০০টি (প্রতিটি সারিতে আলাদা)। আর একটি শর্তের selectivity হলো শর্তটি মোট সারির কত ভাগ রাখে: phone = '01300332598' রাখে ২,০০০-এর ১টি (০.০৫%), কিন্তু status = 'delivered' রাখে ২০,০০০-এর ৬,৬৬৬টি (প্রায় ৩৩%).

কম selectivity-তে index লাভ দেয় না, কারণ খরচের হিসাব বদলে যায়। পুরো টেবিল পড়া হলো page-এর পর page ক্রমানুসারে পড়া, যা ডিস্কে সবচেয়ে সস্তা। index দিয়ে ৬,৬৬৬টি সারি আনা মানে ৬,৬৬৬টি আলাদা table lookup, বেশিরভাগই এলোমেলো page-এ (random I/O) — আর orders-এর প্রায় প্রতিটি page-এ কোনো না কোনো delivered অর্ডার আছে, তাই শেষে সব page-ই পড়া হয়, তাও এলোমেলো ক্রমে আর একাধিকবার। PostgreSQL ডিফল্টে একটি random page-এর খরচ ধরে 4.0, ক্রমিক page-এর 1.0 (random_page_cost, seq_page_cost). তাই সাধারণত শর্তটি সারির সামান্য কয়েক শতাংশের বেশি রাখলেই PostgreSQL Seq Scan বা Bitmap Heap Scan বেছে নেয় — সীমাটি নির্দিষ্ট নয়, statistics আর সারিগুলো ডিস্কে কতটা সাজানো তার ওপর নির্ভর করে। SQLite-এর planner সরলতর; এই ডেটায় status-এ index থাকলে সে status = 'delivered'-এও index ব্যবহার করে (আমাদের পরীক্ষায় SEARCH … USING INDEX idx_status (status=?))। তাই status-এর মতো কলামে একা index খুব কমই কাজের; composite index-এর অংশ হিসেবে, বা partial index-এ (এই অংশের শেষে), সেটি অনেক বেশি উপযোগী।

Sargable শর্ত — কলামকে খালি রাখুন

পর্ব ১৭-এ দেখেছেন, sargable শর্তে কলামটি একা এক পাশে থাকে, কোনো ফাংশন বা হিসাবে মোড়া নয়। কারণটি সহজ: index-এ সাজানো আছে order_date-এর মান, strftime('%Y', order_date)-এর নয়। ফাংশনের ফল কোথায় শুরু, index জানে না — প্রতিটি সারিতে ফাংশন চালিয়ে দেখতে হয়। একই প্রশ্ন দুইভাবে লিখে দেখুন:

CREATE INDEX idx_date ON orders(order_date);
EXPLAIN QUERY PLAN SELECT SUM(amount) FROM orders
WHERE strftime('%Y', order_date) = '2025';
EXPLAIN QUERY PLAN SELECT SUM(amount) FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';
SELECT SUM(amount) FROM orders WHERE strftime('%Y', order_date) = '2025';
SELECT SUM(amount) FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE phone LIKE '%2598';
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE phone GLOB '01300*';

একটি SQLite-বিশেষ খুঁটিনাটি: SQLite-এ LIKE '0130%'-ও এখানে SCAN দেয়, কারণ SQLite-এর LIKE ডিফল্টে বড়-ছোট হাতের অক্ষর আলাদা করে না, অথচ index সাধারণ (BINARY) ক্রমে সাজানো। কলামটি COLLATE NOCASE হলে, বা GLOB ব্যবহার করলে, prefix-খোঁজা index পায়। PostgreSQL-এ LIKE 'x%' সাধারণ B-tree index পায় শুধু "C" collation-এ, নয়তো text_pattern_ops দিয়ে বানানো index লাগে; MySQL-এ LIKE 'x%' সাধারণত range হিসেবে index পায়। আর মাঝখানের শব্দ খোঁজা ('%x%') আসলে full-text search-এর কাজ — পর্ব ৩০.

Index অকেজো করার আরও তিনটি প্রচলিত পথ:

-- phone VARCHAR(15), index আছে
EXPLAIN SELECT * FROM customers WHERE phone = 1711000001;    -- type: ALL (পুরো টেবিল)
EXPLAIN SELECT * FROM customers WHERE phone = '01711000001'; -- type: const/ref, key: phone

Expression index আর partial index — কোয়েরি বদলানো না গেলে

কখনো কোয়েরি বদলানো যায় না — হয়তো একটি রিপোর্টিং টুল নিজেই strftime('%Y', …) লেখে, বা email সবসময় lower(email) দিয়ে খোঁজা হয়। তখন ফাংশনের ফলের ওপরই index বানান: expression index. ফাংশনটি অবশ্যই deterministic হতে হবে — একই ইনপুটে সবসময় একই ফল। আর যদি কোয়েরি টেবিলের একটি নির্দিষ্ট অংশই বারবার খোঁজে — যেমন pending অর্ডার, যা মোটের প্রায় এক-পঞ্চমাংশ — তবে শুধু সেই সারিগুলোর index বানান: partial index. ছোট index, কম লেখার খরচ।

CREATE INDEX idx_order_year ON orders(strftime('%Y', order_date));
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM orders
WHERE strftime('%Y', order_date) = '2025';
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM orders
WHERE substr(order_date, 1, 4) = '2025';
CREATE INDEX idx_pending ON orders(order_date) WHERE status = 'pending';
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE status = 'pending' AND order_date >= '2025-12-01';
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE status = 'shipped' AND order_date >= '2025-12-01';
SELECT COUNT(*) FROM orders WHERE status = 'pending' AND order_date >= '2025-12-01';

অন্য DBMS-এ: PostgreSQL-এ দুটিই আছে (CREATE INDEX … ON users (lower(email)), আর … WHERE status = 'pending'). MySQL 8.0.13 থেকে functional key part আছে, লিখতে হয় দুই বন্ধনীতে — ((lower(email))) — কিন্তু partial index নেই। SQL Server-এ partial index-এর নাম filtered index, আর expression-এর জন্য computed column বানিয়ে তাতে index দিতে হয়।

৮ · Foreign key-এর index, আর বেশি index-এর দাম

পর্ব ৬ আর ২৩-এ foreign key দেখেছেন। একটি বহুল প্রচলিত ভুল: ধরে নেওয়া যে foreign key কলামে index আপনা-আপনি হয়। PostgreSQL, SQL Server আর SQLite-এ হয় না — শুধু referenced দিকে (primary key বা UNIQUE) index থাকে। MySQL InnoDB ব্যতিক্রম: FK কলামে উপযুক্ত index না থাকলে নিজেই বানিয়ে নেয়। FK কলামে index দুই জায়গায় লাগে — JOIN-এ, আর parent সারি মোছা বা বদলানোর সময় child খোঁজায় (পর্ব ২৩-এর CASCADE বা RESTRICT যা-ই হোক):

EXPLAIN QUERY PLAN
SELECT c.name, o.order_date, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
WHERE c.phone = '01300332598';
EXPLAIN QUERY PLAN DELETE FROM customers WHERE customer_id = 42;
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT c.name, o.order_date, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
WHERE c.phone = '01300332598';
EXPLAIN QUERY PLAN DELETE FROM customers WHERE customer_id = 42;

প্রতিটি index লেখার খরচ বাড়ায়

Index বিনা পয়সায় আসে না। k-টি index-ওয়ালা টেবিলে একটি INSERT মানে টেবিলের গাছে একটি আর প্রতিটি index-এর গাছে একটি করে, মোট k + 1টি B+ tree-তে লেখা — প্রতিটিতে প্রায় h-টি page ছোঁয়া, মাঝে মাঝে page split. DELETE-ও তাই। UPDATE শুধু সেই index-গুলো বদলায়, যাদের কলাম বদলেছে। একটি লেখা থেকে এই বহুগুণ কাজকে বলে write amplification. সাথে ডিস্কের জায়গা, buffer pool-এর মেমোরি (পর্ব ৩৯), আর optimizer-এর বাছাইয়ের সময়।

তাই নিয়মিত তিন ধরনের index খুঁজে মুছুন:

• দ্বৈত: হুবহু একই কলাম একই ক্রমে, ভিন্ন নামে — ORM আর হাতের migration মিলে প্রায়ই হয়।
• Prefix-দ্বৈত: (customer_id) আর (customer_id, order_date) দুটিই আছে; প্রথমটির কাজ দ্বিতীয়টি করে। ব্যতিক্রম: ছোটটি UNIQUE হলে (অনন্যতার নিয়ম রক্ষা করে), বা বড়টি অনেক চওড়া হলে।
• অব্যবহৃত: PostgreSQL-এ pg_stat_user_indexes-এ idx_scan = 0, MySQL-এ sys.schema_unused_indexes — তবে পরিসংখ্যান যথেষ্ট দিন ধরে জমেছে কি না দেখুন; মাসে একবার চলা রিপোর্টের index এক সপ্তাহে অব্যবহৃত দেখাতে পারে।

৯ · Statistics, ANALYZE আর tuning-এর কর্মপদ্ধতি

Optimizer বাছাই করে অনুমান দিয়ে: কোন শর্ত কতগুলো সারি রাখবে। অনুমানের উৎস statistics — টেবিলে কত সারি, প্রতিটি index-এর কলামে গড়ে একটি মানে কতগুলো সারি। statistics বানানোর কমান্ড ANALYZE. SQLite ফলটি রাখে sqlite_stat1 টেবিলে:

CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_cust_date ON orders(customer_id, order_date);
CREATE INDEX idx_status_date ON orders(status, order_date);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE order_date = '2025-12-01';
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1 ORDER BY tbl, idx;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE order_date = '2025-12-01';
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE status = 'delivered' AND customer_id = 42;

Statistics পুরোনো হলে optimizer ভুল অনুমানে ভুল plan বানায় — যেমন রাতারাতি লাখো সারি আমদানির পর। PostgreSQL-এর autovacuum নিয়মিত নিজে ANALYZE চালায়, MySQL InnoDB টেবিলের উল্লেখযোগ্য অংশ বদলালে নিজে হিসাব নতুন করে; SQLite-এ নিজে চালাতে হয় (SQLite-এর নির্দেশিকা সংযোগ বন্ধের আগে PRAGMA optimize চালানোর পরামর্শ দেয়)। বড় আমদানির পর সব DBMS-এ হাতে ANALYZE চালানো ভালো অভ্যাস।

Tuning-এর কর্মপদ্ধতি

সব মিলিয়ে index ডিজাইন অনুমানের খেলা নয়, একটি মাপজোখের চক্র। একটি বাস্তবধর্মী উদাহরণে (PostgreSQL, ৫০ লাখ অর্ডার; সংখ্যাগুলো ব্যাখ্যার জন্য ধরে নেওয়া) চক্রটি দেখুন:

১০ · AI-এর যুগে index ডিজাইন

“কোয়েরি ধীর, index বলে দাও” — AI দ্রুত একটি CREATE INDEX লিখে দেয়। সমস্যা হলো, সে আপনার ডেটার মাপ, মানগুলোর বণ্টন, অন্য কোয়েরি আর লেখার হার জানে না; প্রায়ই plan না দেখেই উত্তর দেয়। সাধারণ ভুলগুলো:

AI-এর দেওয়া index চালু করার আগে — চেকলিস্ট

• আগের plan আর সময় সংরক্ষণ করেছি; নতুন index-এর পর EXPLAIN (ANALYZE)-এ সত্যিই সেটি ব্যবহৃত হচ্ছে
• আসল মাপের ডেটায় (staging) মেপেছি — ২০টি সারির টেস্ট টেবিলে সব plan-ই এক রকম দেখায়
• কলামের ক্রম যাচাই করেছি: সমতা → range বা sort; plan-এ অপ্রত্যাশিত temp B-tree বা Sort নেই
• ফলের সারির সংখ্যা index-এর আগে-পরে একই (index ফল বদলায় না, কিন্তু সঙ্গে কোয়েরি বদলালে বদলাতে পারে)
• বিদ্যমান index-এর সাথে দ্বৈত বা prefix-দ্বৈত কি না দেখেছি; লেখার সময় আর index-এর আকার মেপেছি
• চালু সিস্টেমে লেখা আটকানো ছাড়া বানানোর পথ (যেমন CONCURRENTLY) আর ফেরার পথ (DROP INDEX) ঠিক করেছি