SQLite Indexes: CREATE INDEX, מתי להשתמש בהם ולמה

מה זה בעצם אינדקס

אינדקס הוא מבנה נתונים נפרד, B-tree ממוין, שמאפשר ל-SQLite למצוא שורות לפי ערך של עמודה בלי לסרוק את כל הטבלה. בלעדיו, שאילתה כמו WHERE email = 'rosa@example.com' קוראת כל שורה ובודקת כל אחת. עם אינדקס על email, SQLite עוברת על העץ בערך ב-log(n) צעדים וקופצת ישר להתאמה.

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

יצירת אינדקס

התחביר הבסיסי:

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

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

מוסכמת שמות: רוב הצוותים משתמשים ב-idx_<table>_<column> כדי שיהיה ברור למה האינדקס נועד. השם חייב להיות ייחודי בכל מסד הנתונים, לא רק בטבלה, ולכן שם הטבלה הוא חלק ממנו.

כדי להסיר אינדקס:

DROP INDEX idx_users_email;

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

אינדקסים ייחודיים

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

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

ההכנסה השלישית נכשלת עם UNIQUE constraint failed: accounts.username. SQLite כבר יוצרת אינדקסים ייחודיים אוטומטית עבור עמודות PRIMARY KEY ו-UNIQUE, ותראו אותם בשם sqlite_autoindex_<table>_<n>. צריך לכתוב CREATE UNIQUE INDEX רק כשהאילוץ לא הוצהר על הטבלה עצמה.

מה המתכנן עושה בפועל

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

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

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

חפשו בפלט SEARCH ... USING INDEX idx_orders_customer: זה אומר שנעשה שימוש באינדקס. אם אתם רואים SCAN orders, המתכנן החליט שסריקה מלאה של הטבלה זולה יותר (לרוב נכון בטבלאות קטנטנות), או שצורת השאילתה מנעה ממנו להשתמש באינדקס. בהמשך יש עמוד שלם על קריאת התוכניות האלה.

מתי לא ייעשה שימוש באינדקס

לאינדקסים יש כמה נקודות עיוורון מוכרות. כל אחת מאלה מנטרלת את האינדקס על email:

-- פונקציה עוטפת את העמודה
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- תו כללי בהתחלה ב-LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- אי-התאמה בטיפוס מאלצת המרה
SELECT * FROM users WHERE email = 12345;

ה-B-tree ממוין לפי הערך הגולמי של email, כך שכל דבר שמשנה את העמודה בזמן השאילתה מאלץ סריקה. הפתרונות משתנים: לשמור את הנתונים כבר מנורמלים (עמודת email_lower), להשתמש באינדקס על ביטוי (CREATE INDEX idx ON users(lower(email))), או להשתמש בחיפוש הטקסט המלא של SQLite להתאמת תת-מחרוזות.

אינדקסים מכסים

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

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

מכיוון ששתי העמודות שהשאילתה מבקשת נמצאות באינדקס, SQLite מדווחת USING COVERING INDEX. לא צריך לשלוף את השורה. אינדקסים מכסים הם אחת האופטימיזציות המשתלמות ביותר לנתיבי קריאה חמים, והמחיר הוא אינדקס גדול יותר. אינדקסים מרובי עמודות הם נושא בפני עצמו, והעמוד הבא עוסק בהם כמו שצריך.

הצגה ובדיקה של אינדקסים

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

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

זה נותן לכם כל אינדקס במסד הנתונים יחד עם פקודת ה-CREATE שלו. לטבלה אחת, PRAGMA index_list('products'); מציג רק את האינדקסים של הטבלה הזו, ו-PRAGMA index_info('idx_products_name'); מראה אילו עמודות כל אחד מאנדקס. כל דבר שמתחיל ב-sqlite_autoindex_ נוצר אוטומטית עבור אילוץ PRIMARY KEY או UNIQUE, ואי אפשר למחוק אותם.

מתי לא להוסיף אינדקס

כמה מצבים שבהם הוספת אינדקס מחמירה את המצב:

• טבלאות קטנטנות. כמה מאות שורות נסרקות במיקרו-שניות. המתכנן כנראה יתעלם מהאינדקס בכל מקרה, והוספתם עלות כתיבה בשביל כלום.
• עמודות עם הרבה כתיבות שכמעט לא נשלפות. כל כתיבה מעדכנת כל אינדקס. אינדוקס של עמודה שכמעט אף פעם לא מסננים לפיה הוא עלות נטו.
• עמודות עם מעט ערכים שונים, לבדן. אינדקס על עמודת status עם שלושה ערכים אפשריים לא מצמצם הרבה. הוא עדיין יכול לעזור כעמודה השנייה באינדקס מורכב, או כאינדקס חלקי, אבל לבדו הוא לרוב לא שווה את זה.
• כבר מכוסה. אם יש לכם אינדקס על (a, b), אתם לא צריכים גם אחד על (a). SQLite משתמשת בעמודות המובילות של אינדקס מורכב עבור שאילתות שמסננות רק לפי a.

התשובה הכנה לשאלה "האם להוסיף את האינדקס הזה?" היא כמעט תמיד: נסו, הריצו EXPLAIN QUERY PLAN, מדדו עם נתונים מציאותיים, והחליטו.

הצעד הבא: אינדקסים מורכבים

אינדקס על עמודה אחת מכסה הרבה, אבל שאילתות אמיתיות מסננות וממיינות לעתים קרובות לפי כמה עמודות בבת אחת. אינדקסים מורכבים, אינדקסים על (a, b, c), מטפלים בזה, וסדר העמודות חשוב יותר ממה שאנשים מצפים. זה העמוד הבא.