（十六）索引与分批取得结果

1. 索引改变查找的起点

重复查询某本书的阅读日志时，没有索引就可能每次遍历整张日志表。索引把查询键按可查找的结构组织起来，让数据库先定位对应范围，再读取结果。

# EXPLAIN QUERY PLAN 观察本次查询的计划，不把计划当作实际耗时。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE logs(book_id INTEGER, minutes INTEGER)")
        connection.executemany("INSERT INTO logs VALUES (?, ?)", ((index % 100, 1) for index in range(1000)))
    query = "SELECT SUM(minutes) FROM logs WHERE book_id = ?"
    before = connection.execute("EXPLAIN QUERY PLAN " + query, (42,)).fetchall()
    with connection:
        connection.execute("CREATE INDEX idx_logs_book ON logs(book_id)")
    after = connection.execute("EXPLAIN QUERY PLAN " + query, (42,)).fetchall()
    print(any("idx_logs_book" in row[3] for row in before))
    print(any("idx_logs_book" in row[3] for row in after))
    print(connection.execute(query, (42,)).fetchone()[0])

输出 False、True、10。创建索引不会改变求和结果，改变的是找到那些行的路径。可以按有序索引定位加读取命中项估算为 $O(\log n+k)$ ：n 为索引记录数，k 为命中数；真实读取还受页、缓存与查询计划影响。

索引也要占空间，新增或修改记录时要同时维护它。多列索引按列组合排序，例如 (book_id, day) 先按书编号再按日期组织；根据实际筛选条件与计划选择索引，不能只按“出现过的每个字段”全部建立。SQLite 查询计划

2. `executemany` 与 `fetchmany`

# 写入用同一事务中的多组参数，读取每次只收集两条结果。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE numbers(value INTEGER)")
        connection.executemany("INSERT INTO numbers VALUES (?)", ((value,) for value in range(5)))
    cursor = connection.execute("SELECT value FROM numbers ORDER BY value")
    while True:
        rows = cursor.fetchmany(2)
        if not rows:
            break
        print([row[0] for row in rows])

得到 [0, 1]、[2, 3]、[4]。fetchmany 减少 Python 一次收集的结果数量，数据库内部的排序与查询仍按它自己的计划执行。流式处理、结果缓存和数据库临时空间要分别计算，不能因为写了生成器，就把整个查询的空间一并记成常数。