数据分析师训练营/ 第三篇 SQL/ 第十七章
第十七章 · SQL 工程化

SQL 性能与交付规范

写出结果是 60 分,跑得快、说得清、对得上才是 100 分。这一章解决三个问题: 为什么这句 SQL 慢、怎么让索引真正被用上、交出去之前怎么证明数是对的。

前半章讲索引和 EXPLAIN QUERY PLAN,用 20,000 行订单表做实验; 后半章讲 Code Review 和对账四步,把“能跑”变成“敢交付”。 注意:本章按 SQLite 写,MySQL 的字段名会单独对照说明。

理论 : 实操 = 5 : 5 约 6 学时 前置 第 16 章

17.1原理:索引与执行计划

这一节不背概念,只回答三个问题:索引长什么样、优化器有没有用它、什么写法会让它用不上。 所有结论都能在 17.3 的 8 个步骤里复现。

17.1.1索引是什么:B+ 树直觉、最左前缀

把 20,000 行想成 2 万张卡片。没有索引,找 6 月订单只能一张张翻,这叫 全表扫描。 索引先按 order_date 排好、每 100 张装一盒;找 6 月,只翻对应的一叠。

数据库用 B+ 树:根页、枝页、叶子页,叶子之间按顺序连成链表。层数少,20,000 行 2–3 层就能定位; 范围查询顺着叶子链表扫。代价是写入要维护树,所以索引不是越多越好。

层级存什么作用
根页区间边界(如 2023-07、2024-01、2024-07)决定往哪个枝页走
枝页更细的区间边界继续缩小范围
叶子页索引键 + 行号(rowid)找到行,再回表取其他列
叶子链表按索引键顺序相连范围查询顺着链表扫,不用回到根
图 17.1.1 B+ 树:根页定位区间,叶子页存键和行号,叶子之间按顺序相连

SQLite 的表本身就是一棵以 rowid 为键的 B 树;二级索引里存的是“索引列的值 + rowid”。 所以通过二级索引查一行,通常是两步:先在索引里找到 rowid,再回表取整行。如果查询只要索引里的列, 就不用回表,这叫 覆盖索引,执行计划里会写 USING COVERING INDEX。

复合索引的关键是最左前缀。建一个 (city, channel) 的索引,相当于先按城市、再按渠道排好的电话簿: 你可以找“杭州的所有渠道”,也可以找“杭州 + 信息流”,但没法直接找“所有城市的信息流”——因为第二列只在第一列相同时才有顺序。

1

索引是“排序 + 目录”,复合索引遵守最左前缀

单列索引解决“这一列等值/范围”的查找;复合索引解决“先按 A 过滤,再按 B 过滤/排序”的查找。 判断能不能用,看查询条件里有没有从最左列开始的连续前缀。跳过第一列,索引就只能全扫,不能定位。

17.1.2EXPLAIN 的关键字段:type / key / rows / Extra

MySQL 里看执行计划用 EXPLAIN,输出一张表,四个关键字段是:

字段含义怎么看
type访问类型:ALL / index / range / ref / eq_ref / constALL 是全表扫描,最差;range、ref 通常可接受
key实际用上的索引NULL 表示没走索引
rows优化器预估要扫多少行越小越好;和实际行数差太远,说明统计信息过期
Extra附加信息Using index 好;Using filesort、Using temporary 要留意

SQLite 没有这套字段。它的执行计划命令是 EXPLAIN QUERY PLAN,输出的是 id / parent / notused / detail 四列,真正的信息全在 detail 的文本里。 两者可以这样对照:

MySQLSQLite 的 detail含义
type=ALLSCAN orders全表扫描,逐行读
type=rangeSEARCH ... USING INDEX ... (a>? AND a<?)用索引定位一个范围
key=idxUSING INDEX idx_orders_date实际使用的索引名
rows不直接给用 COUNT(*) 估算,或 ANALYZE 后看 sqlite_stat1
Extra=Using indexUSING COVERING INDEX覆盖索引,不回表
Extra=Using filesortUSE TEMP B-TREE FOR ORDER BY需要额外排序
Extra=Using temporaryUSE TEMP B-TREE FOR GROUP BY需要临时结构做分组
图 17.1.2 MySQL 的 EXPLAIN 字段与 SQLite 的 EXPLAIN QUERY PLAN 对照

