אינדקסים חלקיים ב-SQLite: CREATE INDEX ... WHERE

אינדקס חלקי מכסה רק חלק מהשורות

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

התחביר הוא CREATE INDEX רגיל עם WHERE בסוף:

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
);

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

idx_orders_pending מכיל רשומות רק לשורות שבהן status = 'pending'. הזמנות שנשלחו, בוטלו או הוחזרו לא נמצאות בו בכלל. אם 95% מטבלת ה-orders שלכם היסטוריים ואתם שולפים בעיקר את הפתוחות, זה אינדקס קטן פי 20 לאותה מהירות שאילתה.

מתי המתכנן באמת ישתמש בו

אפשר להשתמש באינדקס חלקי רק כש-SQLite יכולה להוכיח שהשאילתה שלכם מוגבלת לאותן שורות שהאינדקס מכסה. הדרך הנקייה ביותר היא לחזור על פסוקית ה-WHERE של האינדקס בשאילתה:

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
);

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

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

התוכנית אמורה להזכיר USING INDEX idx_orders_pending. הסירו את status = 'pending' מהשאילתה, והמתכנן חוזר לסריקה מלאה של הטבלה: אין לו דרך לדעת שהשאילתה נשארת בתוך התת-קבוצה המאונדקסת.

כלל האצבע: ה-WHERE של השאילתה חייב לגרור את ה-WHERE של האינדקס. שוויון על אותה עמודה ואותו ערך הוא המקרה הבטוח והברור. אי-שוויונות ו-OR מסובכים יותר, בדקו עם EXPLAIN QUERY PLAN.

למה לטרוח: שלושת היתרונות

שלוש סיבות קונקרטיות לכך שאינדקסים חלקיים משתלמים:

1. קטן יותר בדיסק. נשמרות רק השורות המתאימות. בעומס עבודה שבו "1% מהטבלה חם", האינדקס הוא בערך 1% מאינדקס מלא.
2. כתיבות זולות יותר. הכנסות ועדכונים נוגעים באינדקס רק כשהשורה מתאימה לסינון. הכנסה עם status = 'shipped' לטבלה שלמעלה לא נוגעת ב-idx_orders_pending בכלל.
3. אותה מהירות חיפוש. חיפוש ב-B-tree הוא לוגריתמי בגודל האינדקס. אינדקס קטן יותר נותן חיפושים מעט מהירים יותר, אבל הרווח הגדול הוא בכל מה שמסביב: פחות החטאות מטמון, פחות I/O.

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

אינדקסים ייחודיים חלקיים (התכונה המנצחת)

אילוצי UNIQUE רגילים חלים על כל שורה. זו בעיה ברגע שמכניסים מחיקה רכה:

-- נכשל: יש שתי שורות עם email = 'a@x.com', למרות שאחת מהן נמחקה.
CREATE UNIQUE INDEX idx_users_email ON users(email);

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

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- נמחקה מחיקה רכה
    ('a@x.com', '2026-01-05'),  -- נמחקה מחיקה רכה שוב
    ('a@x.com', NULL);          -- שורה חיה אחת, מותר

SELECT email, deleted_at FROM users;

שלוש שורות, אותו אימייל, בלי הפרת אילוץ, כי רק השורה עם deleted_at IS NULL משתתפת בבדיקת הייחודיות. נסו להכניס שורה חיה שנייה עם אותו אימייל, ו-SQLite תזרוק UNIQUE constraint failed.

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

אינדוקס סביב NULL

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

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

שני NULL מתקיימים יחד בשלום, והשורות EXT-001 ו-EXT-002 מובטחות כייחודיות. גם האינדקס קטן יותר, כי שורות NULL לא נשמרות בו בכלל, ולכן חיפושים לפי external_id מהירים גם כשהטבלה גדלה.

למה הסינון יכול להתייחס

פסוקית ה-WHERE של אינדקס חלקי מוגבלת. היא יכולה להתייחס ל:

• עמודות של הטבלה שמאונדקסת.
• קבועים מילוליים.
• קבוצה קטנה של פונקציות מובנות דטרמיניסטיות.

היא לא יכולה להתייחס ל:

• טבלאות אחרות.
• תת-שאילתות.
• פונקציות לא דטרמיניסטיות כמו random() או CURRENT_TIMESTAMP.
• פרמטרים או משתנים.

זה הגיוני: SQLite צריכה להעריך את הסינון בכל הכנסה ועדכון של שורה, והתוצאה חייבת להיות יציבה. לכן זה עובד:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

אבל WHERE created_at > date('now') לא יעבוד: date('now') משתנה עם הזמן, כך שקבוצת השורות המאונדקסות הייתה זזה מתחת לרגליים של SQLite.

תהליך בדיקת שפיות

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

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. האם המתכנן בוחר באינדקס?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. האם הוא מתעלם משאילתות מחוץ לסינון?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. האם הוא מדלג על שאילתות שאין בהן את הסינון בכלל?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

שאילתה 1 אמורה להשתמש ב-idx_jobs_runnable. שאילתות 2 ו-3 אמורות לחזור לסריקה (או לאינדקס אחר, אם יש לכם כזה). אם המתכנן בוחר באינדקס החלקי לשאילתה שלא ציפיתם, קראו שוב את הסינון: ייתכן שהוא רחב יותר ממה שאתם חושבים.

מתי לא להשתמש בו

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

• הסינון מתאים לרוב הטבלה. אם "פעיל" הוא 90% מהשורות, אינדקס חלקי הוא אינדקס רגיל עם צעדים מיותרים. פשוט אנדקסו את העמודה.
• השאילתות שלכם לא כוללות את הסינון מילולית. אם הקוד שלכם משתמש ב-ORM שבונה WHERE status IN (?, ?, ?) או מחשב את הסינון דינמית, המתכנן לעתים קרובות לא יזהה את ההתאמה. בדקו עם EXPLAIN QUERY PLAN, אל תניחו.
• התת-קבוצה החמה משתנה עם הזמן. אינדקס חלקי על "הזמנות מ-30 הימים האחרונים" נשמע מפתה אבל אי אפשר לבטא אותו: הסינון חייב להיות דטרמיניסטי. תצטרכו לבנות את האינדקס מחדש, או לבחור סכמה אחרת (טבלת recent_orders נפרדת או עמודה בוליאנית archived שמעדכנים כל לילה).

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

הבא: קריאת תוכניות שאילתה

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