⚠️ 加索引前后，判了多少次

下面这段查同一个用户的订单，一次没索引、一次有索引。打出两次各把条件判了多少次（用 / 隔开）。

一个小数据系统（题面里已经搭好）
主库   users 8 行（id / city）｜orders 24 行（id / uid / amt）
       ⚠️ u1 有 6 张订单，u2~u7 各 3 张 —— **热点集中在 u1**

build()               建一个新的主库
plan(db,sql) / first(db,sql) / counted(db,sql)   看计划、看取数方式、数判了几次
Cache()               缓存：get/set(k,v,ex)/drop/tick/size，c.hit / c.miss 记着次数
Origin(db)            包着主库：每查一次 o.reads 就加一
serve(origin,cache,uid)  先看缓存、没有就回源、回来存一份
dump(db) / restore(文本) / counts(db)   备份、恢复、对账（用户数, 订单数）

import sqlite3

USERS = ["u1", "u2", "u3", "u4", "u5", "u6", "u7", "u8"]
# 甲（u1）6 张，其余 7 个各 3 张 —— 6 + 21 = 27... 这里写死成 24：u1 六张、u2~u7 三张
ORDER_OWNER = (["u1"] * 6) + ["u2"] * 3 + ["u3"] * 3 + ["u4"] * 3 \
              + ["u5"] * 3 + ["u6"] * 3 + ["u7"] * 3


def build():
    """建一个主库：users 8 行、orders 24 行。"""
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE users (id TEXT PRIMARY KEY, city TEXT NOT NULL)")
    db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, uid TEXT NOT NULL, "
               "amt INTEGER NOT NULL)")
    CITY = ["杭州", "苏州", "南京", "合肥"]
    db.executemany("INSERT INTO users VALUES (?,?)",
                   [(u, CITY[i % 4]) for i, u in enumerate(USERS)])
    db.executemany("INSERT INTO orders VALUES (?,?,?)",
                   [(i + 1, ORDER_OWNER[i], (i + 1) * 5) for i in range(len(ORDER_OWNER))])
    db.commit()
    return db


def plan(db, sql):
    """执行计划的每一行。"""
    return [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql)]


def first(db, sql):
    """计划第一行的第一个词：SCAN 还是 SEARCH。"""
    return plan(db, sql)[0].split()[0]


def counted(db, sql):
    """这条查询把条件判了多少次（条件里要放 tick()）。
    ⚠️ tick() 必须写在条件的最前面。"""
    hits = {"n": 0}
    db.create_function("tick", 0, lambda: hits.__setitem__("n", hits["n"] + 1) or 1)
    db.execute(sql).fetchall()
    return hits["n"]


# ── 缓存层：够用的迷你 Redis（承 l1_db_12）───────────────────────────
class Cache:
    """键值缓存。get 不到返回 None；set 可以带一个存活步数。"""

    def __init__(self):
        self.d = {}
        self.exp = {}
        self.now = 0
        self.hit = 0
        self.miss = 0

    def tick(self, n=1):
        self.now = self.now + n
        for k in list(self.exp):
            if self.exp[k] <= self.now:
                self.d.pop(k, None)
                self.exp.pop(k, None)

    def set(self, k, v, ex=None):
        self.d[k] = v
        self.exp.pop(k, None)
        if ex is not None:
            self.exp[k] = self.now + ex

    def get(self, k):
        if k in self.d:
            self.hit = self.hit + 1
            return self.d[k]
        self.miss = self.miss + 1
        return None

    def drop(self, k):
        self.d.pop(k, None)
        self.exp.pop(k, None)

    def size(self):
        return len(self.d)


# ── 主库读一次就记一笔，用来数回源次数 ────────────────────────────────
class Origin:
    """包着主库：每查一次 reads 就加一。"""

    def __init__(self, db):
        self.db = db
        self.reads = 0

    def orders_of(self, uid):
        self.reads = self.reads + 1
        return self.db.execute("SELECT COUNT(*) FROM orders WHERE uid = ?",
                               (uid,)).fetchone()[0]


def serve(origin, cache, uid):
    """最常见那套：先看缓存，没有就回源，回来存一份。"""
    v = cache.get("o:" + uid)
    if v is None:
        v = origin.orders_of(uid)
        cache.set("o:" + uid, v)
    return v


# ── 备份与恢复 ────────────────────────────────────────────────────────
def dump(db):
    """把主库导成一串 SQL（这就是一份备份）。"""
    return "\n".join(db.iterdump())


def restore(text):
    """照一份备份重建一个库。"""
    db = sqlite3.connect(":memory:")
    db.executescript(text)
    db.commit()
    return db


def counts(db):
    """(用户数, 订单数) —— 备份前后拿它对账。"""
    return (db.execute("SELECT COUNT(*) FROM users").fetchone()[0],
            db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
db = build()
q = "SELECT * FROM orders WHERE tick() AND uid = 'u1'"
a = counted(db, q)
db.execute("CREATE INDEX ix_uid ON orders(uid)")
b = counted(db, q)
print(str(a) + "/" + str(b))