读 SQLite 的计划,先看 SCAN 还是 SEARCH:SCAN 是扫,SEARCH 是找。 再看 USING INDEX 后面有没有索引名:没有就是没走索引。最后看 有没有 TEMP B-TREE: 有就说明排序或分组额外花了内存/磁盘。

别搞混:EXPLAIN 返回字节码,日常调优用 EXPLAIN QUERY PLAN。
2

看计划只看三件事:SCAN/SEARCH、索引名、TEMP B-TREE

先判断有没有全表扫描,再判断用的索引对不对,最后判断排序/分组有没有额外开销。 这三点看完,80% 的性能问题已经定位;剩下的才是统计信息、连接顺序、数据倾斜。

17.1.3索引失效的四种写法

先给结论:索引失效不是“数据库坏了”,而是你让索引列变成了一个表达式,优化器没法拿它去和 B+ 树里的有序键比较。

第一种:在列上做函数或运算

-- 慢:每行都要先算 strftime,再用结果比较
SELECT COUNT(*) FROM orders
WHERE strftime('%Y', order_date) = '2024';

-- 快:直接比较原始列,能走 idx_orders_date
SELECT COUNT(*) FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
strftime('%Y', order_date) = '2024'
  SCAN orders USING COVERING INDEX idx_orders_date
  100 次中位耗时 3.37 ms

order_date >= '2024-01-01' AND order_date < '2025-01-01'
  SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?)
  100 次中位耗时 0.21 ms

同样的 6,725 行结果,一个 3.37 ms,一个 0.21 ms,差 16 倍。数据量越大,差距越大。 除了 strftime,substr、date()、CAST、order_date + 0 都属于这一类。

第二种:隐式类型转换

-- order_id 是 TEXT,却拿数字去比
SELECT * FROM orders WHERE order_id = 24060100001;
-- 返回 0 行,而且不报错

这条 SQL 在 SQLite 里仍然显示 SEARCH ... USING INDEX,因为类型亲和性会把数字转成文本再查; 但转出来的是 '24060100001',而真实订单号是 'O24060100260',所以一行都查不到。 这比“慢”更危险:它不报错,只是安静地返回空结果。

在 MySQL 里,VARCHAR 列和数字比较也会触发隐式转换,常见结果是索引失效。 两个库表现不同,纪律一样:字面量类型必须和列类型一致,字符串加引号,日期用 'YYYY-MM-DD'。

第三种:前导 %

-- products 没有 product_name 索引:两种都全扫
SELECT COUNT(*) FROM products WHERE product_name LIKE '手机%';    -- 0 行
SELECT COUNT(*) FROM products WHERE product_name LIKE '%手机%';   -- 30 行

-- order_id 有索引:前缀 GLOB 能找,前导 * 只能扫
SELECT COUNT(*) FROM orders WHERE order_id GLOB 'O240601*';  -- 48 行
SELECT COUNT(*) FROM orders WHERE order_id GLOB '*00001';    -- 1 行

这里有两个坑。第一,前缀写法会改变语义:商品名是“Nova 手机01”,不以“手机”开头,所以 LIKE '手机%' 返回 0 行,而 LIKE '%手机%' 返回 30 行。第二,没有索引时两种都扫, “前导 % 失效”的前提是这一列本来有索引。

SQLite 还有独有细节:默认 LIKE 不区分大小写,优化器不会改写成范围,所以 LIKE '2024%' 默认也是 SCAN;改用 GLOB '2024*' 或 PRAGMA case_sensitive_like=ON 才会 SEARCH。 MySQL 的 LIKE '前缀%' 通常能直接走索引,这点不同。

第四种:OR 两侧不平衡

-- city 没索引:整个 OR 退化为全表扫描
SELECT COUNT(*) FROM orders
WHERE user_id = 'U000001' OR city = '北京';

-- 两侧都有索引:SQLite 用 MULTI-INDEX OR,分别查再合并
SELECT COUNT(*) FROM orders
WHERE user_id = 'U000001' OR order_id = 'O24060100260';
user_id = 'U000001' OR city = '北京'
  SCAN orders
  2,441 行

user_id = 'U000001' OR order_id = 'O24060100260'
  MULTI-INDEX OR
  SEARCH orders USING INDEX idx_orders_user (user_id=?)
  SEARCH orders USING sqlite_autoindex_orders_1 (order_id=?)
  11 行

