MySQL表设计进阶-约束范式连接索引与事务

MySQL 表设计进阶约束、范式、连接、索引与事务会写SELECT之后很快会碰到另一个问题同样的数据为什么有人设计出来的表很好查、很好改有人的表却要靠一堆补丁维持差别往往不在 SQL 写得多花哨而在几个基础决定哪些字段必须有值哪些值不能重复一条信息到底应该放在哪张表表与表之间怎样建立关系哪些查询值得加索引多条写操作怎样保证一起成功或一起失败。下面继续用学生成绩库做例子。环境按 MySQL 8.0 编写重点不只是“语法怎么写”也会说明为什么这样设计。一、先看完整的表关系这个小系统有四张表class班级student学生每个学生属于一个班级course课程score成绩用来连接学生和课程。建表语句如下createdatabaseifnotexistsschool_demodefaultcharactersetutf8mb4collateutf8mb4_0900_ai_ci;useschool_demo;createtableclass(idbigintunsignedprimarykeyauto_increment,namevarchar(30)notnull,uniquekeyuk_class_name(name))engineInnoDB;createtablestudent(idbigintunsignedprimarykeyauto_increment,namevarchar(20)notnull,agetinyintunsigned,gendertinyintnotnulldefault0,class_idbigintunsigned,created_atdatetimenotnulldefaultcurrent_timestamp,constraintck_student_agecheck(ageisnullorage150),constraintck_student_gendercheck(genderin(0,1,2)),constraintfk_student_classforeignkey(class_id)referencesclass(id)onupdatecascadeondeletesetnull,keyidx_student_class_id(class_id))engineInnoDB;createtablecourse(idbigintunsignedprimarykeyauto_increment,namevarchar(50)notnull,creditdecimal(3,1)notnulldefault1.0,uniquekeyuk_course_name(name))engineInnoDB;createtablescore(student_idbigintunsignednotnull,course_idbigintunsignednotnull,scoredecimal(5,2),exam_timedate,primarykey(student_id,course_id),constraintck_score_rangecheck(scoreisnullorscorebetween0and100),constraintfk_score_studentforeignkey(student_id)referencesstudent(id)ondeletecascade,constraintfk_score_courseforeignkey(course_id)referencescourse(id)ondeleterestrict,keyidx_score_course_id(course_id))engineInnoDB;后面的约束、连接和索引都可以从这四张表里找到对应场景。二、约束是数据的最后一道防线1. NOT NULL必须存在的字段namevarchar(20)notnull学生姓名如果允许为NULL这条记录很难再用于点名、查询或展示。对业务上必须存在的字段应该直接在数据库层限制。不过NOT NULL也不要到处加。比如学生暂时没有分班时class_id允许为空就比随便填一个0更清楚。2. DEFAULT常见初始值gendertinyintnotnulldefault0默认值适合“大多数新记录都相同”的字段。它能简化插入语句但不应该掩盖真正缺失的信息。生日未知时保存NULL通常比写一个虚假的1970-01-01更合理。3. UNIQUE不能重复的业务值uniquekeyuk_course_name(name)同一个系统里不允许出现两门同名课程时可以交给唯一约束保证。只在 Java 代码中先查一次再插入并不能完全避免并发请求同时写入重复数据。MySQL 的唯一索引通常允许多个NULL。如果字段要求“必须存在且不能重复”需要一起写phonechar(11)notnullunique4. PRIMARY KEY一行数据的身份证单字段主键idbigintunsignedprimarykeyauto_increment联合主键primarykey(student_id,course_id)score表用(student_id, course_id)做联合主键表达的业务规则很明确一个学生在一门课程下只有一条成绩记录。主键最好稳定、短小并且没有业务含义。手机号、身份证号虽然也可能唯一但它们会变化也涉及隐私不适合作为到处传播的主键。5. AUTO_INCREMENT方便但不保证连续idbigintunsignedprimarykeyauto_increment自增主键只保证生成唯一值不保证严格连续。插入失败、事务回滚、批量预分配都可能留下空洞。因此不要拿自增 ID 推算业务数量也不要因为中间缺了一个编号就手动补回去。6. FOREIGN KEY保证引用关系存在constraintfk_student_classforeignkey(class_id)referencesclass(id)有了这个约束student.class_id不能指向一个不存在的班级。常见外键动作动作含义RESTRICT有子表记录引用时拒绝删除父表记录CASCADE父表更新或删除时子表跟着处理SET NULL父表记录删除后把子表外键设为NULL动作要按业务语义选。删除学生时连带删除成绩通常合理删除课程时直接清空所有历史成绩就未必合理所以示例中课程使用RESTRICT。很多互联网项目会在业务代码中维护关联而不使用物理外键主要是为了分库分表、写入性能和发布灵活性。但即使不用外键也要保留同样的完整性意识并通过代码、任务和监控处理孤儿数据。7. CHECK让取值范围更可信check(scoreisnullorscorebetween0and100)MySQL 8.0.16 及以后版本会真正执行CHECK约束。旧版本可能只解析语法但不生效维护老系统时要先确认版本。三、范式到底在解决什么问题范式不是为了把表拆得越多越好而是减少重复数据和更新异常。1. 第一范式一列只表达一件事不推荐idnameschool1张三吉林大学长春市0431-xxxxschool同时塞了名称、地址和电话后面很难按城市查询也很难单独修改电话。更合理的写法是拆成多个字段或者进一步拆成school表。2. 第二范式非主键字段依赖整个联合主键假设把成绩保存成下面这样学号课程 ID学生姓名课程名学分成绩10011张三MySQL49010012张三Java588主键是(学号, 课程 ID)但学生姓名只依赖学号课程名和学分只依赖课程 ID只有成绩同时依赖学号和课程 ID。所以应该拆成student(id, name) course(id, name, credit) score(student_id, course_id, score)这样修改学生姓名时只改一处不会出现同一个学生在不同成绩记录里名字不一致。3. 第三范式避免非主键字段之间的传递依赖如果学生表同时保存student(id, name, college_id, college_name, college_phone)那么依赖关系是student.id → college_id → college_name、college_phone学院电话只依赖学院不应该在每个学生行里重复。更合理的设计student(id, name, college_id) college(id, name, phone)学院换电话时只改一行。4. 真实项目不一定追求“绝对范式”读多写少、对响应时间很敏感的场景有时会有意保留冗余字段。例如订单明细会保存下单时的商品名称和价格避免商品后来改名、改价后影响历史订单。这不等于范式没用。先按范式把数据关系想清楚再有理由地冗余和一开始就把所有字段堆在一张表里是两回事。四、一对一、一对多和多对多1. 一对一用户基础信息和实名认证信息user(id, username) user_profile(id, user_id, real_name, id_card)在user_profile.user_id上同时加外键和唯一约束user_idbigintunsignednotnullunique2. 一对多一个班级有多个学生一个学生属于一个班级。外键放在“多”的一侧student.class_id → class.id3. 多对多学生可以选择多门课程一门课程也可以被多个学生选择。不要在student表里保存1,2,5这样的课程 ID 字符串而是增加中间表score(student_id, course_id, score)中间表还可以保存这段关系自己的属性例如成绩、考试时间、选课状态。五、多表查询把拆开的数据重新拼起来范式把数据拆到多张表里查询完整信息时就要连接。先插入少量测试数据insertintoclass(name)values(一班),(二班);insertintostudent(name,age,gender,class_id)values(张三,18,1,1),(李四,19,2,1),(王五,18,1,null);insertintocourse(name,credit)values(MySQL,4.0),(Java,5.0);insertintoscore(student_id,course_id,score,exam_time)values(1,1,90,2026-07-20),(1,2,88,2026-07-21),(2,1,82,2026-07-20);1. INNER JOIN只保留匹配成功的数据selects.nameasstudent_name,c.nameasclass_namefromstudent sinnerjoinclass conc.ids.class_id;没有分班的王五不会出现在结果中。2. LEFT JOIN保留左表全部数据selects.nameasstudent_name,sc.scorefromstudent sleftjoinscore sconsc.student_ids.id;即使学生暂时没有成绩也会保留学生这一行右表字段显示为NULL。一个很常见的坑是把右表过滤条件写进WHEREselects.name,sc.scorefromstudent sleftjoinscore sconsc.student_ids.idwheresc.score60;没有成绩的学生会被WHERE过滤掉效果接近内连接。如果需求是“保留所有学生只关联及格成绩”条件应该放在ON中selects.name,sc.scorefromstudent sleftjoinscore sconsc.student_ids.idandsc.score60;3. 多表连接selects.nameasstudent_name,cl.nameasclass_name,co.nameascourse_name,sc.scorefromstudent sleftjoinclass cloncl.ids.class_idleftjoinscore sconsc.student_ids.idleftjoincourse coonco.idsc.course_idorderbys.id,co.id;连接条件用ON最终结果的筛选条件用WHERESQL 会更容易读。4. 自连接员工和上级都在同一张表createtableemployee(idbigintunsignedprimarykey,namevarchar(20)notnull,manager_idbigintunsigned);查询员工及其上级selecte.nameasemployee_name,m.nameasmanager_namefromemployee eleftjoinemployee monm.ide.manager_id;同一张表通过别名扮演了两个角色。5. 子查询查询与张三同班的学生selectid,namefromstudentwhereclass_id(selectclass_idfromstudentwherename张三);查询选修了 MySQL 或 Java 的学生selectid,namefromstudentwhereidin(selectstudent_idfromscorewherecourse_idin(selectidfromcoursewherenamein(MySQL,Java)));子查询不一定比连接慢优化器会做改写。写完后应结合EXPLAIN看真实执行计划而不是只凭语法形式判断。6. UNION 与 UNION ALLselectnamefromstudentunionselectnamefromteacher;UNION会去重UNION ALL直接合并结果通常更快。如果业务上不需要去重优先使用UNION ALL。六、索引少扫数据比“让 SQL 看起来短”更重要可以把索引理解成书的目录。没有目录时为了找一个用户名数据库可能要从第一行扫到最后一行有合适索引时可以快速定位目标范围。1. 为什么 InnoDB 常用 B 树B 树有几个适合磁盘和范围查询的特点分支多、树高低查找需要的页较少数据有序适合BETWEEN、、ORDER BY叶子节点相连连续读取范围数据更方便。Hash 更擅长等值定位但不适合范围和排序普通二叉树分支少数据量大时树会更高磁盘 I/O 更多。2. 聚簇索引和二级索引InnoDB 的主键索引叶子节点保存整行数据所以主键索引也叫聚簇索引。二级索引叶子节点保存的是索引列和主键值。查询二级索引未覆盖的其他列时通常还要拿主键回到聚簇索引再查一次这就是“回表”。3. 创建索引createindexidx_student_nameonstudent(name);createindexidx_score_course_scoreonscore(course_id,score);showindexfromscore;删除索引dropindexidx_student_nameonstudent;4. 联合索引的最左前缀索引createindexidx_student_class_nameonstudent(class_id,name);通常可以支持whereclass_id1whereclass_id1andname张三whereclass_id1andnamelike张%只按name查询时无法利用这棵联合索引的最左列进行有效定位wherename张三联合索引列的顺序要结合实际查询条件、选择性和排序需求设计不能只按字段出现顺序照抄。5. 常见的索引失效或收益很低的写法-- 对索引列做函数计算whereyear(created_at)2026-- 前导模糊匹配wherenamelike%三-- 隐式类型转换例如 phone 是字符串却拿数字比较wherephone13800000001-- 联合索引跳过最左列wherename张三日期查询可以改成范围wherecreated_at2026-01-01andcreated_at2027-01-016. 用 EXPLAIN 验证explainselects.name,sc.scorefromstudent sjoinscore sconsc.student_ids.idwheres.class_id1;重点观察type访问方式key实际选择的索引rows预计扫描行数Extra是否出现临时表、文件排序等信息。索引不是越多越好。每多一个索引插入、更新和删除时就多一份维护成本也会占磁盘空间。低区分度的小表字段建索引后可能几乎没有收益。七、事务让一组操作保持完整转账是最典型的事务场景A 扣款和 B 加款必须一起成功否则数据就不可信。1. ACID特性含义原子性 Atomicity一组操作全部成功或全部回滚一致性 Consistency事务前后都满足业务规则和约束隔离性 Isolation并发事务尽量互不干扰持久性 Durability提交后的结果不会因进程重启而丢失2. 一个更稳妥的转账示例starttransaction;selectid,balancefromaccountwhereidin(1,2)forupdate;updateaccountsetbalancebalance-100whereid1andbalance100;-- 应用程序需要检查上一条 UPDATE 是否确实影响了 1 行updateaccountsetbalancebalance100whereid2;commit;中途发生异常时rollback;FOR UPDATE会对目标记录加锁减少并发转账时互相覆盖的风险。锁定多行时所有事务最好按相同顺序访问账户降低死锁概率。3. 保存点starttransaction;updatescoresetscorescore5wherecourse_id1;savepointafter_mysql;updatescoresetscorescore5wherecourse_id2;rollbacktoafter_mysql;commit;保存点可以只撤销事务后半段但不能替代清晰的业务边界。4. 并发事务的三个问题脏读读到其他事务尚未提交的数据不可重复读同一事务里两次读取同一行结果不同幻读同一条件两次查询记录数量发生变化。5. 隔离级别隔离级别说明READ UNCOMMITTED隔离最弱可能脏读READ COMMITTED只能读已提交数据REPEATABLE READ同一事务内重复读取保持一致SERIALIZABLE隔离最强并发能力最低查看当前隔离级别selecttransaction_isolation;InnoDB 默认通常是REPEATABLE READ。隔离级别不是越高越好要在一致性要求、锁等待和并发量之间取舍。八、三个常见延伸点原稿里还有视图、权限和 JDBC这三部分可以先抓住最实用的规则。1. 视图保存查询逻辑不是复制一份数据createviewv_student_scoreasselects.idasstudent_id,s.nameasstudent_name,c.nameascourse_name,sc.scorefromstudent sjoinscore sconsc.student_ids.idjoincourse conc.idsc.course_id;查询方式与普通表类似select*fromv_student_scorewherescore90;复杂连接、聚合、DISTINCT等视图通常不能直接更新使用前要确认可更新性。2. 权限按最小权限分配createuserreport_user%identifiedbyreplace_with_strong_password;grantselectonschool_demo.*toreport_user%;showgrantsforreport_user%;报表账号只需要读权限就不要直接授予ALL PRIVILEGES。实际部署还要限制允许连接的主机并妥善管理密码。3. JDBC参数一定用 PreparedStatementStringsql select id, name, age from student where class_id ? and name like ? ;try(PreparedStatementpsconnection.prepareStatement(sql)){ps.setLong(1,classId);ps.setString(2,keyword%);try(ResultSetrsps.executeQuery()){while(rs.next()){System.out.println(rs.getLong(id));System.out.println(rs.getString(name));}}}不要用字符串拼接用户输入。PreparedStatement不只是写起来规整更重要的是把 SQL 结构和参数值分开降低 SQL 注入风险。