🏠 总目录📚 本教程 09 · 计划里的连接与索引失效 ← →
📑 本页目录(点开跳转)

09 · 执行计划里的连接,和写法怎么毁掉索引

实验记录怎么读

本页原有耗时、内存、报错和环境值保留为原文记录;本轮内容审校未逐项复跑这些实验。版本、硬件、数据和测量方法都会影响结果,示例中的倍数也不是保证。需要复现时先核对依赖版本,再在同一输入下比较。

⏱ 104 分钟 | ⚠️ 实测:索引在、计划里也写着 COVERING INDEX,它照样是 SCAN 全表

🎯 一句话

执行计划里只有两个动词要认:SCAN = 从头读一遍,SEARCH = 直接定位过去。 ⭐ 索引名出现在计划里,不代表索引在起作用 —— 判据是动词,不是索引名。 本章只讲两件让 SQL 从「对」变成「快」的事:连接顺序,和写法怎么把已经建好的索引废掉。

🧭 一、先划清这一章不讲什么

01 章第四节留了一句话:逻辑顺序回答「结果为什么是这样」,物理计划回答「为什么这么慢」, 后半句欠到这里。但「慢」这个话题很大,本教程只拿走其中跟怎么写 SQL 有关的那一半:

话题 在哪
索引该不该建、怎么建、复合索引的列序 AI 全栈 06 关系数据库(那边有一组 200 次查询 3.802 秒 → 0.0019 秒的实测)
覆盖索引该不该建、为什么别把大字段塞进索引 同上。⭐ 本章只教你在计划里认出它,不教你建
COUNT(*) 在大表上的成本、游标分页怎么用 AI 全栈 06b 列表接口与分页
表怎么设计、事务、连接池、扩容 不在本教程范围内
⭐ 连接顺序为什么会差十倍 本章第三节
⭐ 写法怎么让已有的索引用不上 本章第四到六节

⚠️⚠️ 还有一条免责声明必须写在最前面:执行计划的长相是每个数据库自己的方言。 本章用 SQLite(EXPLAIN QUERY PLAN),Postgres 是 EXPLAIN (ANALYZE, BUFFERS)、 MySQL 是 EXPLAIN FORMAT=TREE,输出的字面完全不一样。 ⭐ 可迁移的是判据(「有没有做全表扫」「索引用上没有」「有没有额外排序」),不可迁移的是字面。 所以下面每一处我都会先说结论、再给 SQLite 的原文,⚠️ 别去背原文。

🔤 二、怎么读一份计划:两个动词,一个陷阱

一张 20 万行的事件表,user_id 上有索引。六种查询各看一次计划:

import sqlite3, random

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE ev (id INTEGER PRIMARY KEY, user_id TEXT, ts TEXT, amount REAL)")
random.seed(3)
db.executemany("INSERT INTO ev VALUES (?,?,?,?)", [
    (i, "u%05d" % random.randint(1, 50000),
     "2026-03-%02d" % random.randint(1, 30), random.random() * 100)
    for i in range(1, 200001)])
db.execute("CREATE INDEX ix_user ON ev(user_id)")
db.commit()
db.execute("ANALYZE")


def plan(sql):
    for r in db.execute("EXPLAIN QUERY PLAN " + sql):
        print("   ", r[3])


print("① 没有条件 —— 整张表都要读")
plan("SELECT * FROM ev")
print("② 条件用上了索引 —— SEARCH")
plan("SELECT * FROM ev WHERE user_id = 'u00123'")
print("③ 只要索引里有的列 —— COVERING INDEX,不回表")
plan("SELECT user_id FROM ev WHERE user_id = 'u00123'")
print("④ 多要一列 amount —— 索引里没有,必须回表")
plan("SELECT user_id, amount FROM ev WHERE user_id = 'u00123'")
print("⑤ 💀 计划里写着 COVERING INDEX,动作却是 SCAN")
plan("SELECT COUNT(*) FROM ev WHERE substr(user_id, 2) = '00123'")
print("⑥ ORDER BY 能不能白嫖索引的顺序")
plan("SELECT user_id FROM ev ORDER BY user_id LIMIT 10")
plan("SELECT user_id FROM ev ORDER BY amount LIMIT 10")

实测输出(Python 3.13.14 / SQLite 3.50.4):

① 没有条件 —— 整张表都要读
    SCAN ev
② 条件用上了索引 —— SEARCH
    SEARCH ev USING INDEX ix_user (user_id=?)
③ 只要索引里有的列 —— COVERING INDEX,不回表
    SEARCH ev USING COVERING INDEX ix_user (user_id=?)
④ 多要一列 amount —— 索引里没有,必须回表
    SEARCH ev USING INDEX ix_user (user_id=?)
⑤ 💀 计划里写着 COVERING INDEX,动作却是 SCAN
    SCAN ev USING COVERING INDEX ix_user
⑥ ORDER BY 能不能白嫖索引的顺序
    SCAN ev USING COVERING INDEX ix_user
    SCAN ev
    USE TEMP B-TREE FOR ORDER BY

逐个拆:

计划里出现 意思 好还是坏
SCAN 表名 把这个东西从头到尾读一遍 ⚠️ 大表上通常是坏消息
SEARCH 表名 USING INDEX ix (col=?) 拿索引直接定位,只读要的那几行 ✅ 通常是好消息
USING COVERING INDEX 要的列全在索引里,连表都不用碰 ✅ 更好一档
USE TEMP B-TREE FOR ORDER BY 结果没法按索引顺序出来,得先全捞出来再排一遍 ⚠️ 数据一大就是主要开销
AUTOMATIC …INDEX ⭐ 数据库临时给你造了个索引 —— 等于它在说「这儿缺一个」 ⚠️ 见下一节

💀💀 ⑤ 是本章的卖点,也是最容易被计划骗到的一次。 它写着 COVERING INDEX ix_user,索引名清清楚楚,看起来「索引用上了」。 ⚠️ 但动词是 SCAN —— 它把 ix_user 这个索引从头到尾读了一遍,20 万行一行不落, 只是省掉了回表而已。这不叫用上了索引,这叫在索引上做全表扫。

⭐ 判据:先看动词,再看索引名。SCAN … USING … INDEX 依然是全扫。