OR 不是天然失效:只要每个分支都能走索引,优化器可以用 MULTI-INDEX OR 分别查再求并集。 只要有一侧没索引,整个条件就可能退化为全表扫描。修法有两种:给缺索引的列补索引,或拆成 UNION ALL 并确保每个分支都能走索引。同一列上的多个 OR,直接改成 IN 更清楚。

3

四种失效写法,一个共同点:索引列没有“裸奔”

列被函数包住、类型对不上、通配符放在前面、OR 的一侧没索引——本质都是让索引列无法直接和有序键比较。 写 SQL 时养成一个习惯:先看 WHERE 里的索引列有没有被包住,再看类型和通配符。

17.1.4大表查询五条纪律

下面五条是写查询时的默认动作,每条都能在本章验证。

#纪律为什么
1先过滤,再关联/聚合WHERE 推到最内层,先把 20,000 行降到 881,再 JOIN。
2只取需要的列SELECT * 放弃覆盖索引,还把大字段读进内存。
3索引列保持“裸”日期用 >= ... AND < ...,不要用 strftime/substr;字符串比较两边同类型。
4小表驱动大表先缩小驱动集,再走被驱动表索引;注意 1:N 翻倍。
5上线前看执行计划用真实数据量跑 EXPLAIN QUERY PLAN,确认没有大表 SCAN、没有意外的 TEMP B-TREE。
4

大表五条纪律:先少、再裸、后看计划

先把行数变少(过滤),再把列变少(只取需要的),让索引列保持裸奔(不套函数), 然后选对驱动表(小表驱动大表),最后用执行计划验证。顺序不能反:先写再优化,往往已经写成了全表扫描。

17.2分析:给慢 SQL 做诊断

下面这段 SQL 能跑出正确结果,但在 881 行数据上要 1.4 秒。先别改,按“看计划 → 找重复 → 改写 → 对结果 → 测时间”五步走。

SELECT order_id,
       (SELECT COUNT(*) FROM orders o2
        WHERE substr(o2.order_date, 1, 7) = substr(o.order_date, 1, 7)
          AND o2.status = '已支付') AS month_orders
FROM orders o
WHERE o.status = '已支付'
  AND o.order_date LIKE '2024-06%'
ORDER BY o.order_id;

它想给 6 月每笔已支付订单附上“当月已支付订单总数”。结果没错,但每行都跑一次子查询,子查询又全扫 o2。

EXPLAIN QUERY PLAN
SCAN o USING INDEX sqlite_autoindex_orders_1
CORRELATED SCALAR SUBQUERY 1
SCAN o2

881 行,month_orders 合计 776,161
耗时约 1,393 ms

看到 CORRELATED 和 SCAN o2,问题就定位了:外层 881 行,每行都把 20,000 行订单扫一遍, 大约是 881 × 20,000 ≈ 1,760 万次行访问。数据量翻 10 倍,耗时会翻 100 倍,因为它是乘法关系。

改写的思路是“先把每月的总数算好,再贴回每一行”。窗口函数正好做这件事: COUNT(*) OVER (PARTITION BY 月份) 只扫一遍订单表,按月份分区计数,再把结果广播给分区内的每一行。

SELECT order_id,
       COUNT(*) OVER (PARTITION BY substr(order_date, 1, 7)) AS month_orders
FROM orders
WHERE status = '已支付'
  AND order_date LIKE '2024-06%';
EXPLAIN QUERY PLAN
CO-ROUTINE (subquery-2)
SCAN orders
USE TEMP B-TREE FOR ORDER BY
SCAN (subquery-2)

881 行,month_orders 合计 776,161(逐行一致)
耗时约 1.9 ms
慢查询:相关子查询

881 行 × 20,000 行
≈ 1,760 万次访问
约 1,393 ms

每行跑一次子查询,外层行数 × 内层表大小,平方级。

改写:窗口函数

扫 20,000 行一次
按月份排序计数
约 1.9 ms

只扫一遍,线性增长;TEMP B-TREE 是分区排序的开销。

改写不是魔法:窗口函数仍然对每行调用了 substr(order_date,1,7),只是不再重复扫表。 如果表有 2,000 万行,下一步应该是加一个“月份”生成列或按月预聚合,而不是继续在函数上硬扛。

17.3实验:8 步看清索引

8 步都在 qingning.db 上完成,顺序不能乱。耗时是本机 100 次中位数,你的机器可能不同,看倍数即可。

