每条 SQL 都很快，为什么打开列表还是慢？

"""Python 3.10+; in-memory SQLite; counts SELECTs, not network latency."""
import sqlite3


def seed(db):
    db.executescript("""
        CREATE TABLE orders(tenant TEXT, id INTEGER,
                            PRIMARY KEY(tenant, id));
        CREATE TABLE items(tenant TEXT, order_id INTEGER, id INTEGER);
        INSERT INTO orders VALUES ('A',1),('A',2),('A',3),('A',4),('B',1);
        INSERT INTO items VALUES
            ('A',2,21),('A',2,22),('A',3,31),('A',4,41),('B',1,999);
        CREATE INDEX item_owner ON items(tenant, order_id, id);
    """)


def load_page(db, after, size, batch_size=None):
    if not 1 <= size <= 20 or (batch_size is not None and not 1 <= batch_size <= 20):
        raise ValueError("size and batch size must be 1..20")
    tenant = "A"  # Trusted fixture context, not a caller-supplied authorization decision.
    statements = []
    db.set_trace_callback(lambda sql: statements.append(sql)
                          if sql.lstrip().upper().startswith("SELECT") else None)
    try:
        ids = [row[0] for row in db.execute(
            "SELECT id FROM orders WHERE tenant=? AND id>? ORDER BY id LIMIT ?",
            (tenant, after, size))]
        result = {order_id: [] for order_id in ids}
        if batch_size is None:
            for order_id in ids:
                result[order_id] = [row[0] for row in db.execute(
                    "SELECT id FROM items WHERE tenant=? AND order_id=? ORDER BY id",
                    (tenant, order_id))]
        else:
            # An empty page takes zero child queries. Each batch binds values.
            for start in range(0, len(ids), batch_size):
                batch = ids[start:start + batch_size]
                slots = ",".join("?" for _ in batch)
                sql = ("SELECT order_id,id FROM items WHERE tenant=? "
                       f"AND order_id IN ({slots}) ORDER BY order_id,id")
                for order_id, item_id in db.execute(sql, [tenant, *batch]):
                    result[order_id].append(item_id)
        return result, len(statements)
    finally:
        db.set_trace_callback(None)


def main():
    with sqlite3.connect(":memory:") as db:
        seed(db)
        expected = {1: [], 2: [21, 22], 3: [31], 4: [41]}
        naive, naive_count = load_page(db, 0, 4)
        batch, batch_count = load_page(db, 0, 4, 20)
        split, split_count = load_page(db, 0, 4, 2)
        empty, empty_count = load_page(db, 4, 4, 2)
        assert naive == batch == split == expected
        assert list(naive) == list(batch) == list(split) == list(expected)
        assert (naive_count, batch_count, split_count) == (5, 2, 3)
        assert empty == {} and empty_count == 1
        print("SELECT counts: naive=5 batch=2 split=3 empty=1")
        print("same parent order and child contents:", naive == batch == split)
        print("empty-child order preserved:", batch[1] == [])
        print("other tenant item excluded:", all(999 not in v for v in batch.values()))
        print("SQLite calls only; no remote database or latency benchmark.")


if __name__ == "__main__":
    main()