⚠️ 反过来也一样:SEARCH 不等于快。 第四节会看到 WHERE user_id IS NOT NULL 实测是 SEARCH ev USING COVERING INDEX ix_user (user_id>?) —— 动词是 SEARCH,耗时却是 2.5847 ms(同表命中一个 user_id 只要 0.0028 ms)。 因为它「定位」到的范围里装着几乎全部的行。 ⭐ 动词只告诉你「怎么找」,不告诉你「找到多少」。

③④ 那一对值得单独说一句:同一个 WHERE,只多要了一列 amount, 计划就从 COVERING INDEX 退回 INDEX —— 因为 amount 不在索引里,必须回表拿。 ⭐ 这正是 01 章说「SELECT * 往往要求索引之外的列」的完整版。 ⚠️ 但请别倒过来读成「那就把所有列塞进索引」——AI 全栈 06 明确否掉了这条路(那等于把整张表再复制一遍)。

⑥ 是很多人不知道索引还能干的事:ORDER BY user_id 不需要排序,因为索引本来就是按 user_id 有序存的, 计划里干干净净;换成 ORDER BY amount 就冒出 USE TEMP B-TREE FOR ORDER BY。 ⭐ ORDER BY + LIMIT 的分页查询要是慢,先去计划里找这一行。

🔀 三、连接顺序:同一对表,谁在外层差十倍

选读 · MySQL 8.4:统计怎样连到计划与排序?

本块只解释 MySQL 8.4 的有限机制。后面的 SQLite 连接实验与 6000 行夹具、历史计划和耗时均保持原义,不能拿它们证明 MySQL 的执行过程。

统计 → 估计行数 → 候选代价 → 选计划。优化器在语义和适用条件允许的候选中比较表扫描、索引范围/查找等访问路径,以及不同连接顺序和可用连接算法;不会保证穷举所有计划。表/索引统计及可用直方图等帮助估计选择性,影响各步输入、输出行数,再结合取行、比较等操作的代价模型比较候选。代价是估计值,不是保证的毫秒数。

例如,若先过滤的一表预计只留下少量行,后续连接的预计组合数可能降低;若估计偏差大,选择也可能不佳。传统格式里的 rows × filtered / 100 可理解为当前表每次访问后预计传给下一连接阶段的行数,完整连接还涉及前序循环。EXPLAIN 看估计,EXPLAIN ANALYZE 会实际执行并报告行数、循环和时间;本块没有新增 MySQL 实测。

诊断时把排序标记与实际证据分开:优化器 trace 的临时文件/峰值内存信息、同一受控时段的 Sort_merge_passes 变化可帮助判断;仅凭 Using filesort 不能判断是否用了磁盘。这里的排序临时文件也不等同于所有内部临时表。

依据:MySQL 8.4 条件过滤与行数、代价模型、ORDER BY 与 filesort、EXPLAIN / ANALYZE。

02 章讲了 JOIN 的语义,欠着代价。代价的核心只有一句话:

⭐ 最基础的连接算法是「嵌套循环」:外层表每出一行,就拿连接键去内层表找一次。 总代价 ≈ 外层的行数 × 内层每次查找的代价。 于是「谁在外层」和「内层能不能用索引」这两件事,直接决定快慢。

2000 个用户、20 万条事件,⭐ SQLite 里 CROSS JOIN 的作用就是禁止优化器重排顺序,正好拿来手工指定谁在外层:

import sqlite3, random, time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users  (id INTEGER PRIMARY KEY, city TEXT)")
db.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL)")
random.seed(7)
db.executemany("INSERT INTO users VALUES (?,?)",
               [(i, random.choice("ABCDE")) for i in range(1, 2001)])
db.executemany("INSERT INTO events VALUES (?,?,?)",
               [(i, random.randint(1, 2000), random.random() * 100)
                for i in range(1, 200001)])
db.commit()
print("users 2000 行,events 200000 行")


def run(label, sql):
    p = [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql)]
    db.execute(sql).fetchall()                      # 预热
    t = time.perf_counter()
    for _ in range(5):
        db.execute(sql).fetchall()
    print("%-14s %7.4f s" % (label, (time.perf_counter() - t) / 5))
    for line in p:
        print("               ", line)


# ⭐ SQLite 里 CROSS JOIN 的作用就是【禁止优化器重排顺序】,用它来手工指定谁在外层
A = "SELECT COUNT(*) FROM users u CROSS JOIN events e ON e.user_id=u.id WHERE u.city='A'"
B = "SELECT COUNT(*) FROM events e CROSS JOIN users u ON u.id=e.user_id WHERE u.city='A'"

print("\n===== 没有任何索引 =====")
run("users 在外层", A)
run("events 在外层", B)

db.execute("CREATE INDEX ix_ev_user ON events(user_id)")
print("\n===== 给 events(user_id) 建了索引之后 =====")
run("users 在外层", A)
run("events 在外层", B)

print("\n===== 换成普通 JOIN,让优化器自己选 =====")
run("优化器自选", "SELECT COUNT(*) FROM users u JOIN events e ON e.user_id=u.id WHERE u.city='A'")

实测输出:

users 2000 行,events 200000 行

===== 没有任何索引 =====
users 在外层       0.2380 s
                SCAN u
                BLOOM FILTER ON e (user_id=?)
                SEARCH e USING AUTOMATIC COVERING INDEX (user_id=?)
events 在外层      0.0440 s
                SCAN e
                SEARCH u USING INTEGER PRIMARY KEY (rowid=?)

===== 给 events(user_id) 建了索引之后 =====
users 在外层       0.0019 s
                SCAN u
                SEARCH e USING COVERING INDEX ix_ev_user (user_id=?)
events 在外层      0.0140 s
                SCAN e USING COVERING INDEX ix_ev_user
                SEARCH u USING INTEGER PRIMARY KEY (rowid=?)

===== 换成普通 JOIN,让优化器自己选 =====
优化器自选           0.0019 s
                SCAN u
                SEARCH e USING COVERING INDEX ix_ev_user (user_id=?)

