אינדקסים מורכבים ב-SQLite: סדר עמודות וכלל הקידומת השמאלית

אינדקס אחד, כמה עמודות

אינדקס מורכב, שלפעמים נקרא גם אינדקס על כמה עמודות, הוא אינדקס יחיד שנבנה על שתי עמודות או יותר. יוצרים אותו על ידי פירוט העמודות לפי הסדר:

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

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

האינדקס idx_orders_customer_status שומר רשומות ממוינות קודם לפי customer_id, ואז לפי status בתוך כל לקוח. הסידור הזה הוא כל הסיפור: כל השאר באינדקסים מורכבים נובע ממנו.

המודל המחשבתי: ספר טלפונים ממוין

דמיינו ספר טלפונים ישן. הרשומות ממוינות לפי שם משפחה, ובתוך כל שם משפחה, לפי שם פרטי. זה בדיוק איך שנראה אינדקס על (last_name, first_name).

חלק מהחיפושים זולים, אחרים לא:

• "מצאו את כל מי ששמו Patel": קל, כל ה-Patel יושבים יחד.
• "מצאו את Priya Patel": קל, קופצים ל-Patel ואז סורקים עד Priya.
• "מצאו את כל מי ששמה Priya": איטי, צריך לסרוק כל עמוד. ה-Priya מפוזרות על פני כל שמות המשפחה.

אינדקס מורכב ב-SQLite עובד באותה דרך. העמודה הראשונה היא מפתח המיון הראשי; העמודה השנייה ממיינת רק רשומות שחולקות את אותו ערך בעמודה הראשונה.

כלל הקידומת השמאלית

SQLite יכולה להשתמש באינדקס מורכב לשאילתה רק כשפסוקית ה-WHERE מגבילה קידומת שמאלית של העמודות שלו. לאינדקס על (a, b, c):

• סינון לפי a: משתמש באינדקס.
• סינון לפי a ו-b: משתמש באינדקס.
• סינון לפי a, b ו-c: משתמש באינדקס.
• סינון לפי b לבד, או c לבד, או b ו-c: האינדקס לא בשימוש.

אפשר לוודא את זה ישירות עם EXPLAIN QUERY PLAN:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

התוכנית הראשונה מדווחת SEARCH events USING INDEX idx_events_user_kind_time. השנייה נופלת חזרה ל-SCAN events: סינון לפי kind לבד מדלג על העמודה המובילה user_id, ולכן האינדקס חסר תועלת לשאילתה הזו.

סדר העמודות הוא החלטת תכנון

כיוון שהקידומת השמאלית חשובה, הסדר שבו אתם מפרטים עמודות ב-CREATE INDEX הוא בחירה אמיתית, לא עניין של סגנון. שני כללי אצבע:

1. שימו ראשונה את העמודה שאתם מסננים לפיה הכי הרבה. העמודה הזו פותחת את האינדקס למגוון הרחב ביותר של שאילתות.
2. שימו עמודות שוויון לפני עמודות טווח. SQLite יכולה לצלול לתוך האינדקס בעזרת =, ואז לסרוק טווח רציף בעזרת <, > או BETWEEN, אבל רק על העמודה ה_אחרונה_ שבשימוש.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- טוב: שוויון על region קודם, טווח על sold_at אחריו.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

התוכנית מציגה SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite קופצת ישר ל-region = 'EU', ואז מתקדמת לאורך טווח התאריכים. הפכו את סדר העמודות ל-(sold_at, region), ואותה שאילתה תצטרך לסרוק כל שורה בטווח התאריכים ולבדוק שוב את region בכל אחת.

מורכב מול כמה אינדקסים על עמודה אחת

שאלה נפוצה: ליצור אינדקס אחד על (a, b), או שני אינדקסים נפרדים על a ועל b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- אפשרות א: אינדקס מורכב אחד.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- אפשרות ב (חלופה): שני אינדקסים על עמודה אחת.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

לסינון המשולב, האינדקס המורכב מהיר יותר: SQLite הולכת ישר לרשומות (project_id, state) המתאימות. עם שני אינדקסים על עמודה אחת, SQLite בדרך כלל בוחרת אחד, משתמשת בו כדי לצמצם שורות, ואז בודקת שוב את העמודה השנייה בכל שורה מתאימה. לפעמים היא יכולה לחתוך ביניהם, אבל האינדקס המורכב הוא התשובה הנקייה יותר כשהעמודות נשאלות יחד.

