SQLite 索引缺失导致全表扫描：EXPLAIN QUERY PLAN 显示 SCAN 而非 SEARCH，管道却报 ok、退出码 0

SELECT COUNT(*) FROM external_contacts
WHERE email = ? AND account_id = ? AND source = 'mail';

[index_audit] 索引数量 = 0
[index_audit] 匹配语句执行计划 = ['SCAN external_contacts']
[index_audit] 没有以 email 打头、且覆盖 (email,account_id,source) 的索引 —— 匹配只能全表扫描
RESULT: BAD

git fetch origin
git checkout 2026-09-30-0800
cd scenario/2026-09-30-0800

docker compose down -v && rm -rf data
docker compose up -d --build

docker compose exec -T db bash /srv/ops/repro.sh

docker compose exec -T db bash /srv/ops/verify.sh
# 问题态: PASS=11 FAIL=3 / RESULT: BAD

=== [2] 管道自述 ===
  stage match   max_ms=296.58   budget_ms=4000.0   self_reported_ok=True
  indexes       = {'check_enabled': False, 'match_uses_index': False}
  errors        = []

=== [3] 外部探针 ===
[probe] 匹配总耗时 294 ms（预算 4000 ms）

=== [4] 索引事实 ===
[index_audit] 索引数量 = 0
[index_audit] 匹配语句执行计划 = ['SCAN external_contacts']
RESULT: BAD

docker compose exec -T db python3 -c "
import sqlite3
c=sqlite3.connect('file:/srv/data/contacts.db?mode=ro',uri=True)
print('idx:', c.execute(\"SELECT name FROM sqlite_master WHERE type='index'\").fetchall())
print('plan:', [r[-1] for r in c.execute(
    \"EXPLAIN QUERY PLAN SELECT COUNT(*) FROM external_contacts \"
    \"WHERE email=? AND account_id=? AND source='mail'\", ('x',1))])
"
# idx: []
# plan: ['SCAN external_contacts']

# 数据集更新流程：先重灌数据，再让 DBA 按变更单补索引。
# 这一批数据是直接从上游导出的，索引要在后面的一次「结构同步」里补。
con.commit()
con.close()

if rep["indexes"]["check_enabled"]:
    quick = con.execute("SELECT COUNT(DISTINCT email) ...").fetchone()   # 只是查个数
    rep["indexes"]["probe"] = quick
rep["indexes"]["match_uses_index"] = any("USING" in s.upper() for s in scripts)

3day-91-seed   | indexed idx_ext_email_account_source     ← seed 明明建好了索引
3day-91-ingest | [runner] round 1 rc=4
  | errors=1   match=293.86
ingest exit=4

docker compose exec -T db python3 -c "
import sqlite3
c=sqlite3.connect('file:/srv/state/contacts-live.db?mode=ro',uri=True)
print('idx:', c.execute(\"SELECT name FROM sqlite_master WHERE type='index'\").fetchall())
print('pages:', c.execute('PRAGMA page_count').fetchone())"
# idx: []            pages: (1885,)

/srv/state/contacts-live.db  7720960  00:16:53.147
/srv/data/contacts.db       13008896  00:16:53.203

ingest:
  depends_on: [db]        # ← 只等了 db，没等 seed

docker compose down -v && rm -rf data && mkdir -p data
docker compose up -d --build seed
for i in 1 2 3 4 5 6 7 8; do python3 -c "
import os,sqlite3
p='data/contacts.db'
if not os.path.exists(p): print('$i: 不存在')
else:
    s=os.path.getsize(p)
    c=sqlite3.connect('file:%s?mode=ro'%p,uri=True)
    print('$i: size=%d rows=%d indexes=%d' % (s,
        c.execute('SELECT COUNT(*) FROM external_contacts').fetchone()[0],
        c.execute(\"SELECT COUNT(*) FROM sqlite_master WHERE type='index'\").fetchone()[0]))
"; sleep 0.25; done

1: 不存在
2: size=651264   rows=140000  indexes=0     ← 命中！完整可读、但没索引的快照
3: size=13008896 rows=140000  indexes=1
...

if not os.path.exists(WORK_COPY):
    ...backup...

MATCH_INDEX = "idx_ext_email_account_source"
MATCH_INDEX_SQL = (
    "CREATE INDEX IF NOT EXISTS %s ON external_contacts(email, account_id, source)"
    % MATCH_INDEX
)

def build_indexes(con):
    con.execute(MATCH_INDEX_SQL)
    con.execute("ANALYZE")
    con.commit()
    return [r[0] for r in con.execute(
        "SELECT name FROM sqlite_master WHERE type='index'").fetchall()]

rep["indexes"]["match_uses_index"] = db.uses_index(con, MATCH_SQL)
if rep["indexes"]["check_enabled"] and not rep["indexes"]["match_uses_index"]:
    rep["errors"].append(
        "index_not_ready: 索引未就绪，匹配语句仍在全表扫描（计划: %s）"
        % " / ".join(scripts))

-CHECK_INDEXES=0
+CHECK_INDEXES=1

ingest:
  depends_on:
    seed:
      condition: service_completed_successfully
    db:
      condition: service_started

docker compose down -v && rm -rf data
docker compose up -d --build

3day-91-seed   | indexed idx_ext_email_account_source
3day-91-ingest | [runner] round 1 rc=0
  | [contacts] matched=70 enriched=20000 imported=5000
      stages={'import': 3.98, 'enrich': 219.38, 'match': 0.31, 'verify_accuracy': 1.22}
      errors=0
[runner] match round OK
ingest exit=0

docker compose exec -T db bash /srv/ops/verify.sh

A1 [PASS] 索引就绪（index_audit rc=0）
A2 [PASS] 计划 = SEARCH external_contacts USING COVERING INDEX idx_ext_email_account_source (email=? AND account_id=? AND source=?)
B2 [PASS] 40 条样本匹配实测 = 0 ms（预算 4000 ms）
D1 [PASS] CHECK_INDEXES=1
======================================
PASS=14 FAIL=0
RESULT: OK

docker compose exec -T db bash -lc '
python3 -c "
import sqlite3
c=sqlite3.connect(\"/srv/state/contacts-live.db\")
c.execute(\"DROP INDEX IF EXISTS idx_ext_email_account_source\"); c.commit()"
SCHED_RETRY_MAX=1 python3 /srv/app/runner.py; echo "rc=$?"'

[runner] round 1 rc=4
[runner] all 1 rounds failed (rc=4)
rc=4

"errors": ["index_not_ready: 索引未就绪，匹配语句仍在全表扫描（计划: SCAN external_contacts）"],
"indexes": {"check_enabled": true, "match_uses_index": false},
"exit_code": 4