⚠️ 绝对耗时随机器和当时的机器负载变(本机复跑四次,第一格在 0.2380 ~ 0.4739 秒之间跳)。 ⭐ 但四次跑出来的计划一字不差,下面这三条关系也每次都一样 —— 它们是结构性的,不是噪声。

① 没索引时,大表在外层反而快(0.0440 s vs 0.2380 s,约 5 倍)。 users 在外层时,内层的 events 没有可用索引,数据库只好现造一个—— 计划里那句 SEARCH e USING AUTOMATIC COVERING INDEX 就是它在说:「这儿缺一个索引,我先临时建一个用用。」

⭐ 看到 AUTOMATIC 就该去 AI 全栈 06 补一个真索引 —— 临时索引每次查询都要重建一遍。

(旁边那句 BLOOM FILTER 是 SQLite 3.38+ 的优化:先用一个便宜的过滤器挡掉大部分注定连不上的行。)

② 建完索引之后,最优顺序反了过来(0.0019 s vs 0.0140 s,约 7 倍,方向和上一条相反)。 现在 users 在外层才是对的:先用 city='A' 把外层缩到约 400 行,每行拿索引去 events 精准定位。 ⭐ 同一对表、同一个问题,最优连接顺序会因为「有没有索引」而反转 —— 这就是不要手工钉死连接顺序的理由。

③ 交给优化器,它选对了。 把 CROSS JOIN 换回普通 JOIN,它给出的计划和手工最优那条一字不差(SCAN u + SEARCH e USING COVERING INDEX)。

⭐ 实践判据:先看计划、再考虑改写,而不是先改写。 手工调顺序(CROSS JOIN、MySQL 的 STRAIGHT_JOIN、各种 hint)是最后一招—— 它把「今天的数据分布」和「今天的索引」焊进了 SQL 里,⚠️ 而这两样都会变。

⚠️ 连接算法要按引擎与版本看:SQLite 的连接计划由嵌套扫描组成;PostgreSQL 18 的规划器可考虑 Nested Loop、Hash Join、Merge Join;MySQL 8.4 有嵌套循环与适用条件下的 Hash Join,不能把 PostgreSQL 的 Merge Join 清单照搬过去。Hash 的构建/探测与内存、Merge 的排序/已有顺序、循环的内层索引与重复次数都是不同成本项;没有一种对所有表规模和谓词必胜。

上述跨库能力为文档核对,未实跑相应服务。依据:SQLite 计划、PostgreSQL 18 规划器、MySQL 8.4 Hash Join。


🧨 四、sargability:六种把索引废掉的写法

前面都是「索引够不够」,这一节反过来 —— ⭐ 索引明明在,是你的写法让它用不上。

对本例的普通列索引,将条件写成原列与常量范围的比较,通常更容易利用索引;用 substr(ts,...) 包住列未必能使用原来的 ts 索引。表达式索引、生成列索引、优化器改写是例外,最终仍需查看实际计划与耗时。参见 SQLite 表达式索引。日期筛选使用 ts >= 起点 AND ts < 下一天,避免漏掉秒内的小数部分。

20 万行,user_id 和 ts 上都有索引:

import sqlite3, random, time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE ev (id INTEGER PRIMARY KEY, user_id TEXT, ts TEXT, amount REAL)")
random.seed(3)
db.executemany("INSERT INTO ev VALUES (?,?,?,?)", [
    (i, "u%05d" % random.randint(1, 50000),
     "2026-03-%02d %02d:00:00" % (random.randint(1, 30), random.randint(0, 23)),
     random.random() * 100)
    for i in range(1, 200001)])
db.execute("CREATE INDEX ix_user ON ev(user_id)")
db.execute("CREATE INDEX ix_ts   ON ev(ts)")
db.commit()
db.execute("ANALYZE")


def show(label, sql):
    p = [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql)]
    db.execute(sql).fetchall()                      # 预热
    t = time.perf_counter()
    for _ in range(20):
        db.execute(sql).fetchone()
    ms = (time.perf_counter() - t) / 20 * 1000
    print("%-24s %9.4f ms   %s" % (label, ms, p[0]))


print("== ① 函数包住列 ==")
show("substr(ts,1,10)='...'", "SELECT COUNT(*) FROM ev WHERE substr(ts,1,10)='2026-03-05'")
show("date(ts)='...'",        "SELECT COUNT(*) FROM ev WHERE date(ts)='2026-03-05'")
show("✅ 半开区间",            "SELECT COUNT(*) FROM ev WHERE ts>='2026-03-05' AND ts<'2026-03-06'")

print("== ② CAST 包住列 ==")
show("CAST(user_id AS TEXT)", "SELECT COUNT(*) FROM ev WHERE CAST(user_id AS TEXT)='u00123'")
show("✅ 直接比",              "SELECT COUNT(*) FROM ev WHERE user_id='u00123'")

print("== ③ 列上做算术 ==")
show("amount*2 > 199",       "SELECT COUNT(*) FROM ev WHERE amount*2 > 199")
show("user_id||'' = 'u00123'", "SELECT COUNT(*) FROM ev WHERE user_id||''='u00123'")

print("== ④ 前导通配 ==")
show("LIKE '%00123'",        "SELECT COUNT(*) FROM ev WHERE user_id LIKE '%00123'")
show("✅ 范围改写",            "SELECT COUNT(*) FROM ev WHERE user_id>='u00123' AND user_id<'u00124'")

print("== ⑤ 否定条件 ==")
show("user_id <> 'u00123'",  "SELECT COUNT(*) FROM ev WHERE user_id<>'u00123'")
show("user_id IS NOT NULL",  "SELECT COUNT(*) FROM ev WHERE user_id IS NOT NULL")

print("== ⑥ OR 一侧没索引 ==")
show("OR amount>99.9",       "SELECT COUNT(*) FROM ev WHERE user_id='u00123' OR amount>99.9")
show("OR 两侧都有索引",        "SELECT COUNT(*) FROM ev WHERE user_id='u00123' OR ts>='2026-03-30'")

实测输出:

