测试工程师SQL实战指南：从聚合统计到索引优化

测试工作干到一定阶段你会发现SQL已经不只是“会用”的程度而是你每天都要靠它去定位数据问题、校验业务逻辑、准备一堆测试数据甚至帮开发同事验证某个修复到底有没有生效。上篇讲了SELECT的基础查询、条件过滤、排序和LIMIT分页这篇直接上测试工作中真正高频的硬核操作聚合统计、多表关联、CASE WHEN逻辑判断、子查询以及最容易出事故的UPDATE和DELETE。每一段我都会贴出真实可跑通的SQL语句和执行结果用一套电商测试数据贯穿全文方便你直接复制到本地去验证。1. 环境准备一套可控的示例数据是后面一切操作的前提1.1 为什么测试人员要专门准备一套自己的数据很多测试新手喜欢直接连测试环境数据库在生产或公共测试库上跑各种查询查归查一旦碰到UPDATE和DELETE就很容易误伤别人的数据。我自己的习惯是本地维护一套专属的小型MySQL库表结构尽量模拟业务但数据量控制在十几条以内。这样无论是练习、验证SQL逻辑还是排查一个接口取数是否符合预期都能快速定位不依赖别人。这不是小题大做。你想想你测一个订单列表接口如果库里连一条订单都没有你怎么知道返回字段是不是正确如果库里数据乱成一片你怎么断言“状态为已完成的订单只有3条”这个结果是准确的测试的本质是“可控条件下的验证”数据越可控断言就越有底气。1.2 三张业务表的建表语句与初始化数据后面所有案例都围绕一个简单电商场景三张表用户表、商品表、订单表。我先贴建表语句。CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT, city VARCHAR(50), reg_date DATE ); CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2), category VARCHAR(50), stock INT ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT, amount DECIMAL(10,2), status VARCHAR(20), order_date DATE );初始化数据INSERT INTO users (name, age, city, reg_date) VALUES (张三, 25, 北京, 2024-01-10), (李四, 30, 上海, 2024-02-14), (王五, 28, 广州, 2024-03-20), (赵六, 35, 北京, 2024-04-05), (孙七, 22, 深圳, 2024-05-11), (周八, 27, 杭州, 2024-06-20); INSERT INTO products (product_name, price, category, stock) VALUES (笔记本电脑, 4999.00, 数码, 20), (机械键盘, 349.00, 外设, 50), (无线鼠标, 89.00, 外设, 100), (显示器, 1299.00, 数码, 30), (USB扩展坞, 159.00, 配件, 80); INSERT INTO orders (user_id, product_id, quantity, amount, status, order_date) VALUES (1, 1, 1, 4999.00, 已完成, 2024-06-01), (1, 2, 2, 698.00, 待付款, 2024-06-03), (2, 3, 1, 89.00, 已完成, 2024-06-05), (3, 4, 1, 1299.00, 已取消, 2024-06-08), (4, 2, 1, 349.00, 已完成, 2024-06-10), (5, 5, 3, 477.00, 待发货, 2024-06-12), (2, 1, 1, 4999.00, 已完成, 2024-06-15), (3, 3, 2, 178.00, 待付款, 2024-06-18);这里我故意没有给orders表建外键实际开发中很多业务表为了性能也会刻意不用外键。所以测试人员在写JOIN的时候就要格外小心一旦两张表的关联字段出现孤儿数据查出来的结果集就会和预期差很多这一点后面我会专门踩坑说明。2. 聚合统计与分组分析结果校验的核心武器2.1 COUNT、SUM、AVG、MAX、MIN不只是面试题测试里最常见的场景是什么开发说“我已经把数据清掉了”你怎么验证开发说“这个接口返回的订单总数是10”你凭什么信靠的就是聚合函数。SELECT COUNT(*) AS total_users, COUNT(DISTINCT city) AS city_count FROM users;运行结果------------------------- | total_users | city_count | ------------------------- | 6 | 6 | -------------------------这个统计看起来简单实际使用中有个很重要的细节COUNT(*)是统计行数COUNT(字段)是统计该字段非NULL的个数。如果你用COUNT(city)去统计用户数恰好有某个用户city字段为NULL那结果就会少一行。测试人员在核对总数时如果发现数字对不上第一个排查方向就是有没有NULL值在作怪。再看金额类校验。测试一个订单列表页页面上显示“已完成订单总额为10447.00”你要验证这个数字对不对直接跑SELECT COUNT(*) AS completed_count, SUM(amount) AS total_amount, ROUND(AVG(amount), 2) AS avg_amount FROM orders WHERE status 已完成;运行结果------------------------------------------- | completed_count | total_amount | avg_amount | ------------------------------------------- | 3 | 10447.00 | 3482.33 | -------------------------------------------SUM验证的是总额AVG验证的是平均值MAX和MIN通常用来做边界值验证比如查商品最高价、最低价或者查某个用户最早注册日期。跑一下SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;运行结果---------------------- | max_price | min_price | ---------------------- | 4999.00 | 89.00 | ----------------------很多测试同学拿到接口返回的统计数据反应是“开发说是这样的”然后就不管了。其实把SQL一跑自己心里就有数了。这个习惯非常重要尤其是测报表功能、数据看板、支付统计这类模块。2.2 GROUP BY和HAVING是数据分布测试的利器回归测试里我们经常要确认某个字段的不同取值分布是否符合预期。比如“订单状态有哪几种各有多少单”这就是分组统计的典型场景。SELECT status, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY status;运行结果------------------------------------- | status | order_count | total_amount | ------------------------------------- | 已完成 | 3 | 10447.00 | | 待付款 | 2 | 876.00 | | 已取消 | 1 | 1299.00 | | 待发货 | 1 | 477.00 | -------------------------------------你把这个结果跟页面上的状态筛选列表对照一下基本就能看出功能正常不正常。如果页面上显示“待付款有3单”但SQL查出来只有2单说明要么脏数据要么页面取数逻辑有bug这就是一个可提交的缺陷。HAVING是分组之后过滤数据用的和WHERE有本质区别WHERE过滤的是原始行HAVING过滤的是分组结果。举个例子我要找出下过至少2单的用户SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent FROM orders GROUP BY user_id HAVING COUNT(*) 2;运行结果----------------------------------- | user_id | order_count | total_spent | ----------------------------------- | 1 | 2 | 5697.00 | | 2 | 2 | 5088.00 | -----------------------------------这个SQL在做用户分层、复购率验证的时候非常常用。注意HAVING后面不能用SELECT里的别名做过滤吗其实MySQL允许在HAVING中使用别名但为了兼容性考虑我建议还是直接写聚合表达式或原始字段名避免在复杂查询里出现不必要的坑。2.3 聚合查询中那些一言难尽的坑这里必须专门说一下测试人员最容易被坑的三个点。第一个坑是COUNT(*)和COUNT(1)以及COUNT(字段)的结果差异。COUNT(*)和COUNT(1)几乎等价都是统计行数但COUNT(status)会跳过NULL值所在的行。如果你对orders表的status做统计恰好存在status为NULL的订单那你数出来的订单数就会偏少。测试时碰到总数不对一定要想到这个可能。第二个坑是SUM函数遇到全NULL或空表会返回NULL而不是0。很多初学者看到SUM结果是NULL以为算错了其实在MySQL中这是正常行为。所以在写接口返回值校验时需要配合IFNULL或COALESCE处理。SELECT IFNULL(SUM(amount), 0) AS total_amount FROM orders WHERE user_id 999;运行结果-------------- | total_amount | -------------- | 0.00 | --------------第三个坑是GROUP BY的分组字段如果含有NULL值所有NULL值会被分到同一组。测试场景中如果你发现分组统计结果里出现一个“没有名字”的分组不要慌去查一下是不是字段里有NULL脏数据。3. 多表连接查询跨模块验证离不开的JOIN3.1 INNER JOIN只留两边都匹配得上的数据功能测试做到后面你大概率会遇到一个情况接口返回了用户信息和订单信息你要确认这个用户到底有没有订单、订单时间对不对、订的是什么商品。单表查肯定不行这时候就要JOIN。最简单的INNER JOIN取两个表的交集SELECT u.id AS user_id, u.name, o.id AS order_id, o.status FROM users u INNER JOIN orders o ON u.id o.user_id ORDER BY u.id;运行结果------------------------------------- | user_id | name | order_id | status | ------------------------------------- | 1 | 张三 | 1 | 已完成 | | 1 | 张三 | 2 | 待付款 | | 2 | 李四 | 3 | 已完成 | | 2 | 李四 | 7 | 已完成 | | 3 | 王五 | 4 | 已取消 | | 3 | 王五 | 8 | 待付款 | | 4 | 赵六 | 5 | 已完成 | | 5 | 孙七 | 6 | 待发货 | -------------------------------------这个结果就是“有订单的用户以及他们的订单”。注意周八user_id6没有出现在结果里因为INNER JOIN只保留两边都匹配的行。测试中JOIN最常见的翻车场景是什么是忘了写关联条件。你写一句FROM users u INNER JOIN orders o不带ONMySQL不会报错它会把两张表做笛卡尔积6个用户乘8条订单返回48行。怎么看怎么不对。所以执行完JOIN查询第一件事先看一眼返回行数是不是合理范围再核对内容。3.2 LEFT JOIN专门用来查“缺失数据”测试里查“哪些用户没有下过单”“哪些订单没有对应用户”这属于异常数据校验。LEFT JOIN IS NULL是这类问题的标准解法。SELECT u.id, u.name FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.id IS NULL;运行结果------------ | id | name | ------------ | 6 | 周八 | ------------这里我用的是WHERE o.id IS NULL意思是左表users中那些在右表orders里找不到任何匹配的记录。NULL是“没有匹配”的标志判断时必须用IS NULL不能写成o.id NULL后者永远为假查不到任何结果这也是一个高频错误。LEFT JOIN测试中还有个细节如果右表有重复数据左表的行会被翻倍。比如orders表里同一个人下了3单LEFT JOIN出来这个人就会出现3行如果你只是想列出所有用户这个结果就“看起来不对”。要避免这种情况可以使用EXISTS或子查询后面会讲。3.3 关联条件里的坑ON还是WHERE结果差很多这个问题很多人学SQL的时候没想明白测试的时候却容易踩雷。区别在于ON是在JOIN的时候用于决定两个表怎么匹配的WHERE是在JOIN完成之后对结果行进行过滤。看一个对比。我要查“已完成订单的用户名称和订单金额”下面两种写法结果是一样的SELECT u.name, o.amount FROM orders o INNER JOIN users u ON o.user_id u.id WHERE o.status 已完成; SELECT u.name, o.amount FROM orders o INNER JOIN users u ON o.user_id u.id AND o.status 已完成;但换成LEFT JOIN结果就不一样了。如果把过滤条件放在ON子句里那些“没匹配上的左表行”还是会保留如果放在WHERE里这些行就会被干掉。所以当你发现LEFT JOIN查出来的结果数量比自己预想的少先检查是不是过滤条件被写到WHERE里把本应保留的NULL行过滤掉了。4. CASE WHEN与DISTINCT测试断言里的逻辑判断和数据质量4.1 用CASE WHEN给数据“打标签”接口测试中经常需要验证前端展示的文案规则是否正确。比如订单金额低于500元显示“小额订单”500到2000显示“中额订单”高于2000显示“大额订单”。这种规则用CASE WHEN在数据库里模拟一遍马上就知道规则有没有写错。SELECT id AS order_id, amount, CASE WHEN amount 500 THEN 小额订单 WHEN amount BETWEEN 500 AND 2000 THEN 中额订单 ELSE 大额订单 END AS order_level FROM orders;运行结果-------------------------------- | order_id | amount | order_level | -------------------------------- | 1 | 4999.00 | 大额订单 | | 2 | 698.00 | 中额订单 | | 3 | 89.00 | 小额订单 | | 4 | 1299.00 | 中额订单 | | 5 | 349.00 | 小额订单 | | 6 | 477.00 | 小额订单 | | 7 | 4999.00 | 大额订单 | | 8 | 178.00 | 小额订单 | --------------------------------这个SQL表面上是“按金额分档”实际开发中大量用在这种场景把数据库里的状态码翻译成业务文案、把数值字段打上业务标签。测试人员在验证规则类需求时直接跑一条CASE WHEN把结果跟开发代码里的if-else逻辑对比一下比手工造数据挨个看页面快得多。还有一种更进阶的用法是CASE WHEN配合聚合函数做条件统计。比如我想统计“已付款状态已完成待发货和未付款状态待付款各自的订单总额”一条SQL搞定SELECT SUM(CASE WHEN status IN (已完成, 待发货) THEN amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN status 待付款 THEN amount ELSE 0 END) AS unpaid_amount FROM orders;运行结果---------------------------- | paid_amount | unpaid_amount | ---------------------------- | 10924.00 | 876.00 | ----------------------------这种写法用来验证统计报表非常有效不用写代码不用写Python脚本一个SQL就能把业务规则翻译出来。4.2 DISTINCT去重与数据质量校验测试中经常要检查“这张表到底有多少种状态”“有多少个不同的城市”直接使用DISTINCTSELECT DISTINCT status FROM orders;运行结果---------- | status | ---------- | 已完成 | | 待付款 | | 已取消 | | 待发货 | ----------还有一个更常见的场景检查数据是否存在重复。用户表正常应该是ID唯一但如果你怀疑有脏数据导致重复可以这样验证SELECT name, COUNT(*) AS cnt FROM users GROUP BY name, city HAVING COUNT(*) 1;运行结果就是空集说明当前没有重复。如果查出有记录那就要考虑是不是存在相同人重复录入的问题这就是一个数据质量缺陷。我习惯在做数据迁移、清库、导入导出后跑一遍这种“重复自检SQL”非常省事。4.3 子查询JOIN之外的另一种思路JOIN和子查询经常能互相替代但测试场景里有各自的适用场景。比如我要查“下单次数大于等于2次的用户的姓名和城市”可以先用GROUP BY查出user_id再通过子查询去users表取用户信息SELECT name, city FROM users WHERE id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) 2 );运行结果---------------- | name | city | ---------------- | 张三 | 北京 | | 李四 | 上海 | ----------------这段SQL相当于先做“用户筛选”再拿筛选出的ID集合去匹配用户表。它和LEFT JOIN GROUP BY相比逻辑上更直观。还有一个常用场景是查“订单金额高于所有订单平均金额的订单”SELECT id, amount FROM orders WHERE amount (SELECT AVG(amount) FROM orders);运行结果------------- | id | amount | ------------- | 1 | 4999.00 | | 4 | 1299.00 | | 7 | 4999.00 | -------------子查询的性能在数据量小的时候感知不明显但测试环境数据量如果比较大子查询特别依赖外层和里层的索引这一点在执行计划章节会展开。5. 数据造数与清理INSERT/UPDATE/DELETE的安全实践5.1 INSERT造数从手工一行到批量生成测试最烦的就是造数据。如果每次都在Navicat里手工点着插入效率低还容易出错。SQL本身提供了非常灵活的造数方式你完全可以用一条SQL从现有表里批量生成数据。比如我想给周八id6造一单已完成订单product_id取3quantity取1INSERT INTO orders (user_id, product_id, quantity, amount, status, order_date) SELECT id, 3, 1, 89.00, 已完成, 2024-07-01 FROM users WHERE name 周八;这条SQL的妙处在于不用硬编码user_id直接从users表按名字解析出id。批量造数据时把SELECT部分换成一个范围查询或JOIN就能一次性插入几十条结构正确的模拟订单。这也是我在验收数据导入功能时常用的校验手段先用SQL造一批预期数据再跑业务接口最后对比数据是否一致。5.2 UPDATE先查后改事务兜底UPDATE是测试环境里最容易出事故的操作。很多测试新人在测试环境执行UPDATE忘记加WHERE条件一条语句把所有订单状态全改了。改完一看傻眼了。所以要养成一个习惯先SELECT查一遍确认要影响的行数再改成UPDATE。-- 先查 SELECT id, status FROM orders WHERE id 8; -- 再改 UPDATE orders SET status 已发货 WHERE id 8;运行结果UPDATE成功时Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0这里“Rows matched: 1”的意思是匹配到1行“Changed: 1”表示实际修改了1行。如果你执行UPDATE时发现Rows matched很大但Changed是0说明只是把值改成了原值MySQL会认为没有实际变化。另一个更稳的做法是把UPDATE放进事务里执行改完先查一遍确认无误再COMMITSTART TRANSACTION; UPDATE orders SET status 已发货 WHERE id 8; -- 此时手动检查一下结果 -- 如果不对执行 ROLLBACK; COMMIT;这个习惯在改复杂业务表时特别重要。测试环境没有备份的情况下一个UPDATE下去就无法反悔了。事务是测试人员保护自己的最低成本手段。5.3 DELETE、TRUNCATE、DROP怎么选DELETE是删行支持WHERE条件可以用事务回滚。TRUNCATE是清空整张表保留表结构不能回滚事务回滚也对TRUNCATE无效。DROP是连表结构一起删掉。这三者的区别面试常考实际测试中更常踩。删除指定测试数据使用DELETEDELETE FROM orders WHERE id 10;如果你只想清空orders表数据但保留表结构方便重新插入测试数据就用TRUNCATETRUNCATE TABLE orders;要注意的是TRUNCATE会重置自增ID也就是你清空后插入的第一条订单ID会从1开始。如果你想保留自增位置用DELETE FROM orders不加WHERE反而更合适。这个差异在测试里实际会影响数据预期比如你清空后重新造数接口返回的ID和预期对不上可能不是bug而是TRUNCATE重置了自增。最危险的是DROP一条语句下去表和所有数据都没了。测试环境可以这么操作但如果连到错误的库后果就是灾难。我的建议是执行DROP前先用SHOW TABLES确认当前所在库再确认表名拼写最后再执行。6. EXPLAIN执行计划测试环境慢查询的初级定位思路6.1 EXPLAIN到底能看出什么测试人员在功能测试阶段一般不会关注SQL性能但如果你的项目里有列表查询超时、接口响应很慢这类问题这时候EXPLAIN就是分析SQL的第一步。EXPLAIN SELECT * FROM orders WHERE user_id 2;运行结果------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 10.00 | Using where | ------------------------------------------------------------------------------------------------------------对于测试人员来说最值得关注的是两个字段type和rows。type表示访问类型从好到差大概是system、const、eq_ref、ref、range、index、ALL。上面这个查询typeALL说明全表扫描rows8表示扫描了8行。这个数据量不大所以无所谓但如果表里有几十万条订单ALL就会非常慢。6.2 怎么判断要不要索引看到ALL不代表一定要加索引还要看表的数据量和查询频率。如果表只有几十行全表扫描和走索引几乎没有差别如果表是百万级每次查询都扫全表接口必然会慢。给orders表的user_id字段加上索引后再看EXPLAINCREATE INDEX idx_user_id ON orders(user_id); EXPLAIN SELECT * FROM orders WHERE user_id 2;运行结果-------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | ref | idx_user_id | idx_user_id | 4 | const | 2 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------此时type从ALL变成了refrows从8降为2说明MySQL可以通过索引快速定位到user_id2的只有两行。测试人员不需要自己建索引去动表结构但看到这样一个对比就能理解开发为什么要给某些字段加索引也能在测试数据量大的时候判断一个慢查询到底是不是因为缺少索引。6.3 一个典型的慢查询定位过程举个实际场景。测试环境订单表有几十万条数据接口“查询用户历史订单”很慢页面要转好几秒。开发说本地没问题环境差异。这时候我可以直接在数据库里执行对应的SQL并加上EXPLAINEXPLAIN SELECT o.id, o.amount, o.status, o.order_date FROM orders o INNER JOIN users u ON o.user_id u.id WHERE u.phone 13800138000;注意这里我用了一个不存在的字段phone来模拟业务查询实际情况中业务方通常用一个唯一标识去查用户然后拿user_id去关联订单表。如果users表的phone字段没有索引EXPLAIN大概率也是ALL即使users表查到了用户orders表如果没有user_id索引依然会全表扫描。我们当时定位到问题就是orders表缺少user_id索引加索引之后查询从几百毫秒降到了个位数毫秒。我不打算在这里展开索引优化等DBA才需要掌握的内容只是想告诉你测试人员不需要成为数据库专家但看到EXPLAIN里的rows从大变小能看到加了索引和没加索引的差别你就能更准确地判断性能问题是不是出在SQL层面。说回这些SQL本身。测试工作里SQL不是一个“会不会写”的问题而是“写得对不对、查得全不全、改得安不安全”的问题。我见过太多测试同学在面试里能把LEFT JOIN和INNER JOIN的区别背得滚瓜烂熟一到实际工作中查个数据还要开三个窗口反复试。其实功夫就在平时自己搭一套数据把上面这些场景全部跑一遍跑完顺手把报错也记下来。等你在测试环境真的碰上脏数据、慢查询、重复数据的时候脑子里对这些SQL已经有肌肉记忆了处理起来自然比临时查文档快得多。我自己的习惯是把常用的这些SQL语句整理成一个sql文件按场景分类比如“造数”、“数据校验”、“数据清理”、“重复检查”、“性能定位”。每次新项目开始先根据业务表结构调整一遍整个测试周期都在用。节省下来的时间远比当时整理花掉的两三个小时划算。你如果还在靠手工点界面造数、靠肉眼核对数据不妨从这套环境开始把SQL用起来效率会明显不一样。