2026.11.05 AI学习

AI编程（五十九）：性能优化入门 — 让查询从”翻全书”变”查目录”

作者：climbing

数据库用着用着就”变慢”，不是它坏了，是它在老老实实干笨活：每次查询都逐行翻完整本账。今天给它装上”目录”——索引。概念只此一个，效果立竿见影，是从”能用”到”好用”的第一道分水岭。

一、真实场景：查一条记录，等到去接了杯水

接着讲你的价格监控工具。数据库一路用到今天，prices 表里攒了小半年的价格记录——三万八千条。某天老板路过你工位：”帮我查下’空气炸锅’最近的价格。”你信心满满地敲下：

SELECT * FROM prices WHERE product = '空气炸锅';

然后——光标闪啊闪，一秒、两秒、三秒……你起身去接了杯水，回来结果才慢悠悠刷出来。老板留下一句”你这系统有点卡啊”走了，留下你对着屏幕怀疑人生：数据库不是”快”的代名词吗？怎么三万条就趴窝了？

其实数据库一点没坏，它只是在逐行翻账本：你让它找”空气炸锅”，它老老实实从第 1 行翻到第 38000 行，每行看一眼”是不是你”。三万条翻三万次，几秒钟就是这么花掉的。这不叫”慢”，这叫”笨”——而笨的解药今天只有一个名字：索引。

好消息是：这一课的新概念只有”索引”一个，剩下的全是老朋友。而且加索引往往就一行代码，效果却能从”三秒”变”瞬间”。这是投入产出比最高的一课。

二、新概念：索引就是书末的”目录”

你查一个词在不在一本 500 页的书里，有两种查法：

  1. 翻全书：从第 1 页翻到第 500 页，每页找一遍——保证能找到，但得翻半小时；
  2. 查目录：翻到书末的索引页，”空气炸锅……127″，直接翻到 127 页——十秒钟搞定。

没有索引的数据库查询就是查法 1，有索引就是查法 2。索引（index）就是数据库给某一列单独建的”目录”：它把这一列的所有值排好序、记好”每个值在哪一行”，查询时先查目录定位，再一步翻到那一页——不用再逐行翻全书。

来动手看效果。先造点数据，把”慢”复现出来：

# make_big.py — 造 3 万条价格记录，把"慢"复现出来
import sqlite3
import random

conn = sqlite3.connect("shop.db")
conn.execute("""CREATE TABLE IF NOT EXISTS prices (
    id INTEGER PRIMARY KEY,
    product TEXT,
    price REAL,
    date TEXT
)""")

products = ["空气炸锅", "电饭煲", "蓝牙耳机", "机械键盘", "显示器", "台灯"]
conn.execute("DELETE FROM prices")          # 清场，方便反复跑
rows = [
    (products[random.randrange(6)], round(random.uniform(50, 2000), 2), f"2026-{random.randrange(1,13):02d}-{random.randrange(1,29):02d}")
    for _ in range(38000)
]
conn.executemany("INSERT INTO prices (product, price, date) VALUES (?, ?, ?)", rows)
conn.commit()
print("造好 38000 条")
conn.close()

跑完，来掐个表看它到底多慢：

# slow_query.py — 没有索引时，查询有多慢
import sqlite3
import time

conn = sqlite3.connect("shop.db")
start = time.time()
result = conn.execute("SELECT * FROM prices WHERE product = '空气炸锅'").fetchall()
elapsed = time.time() - start
print(f"查到 {len(result)} 条，耗时 {elapsed:.3f} 秒")
conn.close()

三万八千条、查出来的结果可能就六千多条，但数据库得把三万八千行全部看一遍才能凑出这六千条。在配置一般的机器上，这一步零点几秒到一两秒——单次不算致命，但你的监控工具要是每天跑几十次查询，用户每次点一下就”想想”，体验就是”卡”。

三、一行代码：给”目录”开张

现在加索引，就一行：

CREATE INDEX idx_prices_product ON prices(product);