== ① 函数包住列 ==
substr(ts,1,10)='...'      16.2629 ms   SCAN ev USING COVERING INDEX ix_ts
date(ts)='...'             26.2926 ms   SCAN ev USING COVERING INDEX ix_ts
✅ 半开区间                      0.2750 ms   SEARCH ev USING COVERING INDEX ix_ts (ts>? AND ts<?)
== ② CAST 包住列 ==
CAST(user_id AS TEXT)      10.5548 ms   SCAN ev USING COVERING INDEX ix_user
✅ 直接比                       0.0028 ms   SEARCH ev USING COVERING INDEX ix_user (user_id=?)
== ③ 列上做算术 ==
amount*2 > 199             11.7823 ms   SCAN ev
user_id||'' = 'u00123'     12.0084 ms   SCAN ev USING COVERING INDEX ix_user
== ④ 前导通配 ==
LIKE '%00123'              14.0207 ms   SCAN ev USING COVERING INDEX ix_user
✅ 范围改写                      0.0042 ms   SEARCH ev USING COVERING INDEX ix_user (user_id>? AND user_id<?)
== ⑤ 否定条件 ==
user_id <> 'u00123'         9.9813 ms   SCAN ev USING COVERING INDEX ix_user
user_id IS NOT NULL         2.5847 ms   SEARCH ev USING COVERING INDEX ix_user (user_id>?)
== ⑥ OR 一侧没索引 ==
OR amount>99.9             21.2903 ms   SCAN ev
OR 两侧都有索引                   0.6862 ms   MULTI-INDEX OR

⚠️ 绝对毫秒数随机器和缓存状态变,量级差异是结构性的。 一张表读完:

# 写法 计划动词 实测 为什么 ✅ 怎么改
① substr(ts,1,10)='2026-03-05' SCAN 16.2629 ms 索引存的是 ts 的值,不是 substr(ts) 的值 ⭐ 半开区间 ts>='2026-03-05' AND ts<'2026-03-06',0.2750 ms(约 59 倍)
① date(ts)='2026-03-05' SCAN 26.2926 ms 同上,而且函数更贵 同上
② CAST(user_id AS TEXT)='u00123' SCAN 10.5548 ms ⭐ CAST 也是函数 直接比,0.0028 ms(约 3800 倍)
③ amount*2 > 199 SCAN 11.7823 ms 列上做了算术 把算术挪到另一边:amount > 99.5
③ user_id 拼上空串再比(上面代码块第三段) SCAN 12.0084 ms 字符串拼接也是把列包住了 把那个拼接去掉,直接比
④ user_id LIKE '%00123' SCAN 14.0207 ms 索引按前缀有序,不知道后缀在哪 ⚠️ 没有等价改写。真要按后缀查就换工具(全文索引 / 反转列)
⑤ user_id <> 'u00123' SCAN 9.9813 ms 「不等于」意味着几乎所有行都要,索引帮不上忙 通常没法改 —— ⭐ 这条本来就该全扫
⑤ user_id IS NOT NULL SEARCH 2.5847 ms ⭐ 动词是 SEARCH,但范围覆盖几乎全表 ⚠️ SEARCH ≠ 快,见第二节
⑥ … OR amount>99.9 SCAN 21.2903 ms amount 没索引,本例顶层 OR 选择全扫;整条查询仍须看其他条件与成本 见下一节

⭐ ①②③说明的是本例普通列索引的限制。原索引按列值组织;对列再做 substr()、date()、CAST()、*2 或 || '' 等变换,查询未必还能直接使用原列索引定位。本例 substr(ts,1,10) 的计划不能推广成“列外只要包一层,所有索引都不能用”。

表达式/函数索引、适当的生成列索引,或优化器可证明的等价改写,可能提供其他访问路径。SQLite 的表达式索引要求查询中的表达式与索引表达式匹配(容许空白等少量语法差异),不依赖数学上等价就自动交换写法;其中函数须满足确定性要求。是否实际采用仍看计划与成本,不能承诺更快。这里按 SQLite 的条件解释;其他引擎与版本要分别核对支持和限制,不能直接套用。依据:SQLite 官方 Indexes On Expressions。

⭐ 本例优先尝试:在语义等价的前提下,把变形从列这边挪到常量那边,再核实际计划。

⚠️ 注意右端一律用 < 不用 <=(半开区间)—— 用 <= '2026-03-05 23:59:59' 会漏掉那一秒里的毫秒。

⚠️ 另一条路是给表达式本身建索引(Postgres/SQLite 的表达式索引、MySQL 的生成列索引)。 它有效,但属于「索引怎么建」,是 AI 全栈 06 的题目。 ⭐ 本教程的立场:先核对改写前后的语义、类型与排序规则,再在目标引擎版本、索引、统计和数据下核计划与耗时。


🔧 五、OR 的三种命运,和一个 LIKE 的意外

本节用同一张表比较三种 OR 写法:分支能否索引化会影响可选路径,是否采用仍由优化器比较成本。

先限定下面历史记录的前提:原代码生成统一小写的 ASCII u 加数字 ID,原列/索引采用默认 BINARY 比较;在这批固定数据上,LIKE 'u00123%' 与 >='u00123' AND <'u00124' 选中相同的行。原代码和旧输出中的“范围改写(永远有效)”标签保留作历史记录,不是当前的普遍建议:这个范围谓词不受 LIKE 开关影响,不代表它在允许大小写混合或不同排序规则的数据上仍与 LIKE 等价。先核结果语义,再核计划和耗时。

import sqlite3, random, time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE ev (id INTEGER PRIMARY KEY, user_id TEXT, uid_int INTEGER, amount REAL)")
random.seed(11)
db.executemany("INSERT INTO ev VALUES (?,?,?,?)",
               [(i, "u%05d" % random.randint(1, 50000), random.randint(1, 50000),
                 random.random() * 100) for i in range(1, 200001)])
db.execute("CREATE INDEX ix_user   ON ev(user_id)")     # TEXT 列
db.execute("CREATE INDEX ix_uidint ON ev(uid_int)")     # INTEGER 列
db.commit()
db.execute("ANALYZE")


def show(label, sql):
    p = [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql)]
    db.execute(sql).fetchall()
    t = time.perf_counter()
    for _ in range(20):
        db.execute(sql).fetchall()
    print("%-22s %8.4f ms" % (label, (time.perf_counter() - t) / 20 * 1000))
    for line in p:
        print("                       ", line)


