数据库错题集：从SQL语义到索引优化的避坑经验

去年年末整理资料时我翻出了自己那本数据库错题集。上面密密麻麻记录着各种“低级错误”比如“为什么COUNT(字段)查出来的数对不上”“为什么明明有索引却还是慢查询”。当初每一条都让我懊恼现在回看却发现这些错题其实都是同一个东西我对数据库某个规则的理解存在盲区。也正是这本错题集帮我在后来的课程考核和一次模拟系统开发里少走了很多弯路。如果你也在学数据库、准备考试或面试或者正在被SQL、事务、索引这些概念折磨我希望这篇基于我真实错题经验的整理能让你少踩几个我已经踩过的坑。1. 我整理数据库错题集的方法和大多数人不太一样1.1 错题不是“错题”是“规则盲区”刚开始整理错题时我的做法很简单把题目和正确答案抄一遍旁边写个“粗心了”。结果到了下次测试同样的知识点换个马甲我又错了。后来我才明白如果只是“背答案”那错题集就变成了抄题本除了浪费时间没有任何价值。正确的做法是把每道错题转化成一条“我曾误解的规则”。比如我错过的这道题SELECT dept_id, COUNT(*) FROM emp WHERE COUNT(*) 3 GROUP BY dept_id。当初我抄答案时只记得“WHERE里不能用聚合函数”没有追问为什么。后来我写下的是“WHERE在分组之前执行无法访问分组后的聚合结果如果要对聚合结果过滤必须用HAVING。”这样一来错题就从一道题变成了一条可迁移的规则遇到类似题目都能用上。1.2 我会把错题分成四类随着错题越积越多我按主题做了分类。整理下来发现绝大部分数据库错题逃不出四个方向分类典型特征我常犯的错误SQL语义类查询结果与预期不一致把筛选条件放错位置、忽略NULL事务并发类隔离级别、锁问题靠死记硬背不理解快照与当前读索引优化类慢查询、索引失效以为建了索引就一定生效设计范式类表结构设计不合理盲目反范式或过度规范化这样分类的最大好处是复习时不用再逐条看题目而是针对每个类别集中补弱点。比如有一段时间我频繁在索引类错题上翻车我就专门把索引相关的所有错题放在一起做横向对比很快发现“最左前缀”才是我真正的难点而不是“创建索引”本身。2. 高频SQL错题不是语法不会是语义没吃透2.1 WHERE和HAVING我经常把它们当成同一种筛选这是我在SQL错题里最常出现的翻车点。看下面三个查询-- 错误写法WHERE里用聚合函数 SELECT dept_id, COUNT(*) FROM emp WHERE COUNT(*) 3 GROUP BY dept_id; -- 正确写法先过滤行再分组最后对组过滤 SELECT dept_id, COUNT(*) FROM emp WHERE salary 5000 GROUP BY dept_id HAVING COUNT(*) 3;很多初学者包括我第一眼会觉得两条SQL差不多只是写法不同。实际上执行顺序完全不一样数据库先读表逐行判断salary 5000把符合条件的行组起来再对每组统计数量并判断COUNT(*) 3。如果顺序错乱就会造成“想过滤组却过滤不了行”的尴尬。我后来用一个生活类比记住了两者的区别去聚餐结账时WHERE是点菜前“我不吃香菜所有菜都去掉香菜”HAVING是结账后“总价超过100元的账单才需要报销”。一个在分组前处理行一个在分组后处理组边界非常清晰。另一个容易忽略的点是SELECT中出现的非聚合列在标准SQL里必须出现在GROUP BY中这也是错题的高频出处。2.2 COUNT、AVG与NULL的相爱相杀有一道让我印象深刻的错题是这样的统计一张学生成绩表中“数学成绩的平均分”我写的是SELECT AVG(score) FROM student_score WHERE subject math;看起来没毛病。但结果比手工算出来的低因为考试缺考的学生的score字段存的是NULL而AVG会忽略NULL分母却是参考人数。如果需求是“把缺考按0分计入平均”正确写法就变成了SELECT AVG(COALESCE(score, 0)) FROM student_score WHERE subject math;类似的坑在COUNT上更常见。COUNT(*)统计所有行COUNT(1)等价于COUNT(*)但COUNT(score)只统计score不为NULL的行。我曾在一次模拟统计中用COUNT(score)统计已考试人数结果发现少了缺考学生才想起这个规则。一定要记住写聚合函数前先问自己“字段里有没有NULL要不要忽略NULL”这个意识比背函数定义重要得多。2.3 子查询和JOIN的等价与不等价很多教程说“能用子查询的地方都能改成JOIN”这话大体没错但有几个细节会在笔试中坑人。最经典的是NOT IN与NULL的组合。我错过的题SELECT * FROM emp WHERE dept_id NOT IN (SELECT dept_id FROM dept);如果子查询返回的dept_id列表里有任何一个NULL整个查询结果会变成空集。原因很简单NOT IN本质是“不等于列表中的每一个”而NULL代表“未知”x NULL的结果是“未知”不是TRUE所以所有行都被过滤掉了。正确做法有两种要么在子查询里加WHERE dept_id IS NOT NULL要么改用NOT EXISTS关联子查询。另一个常见错题是子查询返回多行却用了。比如WHERE salary (SELECT MAX(salary) FROM emp GROUP BY dept_id)这条语句在大多数数据库中直接报错因为右子是多行结果。除非明确知道只返回一行否则多行比较应该用IN、ANY或ALL。理解这一点后我会在做题前先判断子查询可能的行数而不是死记写法。3. 事务与隔离级别考试经常出实操更容易翻车3.1 脏读、不可重复读、幻读我一直分不清这三个术语我背了无数遍但直到自己做并发实验才彻底分清。简单说读未提交能读到其他事务未提交的数据这就是脏读。比如A改了一笔订单金额但还没提交B已经看到新金额A又回滚了B读到的就是脏数据。读已提交只读已提交数据解决脏读但可能出现不可重复读。即同一个事务内两次SELECT同一行结果却不同因为期间有其他事务提交了修改。可重复读保证事务内多次读取同一行结果一致解决不可重复读但仍可能出现幻读。幻读是指同一事务内两次查询的“记录集合”数量或结构发生了变化比如第一次查出10行第二次查出11行。我用一个“查账”的例子帮助自己记忆读未提交是“人家笔还没写完你就看了”读已提交是“只有签字盖章的文件你才认”可重复读是“你锁定了自己看到的账目数字别人改你也看不见”串行化则是“所有查账的人都排队一个查完另一个才能动账本”。这样理解之后四道题背后的逻辑就通了。3.2 一次模拟并发场景让我真正理解了快照读和当前读隔离级别并不是一个简单的开关。我记得自己在本地数据库里做过一个实验开了两个终端连接同一个库模拟两个事务事务A执行BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; -- 不提交事务B执行BEGIN; SELECT balance FROM account WHERE id 1;在默认的可重复读隔离级别下B查到的仍是旧值这是因为普通SELECT走的是快照读读取的是事务开始时的快照不会看到A未提交的修改。但如果B执行的是当前读比如SELECT balance FROM account WHERE id 1 FOR UPDATE;这条语句会阻塞因为FOR UPDATE会对记录加锁A持有该行的锁B只能等待。所有“读到旧值”和“等待锁”的现象都源于普通查询是快照读而加锁语句是当前读。这个实验让我彻底明白背出隔离级别只能得分理解锁和快照才能解决真实问题。3.3 死锁的经典错题加锁顺序不一致有一次我在模拟一个库存扣减系统时写了两条更新语句顺序一个从上到下一个从下到上结果在并发测试中出现了死锁。反向过账的经典过程是事务1先锁A再锁B事务2先锁B再锁A双方都持有一把锁并等待对方的锁谁也不会先释放。解决死锁的办法我在错题集里记了三条第一多个事务访问多个表或多行时尽量约定同一个加锁顺序第二把事务尽量缩短减少持锁时间比如不要在事务中途做耗时操作第三确保更新条件走索引否则行锁可能升级为表锁并发度会断崖式下降。后来我在一次代码评审里看到同事用UPDATE时条件列没索引表一更新并发全部串行化这就是“索引影响锁粒度”的典型例子。4. 索引与执行计划错题集里最有含金量的一部分4.1 最左前缀法则我背了十年还是用错联合索引是最容易被“想当然”的地方。很多人知道“最左前缀”但真正做题时还是会写出无法走索引的查询。假设有一个联合索引idx(a, b, c)下面这些场景我全部错做过查询条件是否能有效利用索引说明WHERE b 1否没有从最左列a开始索引失效WHERE a 1 AND c 2部分能用到a但c因为中间缺少b无法完全利用WHERE a 1 AND b 100 AND c 2部分范围查询右侧的列c无法继续使用索引排序或定位WHERE a 1 AND b 2 ORDER BY c是可以避免额外排序Extra中会出现Using index condition等这些规律不是靠背而是要理解联合索引的底层结构它先把a列排好再在a相同的前提下排b再排c就像一本先按姓氏、再按名字、再按中间字排序的电话簿。你跳过了“姓”直接查“名”自然没法快速定位。我在错题集里专门画了一张“跳列失效”的示意图之后再也没有犯过“跳过中间列去按第三列查询”的错。4.2 失效场景远不止函数计算只要提到索引失效很多人第一反应是“左边加函数、做运算”。这类确实是经典错题比如WHERE YEAR(create_time) 2025会让create_time上的索引失效正确写法应该是WHERE create_time BETWEEN 2025-01-01 AND 2025-12-31。但我在实际项目里发现更隐蔽的是隐式类型转换。我做过一张用户表手机号字段定义为varchar查询时用了WHERE phone 13800001111没有加引号。MySQL里这个比较会把字符串列转换为数字相当于在索引列上用了CAST(phone AS UNSIGNED)索引整列失效全表扫描。后来我改成了WHERE phone 13800001111查询时间从几百毫秒降到几十毫秒。这个坑让我明白判断索引是否失效不能只看有没有函数还要看“优化器有没有隐式地给列套上函数”。4.3 从一次慢查询看执行计划有段时间我负责维护一个模拟订单查询功能SQL很简单SELECT user_name FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 AND status 1;在数据量不大的测试库里一切正常但线上数据量一大就慢得离谱。用EXPLAIN一看type是ALLExtra是Using where; Using filesort之类的提示说明没有有效索引。初步建了(create_time)索引后查询快了一点但Extra出现了Using index condition仍然需要回表读取status和user_name。更好的方案是建立覆盖索引(create_time, status, user_name)。这样查询所需列都在索引里Extra显示Using index避免了回表。这个案例让我记住了执行计划里type、key、rows、Extra四列的组合含义也让我养成了写SQL前先看执行计划的习惯。判断一次慢查询不能只问“有没有建索引”还要问“索引结构是否覆盖了查询所需的所有列”。5. 数据库设计范式理论没有错错在用错了地方5.1 判断范式时我总把依赖关系搞反范式判断是数据库理论最容易出错的题型之一。第一范式要求字段原子第二范式要求消除非主属性对候选键的部分依赖第三范式要求消除非主属性对候选键的传递依赖。我错过的经典题是设计一张“学生-学院”表student(sno, sname, college_name, college_phone)。假设college_name唯一且由sno决定同时college_phone由college_name决定那么sno - college_name - college_phone这条路径就是传递依赖所以该表不满足第三范式。如果需求是查询学院的电话最好把学院信息拆分到独立表否则更新学院电话时需要修改多行容易造成数据不一致。我在错题集里总结了一个快速判断法列出所有函数依赖找出主键再看每个非主属性与主键之间是否存在“间接关系”。如果A依赖主键B依赖A且B也非主属性那么这里多半就是传递依赖。这个方法让我的范式题正确率明显提高。5.2 反范式不是“反模式”曾经我一看到“非规范化”“冗余字段”就觉得是反面教材直到在真实项目里写报表查询时才被打脸。一个订单表每天新增几十万行要统计每个城市的订单金额和用户数。如果严格按范式拆表每次统计都需要多表关联查询时间非常不理想。后来在别人设计的表里看到了冗余的城市字段一开始我还觉得设计不行结果查询速度提升了好几个量级。必须承认在“读多写少、聚合频繁”的场景下适量冗余字段可以减少关联换取更快的响应。但前提是要管理好数据一致性比如通过应用层保证冗余字段和源字段同步更新。反范式不是“不规范化”而是有意识地用空间换时间是一种权衡而不是错误。5.3 主键设计的一个错题用业务字段做主键另一个高频错题是“是否可以用身份证号、手机号作为主键”。初学阶段我觉得这些字段唯一拿来做主键多方便。但一次设计会员系统时我遇到实际困难会员改手机号了如果手机号是主键改一次主键会造成所有关联表的外键级联更新代价极大。另一个问题是业务字段虽唯一但可能为空比如用户还没绑定手机号而主键不允许为空。后来我改成了无意义的自增主键把手机号设置为唯一索引。这个改动让我理解了主键设计的核心主键最好永远不变、无业务含义才不容易引发连锁更新。至于自增主键、UUID还是雪花ID要看场景。自增主键简单、页存储紧凑但分布式情况下容易冲突UUID全局唯一但无序可能导致页分裂雪花ID适合分布式系统兼顾趋势递增。我的建议是单机小表用自增分布式大表优先考虑雪花或其它分布式ID方案原则都是“不要用会被外部修改的业务字段作为主键”。6. 考前一晚我是怎么把错题集价值最大化的6.1 把错题当成面试官反推考点我一轮复习时会做一件很特殊的事把每条错题反向拆解问自己“这道题究竟想考什么”。比如一道关于SELECT中非聚合列没有出现在GROUP BY里的错题背后的考点其实是ONLY_FULL_GROUP_BY模式。再比如一道关于DELETE和TRUNCATE区别的错题考点是“事务日志是否记录每一行删除”。这种反推可以让我从一道题辐射出一系列知识点。操作上我会在错题集右侧留一栏专门写“考点标签”像事务隔离、联合索引最左、NULL陷阱、范式判断。复习时只看标签不看具体题目如果标签对应的内容自己能复述清楚就代表这个知识点过关了。6.2 三遍重做法第一遍重做、第二遍变式、第三遍讲给别人听错题只“看”不做等于白记。我的方法是至少过三遍每一遍的形式都不同第一遍合上答案把错题当成新题做一遍检验自己能否独立做对。第二遍自己给这道题改一个条件变成一道变式题。比如把WHERE改为HAVING、把IN改为EXISTS、把单列索引改为联合索引再判断结果会不会变。第三遍把这道题讲给周围同学听要求讲清楚“错在哪、为什么错、正确解法是什么、还有哪些类似坑”。讲一遍比自己默默背十遍效果都好。6.3 我还会整理一页“错题速查表”考前最后一天我不再看厚厚一本错题集而是看我自己整理的一页速查表。这页速查表把四类错题中最容易反复犯的规则浓缩成一张表例如错题点一句话正确认知COUNT(字段)与COUNT(*)COUNT(字段)忽略NULL明确需求后再选WHERE与HAVINGWHERE过滤行HAVING过滤组NOT IN与NULL子查询结果含NULL时NOT IN结果为空联合索引最左前缀查询条件必须从联合索引第一列开始隐式类型转换字符串列用数字比较会导致索引失效隔离级别不只背定义还要分快照读与当前读反范式读多写少、聚合频繁时冗余字段是合理取舍主键设计不用可变的业务字段做主键这张表是我整个错题集的精华也是我后来分享给学弟学妹时最受欢迎的一页。整理错题集这件事对我而言最大的收获并不是考试成绩提升了多少而是它逼着我去追问每一个“为什么”。很多知识点我原本只停留在“好像听说过”的层面因为做错题目才真正沉下去弄懂原理。如今我再看到数据库相关的概念和题目即便遇到不会的新题型也能顺着之前的分类和标签快速定位到相关知识块而不是重新从零开始。如果你也在学数据库建议你也准备一个自己的错题集每条错题都记录“我原本想的是什么、正确规则是什么、下次怎么避免”坚持一个学期你会明显感觉到自己的思路清晰了很多。