SQLite LIMIT ו-OFFSET: דפדוף וחיתוך של תוצאות

LIMIT מגביל את מספר השורות

LIMIT הוא הכפתור הפשוט ביותר ב-SQL: הוא אומר ל-SQLite "תני לי לכל היותר כך וכך שורות". שימו אותו בסוף SELECT ותקבלו בחזרה עד המספר הזה של תוצאות, לא יותר, ואולי פחות אם אין בטבלה מספיק שורות.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

INSERT INTO books (title, year) VALUES
    ('The Pragmatic Programmer', 1999),
    ('Code', 1999),
    ('Refactoring', 1999),
    ('Clean Code', 2008),
    ('Designing Data-Intensive Applications', 2017);

SELECT title, year FROM books LIMIT 3;

אתם מקבלים בחזרה את שלוש השורות הראשונות. אילו שלוש בדיוק? כאן הקאץ': בלי ORDER BY, SQLite בוחרת בכל סדר שנוח לה. היום זה אולי סדר ההכנסה; מחר, אחרי עדכון או שינוי באינדקס, אולי לא. LIMIT לבדו מתאים ל"הראו לי דוגמה", אבל ברגע שהסדר חשוב, צריך להגדיר אותו במפורש.

OFFSET מדלג על שורות מההתחלה

צרפו OFFSET ל-LIMIT ותוכלו לבקש פרוסה מאמצע קבוצת התוצאות. OFFSET k זורק את k השורות הראשונות; LIMIT n מחזיר אז עד n שורות ממה שנשאר.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

INSERT INTO books (title, year) VALUES
    ('The Pragmatic Programmer', 1999),
    ('Code', 1999),
    ('Refactoring', 1999),
    ('Clean Code', 2008),
    ('Designing Data-Intensive Applications', 2017);

SELECT title, year
FROM books
ORDER BY year, title
LIMIT 2 OFFSET 2;

זה "דלגו על שתי שורות, החזירו את השתיים הבאות": שורות 3 ו-4 בתוצאה הממוינת. המודל המנטלי: WHERE מסנן, ORDER BY ממיין, OFFSET מדלג, LIMIT מגביל. הם רצים בסדר הזה, וכולם חשובים.

דפדוף תמיד דורש ORDER BY

השימוש הנפוץ ביותר ב-LIMIT וב-OFFSET הוא דפדוף: פיצול רשימה ארוכה לעמודים של, נניח, 20 שורות כל אחד. עמוד 1 הוא LIMIT 20 OFFSET 0, עמוד 2 הוא LIMIT 20 OFFSET 20, וכן הלאה.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    created_at TEXT
);

INSERT INTO posts (title, created_at) VALUES
    ('First post', '2024-01-01'),
    ('Second post', '2024-01-02'),
    ('Third post', '2024-01-03'),
    ('Fourth post', '2024-01-04'),
    ('Fifth post', '2024-01-05');

-- עמוד 2, עם 2 פוסטים בכל עמוד
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2 OFFSET 2;

שני דברים לשים לב אליהם. ראשית, ה-ORDER BY לא נתון למשא ומתן: בלעדיו, ל"עמוד 2" אין משמעות מוגדרת ושורות יכולות להתערבב בין טעינות. שנית, מפתח המיון כולל את id כשובר שוויון. אם לשני פוסטים יש אותו created_at, צריך עמודה ייחודית כדי לתת להם סדר דטרמיניסטי, אחרת המיקומים שלהם יכולים להתחלף ושורה יכולה לדלוף בין עמודים.

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

קיצור: LIMIT n, m

SQLite תומכת בתחביר ישן עם פסיק, לשם תאימות לאחור עם MySQL: LIMIT offset, count. המשמעות זהה ל-LIMIT count OFFSET offset, אבל הסדר הפוך וקל לקרוא אותו לא נכון.

-- שתי אלה שקולות:
SELECT * FROM books LIMIT 10 OFFSET 20;
SELECT * FROM books LIMIT 20, 10;     -- קודם ההיסט, ואז הכמות