print("== OR 的三种命运(20 万行)==")
show("① 两侧都有索引", "SELECT id FROM ev WHERE user_id='u00123' OR uid_int=7")
show("② 改写成 UNION",
     "SELECT id FROM ev WHERE user_id='u00123' UNION SELECT id FROM ev WHERE uid_int=7")
show("③ 一侧没有索引", "SELECT id FROM ev WHERE user_id='u00123' OR amount>99.9")

print("\n== 类型对不上时,SQLite 会不会丢索引 ==")
show("TEXT 列 = 整数字面量", "SELECT COUNT(*) FROM ev WHERE user_id = 123")
show("INTEGER 列 = 字符串", "SELECT COUNT(*) FROM ev WHERE uid_int = '123'")

print("\n== LIKE 前缀能不能用索引,取决于一个 PRAGMA ==")
show("LIKE 'u00123%'(默认)", "SELECT COUNT(*) FROM ev WHERE user_id LIKE 'u00123%'")
db.execute("PRAGMA case_sensitive_like=ON")
show("同一句,PRAGMA 打开后", "SELECT COUNT(*) FROM ev WHERE user_id LIKE 'u00123%'")
show("范围改写(永远有效)", "SELECT COUNT(*) FROM ev WHERE user_id>='u00123' AND user_id<'u00124'")

实测输出:

== OR 的三种命运(20 万行)==
① 两侧都有索引                 0.0066 ms
                        MULTI-INDEX OR
                        INDEX 1
                        SEARCH ev USING INDEX ix_user (user_id=?)
                        INDEX 2
                        SEARCH ev USING INDEX ix_uidint (uid_int=?)
② 改写成 UNION              0.0179 ms
                        COMPOUND QUERY
                        LEFT-MOST SUBQUERY
                        SEARCH ev USING COVERING INDEX ix_user (user_id=?)
                        UNION USING TEMP B-TREE
                        SEARCH ev USING COVERING INDEX ix_uidint (uid_int=?)
③ 一侧没有索引                15.7034 ms
                        SCAN ev

== 类型对不上时,SQLite 会不会丢索引 ==
TEXT 列 = 整数字面量           0.0024 ms
                        SEARCH ev USING COVERING INDEX ix_user (user_id=?)
INTEGER 列 = 字符串          0.0022 ms
                        SEARCH ev USING COVERING INDEX ix_uidint (uid_int=?)

== LIKE 前缀能不能用索引,取决于一个 PRAGMA ==
LIKE 'u00123%'(默认)      10.3474 ms
                        SCAN ev USING COVERING INDEX ix_user
同一句,PRAGMA 打开后           0.0033 ms
                        SEARCH ev USING COVERING INDEX ix_user (user_id>? AND user_id<?)
范围改写(永远有效)               0.0034 ms
                        SEARCH ev USING COVERING INDEX ix_user (user_id>? AND user_id<?)

三件事:

① 本例 SQLite 在两侧可索引化时,选择各走索引再合并(计划里那句 MULTI-INDEX OR,实测 0.0066 ms)。 ⭐ 所以「OR 一定用不上索引」是个过时的说法。 🗓️ 未实跑:MySQL 管这叫 index_merge,Postgres 叫 BitmapOr,⚠️ 三家的触发条件都不一样。

② 原夹具中,amount 分支无可用索引,这个顶层 OR 的旧计划选择 SCAN(实测 15.7034 ms,比①慢约 2400 倍)。 这个结果只描述原夹具,不能推出一侧无索引就必然整条全扫。其他可索引的 AND 条件仍可能先筛行,例如 a=17 AND (b=3 OR c=5) 中,即使 c 无索引,也可能先按 a 定位;实际路径与耗时须在目标版本、索引、统计和数据下核验。 ⚠️ 手工改写成 UNION 在原本机实验反而更慢(0.0179 ms,因为多了一步去重的 TEMP B-TREE)—— ⭐ 别背「OR 要改 UNION」这条口诀,先看计划。依据:SQLite OR 优化。

③ 两个和直觉相反的实测,都值得单独记住:

现象 实测 ⭐ 该记住的
user_id(TEXT 列)= 123(整数) 仍然 SEARCH,0.0024 ms SQLite 有「类型亲和」,会把字面量转成列的类型再比。⚠️ 它不丢索引
user_id LIKE 'u00123%'(前缀,没有前导通配) 💀 默认是 SCAN,10.3474 ms 因为 SQLite 的 LIKE 默认不区分大小写,而索引是区分的,对不上就不敢用
同一句,PRAGMA case_sensitive_like=ON 之后 SEARCH,0.0033 ms(约 3100 倍) ⭐ 同一句 SQL、同一个索引,快慢由一个连接级开关决定

⚠️⚠️ ②③ 合起来给出本节最该带走的一条:访问路径取决于目标数据库版本、设置、比较规则与数据。先确认改写返回相同结果,再查看实际计划;清单只用来猜方向。

下面只加两行 ASCII ID,就能推翻无条件范围替换。新连接先断言默认 LIKE 对 ASCII 大小写不敏感,列显式使用 BINARY:

import sqlite3
db = sqlite3.connect(":memory:")
assert db.execute("SELECT 'a' LIKE 'A'").fetchone() == (1,)
db.execute("CREATE TABLE ids(user_id TEXT COLLATE BINARY)")
db.executemany("INSERT INTO ids VALUES(?)", [("u00123a",), ("U00123a",)])
like = db.execute("SELECT user_id FROM ids WHERE user_id LIKE 'u00123%' ORDER BY user_id").fetchall()
span = db.execute("SELECT user_id FROM ids WHERE user_id >= 'u00123' AND user_id < 'u00124' ORDER BY user_id").fetchall()
assert like == [("U00123a",), ("u00123a",)]
assert span == [("u00123a",)]
print("default LIKE:", like)
print("BINARY range:", span)
db.close()

实际结果:默认 LIKE 返回 U00123a、u00123a 两行;BINARY 范围只返回小写一行。若业务确实约束全部 ID 为统一小写且范围边界与排序规则匹配,须先断言两种条件结果相等,再比计划;不能从更快推断答案没变。下面原有 NOCASE 受控例继续保留 LIKE,并配置匹配的内置 NOCASE 索引,是另一候选方案,仍须满足优化的其他前提。依据:SQLite LIKE 优化条件。

