一条按user_id查订单的 SQL在 50 万行上没建索引时全表扫描要 9.5 毫秒建完索引降到 0.22 毫秒。单看这个数字不大但它随数据量线性放大表涨到五千万行差距会是几千倍。本文用 Python 标准库sqlite3在本地把这套对比完整跑一遍所有数字都是本机实测不靠记忆。环境准备不需要装任何第三方包Python 3.13 自带sqlite3模块即可。先确认版本python -c import sqlite3, sys; print(sys.version.split()[0]); print(sqlite, sqlite3.sqlite_version)本机实测输出3.13.12与sqlite 3.46.0具体版本取决于你的 Python 构建不影响结论。完整基准测试脚本下面这段脚本造 50 万行订单数据分别测「无索引点查 / 有索引点查」「对列套函数 / 范围写法」「覆盖索引 / 回表」三组对比。直接复制保存为benchmark.py运行即可#!/usr/bin/env python3 # -*- coding: utf-8 -*- 本地实测慢查询 索引的差距标准库 sqlite3零三方依赖。 import sqlite3 import time N 500_000 # 50 万行 con sqlite3.connect(:memory:) cur con.cursor() cur.execute( CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, user_id INTEGER, order_date TEXT, amount REAL, status TEXT) ) rows [ ( i, (i % 2000) 1, # 2000 个用户每人约 250 单 f2026-{1 (i % 12):02d}-{(i % 28) 1:02d}, float(i % 1000), paid if i % 4 else cancelled, ) for i in range(1, N 1) ] cur.executemany(INSERT INTO orders VALUES (?,?,?,?,?), rows) con.commit() print(f[数据] 已写入 {N} 行 orders) def best_time(sql, params(), repeat20): for _ in range(3): # 预热排除首次编译/缓存噪声 cur.execute(sql, params).fetchall() best float(inf) for _ in range(repeat): t0 time.perf_counter() cur.execute(sql, params).fetchall() best min(best, time.perf_counter() - t0) return best * 1000.0 def plan(sql, params()): cur.execute(EXPLAIN QUERY PLAN sql, params) return | .join(r[3] for r in cur.fetchall()) print(\n 1) 点查无索引 vs 有索引 ) q SELECT * FROM orders WHERE user_id ? print(无索引 plan:, plan(q, (1234,))) t_no best_time(q, (1234,)) print(f无索引 耗时: {t_no:.3f} ms) cur.execute(CREATE INDEX idx_user ON orders(user_id)) print(有索引 plan:, plan(q, (1234,))) t_yes best_time(q, (1234,)) print(f有索引 耗时: {t_yes:.3f} ms) print(f- 加速比: {t_no / t_yes:.1f}x) print(\n 2) 对列套函数索引直接失效 ) qf SELECT COUNT(*) FROM orders WHERE strftime(%Y-%m, order_date) ? qr SELECT COUNT(*) FROM orders WHERE order_date ? AND order_date ? cur.execute(CREATE INDEX idx_date ON orders(order_date)) print(函数写法 plan:, plan(qf, (2026-03,))) tf best_time(qf, (2026-03,), repeat10) print(f函数写法 耗时: {tf:.3f} ms) print(范围写法 plan:, plan(qr, (2026-03-01, 2026-04-01))) tr best_time(qr, (2026-03-01, 2026-04-01), repeat10) print(f范围写法 耗时: {tr:.3f} ms) print(f- 函数 vs 范围 加速比: {tf / tr:.1f}x) print(\n 3) 覆盖索引少回表 ) cur.execute(CREATE INDEX idx_cover ON orders(user_id, status)) qc SELECT status FROM orders WHERE user_id ? qa SELECT * FROM orders WHERE user_id ? print(只取列 plan:, plan(qc, (1234,))) print(全取 plan:, plan(qa, (1234,))) tc best_time(qc, (1234,)) ta best_time(qa, (1234,)) print(f覆盖索引(只取列) 耗时: {tc:.4f} ms) print(f回表(SELECT *) 耗时: {ta:.4f} ms) print(f- 覆盖索引加速比: {ta / tc:.1f}x)本机实测输出取 20 次最优值:memory:存储 1) 点查无索引 vs 有索引 无索引 plan: SCAN orders 无索引 耗时: 9.528 ms 有索引 plan: SEARCH orders USING INDEX idx_user (user_id?) 有索引 耗时: 0.222 ms - 加速比: 42.9x 2) 对列套函数索引直接失效 函数写法 plan: SCAN orders USING COVERING INDEX idx_date 函数写法 耗时: 87.147 ms 范围写法 plan: SEARCH orders USING COVERING INDEX idx_date (order_date? AND order_date?) 范围写法 耗时: 0.766 ms - 函数 vs 范围 加速比: 113.8x 3) 覆盖索引少回表 只取列 plan: SEARCH orders USING COVERING INDEX idx_cover (user_id?) 全取 plan: SEARCH orders USING INDEX idx_user (user_id?) 覆盖索引(只取列) 耗时: 0.0676 ms 回表(SELECT *) 耗时: 0.2170 ms - 覆盖索引加速比: 3.2x结果一点查从全表扫描到索引查找没索引时规划器只能SCAN orders从第 1 行扫到第 50 万行逐条比对user_id建了idx_user后计划变成SEARCH orders USING INDEX idx_user (user_id?)通过 B 树直接定位。本机实测 9.528ms 对 0.222ms约42.9 倍。差距的本质是复杂度全表扫描是 O(N)索引查找是 O(log N)。所以数据量翻 100 倍全表扫描耗时大致翻 100 倍索引查找只多几层树高。这正是「大表上一句慢 SQL 能把连接池打满」的底层原因——它不是慢一点是随表膨胀线性变慢。结果二对列套函数索引直接失效第二组最值得记。给order_date建了索引却写出WHERE strftime(%Y-%m, order_date) 2026-03。规划器返回SCAN orders USING COVERING INDEX idx_date——注意它仍然是个 SCAN只是扫的是索引而不是整表并没有 SEEK 定位。原因是strftime(order_date, ...)这个表达式对每一行都要现算规划器没法用它去索引里「跳」。改写成范围写法order_date 2026-03-01 AND order_date 2026-04-01列本身干干净净出现在比较符号左边计划立刻变成SEARCH ... USING COVERING INDEX本机实测 87.147ms 降到 0.766ms约113.8 倍。MySQL 里一模一样WHERE DATE(create_time)2026-03-15会让create_time索引失效必须改成 AND 。结果三覆盖索引少回表SELECT *时规划器虽然走了idx_user但索引里只有user_id要的其他列得拿rowid回主表再捞一次这趟叫回表。改成SELECT status并建联合索引(user_id, status)计划里多了COVERING关键字——要查的status和定位用的user_id都在索引里不用回表。本机实测 0.2170ms 对 0.0676ms约3.2 倍。列表页、统计接口这类只读少量列的场景覆盖索引收益最直接。怎么判断你的 SQL 走没走索引不用猜一条命令看执行计划EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id 1234; -- 健康: SEARCH orders USING INDEX idx_user (user_id?) -- 异常: SCAN ordersSCAN是全扫SEARCH ... USING INDEX才是走索引定位。MySQL 看EXPLAIN的type列ALL等价于 SCANref/range才是用上索引Extra里出现Using filesort或Using temporary也要警惕。三个会咬人的坑深翻页LIMIT 100000, 20偏移量越大越慢数据库得先数出前 10 万行再扔掉。改成游标分页记住上一页最大id下页用WHERE id ? LIMIT 20索引直接定位不数废行。隐式类型转换user_id是整型接口传进来个字符串123数据库会在比较时做类型转换等于对列套了函数索引当场失效退化成全表扫描。接口层务必保证入参类型和字段类型一致。索引不是越多越好每插一行、改一行所有相关索引都得跟着更新。写多读少的流水表如日志加一堆索引写入直接被拖死。只给高频查询条件建定期清理用不上的冗余索引。小结加索引不是魔法它把 O(N) 的逐行扫描换成 O(log N) 的定位。上线前先跑一条EXPLAIN QUERY PLAN看到SCAN就排查——这比上线后被慢查询拖垮连接池便宜得多。
一条按user_id查订单的 SQL在 50 万行上没建索引时全表扫描要 9.5 毫秒建完索引降到 0.22 毫秒。单看这个数字不大但它随数据量线性放大表涨到五千万行差距会是几千倍。本文用 Python 标准库sqlite3在本地把这套对比完整跑一遍所有数字都是本机实测不靠记忆。环境准备不需要装任何第三方包Python 3.13 自带sqlite3模块即可。先确认版本python -c import sqlite3, sys; print(sys.version.split()[0]); print(sqlite, sqlite3.sqlite_version)本机实测输出3.13.12与sqlite 3.46.0具体版本取决于你的 Python 构建不影响结论。完整基准测试脚本下面这段脚本造 50 万行订单数据分别测「无索引点查 / 有索引点查」「对列套函数 / 范围写法」「覆盖索引 / 回表」三组对比。直接复制保存为benchmark.py运行即可#!/usr/bin/env python3 # -*- coding: utf-8 -*- 本地实测慢查询 索引的差距标准库 sqlite3零三方依赖。 import sqlite3 import time N 500_000 # 50 万行 con sqlite3.connect(:memory:) cur con.cursor() cur.execute( CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, user_id INTEGER, order_date TEXT, amount REAL, status TEXT) ) rows [ ( i, (i % 2000) 1, # 2000 个用户每人约 250 单 f2026-{1 (i % 12):02d}-{(i % 28) 1:02d}, float(i % 1000), paid if i % 4 else cancelled, ) for i in range(1, N 1) ] cur.executemany(INSERT INTO orders VALUES (?,?,?,?,?), rows) con.commit() print(f[数据] 已写入 {N} 行 orders) def best_time(sql, params(), repeat20): for _ in range(3): # 预热排除首次编译/缓存噪声 cur.execute(sql, params).fetchall() best float(inf) for _ in range(repeat): t0 time.perf_counter() cur.execute(sql, params).fetchall() best min(best, time.perf_counter() - t0) return best * 1000.0 def plan(sql, params()): cur.execute(EXPLAIN QUERY PLAN sql, params) return | .join(r[3] for r in cur.fetchall()) print(\n 1) 点查无索引 vs 有索引 ) q SELECT * FROM orders WHERE user_id ? print(无索引 plan:, plan(q, (1234,))) t_no best_time(q, (1234,)) print(f无索引 耗时: {t_no:.3f} ms) cur.execute(CREATE INDEX idx_user ON orders(user_id)) print(有索引 plan:, plan(q, (1234,))) t_yes best_time(q, (1234,)) print(f有索引 耗时: {t_yes:.3f} ms) print(f- 加速比: {t_no / t_yes:.1f}x) print(\n 2) 对列套函数索引直接失效 ) qf SELECT COUNT(*) FROM orders WHERE strftime(%Y-%m, order_date) ? qr SELECT COUNT(*) FROM orders WHERE order_date ? AND order_date ? cur.execute(CREATE INDEX idx_date ON orders(order_date)) print(函数写法 plan:, plan(qf, (2026-03,))) tf best_time(qf, (2026-03,), repeat10) print(f函数写法 耗时: {tf:.3f} ms) print(范围写法 plan:, plan(qr, (2026-03-01, 2026-04-01))) tr best_time(qr, (2026-03-01, 2026-04-01), repeat10) print(f范围写法 耗时: {tr:.3f} ms) print(f- 函数 vs 范围 加速比: {tf / tr:.1f}x) print(\n 3) 覆盖索引少回表 ) cur.execute(CREATE INDEX idx_cover ON orders(user_id, status)) qc SELECT status FROM orders WHERE user_id ? qa SELECT * FROM orders WHERE user_id ? print(只取列 plan:, plan(qc, (1234,))) print(全取 plan:, plan(qa, (1234,))) tc best_time(qc, (1234,)) ta best_time(qa, (1234,)) print(f覆盖索引(只取列) 耗时: {tc:.4f} ms) print(f回表(SELECT *) 耗时: {ta:.4f} ms) print(f- 覆盖索引加速比: {ta / tc:.1f}x)本机实测输出取 20 次最优值:memory:存储 1) 点查无索引 vs 有索引 无索引 plan: SCAN orders 无索引 耗时: 9.528 ms 有索引 plan: SEARCH orders USING INDEX idx_user (user_id?) 有索引 耗时: 0.222 ms - 加速比: 42.9x 2) 对列套函数索引直接失效 函数写法 plan: SCAN orders USING COVERING INDEX idx_date 函数写法 耗时: 87.147 ms 范围写法 plan: SEARCH orders USING COVERING INDEX idx_date (order_date? AND order_date?) 范围写法 耗时: 0.766 ms - 函数 vs 范围 加速比: 113.8x 3) 覆盖索引少回表 只取列 plan: SEARCH orders USING COVERING INDEX idx_cover (user_id?) 全取 plan: SEARCH orders USING INDEX idx_user (user_id?) 覆盖索引(只取列) 耗时: 0.0676 ms 回表(SELECT *) 耗时: 0.2170 ms - 覆盖索引加速比: 3.2x结果一点查从全表扫描到索引查找没索引时规划器只能SCAN orders从第 1 行扫到第 50 万行逐条比对user_id建了idx_user后计划变成SEARCH orders USING INDEX idx_user (user_id?)通过 B 树直接定位。本机实测 9.528ms 对 0.222ms约42.9 倍。差距的本质是复杂度全表扫描是 O(N)索引查找是 O(log N)。所以数据量翻 100 倍全表扫描耗时大致翻 100 倍索引查找只多几层树高。这正是「大表上一句慢 SQL 能把连接池打满」的底层原因——它不是慢一点是随表膨胀线性变慢。结果二对列套函数索引直接失效第二组最值得记。给order_date建了索引却写出WHERE strftime(%Y-%m, order_date) 2026-03。规划器返回SCAN orders USING COVERING INDEX idx_date——注意它仍然是个 SCAN只是扫的是索引而不是整表并没有 SEEK 定位。原因是strftime(order_date, ...)这个表达式对每一行都要现算规划器没法用它去索引里「跳」。改写成范围写法order_date 2026-03-01 AND order_date 2026-04-01列本身干干净净出现在比较符号左边计划立刻变成SEARCH ... USING COVERING INDEX本机实测 87.147ms 降到 0.766ms约113.8 倍。MySQL 里一模一样WHERE DATE(create_time)2026-03-15会让create_time索引失效必须改成 AND 。结果三覆盖索引少回表SELECT *时规划器虽然走了idx_user但索引里只有user_id要的其他列得拿rowid回主表再捞一次这趟叫回表。改成SELECT status并建联合索引(user_id, status)计划里多了COVERING关键字——要查的status和定位用的user_id都在索引里不用回表。本机实测 0.2170ms 对 0.0676ms约3.2 倍。列表页、统计接口这类只读少量列的场景覆盖索引收益最直接。怎么判断你的 SQL 走没走索引不用猜一条命令看执行计划EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id 1234; -- 健康: SEARCH orders USING INDEX idx_user (user_id?) -- 异常: SCAN ordersSCAN是全扫SEARCH ... USING INDEX才是走索引定位。MySQL 看EXPLAIN的type列ALL等价于 SCANref/range才是用上索引Extra里出现Using filesort或Using temporary也要警惕。三个会咬人的坑深翻页LIMIT 100000, 20偏移量越大越慢数据库得先数出前 10 万行再扔掉。改成游标分页记住上一页最大id下页用WHERE id ? LIMIT 20索引直接定位不数废行。隐式类型转换user_id是整型接口传进来个字符串123数据库会在比较时做类型转换等于对列套了函数索引当场失效退化成全表扫描。接口层务必保证入参类型和字段类型一致。索引不是越多越好每插一行、改一行所有相关索引都得跟着更新。写多读少的流水表如日志加一堆索引写入直接被拖死。只给高频查询条件建定期清理用不上的冗余索引。小结加索引不是魔法它把 O(N) 的逐行扫描换成 O(log N) 的定位。上线前先跑一条EXPLAIN QUERY PLAN看到SCAN就排查——这比上线后被慢查询拖垮连接池便宜得多。