翻译成人话：”给 prices 表的 product 列建一个叫 idx_prices_product 的目录。”再跑一遍 slow_query.py——同样的查询，耗时掉到零点零零几秒，快几十倍甚至上百倍。不是数据库突然勤快了，是它终于不用翻全书了。

来验证”目录”确实存在、也确实被用上了。两个行家工具：

# check_index.py — 验证索引：在不在？用没用上？
import sqlite3

conn = sqlite3.connect("shop.db")

# 工具1：EXPLAIN QUERY PLAN —— 让数据库"招供"它打算怎么查
for row in conn.execute("EXPLAIN QUERY PLAN SELECT * FROM prices WHERE product = '空气炸锅'"):
    print(row)
# 加索引前：SCAN prices（全表扫描 = 翻全书）
# 加索引后：SEARCH prices USING INDEX idx_prices_product (product=?)（用目录 = 查目录）

# 工具2：看看这张表上都有哪些索引
for row in conn.execute("SELECT name FROM sqlite_master WHERE type = 'index'"):
    print("已有索引：", row[0])
conn.close()

EXPLAIN QUERY PLAN 是今天最值钱的工具，值得单独说：它不执行查询，只让数据库说出它的查询计划。看到 SEARCH ... USING INDEX 就是用上目录了，看到 SCAN prices 就是还在翻全书。以后你觉得”这条查询怎么这么慢”，先让它招供，别瞎猜。

两个输出对照着看，你会真切感受到：同一个查询、同一个数据库，差别只在有没有索引——这就是”性能优化”的入门第一课：不是换电脑、不是换数据库，是让数据库别干笨活。

跟AI说需求：把 slow_query.py 和 EXPLAIN QUERY PLAN 的输出发给AI：”这条查询耗时 1.2 秒，查询计划显示 SCAN，帮我优化”——它会告诉你加哪列的索引、命令怎么写，你负责跑一遍掐表验证效果。

四、原理再深一层：为什么目录能快这么多

“翻全书”的慢，慢在看得多：38000 行就得看 38000 次，数据涨十倍，时间就涨十倍——行话叫”线性增长”，数据越多越绝望。

“查目录”的快，快在跳着走。数据库把目录按字母排好序之后，查找用的不是”从头翻”，而是”二分查找”：先翻到中间看偏前还是偏后，一刀砍掉一半，再翻剩下的中间，再砍一半……38000 条的目录，最多砍 16 刀就能定位（2 的 16 次方是 6 万多）。数据涨十倍？最多多砍一两刀而已——这就是为什么几百万条的数据库查询照样瞬间出结果。

用生活类比：在一本 500 页的书里找”索引”这个词，翻全书要 500 眼；查目录 10 秒。但注意目录本身也是”成本”——书末的目录占了 30 页纸，编辑书的时候每次加内容都得顺手更新目录。索引也一样，它要占磁盘空间、会让”写入”变慢一点（每插一条记录，目录得多记一笔）。天下没有免费的午餐，但索引这顿午餐几乎是免费的——查多写少的表，闭眼加。

五、什么时候加索引？跟着”WHERE”走

不是每一列都值得建目录。判断标准一句话：哪一列经常出现在 WHERE 后面，就给哪列建索引。

  • WHERE product = '空气炸锅' 老在查 → 给 product 建索引 ✅
  • WHERE date > '2026-10-01' 老在按日期筛 → 给 date 建索引 ✅
  • price 列只用来显示、从不筛？→ 没必要建 ❌（建了白占地方）

回顾这几课你写过的查询，思路就串起来了：第51篇的 WHERE 是”查什么”，第53篇的 ORDER BY 是”怎么排”，今天的索引是”怎么查得快”。三件套凑齐，你的数据库操作就是正经路数了。

顺带一个进阶彩蛋：按 product 建了索引后，WHERE product = ... 快了，ORDER BY product 往往也跟着快——因为目录本来就是排好序的，直接顺着目录读就是排好序的结果。一个索引，两头受益。