⭐ 类型比较要承接 02 章的亲和性边界:02 章原来的 a(k)/b(k) 都未声明类型,字符串 '1' 与整数 1 不匹配,原两行结果少一半;这不能推广到所有 CSV 文本与整数连接。声明 INTEGER / TEXT 列时,SQLite 可在比较前应用数值亲和性,使这对可转换值配上。入库前应明确类型与格式契约,不能把自动转换当业务校验。

🗓️ MySQL 8.4 官方说明的具体限制是:字符串列与数字比较时,不能使用该字符串列索引快速查找该值。不能由此断言所有索引、反方向比较或连接路径都失效;其他情况要查目标版本的实际计划。这里仅作文档核对,没有运行 MySQL 服务。

可选深入:把 IN、LIKE 与连接键放进同一个受控小例

先核结果,再比较访问路径

下例保持 6000 行 ev、100 个用户和确定的分布。列使用内置 NOCASE,保留 SQLite 默认的 ASCII 大小写不敏感 LIKE;显式关闭自动索引,避免它掩盖连接键有无持久索引的差别。每次建索引后重新 ANALYZE。这里的参数是绑定的值/模式串,不把列名当参数。

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA automatic_index=OFF")
db.execute("CREATE TABLE ev(id INTEGER PRIMARY KEY,user_id TEXT COLLATE NOCASE,uid_int INT,amount REAL)")
db.execute("CREATE TABLE users(id INTEGER PRIMARY KEY,city TEXT)")
data = [(i, f"u{i%100:03d}", i%100, float(i%10)) for i in range(6000)]
db.executemany("INSERT INTO ev VALUES(?,?,?,?)", data)
db.executemany("INSERT INTO users VALUES(?,?)", [(i,"A" if i in (7,9) else "B") for i in range(100)])
db.execute("CREATE INDEX ix_ev_user ON ev(user_id)")
db.execute("ANALYZE")
print("SQLite", sqlite3.sqlite_version)
tests = [
 ("IN", "user_id IN (?,?)", ("u007","u009"), [r[0] for r in data if r[2] in (7,9)]),
 ("prefix LIKE", "user_id LIKE ?", ("u00%",), [r[0] for r in data if r[1].startswith("u00")]),
 ("leading wildcard", "user_id LIKE ?", ("%007",), [r[0] for r in data if r[1].endswith("007")])]
for label, predicate, args, expected in tests:
    sql = "SELECT id FROM ev WHERE " + predicate + " ORDER BY id"
    actual = [r[0] for r in db.execute(sql,args)]
    assert actual == expected
    print(label, len(actual), db.execute("EXPLAIN QUERY PLAN "+sql,args).fetchall())
join = "SELECT e.id FROM users u JOIN ev e ON e.uid_int=u.id WHERE u.city=? ORDER BY e.id"
expected = [(r[0],) for r in data if r[2] in (7,9)]
for indexed in [False,True]:
    if indexed:
        db.execute("CREATE INDEX ix_ev_uid ON ev(uid_int)")
        db.execute("ANALYZE")
    actual = db.execute(join,("A",)).fetchall()
    assert actual == expected
    print("join index", indexed, "rows", len(actual), db.execute("EXPLAIN QUERY PLAN "+join,("A",)).fetchall())
db.close()

本轮 SQLite 3.53.1 核查的结果数依次为 120、600、60;连接两种状态均返回同一 120 行。计划只作为该版本/分布/索引/统计信息的观察,不断言未来必须出现某段文本,也没有据此发布固定倍速。

条件为何有这条机会 / 限制
IN 的两个离散键可能做多次索引等值访问;列表很大或命中占比高时也可能选扫描,不能写“IN 必走索引”
LIKE 'u00%'存在固定前缀,可变成有序范围候选;还须符合 SQLite 的 TEXT 亲和性、内置 LIKE、collation/大小写设置与绑定准备接口等条件
LIKE '%007'没有可用的起始前缀,这种范围优化不适用;仍可能扫描覆盖索引,扫描索引不等于缩小成选择性 SEARCH
JOIN 键是否有索引外层候选、连接键匹配数与内层定位成本一起改变工作量;不是“表越少越快”

按此例数据手算:外层 city='A' 留 2/100 个用户,每个键对应 60 条事件,共 120 个匹配。若用外层用户逐个扫内层,就是约 2×6000 次候选检查;有连接键访问时可定位两个匹配段。实际优化器还可能反转内外层,改成扫 6000 个事件再查用户;这份访问次数手算不是声明实际计划一定做了其中哪条路径。连接语义还受一对多、过滤位置与外连接保留行影响,改写后要继续断言结果。

物理索引结构与复合键顺序见 AI 全栈 06 的可选深入,本页只解释查询路径。依据:SQLite 优化器与 LIKE 条件、EXPLAIN QUERY PLAN。

🧯 六、一条查询慢了,按什么顺序查

⭐ 这一节是前面所有内容的使用说明。

第 0 步:先确认慢的真是这条 SQL。 ⚠️ 「接口慢」里有相当一部分根本不在数据库:连接池排队、一次取回十万行的序列化、 ⭐ 以及在循环里发了 200 条查询(N+1)—— 每条都是 0.1 ms,加起来 20 ms 全花在往返上, 而你去看单条计划,每条都完美。先看「跑了几条」,再看「每条多快」。

第 1 步:EXPLAIN,只找三样东西。

找什么 看到了意味着
有没有 SCAN 大表 全表扫。⚠️ SCAN … USING … INDEX 也算(第二节 ⑤)
有没有 TEMP B-TREE 在做额外的排序或去重
有没有 AUTOMATIC INDEX ⭐ 数据库在明说「这儿缺一个索引」

第 2 步:看到 SCAN 大表,按顺序问三句。

  1. ⭐ WHERE 里那个列被什么东西包住了吗?(第四节的六种)——这一条最常中,而且改起来最便宜。
  2. 这个列上到底有没有索引? 没有就去 AI 全栈 06,那是它的题目。
  3. ⭐ 是不是本来就该全扫? 「统计全表」「取走大半张表」时,SCAN 是正确答案 —— ⚠️ 逐行走索引再回表反而更慢。优化器算过这笔账,别硬掰它。