已完成 0 / 8 预计 60 分钟
01

先看一张表的“默认计划”

5 分钟 · 对应 17.1.2
做什么
确认 EXPLAIN QUERY PLAN 的输出格式,知道 SCAN 长什么样。
为什么
后面所有对比都要先有一个“全表扫描”的基准。没有基准,你不知道 SEARCH 到底省了多少。
怎么做
  1. 打开 DB Browser for SQLite,选中 qingning.db
  2. 在“执行 SQL”里输入下面的语句
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE status IS NOT NULL;
看到什么
SCAN orders。20,000 行全部读一遍;status 没有索引,只能逐行判断。
02

列上套函数:计划从 SEARCH 退化成 SCAN

8 分钟 · 对应 17.1.3 第一种
做什么
对比 strftime 和范围条件,确认两者结果相同、计划不同。
为什么
这是最常见的“索引失效”:不是没建索引,而是索引列被函数包住,B+ 树的有序键用不上。
怎么做
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE strftime('%Y', order_date) = '2024';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
看到什么
第一条 SCAN ... USING COVERING INDEX idx_orders_date,3.37 ms;第二条 SEARCH ... (order_date>? AND order_date<?),0.21 ms。两者都返回 6,725。
为什么第一条也写了 USING INDEX

因为它扫的是索引页而不是表页(覆盖索引),但仍然是整棵索引从头扫到尾,不是按范围定位。看到 SCAN 就当全扫处理,别被后面的 USING INDEX 骗了。

03

LIKE、GLOB、范围:三种写法的计划

8 分钟 · 对应 17.1.3 第三种
做什么
对比 LIKE '2024%'、GLOB '2024*' 和范围条件。
为什么
很多教程说“前缀 LIKE 能走索引”,这句话在 SQLite 默认配置下不成立。必须自己看计划。
怎么做
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_date LIKE '2024%';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_date GLOB '2024*';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
看到什么
LIKE 是 SCAN(0.67 ms),GLOB 和范围都是 SEARCH(0.21 ms)。LIKE 比 strftime 快 5 倍,但还没走索引。
怎么让 LIKE 也走索引

先执行 PRAGMA case_sensitive_like = ON;,再跑 LIKE '2024%',计划会变成 SEARCH。这个设置只对当前连接有效,重连就恢复默认。生产环境更稳的做法是用范围条件或 GLOB。

04

前导 %:先看有没有索引,再看通配符位置

8 分钟 · 对应 17.1.3 第三种
做什么
在 products.product_name 和有索引的 orders.order_id 上各做一次前缀/中缀对比。
为什么
“前导 % 导致索引失效”有一个前提:这一列本来有索引。没有索引时,两种写法都扫,别把锅甩给通配符。
怎么做
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM products WHERE product_name LIKE '手机%';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM products WHERE product_name LIKE '%手机%';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_id GLOB 'O240601*';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_id GLOB '*00001';
看到什么
前两条都是 SCAN products,但结果一个 0 行、一个 30 行;后两条一个 SEARCH(48 行)、一个 SCAN(1 行)。
为什么 '手机%' 是 0 行

商品名以品牌开头,不是“手机”。前缀匹配只匹配开头,所以返回 0;改通配符前先确认语义没变。

05

OR:两侧都有索引才是“合并”,否则是全扫

6 分钟 · 对应 17.1.3 第四种
做什么
对比“一侧无索引”和“两侧有索引”的 OR。
为什么
OR 不是天然慢;慢是因为优化器没法把没有索引的那一侧变成一次查找。
怎么做
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE user_id = 'U000001' OR city = '北京';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE user_id = 'U000001' OR order_id = 'O24060100260';
看到什么
第一条 SCAN orders,2,441 行;第二条 MULTI-INDEX OR,两个 SEARCH,11 行。
改写建议

给 city 补索引后,第一条也会变成 MULTI-INDEX OR;不能加索引就拆 UNION ALL,且保证每支都能走索引。

06

联合索引:亲手验证最左前缀

10 分钟 · 对应 17.1.1
做什么
建一个 (city, channel) 的联合索引,测试三种 WHERE,最后删掉索引。
为什么
最左前缀是背不会的,必须亲眼看一次:为什么单独查 channel 用不上,单独查 city 却可以。
怎么做
CREATE INDEX idx_orders_city_channel ON orders(city, channel);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE city = '杭州';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders
WHERE city = '杭州' AND channel = '信息流';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE channel = '信息流';

