SQLite EXPLAIN QUERY PLAN: קריאת הפלט וזיהוי שאילתות איטיות

EXPLAIN QUERY PLAN מראה איך שאילתה תרוץ

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

פשוט הוסיפו את מילות המפתח לפני כל פקודה:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

הפלט נראה בערך כך:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

השורה הבודדת הזו אומרת הרבה: SQLite מבצעת SEARCH (ולא סריקה) על הטבלה users, בעזרת האינדקס הייחודי שנוצר אוטומטית עבור email, כש-email הוא מפתח החיפוש. בדיוק מה שהייתם רוצים לראות.

SCAN מול SEARCH: הדבר הראשון שקוראים

כל שורה בתוכנית מתחילה ב-SCAN או ב-SEARCH. ההבחנה הזו היא האות החשוב ביותר בכל הפלט.

• SCAN <table>: SQLite קוראת כל שורה בטבלה (או כל רשומה באינדקס). העלות גדלה עם גודל הטבלה.
• SEARCH <table> USING ...: SQLite קופצת ישירות לשורות המתאימות דרך אינדקס או מפתח ראשי. העלות גדלה עם גודל התוצאה, לא עם גודל הטבלה.

הנה השוואה זו לצד זו. לעמודה אחת יש אינדקס, ולשנייה אין:

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

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

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

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

התוכנית הראשונה מדווחת SEARCH orders USING INDEX idx_orders_customer. השנייה מדווחת SCAN orders: אין אינדקס על status, ולכן SQLite קוראת כל שורה. בטבלה קטנה זה בלתי מורגש; בטבלה של מיליון שורות זה ההבדל בין אלפיות שנייה לשניות.

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

איך מוודאים שנעשה שימוש באינדקס

הביטוי שצריך לחפש הוא USING INDEX <name> (או USING COVERING INDEX <name>, על זה בהמשך). אם יצרתם אינדקס בתקווה שהמתכנן ישתמש בו, כך בודקים:

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

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

אתם אמורים לראות SEARCH events USING INDEX idx_events_user (user_id=?). אם במקום זה התוכנית אומרת SCAN events, משהו מונע מהמתכנן להשתמש באינדקס. הסיבות הנפוצות: עטיפת העמודה בפונקציה (WHERE lower(user_id) = ...), השוואה בין טיפוסים שונים, או שימוש ב-LIKE '%foo%' עם תו כללי בהתחלה.

בדיקה מהירה של זה:

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

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

ה-+ 0 הזה מנטרל את האינדקס, והתוכנית חוזרת ל-SCAN events. כל ביטוי על העמודה המאונדקסת עושה אותו דבר.

אינדקסים מכסים נראים אחרת

כשאינדקס מכיל את כל העמודות שהשאילתה צריכה, SQLite יכולה לענות על השאילתה מהאינדקס בלבד, בלי לגעת בטבלה. התוכנית מדווחת USING COVERING INDEX:

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

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

התוכנית: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). השאילתה מבקשת את price, והאינדקס כבר שומר את sku ואת price, ולכן SQLite אף פעם לא קוראת את הטבלה עצמה. אינדקס מכסה (covering index) הוא התוכנית המהירה ביותר שאפשר לקבל בחיפוש, וכדאי להכיר אותו כשבוחרים אילו עמודות לאנדקס יחד.

קריאת תוכניות של JOIN

ב-JOIN התוכניות נעשות מעניינות. כל שורה בתוכנית מתאימה לטבלה אחת ב-JOIN, וסדר השורות הוא הסדר שבו SQLite מבקרת בטבלאות. הטבלה הראשונה היא הטבלה החיצונית (outer); בטבלאות הבאות מחפשים פעם אחת לכל שורה של הטבלה החיצונית.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

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

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

תוכנית טיפוסית:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

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

אם הייתם רואים SCAN o בשורה השנייה, כל חיפוש של לקוח היה מפעיל מעבר מלא על orders. בטבלה גדולה זה אסון. הפתרון כמעט תמיד הוא אינדקס על עמודת ה-JOIN.

שאילתות מורכבות ותת-שאילתות

תוכניות של UNION, EXCEPT ותת-שאילתות מקוננות. כל ענף מופיע מוזח מתחת לאב שלו:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

תראו שתי שורות בנות מתחת לכותרת COMPOUND QUERY, אחת לכל ענף. תת-שאילתות ו-CTE עובדים באופן דומה: כל אחד מקבל צומת תוכנית מוזח משלו, ואת כל אחד קוראים באותה עדשה של SCAN מול SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

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

EXPLAIN מול EXPLAIN QUERY PLAN

אלה שני דברים שונים, ואנשים מבלבלים ביניהם.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (בלי QUERY PLAN) שופך את ה-bytecode שהמכונה הווירטואלית של SQLite תריץ: עשרות opcodes ברמה נמוכה כמו OpenRead, SeekRowid, Column, ResultRow. שימושי אם אתם מדבגים את המנוע עצמו. כמעט אף פעם לא שימושי לכוונון.

EXPLAIN QUERY PLAN הוא הסיכום הקריא שאתם באמת רוצים. במקרה של ספק, תמיד פנו ל-EXPLAIN QUERY PLAN.

תהליך עבודה לשאילתות איטיות

כששאילתה איטית, הלולאה נראית כך:

1. הריצו עליה EXPLAIN QUERY PLAN.
2. עבור כל שורת טבלה, שאלו: האם זה SCAN או SEARCH? בטבלה גדולה, SCAN הוא החשוד.
3. אם SCAN מסנן לפי עמודה כלשהי, שקלו אינדקס על העמודה הזו.
4. ב-JOIN, ודאו שהטבלאות בלולאה הפנימית משתמשות ב-SEARCH USING INDEX על עמודת ה-JOIN.
5. הריצו שוב EXPLAIN QUERY PLAN אחרי הוספת האינדקס. התוכנית אמורה להשתנות. אם היא לא השתנתה, המתכנן החליט שהאינדקס לא שווה שימוש, בדרך כלל כי הטבלה קטנה או שהמסנן לא סלקטיבי מספיק.

דוגמה מעשית לשלב 5:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

התוכנית השתנתה מ-SCAN ל-SEARCH. זה האות שהאינדקס עושה את העבודה שלו. (בטבלה חדשה וכמעט ריקה המתכנן עשוי עדיין לסרוק, כי אין מספיק נתונים כדי שהאינדקס ישתלם. מלאו את הטבלה או הריצו ANALYZE, ולעתים קרובות הבחירה מתהפכת.)

מה התוכנית לא תגיד לכם

EXPLAIN QUERY PLAN מתאר אסטרטגיה, לא עלות. הוא לא יגיד לכם שהשאילתה לקחה 800 ms או החזירה 50,000 שורות. בשביל זה צריך מדידת זמן (.timer on ב-CLI) וספירת שורות. התוכנית והמדידה משלימות זו את זו: התוכנית אומרת לכם למה שאילתה איטית, והטיימר אומר לכם אם היא באמת איטית.

עוד שתי מגבלות שכדאי להכיר:

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

הצעד הבא: ANALYZE ו-VACUUM

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