第 3 步:改完再看一次计划,并且量一次。 ⭐ 两样都要:只看计划可能计划变漂亮了但更慢,只看耗时可能只是缓存热了。

⚠️ 两条容易忘的:

💀 最后一条,也是最容易的一次自我欺骗:「计划里有索引名」和「这条查询快」是两件事。 本章第二节 ⑤ 那条 SCAN ev USING COVERING INDEX ix_user, 拿去给任何一个只扫一眼的人看,都会得到「索引用上了呀」的结论。

从线上现象到回归:先找负载入口

单条计划只是闭环中间一步。先固定观察时间窗,按标准化语句/查询类别聚合,保留有代表性的参数分布与返回行数;同一 SQL 模板在不同参数上可能选择不同计划。

阶段最少留下的证据
日志 / 监控召回频次、平均与分位耗时、总耗时,以及数据库耗时与应用排队/取数耗时的边界
按负载排序高频中等耗时也可能比偶发慢查询更贵;p95 不能从一个平均值推出来
代表性重现语句、参数分布、数据量/选择性、索引、统计信息与目标引擎版本
计划与运行对照扫描/返回行数、连接重复次数、排序与估算偏差;普通 EXPLAIN 不等于实际耗时
受控修改 / 回归一次改变一个索引或写法,先核结果,再在相同负载比较延迟、写入/空间成本与其他代表查询

MySQL 8.4 文档入口,未启动服务实测:慢日志受 long_query_time、min_examined_row_limit 等配置影响,记录 Query_time、Lock_time、Rows_sent、Rows_examined 等信息;阈值/限流或外部采样会改变观察范围,日志没出现不能证明它没有消耗。启用相应采集时,Performance Schema 的 events_statements_summary_by_digest 可按摘要看 COUNT_STAR、SUM_TIMER_WAIT、AVG_TIMER_WAIT、SUM_ROWS_EXAMINED 等累计指标;它们有自身统计窗、重置和采集条件。慢日志与摘要统计各补一面,不能当同一个无偏样本。

例如一类请求 20 ms、每分钟 10000 次,另一类 2 s、每分钟 1 次:仅把平均耗时乘频次作粗算,前者累计 200 s、后者 2 s。它提示优化优先级,却不代表 200 s 墙钟串行耗时,也不提供 p95 或生产提速承诺。依据:MySQL 8.4 慢日志、语句摘要统计;计划命令的执行副作用继续看 原速查。

🔗 这一章连到哪里

相关的地方 为什么
01-一条查询是怎么跑的.html ⭐ 它欠的就是这一章:「逻辑顺序答为什么是这样,物理计划答为什么这么慢」。它讲的 SELECT * 让覆盖索引失效,在本章第二节 ③④ 有完整实测
02-JOIN的几种语义.html 那一章讲连接的语义(含扇出把 150 变成 250),本章补代价:谁在外层、内层能不能走索引
05-子查询的三种形状.html IN / EXISTS / JOIN 选哪个,最终也要落到计划上看 —— ⭐ 那一章给语义判据,本章给验证手段
04-NULL与三值逻辑.html IS NOT NULL 实测是 SEARCH 却要 2.5847 ms —— ⚠️ 想清楚 NULL 的语义之后,还得看它到底筛掉了多少行
08-窗口帧.html 上一章。那里的问题是「数对不对」,这一章的问题是「快不快」—— ⭐ 顺序别反:先把数弄对
10-拿SQL审问一份数据.html 下一章把前九章的东西合起来用在一份真数据上
AI 全栈 06 关系数据库 ⭐ 索引怎么建全在那边(复合索引列序、覆盖索引该不该建、200 次查询 3.802 秒 → 0.0019 秒)。本章看到 AUTOMATIC INDEX 或「这列压根没索引」时,就是去那一章的时候
AI 全栈 06b 列表接口与分页 那一章实测过 COUNT(*) 走覆盖索引 0.031 ms、命不中索引的 LIKE '%..%' 要 30.7 ms —— ⭐ 两条都是 SCAN,用本章第二节的动词判据正好能读懂它那张表

✅ 检查点

  1. 执行计划里必须认的两个动词是什么?各代表什么动作?
  2. ⭐ 看到 SCAN ev USING COVERING INDEX ix_user,该怎么判断?为什么它是本章的卖点?
  3. 同一个 WHERE,SELECT user_id 和 SELECT user_id, amount 的计划为什么不一样?这解释了 01 章的哪句话?
  4. USE TEMP B-TREE FOR ORDER BY 什么时候出现、什么时候不出现?
  5. ⭐ 2000 用户 / 20 万事件那组实测:没索引时谁在外层快?建完索引之后呢?这个反转说明了什么实践判据?
  6. 计划里出现 AUTOMATIC COVERING INDEX 是什么意思?该怎么办?
  7. 本例普通 ts 列索引为何未直接服务 substr(ts,1,10)='2026-03-05'?范围改写的结果和历史耗时怎样?若有匹配的表达式索引,结论还能直接照搬吗?
  8. amount * 2 > 199 和 YEAR(created) = 2026 分别该怎么改写?为什么右端要用 < 不用 <=?
  9. ⭐ OR 的三种命运各是什么?实测差多少?为什么说「别背『OR 要改 UNION』」?
  10. user_id LIKE 'u00123%' 没有前导通配,为什么历史实测仍为 SCAN?若把默认 LIKE 换成 BINARY 范围,加入 U00123a 后结果还相同吗?应该先核什么、再核什么?
  11. 一条查询慢了,第 0 步先确认什么?看到 SCAN 大表之后按顺序问哪三句?