אם project_id ו-state נשאלות גם בנפרד, ייתכן שתרצו את שניהם: את המורכב לסינון המשולב, ובנוסף אינדקס על עמודה אחת על state לשאילתות שמסננות רק לפיה.

Covering Indexes

כשאינדקס כולל כל עמודה ששאילתה צריכה, גם עמודות הסינון וגם העמודות הנבחרות, SQLite יכולה לענות על השאילתה בלי לגעת בטבלה בכלל. זה covering index, וזה המהיר ביותר ששאילתה יכולה להיות.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'first'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'rush');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

התוכנית מציגה USING COVERING INDEX idx_invoices_cover. השאילתה קוראת את issued_at ו-total ישירות מהאינדקס: אין צורך ב-notes וב-id, ולכן הטבלה עצמה אף פעם לא נפתחת. הוספת עמודה לאינדקס מורכב רק כדי לכסות שאילתה חמה היא עסקה משתלמת כשהשאילתה הזו רצה כל הזמן.

אילוצי UNIQUE מורכבים

אינדקסים מורכבים גם אוכפים ייחודיות על צירופי עמודות. שימושי כשאף עמודה לא ייחודית בפני עצמה, אבל הצירוף חייב להיות:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- תקין, קורס אחר
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- נכשל: הזוג חוזר על עצמו

ההכנסה השלישית זורקת UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. אותו זוג כבר קיים באינדקס, ולכן SQLite מסרבת לכפילות.

מלכודות שכדאי להכיר

• OR בין עמודות שאינן מובילות חוסם את האינדקס. WHERE a = 1 OR b = 2 על אינדקס (a, b) בדרך כלל לא יכול להשתמש באינדקס בכלל: SQLite צריכה לשקול כל ענף בנפרד.
• פונקציות על עמודות עם אינדקס מנטרלות את האינדקס. WHERE lower(email) = 'x' לא ישתמש באינדקס על email. בנו אינדקס על הביטוי במקום, או נרמלו את הנתונים בזמן ההכנסה.
• אינדקסים לא באים בחינם. כל אינדקס מתעדכן בכל INSERT, UPDATE (של עמודות באינדקס) ו-DELETE. שלושה אינדקסים מורכבים על טבלה עם הרבה כתיבות יכולים להשתלט על עלות הכתיבה.
• הריצו ANALYZE אחרי בניית אינדקסים. המתכנן של SQLite משתמש בסטטיסטיקות ש-ANALYZE אוסף כדי לבחור בין אינדקסים מועמדים. בלי הסטטיסטיקות האלה, הוא נופל חזרה להיוריסטיקות שלא תמיד אופטימליות.

תהליך עבודה מעשי

כשמכווננים שאילתה איטית, הלולאה נראית בדרך כלל כך:

1. הריצו EXPLAIN QUERY PLAN על השאילתה כדי לראות מה SQLite עושה היום.
2. אם היא סורקת, הסתכלו על פסוקית ה-WHERE: מה עמודת השוויון? מה עמודת הטווח? מה נבחר?
3. בנו אינדקס מורכב שמסודר קודם לפי שוויון ואחר כך לפי טווח, והוסיפו בסוף את העמודות הנבחרות אם כיסוי עוזר.
4. הריצו ANALYZE.
5. הריצו שוב EXPLAIN QUERY PLAN. ודאו שהתוכנית השתנתה ושהאינדקס בשימוש.
6. מדדו את זמן השאילתה לפני ואחרי על נתונים מייצגים.

דלגו על שלב 6 על אחריותכם. אינדקס ש_נראה_ נכון בתוכנית עדיין יכול להיות איטי יותר בפועל אם הטבלה קטנה או אם המתכנן בוחר נתיב אחר.

הבא בתור: אינדקסים חלקיים

אינדקסים מורכבים מכסים כל שורה בטבלה. אבל לעיתים קרובות רק תת קבוצה קטנה של שורות חשובה: פניות פתוחות, משימות שלא עובדו, רשומות שלא נמחקו. אינדקס חלקי מאפשר לבנות אינדקס רק על השורות האלה, עם פסוקית WHERE שאפויה לתוך האינדקס עצמו. זה העמוד הבא.