DROP INDEX idx_orders_city_channel;
看到什么
city 单独:SEARCH ... (city=?),2,588 行;city+channel:SEARCH ... (city=? AND channel=?),786 行;channel 单独:SCAN ... USING COVERING INDEX,6,016 行。
为什么 channel 单独是 SCAN 而不是 SEARCH

联合索引先按 city 排序,city 相同的才按 channel 排序。跨城市看 channel,值是无序的,没法二分定位,只能扫整个索引;好在覆盖索引不用回表。

07

把相关子查询改成窗口函数

10 分钟 · 对应 17.2
做什么
复现 17.2 的慢查询,改写,确认结果一致、耗时下降。
为什么
性能问题先看计划里的 CORRELATED 和大表 SCAN;这两个词同时出现,基本就是“外层每行扫一次内层”。
怎么做
  1. 先跑 17.2 的慢查询,记下行数和 month_orders 合计
  2. 再跑窗口函数版本
  3. 两边都按 order_id 排序,逐行比对
看到什么
都是 881 行,合计 776,161;慢查询约 1,393 ms,窗口版约 1.9 ms,约 700 倍。
为什么窗口版还有 TEMP B-TREE

PARTITION BY 需要按分区键排序,TEMP B-TREE 就是这个开销,O(n log n) 只排一次;慢查询是 O(n×m)。

08

Code Review:给别人的 SQL 找 5 个问题

10 分钟 · 对应 17.1.3、17.1.4
做什么
下面这段 SQL 是“能跑”的典型:结果有 33 行,看着像那么回事。找出 5 个问题,再改写。
为什么
Code Review 不是挑语法,是挑口径:状态、时间、指标、维度,这四样错一个,数就白算了。
怎么做
SELECT u.city, u.channel,
       COUNT(*) AS user_cnt,
       ROUND(SUM(o.pay_amount), 2) AS gmv,
       ROUND(AVG(o.pay_amount), 2) AS aov
FROM orders o
LEFT JOIN users u ON u.user_id = o.user_id
WHERE o.status != '已取消'
  AND strftime('%Y', o.order_date) = '2024'
GROUP BY 1, 2
ORDER BY gmv DESC;

五个问题:

  1. 状态口径:!= '已取消' 把待支付、已退款也算进 GMV。
  2. 时间写法:strftime 包住索引列,计划是 SCAN orders。
  3. 用户数:COUNT(*) 数的是订单行,不是用户。
  4. 分组写法:GROUP BY 1, 2 依赖 SELECT 的列顺序,加一列就会错位;应写完整列名。
  5. 维度口径:u.city/u.channel 是用户属性,不是订单的收货城市/下单渠道;游客订单会落进 NULL 组。
看到什么
原 SQL:33 行,GMV 合计 61,768,024.41;改写后:32 行,合计 53,577,010.96,差 8,191,013.45(正好是退款 4,028,021.57 + 待支付 4,162,991.88)。
改写后的 SQL
SELECT o.city, o.channel,
       COUNT(DISTINCT o.user_id) AS buyer_cnt,
       ROUND(SUM(o.pay_amount), 2) AS gmv,
       ROUND(SUM(o.pay_amount) / COUNT(*), 2) AS aov
FROM orders o
WHERE o.status = '已支付'
  AND o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01'
GROUP BY o.city, o.channel
ORDER BY gmv DESC;

改写后第一名是杭州自然搜索 2,852,824.44;原 SQL 第一名是西安自然搜索 3,456,049.66。同一份数据,排名完全变了。

17.4验证:数值核对 + 5 道选择题

先填三个数,确认口径没混,再做题。

范围:2024-01-01 至 2024-12-31。
用 pay_amount,不要用 amount。
这是对账用的“原价”,不是 GMV。

17.4.1五道选择题