הצורה השנייה תמציתית, אבל היא עוקצת את מי שמצפה שהמספר הראשון יהיה הכמות. הישארו עם LIMIT n OFFSET k: זה מפורש ונקרא לפי הסדר.

OFFSET בלי LIMIT: הטריק של LIMIT -1

OFFSET לא תקף לבדו: הדקדוק של SQLite דורש שיבוא אחרי LIMIT. אז איך אומרים "דלגו על 10 השורות הראשונות ותנו לי את כל מה שאחריהן"? המוסכמה היא LIMIT -1, ש-SQLite קוראת כ"בלי גבול עליון".

CREATE TABLE numbers (n INTEGER);
INSERT INTO numbers (n) VALUES (1), (2), (3), (4), (5), (6), (7), (8);

-- מדלגים על 3 הראשונות, מחזירים את השאר
SELECT n FROM numbers ORDER BY n LIMIT -1 OFFSET 3;

כל LIMIT שלילי עושה אותו דבר, אבל -1 הוא הביטוי המקובל. תראו את זה בעיקר בסקריפטים שמדפדפים בתוצאה ורוצים שאילתת "תנו לי את השאר" לאצווה האחרונה.

מלכודת הביצועים של OFFSET

הנה הדבר שאף אחד לא מזכיר עד שנתקלים בו: OFFSET לא גורם ל-SQLite לדלג על עבודה, הוא גורם לה לדלג על פלט. כדי להחזיר את השורות 10,001 עד 10,020, המנוע עדיין עובר פנימית על עשרת אלפים השורות הראשונות לפני שהוא מתחיל להוציא תוצאות. היסטים קטנים הם בחינם; היסטים של עשרות או מאות אלפים נעשים איטיים באופן מורגש.

לדפדוף עמוק, הפתרון המקובל הוא keyset pagination: במקום "דלגו על N שורות", זוכרים את מפתח המיון של השורה האחרונה ומבקשים "שורות אחרי זו".

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

INSERT INTO events (created_at, message) VALUES
    ('2024-01-01 10:00', 'login'),
    ('2024-01-01 10:05', 'view'),
    ('2024-01-01 10:10', 'click'),
    ('2024-01-01 10:15', 'logout'),
    ('2024-01-01 10:20', 'login');

-- אחרי שקיבלנו את id=2, העמוד הבא הוא:
SELECT id, created_at, message
FROM events
WHERE id > 2
ORDER BY id
LIMIT 2;

כל עמוד מבצע חיפוש באינדקס במקום לסרוק את כל מה שבא לפניו. המחיר: אי אפשר לקפוץ ל"עמוד 47", רק להתקדם קדימה בנתונים. לפידים עם גלילה אינסופית ולסמנים (cursors) של API, זה בדיוק מה שרוצים.

דפדוף מבוסס OFFSET מתאים לטבלאות ניהול ולקבוצות תוצאות קטנות. לכל דבר שגדל בלי גבול, השתמשו ב-keyset pagination.

דוגמה מלאה

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

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

INSERT INTO products (name, category, price) VALUES
    ('Pen', 'office', 1.50),
    ('Notebook', 'office', 4.00),
    ('Stapler', 'office', 8.00),
    ('Mug', 'kitchen', 6.00),
    ('Kettle', 'kitchen', 25.00),
    ('Toaster', 'kitchen', 30.00);

-- עמוד 1: 2 פריטי המשרד הזולים ביותר, לפי סדר אלפביתי כשהמחיר שווה
SELECT name, price
FROM products
WHERE category = 'office'
ORDER BY price ASC, name ASC
LIMIT 2 OFFSET 0;

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

הצעד הבא: DISTINCT

LIMIT קובע כמה שורות חוזרות; DISTINCT קובע אם כפילויות חוזרות בכלל. זו הפסוקית הבאה בארגז הכלים של SELECT, וקל מאוד להשתמש בה לא נכון, ועל זה העמוד הבא.