我在带新人入门的时候最常听到的一句话是“数据库到底是个啥感觉就是个存数据的软件呗。”这话没毛病但只对了一半。如果只是存数据Excel也能干为啥要搞出个专门的“王国”出来因为数据库解决的从来不是“怎么存”的问题而是“怎么存得稳、查得快、改得准、挂了还能恢复”的问题。今天这篇我就用“数据王国”这个比喻把数据库底层的运作逻辑掰开揉碎讲一遍再配5道我筛选过的经典题帮你把脑子里那些零散概念串成一条线。这篇文章不挑基础哪怕你完全没写过SQL也能看懂如果你已经会用增删改查了重点看后半部分的题目解析和避坑心得那里面有不少是我在实际项目里踩出来的经验。数据库这个东西难起来可以研究一辈子但入门的时候只要把“表、关系、约束、索引、事务”这几个核心概念吃透你就已经甩开绝大多数“只会背命令”的人了。1. 由表入道数据王国的地基是“二维表”不是“数据库软件”1.1 为什么是“表”而不是“文件柜”先想一个问题你手里有一堆客户信息、订单信息、商品信息最原始的做法是拿个本子记或者塞进Excel。Excel和数据库的表看起来都是“行和列”但本质上有个天大的区别——Excel是给“人”看的数据库的表是给“机器”和“规则”看的。机器看数据讲究的是“严格”。每一列存什么类型长度多少能不能为空有没有默认值这些都在建表的时候定死了。你在Excel里可以随手在一个数字列里敲个“abc”数据库里这么干直接被拒绝执行。这不是死板这叫“约束”。约束保证了数据的质量数据质量是整个数据王国的命根子。在数据库的世界里一张表的结构被称作“关系模式”表本身就是“关系”。你把一张表想象成一个王国的户籍本每个公民行都有唯一的身份证号主键每项登记内容列都有明确规范。没有规矩不成方圆没有主键的表就是黑户遍地查数据、改数据、删数据都容易乱套。1.2 主键数据王国的身份证主键Primary Key这块我得多说两句。它是每张表里最核心的字段作用就是唯一标识一行记录。选主键有两条铁律第一不能为空第二不能重复。你可能会想“那我用身份证号当主键不就行了吗”行但如果你的业务里没有天然唯一字段呢比如一张订单表没有订单号只能靠“用户ID下单时间”来拼。这种情况我建议你用自增ID或者一串随机生成的唯一编号。我在实际项目里见过有人用“姓名手机号”做主键结果两个同事的手机号录错了撞了主键整个表写不进去数据排查半天才发现是脏数据问题。记住主键最好用无业务含义的纯ID业务字段随时可能变变了主键就崩了。1.3 字段类型的坑别把什么都存成字符串建表的时候字段类型的选型是新手最爱忽略的。我见过最典型的操作是把手机号存成INT结果号码显示成“138****”后面被抹位其实不是显示问题是数值溢出后被自动转了。还有把日期存成字符串然后查“最近7天下单用户”的时候用字符串比较大小格式一不统一就漏数据。常用的字段类型几句话讲明白INT/BIGINT整数类型BIGINT比INT范围大得多自增主键建议直接BIGINT别省。DECIMAL(M,D)定点小数存钱用这个绝对不要用FLOAT/DOUBLE浮点数有精度丢失账算不平。VARCHAR(N)变长字符串存姓名、地址这种长度不固定的内容。CHAR(N)定长字符串存身份证号、手机号这种固定长度的性能比VARCHAR好一点。DATETIME/TIMESTAMP存时间TIMESTAMP有2038年问题能用DATETIME尽量用DATETIME。TEXT/BLOB存大文本和二进制能用文件路径代替就别直接往库里塞大文件库会变得又肥又慢。这里补一个很多新手不知道的细节VARCHAR括号里的N是“字符数”而不是“字节数”。如果库的字符集是UTF-8一个汉字占3个字节VARCHAR(10)能存下10个汉字但如果你用VARCHAR(10)去存一个包含数字字母混合的字符串长度上限是10个字符不是10个字节。很多报错“Data too long for column”就是这么来的。2. 数据王国的通用语言SQL的四大基石操作2.1 建库建表先规划后动手学习阶段没关系随便建。工作里建表之前一定要先规划清楚这张表要存什么谁要读谁要写大概多大的数据量这些想清楚了再写CREATE TABLE否则后面改表结构极度痛苦尤其是表里有数据了之后ALTER TABLE在大表上可能直接锁表业务就停了。我建议每个入门的人练习的时候建一个“学生管理系统”的库三个表学生表、课程表、选课表。这个场景足够经典把三个表建出来你就理解了什么叫“实体”和“关系”。一份基本的学生表创建语句CREATE TABLE student ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 学生ID, name VARCHAR(50) NOT NULL COMMENT 学生姓名, gender CHAR(1) DEFAULT 男 COMMENT 性别, birth_date DATE COMMENT 出生日期, phone VARCHAR(20) UNIQUE COMMENT 手机号, enrollment_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入学时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;这里有几个点值得研究一下AUTO_INCREMENT是自增不需要你手动传IDNOT NULL和DEFAULT一起用既保证字段必须存在又允许自动填默认值UNIQUE约束保证手机号不重复ENGINEInnoDB是当前最主流的存储引擎后面讲事务的时候会专门提。2.2 增删改查四个动作覆盖九成需求增删改查对应SQL里的INSERT、DELETE、UPDATE、SELECT。这四个单词背下来你已经会一半SQL了。查询SELECT是所有操作里最常用的也是把新手和老手区分开的分水岭。最简单的查询就是SELECT * FROM student但面试和实际项目里没人会满意你每次都全表扫描。你要学会WHERE条件过滤、ORDER BY排序、LIMIT分页、GROUP BY分组、聚合函数COUNT/SUM/AVG/MAX/MIN、JOIN多表连接。这些知识点我不在这里展开第4节的5道经典题里会逐个过一遍。写入INSERT要注意的一点是批量插入。一次插一条在数据量小的时候无所谓但如果你要导几万条数据一条条insert能把人的心态搞崩。直接上批量语法INSERT INTO student (name, gender, birth_date, phone) VALUES (张三, 男, 2000-01-01, 13800000001), (李四, 女, 2001-02-02, 13800000002), (王五, 男, 2002-03-03, 13800000003);改成UPDATE和删除DELETE是风险最大的操作。我就犯过一个经典错误给一张用户表更新状态忘了加WHERE条件一条UPDATE下去全表的用户状态全变成了同一个值。那几秒钟脑子是空白的心跳是加速的。从那以后我的习惯是先写SELECT把要改的数据查出来看一遍确认WHERE条件没写错再改成UPDATE执行。这个习惯也推荐给你。2.3 WHERE条件的执行逻辑别被NULL坑了WHERE这一节我要重点讲NULL。很多新手在看“等值匹配”的时候会默认“空值也能等于空值”但SQL里NULL是“未知”不是“空字符串”。拿WHERE phone NULL去查永远查不到任何东西正确写法是WHERE phone IS NULL。这里有个坑中坑在JOIN连接的时候如果连接字段里有NULL这个连接会直接失败记录会被悄悄丢弃。我在处理用户表和订单表关联时踩过这个结果订单统计数一直对不上查了一上午才发现是有几个订单的user_id存了NULL。生成环境里遇到这种“数据莫名变少”的问题第一个怀疑方向就是连接字段里有NULL。3. 数据王国的管理法则约束、关系与范式3.1 外键表与表之间的合同建表时我们把学生、课程、选课分成三张表那这三张表怎么产生联系靠外键FOREIGN KEY。外键的意思就是某一列的值必须来自另一张表的某个字段值或者必须是NULL。选课表的设计CREATE TABLE course_selection ( id BIGINT AUTO_INCREMENT PRIMARY KEY, student_id BIGINT NOT NULL, course_id BIGINT NOT NULL, score DECIMAL(5,2), CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(id) );有了外键约束你往选课表里插一个不存在的student_id数据库直接报错根本插不进去。从数据王国治理的角度讲这就是“法律条文”——谁都不能往选课记录里塞一个不在籍的学生。但外键在互联网业务高并发场景下是“奢侈品”。因为外键会影响写入性能每次插入都要去校验关联表在大流量写入场景下很多公司会放弃数据库外键改在应用层做校验。这是理论知识和实际工程的矛盾点之一面试的时候如果被问到“你平时写外键吗”答“看场景低并发实时性要求高的场景用物理外键高并发大流量场景用逻辑外键”就非常加分。3.2 三大范式数据冗余的克星范式Normal Form是数据库设计的规范一共六种级别实际工作中掌握到第三范式就够用了。第一范式1NF每列都不可再分。比如你把兴趣写成“篮球,足球,羽毛球”存在一个字段里这就违反了1NF应该拆成三行或者单拆一张兴趣表。第二范式2NF在1NF基础上每一列都完全依赖主键而不是依赖主键的一部分。举个例子一个选课表的主键如果是(student_id, course_id)那成绩字段score依赖整个主键没问题但如果你把“课程老师”也塞进选课表它就只依赖course_id不依赖student_id这就是部分依赖违反2NF。结果就是同一个课程老师的名字会在几十行里重复改一次老师名要改几十行。第三范式3NF在2NF基础上非主键字段不能依赖其他非主键字段。比如订单表里有“用户ID”和“用户手机号”手机号由用户ID决定不是直接依赖订单号这就是传递依赖。应该把手机号留在用户表订单表只存用户ID。范式的本质是消除冗余但现实中见过很多项目为了性能刻意反范式比如同步一份冗余字段到查询频繁的表里避免JOIN。这算工程权衡不犯法前提是你清楚自己付出了维护一致性的代价。3.3 综合案例设计一个简单的博客系统把前面的知识点揉在一起我带你设计一个迷你博客系统。这个场景我在讲解时经常用因为它贴近生活逻辑也不复杂。需要四张表用户表user、文章表article、分类表category、评论表comment。用户表id、用户名、密码密文、昵称、注册时间。 文章表id、标题、正文、作者ID外键指向用户表的id、分类ID外键指向分类表的id、发布时间、状态。 分类表id、名称、排序值。 评论表id、内容、文章ID外键指向文章表id、用户ID外键指向用户表id、评论时间。这个设计里文章表不直接存作者名字只存作者ID想知道作者名就通过ID去用户表查。为什么因为作者名字如果改了文章表里的数据不用动始终查出来是最新的。如果你写死“作者名”字段作者改名以后历史文章全部要跟着改。这个理念是数据库外键关系最核心的价值单一事实来源。4. 进阶修炼索引与事务数据王国的两大护法4.1 索引让数据王国不再“翻箱倒柜”没有索引的时候数据库查一张100万行的表要一行一行从头扫到尾这叫全表扫描耗时可能几百毫秒甚至秒级。加了索引之后数据库相当于给字段建了一棵查找树查一次只需要走树的高度10几层就找到了耗时瞬间到毫秒级。索引的选型思路用不用背B树原理面试会问但实际操作里你只需要知道索引是排好序的数据结构它用额外的存储空间换查询速度。对哪个字段建索引取决于你的WHERE条件、ORDER BY、JOIN字段。我建议新手的建索引标准主键默认就是唯一索引别重复建。WHERE常用的条件字段优先建索引。JOIN连接的字段建议建索引否则多表关联会变成灾难。区分度低的字段不要建索引比如“性别”只有男女两个值索引根本没有过滤效果还耗空间。不要在索引列上做函数运算比如WHERE DATE(birth_date) 2001-01-01这会让索引失效。说到索引失效这是新手排查慢查询的第一大头疼点。常见的三个坑隐式类型转换字符串列不引号或数字列存成字符串去比较、前模糊查询LIKE %abc导致索引失效、联合索引不按最左前缀原则匹配。这三个记住了你排查SQL慢查询的思路就有了。4.2 事务要么全成要么全败你把两个账户之间转账这个业务想一下从A账户扣100块往B账户加100块。如果扣完A的钱数据库突然断电B的钱没加上这事就没法交代。事务Transaction就是干这个用的它把一串操作打包成一个整体要么全部成功要么全部回滚。事务的ACID特性是面试必考但实际理解起来并不难原子性Atomicity一个事务里的操作要么全部完成要么全部不做。一致性Consistency事务完成后数据库从一个合法状态进入另一个合法状态。比如转账后AB的总金额不变。隔离性Isolation多个事务同时操作同一批数据时互不干扰。持久性Durability事务提交以后即使数据库重启了数据也还在。InnoDB引擎支持事务MyISAM引擎不支持这也是我之前建议ENGINEInnoDB的原因。事务的隔离级别有四种读未提交、读已提交、可重复读、串行化。MySQL默认是“可重复读”。隔离级别低并发性能好但可能出现脏读、不可重复读、幻读的问题。这部分内容初学者了解概念即可真正纠结隔离级别是DBA的活。4.3 实操示例用事务保护一笔转账操作举个例子我们用事务把转账的完整过程写出来START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; -- 检查更新是否成功如果余额不足ROLLBACK UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;在你写完第一个UPDATE之后如果发现user_id1的账户余额不够扣你可以立刻ROLLBACK回滚全程不会产生任何脏数据。这在实际业务代码里往往是通过程序先查询余额、再判断是否足够、再决定提交还是回滚来完成的。5. 5道经典题从表结构到查询优化的进阶之路5.1 第一题找出每个学生的最高分课程假设有三张表student(id, name)、course(id, name)、score(student_id, course_id, score)。问题是“每个学生的最高分课程”。这个题的核心是子查询和关联。先找到每个学生的最高分再把这个结果和原表关联起来取课程信息。我教你一个实用的思路拿一张临时结果去JOIN原始表。临时结果结构是(student_id, max_score)和score表按这两个字段关联就能得到对应的course_id再连course表取课程名。这个思路理解了以后很多“找前几条”“找最大一条”的题都通吃。5.2 第二题统计各课程的平均分只保留平均分大于60的这题考GROUP BY和HAVING的组合。新手常见的错误是把过滤条件写在WHERE里但WHERE的执行时机是在分组之前分组之后的筛选必须用HAVING。这个知识点背下来不如踩一次坑记得牢我面试的时候问过好几个人能准确讲出HAVING用途的不超过四成。5.3 第三题查询没有选任何课程的学生这题有三种解法NOT EXISTS、NOT IN、LEFT JOIN IS NULL。面试官通常希望看到你至少写出两种。核心逻辑是先找到“有选课的学生ID集合”然后从学生表里排除。需要提醒的是如果子查询结果集里出现NULLNOT IN的结果会意外为空这是个大坑。用NOT EXISTS或者LEFT JOIN更安全。5.4 第四题删除重复数据只保留最小ID的那条这题在实际工作中经常会遇到属于“数据清洗”场景。原理是先把重复数据的最小ID查出来然后删除“重复但不是最小ID”的记录。写DELETE之前一定先写一条SELECT验证一下你的条件不要上来就删。我亲眼见过同事删重复数据把整张表清空的场景恢复数据花了三个小时教训够深刻。5.5 第五题订单表的分页查询优化订单表1000万条数据执行“SELECT * FROM orders ORDER BY create_time LIMIT 800000, 20”越来越慢。原因很简单随着偏移量增大数据库要扫描的记录数越来越多。优化方案有几种用覆盖索引简化排序记录上一页最后一条的create_time用WHERE条件代替LIMIT偏移或者先查ID再JOIN回原表。第三种方案效果最好但SQL最复杂。这个问题在生产环境太常见了值得记下。6. 避坑实录新手最常见的错误与排查心得6.1 经典错误速查表错误现象常见原因解决方案查询结果莫名变少JOIN连接的字段有NULL检查关联字段的空值或用LEFT JOIN并过滤UPDATE/DELETE影响大量行WHERE条件忘记写或写错先用SELECT验证条件再执行索引没生效查询依旧慢列上用了函数或隐式类型转换改写SQL条件避免左侧函数运算数据写入报“Data too long”字段长度不够字符集是UTF-8扩大字段长度或拆表处理大批量导入时速度极慢一条条INSERT太频繁改成批量INSERT或使用LOAD DATA事务提交后数据却不见了用了不支持事务的引擎确认ENGINEInnoDB检查隔离级别6.2 慢SQL排查的原点思维遇到慢查询第一个反应不是“加索引”而是“先看执行计划”。执行计划是数据库分析你的SQL后给出的执行方案它会告诉你走了哪张表的哪个索引、扫描了多少行。在MySQL里用EXPLAIN关键字在Oracle里是执行计划函数。很多新手分析慢查询上来就加索引加到第五个索引还没解决再回头一看其实是表的数据分布有问题或者SQL写法有缺陷。排查步骤我整理成固定的顺序先看SQL逻辑是否合理再看执行计划选择了哪个索引再看是否出现了隐式类型转换或函数运算最后考虑优化表结构和索引。按这个顺序走下来绝大部分慢查询半小时内能定位。6.3 备份是救命的不是走形式的最后必须讲一句数据库学习阶段养成备份习惯。我见过太多“手滑清空了表”的事故不管你是新手还是老手意外永远在路上。建议至少每周做一次全量备份重要数据可以开启定期自动备份。在备份的生命里要顺便测试一下恢复流程——很多团队备份做了三年真出事的时候才发现备份文件损坏那才是最绝望的场景。恢复流程不演练备份等于白做。7. 写在最后的一点个人体会数据库这门课入门不难难的是“知其然还知其所以然”。同样的SQL不同的人写出来性能可能差几十倍。想真正上手给自己布置个小项目比如搭一个博客系统、做一个记账小程序把建表、约束、查询、事务、索引全部用上一遍。踩几个坑再回来看理论知识你会发现每个概念都活了。我自己带新人的时候常说这么一句话“操作数据库之前多问自己一遍——这条SQL在真实业务里会不会给别的查询带来压力这个表结构下个月需求一变更还能不能撑住”养成这种习惯你就不只是会写SQL的人而是真正懂数据王国运作的人。
我在带新人入门的时候最常听到的一句话是“数据库到底是个啥感觉就是个存数据的软件呗。”这话没毛病但只对了一半。如果只是存数据Excel也能干为啥要搞出个专门的“王国”出来因为数据库解决的从来不是“怎么存”的问题而是“怎么存得稳、查得快、改得准、挂了还能恢复”的问题。今天这篇我就用“数据王国”这个比喻把数据库底层的运作逻辑掰开揉碎讲一遍再配5道我筛选过的经典题帮你把脑子里那些零散概念串成一条线。这篇文章不挑基础哪怕你完全没写过SQL也能看懂如果你已经会用增删改查了重点看后半部分的题目解析和避坑心得那里面有不少是我在实际项目里踩出来的经验。数据库这个东西难起来可以研究一辈子但入门的时候只要把“表、关系、约束、索引、事务”这几个核心概念吃透你就已经甩开绝大多数“只会背命令”的人了。1. 由表入道数据王国的地基是“二维表”不是“数据库软件”1.1 为什么是“表”而不是“文件柜”先想一个问题你手里有一堆客户信息、订单信息、商品信息最原始的做法是拿个本子记或者塞进Excel。Excel和数据库的表看起来都是“行和列”但本质上有个天大的区别——Excel是给“人”看的数据库的表是给“机器”和“规则”看的。机器看数据讲究的是“严格”。每一列存什么类型长度多少能不能为空有没有默认值这些都在建表的时候定死了。你在Excel里可以随手在一个数字列里敲个“abc”数据库里这么干直接被拒绝执行。这不是死板这叫“约束”。约束保证了数据的质量数据质量是整个数据王国的命根子。在数据库的世界里一张表的结构被称作“关系模式”表本身就是“关系”。你把一张表想象成一个王国的户籍本每个公民行都有唯一的身份证号主键每项登记内容列都有明确规范。没有规矩不成方圆没有主键的表就是黑户遍地查数据、改数据、删数据都容易乱套。1.2 主键数据王国的身份证主键Primary Key这块我得多说两句。它是每张表里最核心的字段作用就是唯一标识一行记录。选主键有两条铁律第一不能为空第二不能重复。你可能会想“那我用身份证号当主键不就行了吗”行但如果你的业务里没有天然唯一字段呢比如一张订单表没有订单号只能靠“用户ID下单时间”来拼。这种情况我建议你用自增ID或者一串随机生成的唯一编号。我在实际项目里见过有人用“姓名手机号”做主键结果两个同事的手机号录错了撞了主键整个表写不进去数据排查半天才发现是脏数据问题。记住主键最好用无业务含义的纯ID业务字段随时可能变变了主键就崩了。1.3 字段类型的坑别把什么都存成字符串建表的时候字段类型的选型是新手最爱忽略的。我见过最典型的操作是把手机号存成INT结果号码显示成“138****”后面被抹位其实不是显示问题是数值溢出后被自动转了。还有把日期存成字符串然后查“最近7天下单用户”的时候用字符串比较大小格式一不统一就漏数据。常用的字段类型几句话讲明白INT/BIGINT整数类型BIGINT比INT范围大得多自增主键建议直接BIGINT别省。DECIMAL(M,D)定点小数存钱用这个绝对不要用FLOAT/DOUBLE浮点数有精度丢失账算不平。VARCHAR(N)变长字符串存姓名、地址这种长度不固定的内容。CHAR(N)定长字符串存身份证号、手机号这种固定长度的性能比VARCHAR好一点。DATETIME/TIMESTAMP存时间TIMESTAMP有2038年问题能用DATETIME尽量用DATETIME。TEXT/BLOB存大文本和二进制能用文件路径代替就别直接往库里塞大文件库会变得又肥又慢。这里补一个很多新手不知道的细节VARCHAR括号里的N是“字符数”而不是“字节数”。如果库的字符集是UTF-8一个汉字占3个字节VARCHAR(10)能存下10个汉字但如果你用VARCHAR(10)去存一个包含数字字母混合的字符串长度上限是10个字符不是10个字节。很多报错“Data too long for column”就是这么来的。2. 数据王国的通用语言SQL的四大基石操作2.1 建库建表先规划后动手学习阶段没关系随便建。工作里建表之前一定要先规划清楚这张表要存什么谁要读谁要写大概多大的数据量这些想清楚了再写CREATE TABLE否则后面改表结构极度痛苦尤其是表里有数据了之后ALTER TABLE在大表上可能直接锁表业务就停了。我建议每个入门的人练习的时候建一个“学生管理系统”的库三个表学生表、课程表、选课表。这个场景足够经典把三个表建出来你就理解了什么叫“实体”和“关系”。一份基本的学生表创建语句CREATE TABLE student ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 学生ID, name VARCHAR(50) NOT NULL COMMENT 学生姓名, gender CHAR(1) DEFAULT 男 COMMENT 性别, birth_date DATE COMMENT 出生日期, phone VARCHAR(20) UNIQUE COMMENT 手机号, enrollment_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入学时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;这里有几个点值得研究一下AUTO_INCREMENT是自增不需要你手动传IDNOT NULL和DEFAULT一起用既保证字段必须存在又允许自动填默认值UNIQUE约束保证手机号不重复ENGINEInnoDB是当前最主流的存储引擎后面讲事务的时候会专门提。2.2 增删改查四个动作覆盖九成需求增删改查对应SQL里的INSERT、DELETE、UPDATE、SELECT。这四个单词背下来你已经会一半SQL了。查询SELECT是所有操作里最常用的也是把新手和老手区分开的分水岭。最简单的查询就是SELECT * FROM student但面试和实际项目里没人会满意你每次都全表扫描。你要学会WHERE条件过滤、ORDER BY排序、LIMIT分页、GROUP BY分组、聚合函数COUNT/SUM/AVG/MAX/MIN、JOIN多表连接。这些知识点我不在这里展开第4节的5道经典题里会逐个过一遍。写入INSERT要注意的一点是批量插入。一次插一条在数据量小的时候无所谓但如果你要导几万条数据一条条insert能把人的心态搞崩。直接上批量语法INSERT INTO student (name, gender, birth_date, phone) VALUES (张三, 男, 2000-01-01, 13800000001), (李四, 女, 2001-02-02, 13800000002), (王五, 男, 2002-03-03, 13800000003);改成UPDATE和删除DELETE是风险最大的操作。我就犯过一个经典错误给一张用户表更新状态忘了加WHERE条件一条UPDATE下去全表的用户状态全变成了同一个值。那几秒钟脑子是空白的心跳是加速的。从那以后我的习惯是先写SELECT把要改的数据查出来看一遍确认WHERE条件没写错再改成UPDATE执行。这个习惯也推荐给你。2.3 WHERE条件的执行逻辑别被NULL坑了WHERE这一节我要重点讲NULL。很多新手在看“等值匹配”的时候会默认“空值也能等于空值”但SQL里NULL是“未知”不是“空字符串”。拿WHERE phone NULL去查永远查不到任何东西正确写法是WHERE phone IS NULL。这里有个坑中坑在JOIN连接的时候如果连接字段里有NULL这个连接会直接失败记录会被悄悄丢弃。我在处理用户表和订单表关联时踩过这个结果订单统计数一直对不上查了一上午才发现是有几个订单的user_id存了NULL。生成环境里遇到这种“数据莫名变少”的问题第一个怀疑方向就是连接字段里有NULL。3. 数据王国的管理法则约束、关系与范式3.1 外键表与表之间的合同建表时我们把学生、课程、选课分成三张表那这三张表怎么产生联系靠外键FOREIGN KEY。外键的意思就是某一列的值必须来自另一张表的某个字段值或者必须是NULL。选课表的设计CREATE TABLE course_selection ( id BIGINT AUTO_INCREMENT PRIMARY KEY, student_id BIGINT NOT NULL, course_id BIGINT NOT NULL, score DECIMAL(5,2), CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(id) );有了外键约束你往选课表里插一个不存在的student_id数据库直接报错根本插不进去。从数据王国治理的角度讲这就是“法律条文”——谁都不能往选课记录里塞一个不在籍的学生。但外键在互联网业务高并发场景下是“奢侈品”。因为外键会影响写入性能每次插入都要去校验关联表在大流量写入场景下很多公司会放弃数据库外键改在应用层做校验。这是理论知识和实际工程的矛盾点之一面试的时候如果被问到“你平时写外键吗”答“看场景低并发实时性要求高的场景用物理外键高并发大流量场景用逻辑外键”就非常加分。3.2 三大范式数据冗余的克星范式Normal Form是数据库设计的规范一共六种级别实际工作中掌握到第三范式就够用了。第一范式1NF每列都不可再分。比如你把兴趣写成“篮球,足球,羽毛球”存在一个字段里这就违反了1NF应该拆成三行或者单拆一张兴趣表。第二范式2NF在1NF基础上每一列都完全依赖主键而不是依赖主键的一部分。举个例子一个选课表的主键如果是(student_id, course_id)那成绩字段score依赖整个主键没问题但如果你把“课程老师”也塞进选课表它就只依赖course_id不依赖student_id这就是部分依赖违反2NF。结果就是同一个课程老师的名字会在几十行里重复改一次老师名要改几十行。第三范式3NF在2NF基础上非主键字段不能依赖其他非主键字段。比如订单表里有“用户ID”和“用户手机号”手机号由用户ID决定不是直接依赖订单号这就是传递依赖。应该把手机号留在用户表订单表只存用户ID。范式的本质是消除冗余但现实中见过很多项目为了性能刻意反范式比如同步一份冗余字段到查询频繁的表里避免JOIN。这算工程权衡不犯法前提是你清楚自己付出了维护一致性的代价。3.3 综合案例设计一个简单的博客系统把前面的知识点揉在一起我带你设计一个迷你博客系统。这个场景我在讲解时经常用因为它贴近生活逻辑也不复杂。需要四张表用户表user、文章表article、分类表category、评论表comment。用户表id、用户名、密码密文、昵称、注册时间。 文章表id、标题、正文、作者ID外键指向用户表的id、分类ID外键指向分类表的id、发布时间、状态。 分类表id、名称、排序值。 评论表id、内容、文章ID外键指向文章表id、用户ID外键指向用户表id、评论时间。这个设计里文章表不直接存作者名字只存作者ID想知道作者名就通过ID去用户表查。为什么因为作者名字如果改了文章表里的数据不用动始终查出来是最新的。如果你写死“作者名”字段作者改名以后历史文章全部要跟着改。这个理念是数据库外键关系最核心的价值单一事实来源。4. 进阶修炼索引与事务数据王国的两大护法4.1 索引让数据王国不再“翻箱倒柜”没有索引的时候数据库查一张100万行的表要一行一行从头扫到尾这叫全表扫描耗时可能几百毫秒甚至秒级。加了索引之后数据库相当于给字段建了一棵查找树查一次只需要走树的高度10几层就找到了耗时瞬间到毫秒级。索引的选型思路用不用背B树原理面试会问但实际操作里你只需要知道索引是排好序的数据结构它用额外的存储空间换查询速度。对哪个字段建索引取决于你的WHERE条件、ORDER BY、JOIN字段。我建议新手的建索引标准主键默认就是唯一索引别重复建。WHERE常用的条件字段优先建索引。JOIN连接的字段建议建索引否则多表关联会变成灾难。区分度低的字段不要建索引比如“性别”只有男女两个值索引根本没有过滤效果还耗空间。不要在索引列上做函数运算比如WHERE DATE(birth_date) 2001-01-01这会让索引失效。说到索引失效这是新手排查慢查询的第一大头疼点。常见的三个坑隐式类型转换字符串列不引号或数字列存成字符串去比较、前模糊查询LIKE %abc导致索引失效、联合索引不按最左前缀原则匹配。这三个记住了你排查SQL慢查询的思路就有了。4.2 事务要么全成要么全败你把两个账户之间转账这个业务想一下从A账户扣100块往B账户加100块。如果扣完A的钱数据库突然断电B的钱没加上这事就没法交代。事务Transaction就是干这个用的它把一串操作打包成一个整体要么全部成功要么全部回滚。事务的ACID特性是面试必考但实际理解起来并不难原子性Atomicity一个事务里的操作要么全部完成要么全部不做。一致性Consistency事务完成后数据库从一个合法状态进入另一个合法状态。比如转账后AB的总金额不变。隔离性Isolation多个事务同时操作同一批数据时互不干扰。持久性Durability事务提交以后即使数据库重启了数据也还在。InnoDB引擎支持事务MyISAM引擎不支持这也是我之前建议ENGINEInnoDB的原因。事务的隔离级别有四种读未提交、读已提交、可重复读、串行化。MySQL默认是“可重复读”。隔离级别低并发性能好但可能出现脏读、不可重复读、幻读的问题。这部分内容初学者了解概念即可真正纠结隔离级别是DBA的活。4.3 实操示例用事务保护一笔转账操作举个例子我们用事务把转账的完整过程写出来START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; -- 检查更新是否成功如果余额不足ROLLBACK UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;在你写完第一个UPDATE之后如果发现user_id1的账户余额不够扣你可以立刻ROLLBACK回滚全程不会产生任何脏数据。这在实际业务代码里往往是通过程序先查询余额、再判断是否足够、再决定提交还是回滚来完成的。5. 5道经典题从表结构到查询优化的进阶之路5.1 第一题找出每个学生的最高分课程假设有三张表student(id, name)、course(id, name)、score(student_id, course_id, score)。问题是“每个学生的最高分课程”。这个题的核心是子查询和关联。先找到每个学生的最高分再把这个结果和原表关联起来取课程信息。我教你一个实用的思路拿一张临时结果去JOIN原始表。临时结果结构是(student_id, max_score)和score表按这两个字段关联就能得到对应的course_id再连course表取课程名。这个思路理解了以后很多“找前几条”“找最大一条”的题都通吃。5.2 第二题统计各课程的平均分只保留平均分大于60的这题考GROUP BY和HAVING的组合。新手常见的错误是把过滤条件写在WHERE里但WHERE的执行时机是在分组之前分组之后的筛选必须用HAVING。这个知识点背下来不如踩一次坑记得牢我面试的时候问过好几个人能准确讲出HAVING用途的不超过四成。5.3 第三题查询没有选任何课程的学生这题有三种解法NOT EXISTS、NOT IN、LEFT JOIN IS NULL。面试官通常希望看到你至少写出两种。核心逻辑是先找到“有选课的学生ID集合”然后从学生表里排除。需要提醒的是如果子查询结果集里出现NULLNOT IN的结果会意外为空这是个大坑。用NOT EXISTS或者LEFT JOIN更安全。5.4 第四题删除重复数据只保留最小ID的那条这题在实际工作中经常会遇到属于“数据清洗”场景。原理是先把重复数据的最小ID查出来然后删除“重复但不是最小ID”的记录。写DELETE之前一定先写一条SELECT验证一下你的条件不要上来就删。我亲眼见过同事删重复数据把整张表清空的场景恢复数据花了三个小时教训够深刻。5.5 第五题订单表的分页查询优化订单表1000万条数据执行“SELECT * FROM orders ORDER BY create_time LIMIT 800000, 20”越来越慢。原因很简单随着偏移量增大数据库要扫描的记录数越来越多。优化方案有几种用覆盖索引简化排序记录上一页最后一条的create_time用WHERE条件代替LIMIT偏移或者先查ID再JOIN回原表。第三种方案效果最好但SQL最复杂。这个问题在生产环境太常见了值得记下。6. 避坑实录新手最常见的错误与排查心得6.1 经典错误速查表错误现象常见原因解决方案查询结果莫名变少JOIN连接的字段有NULL检查关联字段的空值或用LEFT JOIN并过滤UPDATE/DELETE影响大量行WHERE条件忘记写或写错先用SELECT验证条件再执行索引没生效查询依旧慢列上用了函数或隐式类型转换改写SQL条件避免左侧函数运算数据写入报“Data too long”字段长度不够字符集是UTF-8扩大字段长度或拆表处理大批量导入时速度极慢一条条INSERT太频繁改成批量INSERT或使用LOAD DATA事务提交后数据却不见了用了不支持事务的引擎确认ENGINEInnoDB检查隔离级别6.2 慢SQL排查的原点思维遇到慢查询第一个反应不是“加索引”而是“先看执行计划”。执行计划是数据库分析你的SQL后给出的执行方案它会告诉你走了哪张表的哪个索引、扫描了多少行。在MySQL里用EXPLAIN关键字在Oracle里是执行计划函数。很多新手分析慢查询上来就加索引加到第五个索引还没解决再回头一看其实是表的数据分布有问题或者SQL写法有缺陷。排查步骤我整理成固定的顺序先看SQL逻辑是否合理再看执行计划选择了哪个索引再看是否出现了隐式类型转换或函数运算最后考虑优化表结构和索引。按这个顺序走下来绝大部分慢查询半小时内能定位。6.3 备份是救命的不是走形式的最后必须讲一句数据库学习阶段养成备份习惯。我见过太多“手滑清空了表”的事故不管你是新手还是老手意外永远在路上。建议至少每周做一次全量备份重要数据可以开启定期自动备份。在备份的生命里要顺便测试一下恢复流程——很多团队备份做了三年真出事的时候才发现备份文件损坏那才是最绝望的场景。恢复流程不演练备份等于白做。7. 写在最后的一点个人体会数据库这门课入门不难难的是“知其然还知其所以然”。同样的SQL不同的人写出来性能可能差几十倍。想真正上手给自己布置个小项目比如搭一个博客系统、做一个记账小程序把建表、约束、查询、事务、索引全部用上一遍。踩几个坑再回来看理论知识你会发现每个概念都活了。我自己带新人的时候常说这么一句话“操作数据库之前多问自己一遍——这条SQL在真实业务里会不会给别的查询带来压力这个表结构下个月需求一变更还能不能撑住”养成这种习惯你就不只是会写SQL的人而是真正懂数据王国运作的人。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