Q1下面哪种写法最可能用上 idx_orders_date?
解析:只有范围条件让索引列保持“裸”的状态,能直接和 B+ 树里的有序键比较。其余三种都在列上套了函数,计划会退化成 SCAN。
Q2SQLite 默认配置下,LIKE '2024%' 为什么可能不走索引?
解析:order_date 有 idx_orders_date,但 SQLite 默认的 LIKE 是大小写不敏感的,优化器不会把它转成范围。用 GLOB,或 PRAGMA case_sensitive_like=ON,才会变成 SEARCH。
Q3EXPLAIN QUERY PLAN 输出 SCAN orders,说明什么?
解析:SCAN 是扫,SEARCH 是找。SCAN 后面即使跟了 USING INDEX,也只是扫索引页,不是按范围定位。
Q4联合索引 (city, channel),下面哪句用不上“查找”(只能扫)?
解析:最左前缀:city 是第一列,能定位;city+channel 两列都能用;只给 channel 跳过第一列,只能全索引扫描。D 仍然能用 city 定位,再在结果里过滤 channel。
Q5Code Review 里 COUNT(*) AS user_cnt 的问题是什么?
解析:一个用户可能有多笔订单,COUNT(*) 数的是 JOIN 后的行数。要数买家,用 COUNT(DISTINCT o.user_id)。西安自然搜索这一格,COUNT(*) 是 329,去重后是 251。
得分:0 / 5

17.5交付规范与对账四步

数跑出来只是半成品。能交付的取数,必须让别人知道“这是什么口径、怎么验过、错了找谁”。

17.5.1取数需求文档模板

接到需求先别写 SQL,先把下面这张表填完。填不出来的格子,就是你要回去问业务的问题。

字段要写什么反例
需求名称一句话说清交付物,如“2024 年 6 月分城市分渠道 GMV”“拉个数”
业务背景这个数用来做什么决策“领导要看”
指标定义GMV = 已支付订单的 pay_amount 之和;是否含退款、含运费“就是销售额”
维度城市、渠道、品类、日期,按什么粒度拆“多维度”
时间范围起止日期,含不含端点,按什么时区“最近”
过滤条件订单状态、用户类型、是否含游客“全部数据”
数据口径用哪张表、哪个字段、去重键是什么“从系统里导”
交付格式CSV / Excel / 数据库视图,列名和顺序“随便”
验收标准总数、抽样、交叉、趋势四项怎么对,截止到几号几点“差不多就行”

17.5.2对账四步:总数、抽样、交叉、趋势

以 2024 年 6 月 GMV 为例,四步都要留下可复查的数字。

第一步 · 总数:订单数、原价合计、优惠合计、实付合计四个数必须能对上公式。

SELECT COUNT(*) AS orders,
       ROUND(SUM(amount), 2) AS amount,
       ROUND(SUM(discount), 2) AS discount,
       ROUND(SUM(pay_amount), 2) AS pay_amount
FROM orders
WHERE status = '已支付'
  AND order_date >= '2024-06-01' AND order_date < '2024-07-01';
orders | amount      | discount | pay_amount
881    | 9046124.04  | 30757.40 | 9015366.64

9,046,124.04 − 30,757.40 = 9,015,366.64,总数这一关通过。

第二步 · 抽样:随机抽 5 单,逐单检查 amount − discount = pay_amount。

order_idamountdiscountpay_amount校验
O2406010026015,359.920.0015,359.92通过
O2406010040415,922.9820.0015,902.98通过
O240601007393,743.500.003,743.50通过
O240601012442,985.2710.002,975.27通过
O2406010264211,714.5620.0011,694.56通过

第三步 · 交叉:用另一条路径算同一个数。订单表的 amount 合计,应该等于订单明细表的 amount 合计。

SELECT ROUND(SUM(i.amount), 2) AS items_amount
FROM order_items i
WHERE i.order_id IN (
  SELECT order_id FROM orders
  WHERE status = '已支付'
    AND order_date >= '2024-06-01' AND order_date < '2024-07-01'
);
items_amount
9046124.04

两条路径都是 9,046,124.04,差额 0.00。交叉这一关通过。

第四步 · 趋势:把 6 月拆到日,确认没有某一天突然归零或暴涨。

指标数值判断
日均 GMV300,512.22与 5 月日均 282,752.92 比,+6.28%
最高单日525,525.32不超过日均的 2 倍,正常
最低单日149,886.68约为日均的一半,正常波动
总 GMV 环比+2.85%与日均环比方向一致
步骤问什么过不了怎么办
总数行数、金额、优惠、实付能对上公式吗?查状态过滤、日期边界、NULL
抽样随机 5–10 单,逐单算得出来吗?查字段含义、单位、精度
交叉换一条路径,结果一样吗?查粒度翻倍、JOIN 条件
趋势按日/周拆开,有异常尖峰或断点吗?查缺失日期、补零、大促
图 17.5.1 对账四步:从总数到明细,再到另一条路径和时间趋势
只发一个结果文件