👀 答案
  1. SCAN = 把这个东西从头到尾读一遍;SEARCH = 拿索引直接定位过去,只读要的那几行。判据是动词,不是索引名。
  2. 动词是 SCAN,所以它把 ix_user 这个索引整个读了一遍(20 万行一行不落),只是省掉了回表 —— 这叫在索引上做全表扫,不叫用上了索引。它是卖点因为「索引名清清楚楚出现在计划里」会让人一眼判成「索引生效了」。⚠️ 反过来 SEARCH 也不等于快:IS NOT NULL 是 SEARCH 却要 2.5847 ms(命中单个 user_id 只要 0.0028 ms),因为它定位到的范围里装着几乎所有行。动词只说「怎么找」,不说「找到多少」。
  3. 只要 user_id 时索引里什么都有 → SEARCH … USING COVERING INDEX;多要一列 amount 时索引里没有、必须回表 → 退回 SEARCH … USING INDEX。这就是 01 章那句「SELECT * 往往要求索引之外的列」的完整版。⚠️ 但别倒过来把所有列塞进索引(AI 全栈 06 否掉了这条路)。
  4. ORDER BY user_id 时不出现(索引本来就按它有序);ORDER BY amount 时出现(得先全捞出来再排)。ORDER BY + LIMIT 的分页慢,先去计划里找这一行。
  5. 没索引时 events 在外层快(0.0440 s vs 0.2380 s,约 5 倍);建完 events(user_id) 索引后反过来,users 在外层快(0.0019 s vs 0.0140 s,约 7 倍)。说明最优连接顺序取决于有没有索引,所以不要手工钉死连接顺序(CROSS JOIN / hint 是最后一招,它把今天的数据分布和索引焊进了 SQL)。换回普通 JOIN 让优化器自己选,它给出的计划和手工最优那条一字不差。
  6. 数据库在说「这儿缺一个索引,我先临时建一个」—— 而临时索引每次查询都要重建。看到它就该去 AI 全栈 06 补一个真索引。
  7. 对本例的普通 ts 列索引,substr(ts,...) 条件未必能直接用它定位;语义等价的原列范围比较可作为候选改写。不能排除匹配的表达式/函数/生成列索引或可证明的优化器改写。SQLite 表达式索引还要求表达式匹配与函数确定性,其他引擎/版本另核;最终看实际计划与耗时。参见 SQLite 表达式索引。日期筛选的半开区间保留,避免漏掉秒内的小数部分。
  8. amount*2 > 199 → amount > 99.5;YEAR(created)=2026 → created >= '2026-01-01' AND created < '2027-01-01'。这些改写要先确认语义等价,再核目标引擎的实际计划。右端用 < 是因为 <= '2026-03-05 23:59:59' 会漏掉那一秒里的毫秒。
  9. 原 SQLite 夹具:① 两侧可索引化 → MULTI-INDEX OR,各走索引再合并,0.0066 ms;② 手工改 UNION → 0.0179 ms,反而更慢(多了去重的 TEMP B-TREE);③ amount 一侧无索引,该顶层 OR 选择 SCAN,15.7034 ms,比①慢约 2400 倍。这些是旧环境记录,不是固定性能关系;其他可索引的 AND 条件仍可能先筛行。改写可能更慢,先核结果,再看目标环境的计划和耗时。
  10. 原 SQLite 实验的默认 LIKE 对 ASCII 大小写不敏感,与普通 BINARY 索引的模式不匹配;原连接打开 PRAGMA case_sensitive_like=ON 后,历史计划从 SCAN 变为 SEARCH,10.3474 ms → 0.0033 ms,约 3100 倍。这些数字仍是旧环境记录,不是本轮基准。范围条件虽不受 LIKE 开关影响,语义却不保证相同:默认 LIKE 在新增两行反例中返回大小写两行,BINARY 范围只返回小写一行。历史“永远有效”仅在原全小写固定数据、BINARY 边界匹配的等价前提下阅读;先断言结果相等,再查目标版本/设置下的计划和耗时。原 NOCASE 受控例保留 LIKE 与匹配索引,不要求靠改开关解决。
  11. 第 0 步:先确认慢的真是这条 SQL —— 连接池排队、一次取十万行的序列化、尤其是循环里发了 200 条查询(N+1,每条 0.1 ms 单看都完美)。看到 SCAN 大表后按顺序问:① WHERE 里的列被包住了吗(最常中、最便宜)② 这列到底有没有索引(有就去 AI 全栈 06)③ 是不是本来就该全扫(统计全表、取走大半张表时 SCAN 就是正确答案,别硬掰优化器)。改完要同时看计划和量耗时。

本节回顾

⚡ 走神救援

⭐ 执行计划里只有两个动词要认:SCAN 是从头读一遍,SEARCH 是直接定位过去。 💀 索引名出现在计划里,并不代表索引在起作用——判据是动词不是索引名。 本章卖点就是那条「SCAN … USING COVERING INDEX」:索引名清清楚楚,动词却是 SCAN,它把索引整个读了一遍,这叫在索引上全表扫。

⚠️ 反过来 SEARCH 也不等于快:定位到的范围里如果装着几乎所有行,照样慢——动词只说「怎么找」,不说「找到多少」。

另外三个记号:USING COVERING INDEX(要的列全在索引里,连表都不碰——多要一列就退回回表,⭐ 这正是「SELECT * 往往要求索引之外的列」的完整版)、USE TEMP B-TREE FOR ORDER BY(分页慢先找它)、AUTOMATIC INDEX(⭐ 数据库在说「这儿缺一个索引」)。

🔗 连接的代价就一句话:嵌套循环,代价 ≈ 外层行数 × 内层每次查找的代价。 ⭐ 同一对表、同一个问题,最优连接顺序会因为有没有索引而反转——所以别手工钉死顺序,那等于把今天的数据分布焊进 SQL。

对本例的普通 ts 列索引,substr(ts,...) 条件未必能直接用它定位;语义等价的原列范围比较可作为候选改写。不能排除匹配的表达式/函数/生成列索引或可证明的优化器改写。SQLite 表达式索引还要求表达式匹配与函数确定性,其他引擎/版本另核;最终看实际计划与耗时。参见 SQLite 表达式索引。日期筛选的半开区间保留,避免漏掉秒内的小数部分。

🔧 OR 分支能否索引化,会影响候选访问路径。原夹具的一侧无索引时,该顶层 OR 选择全扫;整条查询若还有可索引的 AND 条件,仍可能先筛行。别把旧计划和毫秒数当成所有查询的保证。

下一节 👉 10-拿SQL审问一份数据.md

打卡记录保存在你的浏览器里,首页能看到总进度