六、四个坑点疫苗（先打再上路）

  1. 索引不是万能药，”模糊查询”开头通配它救不了。 WHERE product = '空气炸锅' 目录能用；WHERE product LIKE '%炸锅%'（开头带 %）目录就废了——你想啊，目录是按开头字母排的，你连开头是啥都不知道，目录也无从查起。LIKE '空气%'（只在结尾通配）可以走目录。真要”全文随便搜”，那是另一个正经工具（全文索引）的活，先知道边界在哪。
  1. 别给”写多读少”的表堆索引。 日志表天天几万条地插，查询却很少——给它建三五个索引，每次插入都要更新三五本目录，写入速度肉眼可见地变慢。索引是拿”写入速度”换”查询速度”，查多写少的表才划算。价格监控这种”天天查、偶尔写”的，随便加；流水日志那种”天天写、偶尔查”的，省省。
  1. 加完索引要用 EXPLAIN QUERY PLAN 验证”真的用上了”。 白加的情况真实存在：查询写法不对（比如对索引列做了运算 WHERE LOWER(product) = ...）、或者数据量太小数据库觉得翻全书更快。眼见为实，让数据库招供，别自我感觉良好。
  1. 索引名别跟列名重名，改表删表时记得它。 列叫 product，索引就叫 idx_prices_product 这样的格式名（idx_表名_列名 是通行惯例），清清楚楚。删表时索引跟着自动消失，但只删索引得用 DROP INDEX idx_prices_product;——哪天发现”怎么加了索引还是慢”，先 SELECT name FROM sqlite_master WHERE type='index' 看看目录还在不在，别是被哪次清理误删了。

七、跟AI协作的正确姿势（性能特供版）

  1. 让AI先”诊断”再”开药”。 把 EXPLAIN QUERY PLAN 的输出贴给它：”这条查询在做 SCAN，帮我看看该加什么索引”——带上查询计划，AI 的建议才不是瞎猜。这跟看病一个道理：先拍片子，再开药方。
  1. 让AI解释”为什么快了”，别只知道”加了就快”。 追问一句”为什么这个索引能让查询从 1 秒变 0.001 秒”，把二分查找、目录排好序这些原理嚼碎。今天懂了原理，明天遇到”加了索引还是慢”你才有排查的底气——性能优化最怕的不是慢，是不知道为什么慢。
  1. 批量优化让AI列清单。 “这是我的建表语句和所有查询语句，帮我列一下哪些列该加索引、每个索引的命令”——AI 一把给你列全，你逐条加、逐条用 EXPLAIN QUERY PLAN 验证。别一次加十个不验证，出了问题都不知道是哪个的锅。

八、今日小练习

练习1：给自己的表做个”提速前后对照实验”。 用 make_big.py 的套路给你的某张表造几千条数据，掐表跑一次查询（记下耗时），加索引，再掐表跑一次，把两个数字打印出来（提示：time.time() 夹住查询语句；顺手用 EXPLAIN QUERY PLAN 各看一次输出，亲眼对比 SCAN 和 SEARCH ... USING INDEX——数字和”招供”两样都拿到才算实验做完整）。

练习2：找出”加了也白加”的查询。 写两条查询：一条 WHERE product = '空气炸锅'，一条 WHERE product LIKE '%炸锅%'，都加了 product 索引之后分别看 EXPLAIN QUERY PLAN，验证”精确匹配走目录、开头通配不走目录”（提示：对照坑点疫苗1；如果两条都显示 SEARCH，别慌，数据量小的时候数据库可能耍小聪明，造一万条再看）。

练习3：给价格监控工具做一次”体检”。 打开你第48篇的价格监控工具（或任何用过 SQLite 的程序），列出它的所有查询语句，按”哪些列常在 WHERE 后面”的判断标准，给它开一份”索引清单”，逐条加上并验证（提示：让AI帮你从代码里把查询语句全抓出来；想想哪张表是”查多写少”、哪张是”写多读少”，只给前者开药）。

下篇预告

下一篇（第60篇）：阶段项目 — 完整的数据管理系统。 阶段5的毕业作品：把第49到59篇的全部家底——建表、增删改查、关联、Python 接管、网页操作、备份、迁移、索引——揉进一个完整的小系统。一篇之内，把”数据库”这个新台阶踩实站稳。