对方不知道口径、没法复现,出问题只能回来问你。

交付四件套

可执行的 .sql 文件 + 结果文件 + 口径说明 + 对账记录。

17.6常见问题排查

按“现象 → 原因 → 解决”整理,调优和对账都适用。

现象原因解决
EXPLAIN 里还是 SCAN索引列被函数包住、类型不一致、前导 %把列“解放”出来,用范围/同类型字面量/前缀
建了索引还是慢不满足最左前缀,或索引选择性太低按查询条件调整联合索引顺序;先过滤再关联
LIKE 前缀也不走索引SQLite 默认 LIKE 大小写不敏感用 GLOB,或 PRAGMA case_sensitive_like=ON
OR 查询突然变慢OR 一侧的列没有索引补索引,或拆 UNION ALL 并保证每支走索引
结果行数突然变多JOIN 的 1:N 关系把订单按商品行翻倍先聚合到同一粒度,再 JOIN;核对行数沙盘
排序/分组很慢计划里出现 USE TEMP B-TREE给 ORDER BY/GROUP BY 列建索引,或先减少行数
对账差几毛浮点精度、四舍五入位置不一致统一在最后一步 ROUND;金额可用分存储
对账差几万状态口径(含退款/待支付)或 amount/pay_amount 混用回到 17.5 第一步,四个数逐个对

17.7本章小结与练习

  1. 索引是排序目录。单列看选择性,联合看最左前缀;覆盖索引可以少一次回表。
  2. SQLite 看 EXPLAIN QUERY PLAN。先看 SCAN/SEARCH,再看索引名,最后看 TEMP B-TREE。
  3. 四种失效写法:列上函数、隐式转换、前导 %、OR 一侧无索引。共同点都是索引列没能“裸奔”。
  4. 慢查询先找乘法。相关子查询 + 大表 SCAN 是平方级;窗口函数把它降成线性。
  5. 交付靠对账。总数、抽样、交叉、趋势,四步留数字。

17.7.1练习(4 道)

  1. 把 WHERE substr(order_date,1,7)='2024-06' 改成能走 idx_orders_date 的写法,写出两条 SQL 的 EXPLAIN QUERY PLAN 差异。
  2. 给 orders(status, order_date) 建联合索引,判断下面三句哪些能“查找”、哪些只能“扫”:① 只按 status;② status + 日期范围;③ 只按日期范围。
  3. 用两条路径算 2024-06 已支付订单的原价合计,写出对账数字;如果差 0.01,先查什么?
  4. 下面这句 SQL 有 5 个问题,逐条指出并改写:SELECT city, COUNT(*) FROM orders WHERE status != '已取消' GROUP BY city;
参考答案

1. 改成 order_date >= '2024-06-01' AND order_date < '2024-07-01'。前者 SCAN ... USING COVERING INDEX idx_orders_date,后者 SEARCH ... (order_date>? AND order_date<?)。

2. ① 能,SEARCH ... (status=?);② 能,SEARCH ... (status=? AND order_date>? AND order_date<?);③ 用不上这个联合索引,但如果有单独的 idx_orders_date,会走那条。

3. 订单表 SUM(amount) = 9,046,124.04;订单明细表 SUM(order_items.amount) 也是 9,046,124.04,差额 0.00。差 0.01 先查四舍五入位置和浮点累加顺序,再查是否有订单缺明细行。

4. ① 状态口径错(含待支付/退款);② 没按用户去重,COUNT(*) 是订单数;③ 没有时间范围;④ 没有排除游客/NULL 城市;⑤ GROUP BY city 没写清是订单城市还是用户城市。改写:SELECT o.city, COUNT(DISTINCT o.user_id) FROM orders o WHERE o.status='已支付' AND o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01' GROUP BY o.city;

下一章预告:第 18 章开始进入统计篇。SQL 负责把数取对,统计负责判断“这个差异是不是真的”。 如果本章的执行计划你还看不熟,先别急着往下走——后面每一个结论,都建立在“数是对的”之上。
数据分析师训练营 · 第十七章
SQLite 3.45 · EXPLAIN QUERY PLAN · 数字均来自 qingning.db 实跑